Preface
We usually do query operations , It's all through Model name .objects The way to operate . In fact, the name of the model .objects It's a django.db.models.manager.Manager object , and Manager This class is one “ Empty shell ” Class , He doesn't have any attributes or methods . All his methods are through Python The way of dynamic addition , from QuerySet Class . The example is as follows ：
So if we want to learn ORM The search operation of the model , You have to learn first QuerySet Some of the above API Use
QuerySet 21 A commonly used API
filter
filter： Extract the data that meet the conditions , Back to a new QuerySet. See this article for details ：https://www.cnblogs.com/jiakecong/p/14780601.html
exclude
exclude： Exclude data that meets the criteria , Back to a new QuerySet. The sample code is as follows ：
Article.objects.exclude(title__contains='hello')
The above code means to extract those headings that do not contain hello Of books .
annotate
annotate： to QuerySet Each object in is added with a query expression （ Aggregate functions 、F expression 、Q expression 、Func Expression etc. ） New fields for . The sample code is as follows ：
articles = Article.objects.annotate(author_age=F("author__age"))
The above code will add one... To each object author__age Field of , Used to show the age of the author of this article .
order_by
order_by： Specifies to sort the results of a query according to a field . If you want to sort backwards , Then you can put a negative sign in front of this field . The sample code is as follows ：
# Sort according to the created time sequence
articles = Article.objects.order_by("create_time")
# Sort according to the created time in reverse order
articles = Article.objects.order_by("-create_time")
# Sort by author's name
articles = Article.objects.order_by("author__name")
# First, sort according to the time of creation , If the time is the same , Sort by author's name
articles = Article.objects.order_by("create_time",'author__name')
One thing we must pay attention to is , Multiple order_by, Will disrupt the previous sorting rules , And use the following sort . For example, the following code ：
articles = Article.objects.order_by("create_time").order_by("author__name")
He will sort by the author's name , Instead of using the creation time of the article .
values
values： It's used to specify when extracting data , Which fields need to be extracted . By default, all the fields in the table will be extracted , have access to values To specify , And used values After the method , Extracted QuerySet The data type in is not a model , But in values A dictionary of fields and values specified in the ：
articles = Article.objects.values("title",'content')
for article in articles:
print(article)
Printed above article Is similar to the {"title":"abc","content":"xxx"} In the form of . If in values There are no arguments passed in , Then a dictionary will be returned , The dictionary contains all the attributes in the model .
If we want to extract the properties of the associated objects in this model , So it can be , The sample code is as follows ：
articles = Article.objects.values('title', 'content', 'author__name')
The above will extract author Of name Field , If we don't want the name , Want to customize the name , You can use keyword parameters , The sample code is as follows ：
articles = Articles.objects.values('title', 'content', authorName=F('author__name'))
Be careful ： A custom name cannot be the same as a field on the model itself , such as author__name Change the name to author That would be a mistake , because Article The model itself has a field called author, There will be conflicts
values_list
values_list： Be similar to values. Just returned QuerySet in , It's not a dictionary , It's a tuple . The sample code is as follows ：
articles = Article.objects.values_list("id","title")
print(articles)
So in print articles after , The result is <QuerySet [(1,'abc'),(2,'xxx'),...]> etc. .
If in values_list There is only one field in . So you can deliver flat=True, In this way, the returned result is no longer a tuple , It's the value of the entire field , The sample code is as follows ：
articles2 = Article.objects.values_list("title",flat=True)
So the result returned above is
abc
xxx
all
all： Access to this ORM Model QuerySet object .
select_related
select_related： While extracting data from a model , And extract the associated data in advance . For example, extracting article data , have access to select_related take author Information extraction , Use it again later article.author You don't need to visit the database again . Can reduce the number of database queries . The sample code is as follows ：
article = Article.objects.get(pk=1)
>> article.author # Rerun the query statement
article = Article.objects.select_related("author").get(pk=2)
>> article.author # There is no need to re execute the query statement
Be careful ：selected_related It can only be used in one to many or one to one , It can't be used in many to many or many to one . For example, the author of the article can be obtained in advance , But you can't get the author's article through the author , Or get all the tags of an article through an article .
prefetch_related
prefetch_related： This method and select_related Very similar , When accessing data in multiple tables , Reduce the number of queries . This method is to solve the query problem of many to one and many to many relationships . For example, to get the title with hello String of articles and all his tags , The sample code is as follows ：
from django.db import connection
articles = Article.objects.prefetch_related("tag_set").filter(title__contains='hello')
print(articles.query) # Use this command to see the... At the bottom SQL sentence
for article in articles:
print("title:",article.title)
print(article.tag_set.all())
# You can see the execution of the above code through the following code sql sentence
for sql in connection.queries:
print(sql)
But if you're using article.tag_set When , If you create a new one QuerySet Then it will take the previous SQL Optimization is destroyed . For example, the following code ：
tags = Tag.obejcts.prefetch_related("articles")
for tag in tags:
articles = tag.articles.filter(title__contains='hello') # because filter Method will regenerate a QuerySet, So it will destroy the previous sql Optimize
# Through the following code , We can see that in use filter Of , His sql There will be more queries , Instead of using filter Of , There are only two times sql Inquire about
for sql in connection.queries:
print(sql)
If you really want to specify the filter conditions in the query, what should you do , At this time, we can use django.db.models.Prefetch To achieve ,Prefetch This can be defined in advance queryset. The sample code is as follows ：
tags = Tag.objects.prefetch_related(Prefetch("articles",queryset=Article.objects.filter(title__contains='hello'))).all()
for tag in tags:
articles = tag.articles.all()
for article in articles:
print(article)
for sql in connection.queries:
print('='*30)
print(sql)
Because of the use of Prefetch, Even if you use... When querying articles filter, There will only be two queries
defer
defer： In some tables , There may be many fields , But the amount of data in some fields may be huge , And you don't need to , For example, when we get the list of articles , We don't need the content of the article , So at this point we can use defer To filter out some fields . This field follows values It's kind of similar , It's just defer It's not a dictionary that's returned , It's a model . The sample code is as follows ：
articles = Article.objects.defer("title")
for article in articles:
print('article.id')
defer Although it can filter fields , But some fields can't be filtered , such as id, Even if you filter , It's also extracted .
only
only： Follow defer similar , It's just defer Is to filter out the specified fields , and only Is to extract only the specified fields .
get
get： Get the data that meets the conditions . This function can only return one piece of data , And if the given condition has more than one data , So this method will throw MultipleObjectsReturned error , If the given condition doesn't have any data , Then it will throw DoesNotExit error . So this method is getting data , There can only be and only one .
create
create： Create a piece of data , And save it to the database . This method is equivalent to creating an object with the specified model , And then call the object's save Method . The sample code is as follows ：
article = Article(title='abc')
article.save()
# The following line of code is equivalent to the above two lines of code
article = Article.objects.create(title='abc')
get_or_create
get_or_create： Search according to a certain condition , If it is found, return this data , If you don't find , Then create a . The sample code is as follows ：
obj,created= Category.objects.get_or_create(title=' Default category ')
If there is a category with a title equal to the default category , Then it will find out , without , Will be created and stored in the database . The return value of this method is a tuple , The first parameter of the tuple obj It's this object , The second parameter created Represents whether it was created or not .
bulk_create
bulk_create： Create multiple data at once . The sample code is as follows ：
Tag.objects.bulk_create([
Tag(name='111'),
Tag(name='222'),
])
count
Get the number of extracted data . If you want to know how many pieces of data there are , Then it is recommended to use count, Instead of using len(articles) such . because count At the bottom is the use of select count(*) To achieve , This way is better than using len Functions are more efficient .
first and last
first and last： return QuerySet The first and last data in
aggregate
aggregate： Using aggregate functions . Please refer to this article for details ：https://www.cnblogs.com/jiakecong/p/14784109.html
exists
exists： Determine whether the data of a certain condition exists . If you want to determine whether an element of a condition exists , Then it is recommended to use exists, This is better than using count Or direct judgment QuerySet Much more effective . The sample code is as follows ：
result = Book.objects.filter(name=" The romance of The Three Kingdoms ").exists()
print(result)
distinct
distinct： Remove the duplicate data . This method if the underlying database is MySQL, Then you can't pass any parameters . For example, if you want to withdraw all the sales price more than 80 Yuan book , And delete the repeated , Then you can use distinct To help us achieve , The sample code is as follows ：
books = Book.objects.filter(bookorder__price__gte=80).distinct()
It should be noted that , If in distinct I used order_by, So because order_by Can extract order_by The fields specified in , So use distinct It will be unique based on multiple fields , So we don't delete the duplicate data . The sample code is as follows ：
orders = BookOrder.objects.order_by("create_time").values("book_id").distinct()
So the above code because of the use of order_by, Even if distinct, I will repeat it book_id extracted .
update
update： Perform an update operation , stay SQL The bottom is also update command . For example, to put all category Empty article Of article Fields are updated to the default category . The sample code is as follows ：
Article.objects.filter(category__isnull=True).update(category_id=3)
delete
delete： Delete all data that meets the conditions . When deleting data , it is to be noted that on_delete The specified treatment .
section
Slicing operation ： Sometimes we look up data , It's possible that only a part of it is needed . Then we can use slicing operation to help us complete .QuerySet Slicing is the same as slicing lists . The sample code is as follows ：
books = Book.objects.all()[1:3]
for book in books:
print(book)
Slicing is not to extract all the data from the database and then do slicing . It's used at the database level LIMIE and OFFSET To help us finish . So if you only need to take part of the data , I suggest you use slicing operation .
Django take QuerySet Convert to SQL Statement to execute the five cases
- iteration ： In a traverse
QuerySetWhen the object , I'll do this first SQL sentence , And then return the result for iteration . For example, the following code will be converted to SQL sentence ：
for book in Book.objects.all():
print(book)
- Use step size to slice ：
QuerySetYou can slice like a list . Slicing itself doesn't perform SQL sentence , But if the step size is provided for slicing operation , Then it will be carried out immediatelySQLsentence . It should be noted that , It can't be executed after slicingfilterMethod , Otherwise, an error will be reported . - call len function ： call len Function to get
QuerySetHow many pieces of data in total will be executed SQL sentence . - call list function ： call list Function to put a
QuerySetObject to list The object will be executed immediately SQL sentence . - Judge ： If to some
QuerySetJudge , It will be implemented immediately SQL sentence .