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
QuerySet
When 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 :
QuerySet
You 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 immediatelySQL
sentence . It should be noted that , It can't be executed after slicingfilter
Method , Otherwise, an error will be reported . - call len function : call len Function to get
QuerySet
How many pieces of data in total will be executed SQL sentence . - call list function : call list Function to put a
QuerySet
Object to list The object will be executed immediately SQL sentence . - Judge : If to some
QuerySet
Judge , It will be implemented immediately SQL sentence .