Django(19)QuerySet API
Silent, black feather 2021-06-04 10:35:58

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: 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: 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

  1. 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)
  1. 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 immediately SQL sentence . It should be noted that , It can't be executed after slicing filter Method , Otherwise, an error will be reported .
  2. call len function : call len Function to get QuerySet How many pieces of data in total will be executed SQL sentence .
  3. call list function : call list Function to put a QuerySet Object to list The object will be executed immediately SQL sentence .
  4. Judge : If to some QuerySet Judge , It will be implemented immediately SQL sentence .
Please bring the original link to reprint ,thank
Similar articles

2021-08-09

2021-08-09