### Caching and Query Sets Each QuerySet contains a cache to reduce the actual operation on the database. Understanding this concept will help you improve query efficiency.

For the newly created QuerySet, its cache is empty. When the QuerySet is first submitted, the database performs the actual query operation. Django stores the results of the query in the QuerySet cache, and subsequent submissions to the QuerySet reuse the cached data.

To make efficient use of query results and reduce database load, you must be good at using caching. Look at the following example. This will result in two actual database operations, doubling the load of the database. At the same time, due to the time difference, data may be deleted or modified or added between the two operations, resulting in dirty data problems:

print([e.headline for e in Entry.objects.all()])
print([e.pub_date for e in Entry.objects.all()])

To avoid the above problems, the good way to use it is as follows, which only produces one actual query operation and keeps the consistency of the data:

queryset = Entry.objects.all()
print([p.headline for p in queryset]) # Submit query
print([p.pub_date for p in queryset]) # Reuse query caching

When will not be cached, there are operations that do not cache QuerySet s, such as slices and indexes. This results in these operations being unavailable, and the actual database query operations are executed each time. For example:

queryset = Entry.objects.all()
print(queryset[5]) # query data base
print(queryset[5]) # Query the database again

However, if you have traversed the entire QuerySet, it is equivalent to caching, and subsequent operations will use caching, for example:

queryset = Entry.objects.all()
[entry for entry in queryset] # query data base
print(queryset[5]) # Using caching
print(queryset[5]) # Using caching

The following actions will traverse the QuerySet and create a cache:

[entry for entry in queryset]
bool(queryset)
entry in queryset
list(queryset)

Note: A simple print QuerySet does not create a cache because the _repr_() call returns only one slice of the entire query set.

Referencing the fields of the model using F expressions

In the examples so far, we have compared model fields with constants. But what if you want to compare one field of the model with another field of the same model?

Use the F expression provided by Django!

For example, in order to find Entry with more comments than pingbacks, an F() object can be constructed to refer to the number of pingbacks and used in the query:

from django.db.models import F
Entry.objects.filter(n_comments__gt=F('n_pingbacks'))

Django supports arithmetic operations such as addition, subtraction, multiplication, division, modularization and power operation on F() objects. The two operands can be constants and other F() objects. For example, look for Entry with more comments than twice the number of pingbacks. We can write as follows:

Entry.objects.filter(n_comments__gt=F('n_pingbacks') * 2)

Complex Query Using Q Objects

The conditions in ordinary filter functions are "and" logic. What if you want to implement "or" logic? Query with Q!

Q comes from django.db.models.Q, which is used to encapsulate the set of keyword parameters and can be used as keyword parameters for functions such as filter, exclude and get. for example

from django.db.models import Q
Q(question__startswith='What')

Q objects can be combined with'&'or'|' or'~'to represent and or illogical, respectively. It will return a new Q object.

Q(question__startswith='Who')|Q(question__startswith='What')
# This is equivalent to:
WHERE question LIKE 'Who%' OR question LIKE 'What%'
Q(question__startswith='Who') | ~Q(pub_date__year=2005)

You can also use this, by default, comma-separated AND relationships

Poll.objects.get(
Q(question__startswith='Who'),
Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6))
)
# It is equivalent to
# SELECT * from polls WHERE question LIKE 'Who%'
AND (pub_date = '2005-05-02' OR pub_date = '2005-05-06')

When keyword parameters are combined with Q objects, Q objects must be placed in front. If keyword parameters are placed in front of Q objects, errors will be reported. For example:

Poll.objects.get(
Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)),question__startswith='Who',)

Duplicate model instances

Although there is no built-in method for replicating an instance of the model, it is easy to create a new instance and copy all fields of the original instance. The simplest way is to set the pk of the original instance to None, which creates a new instance copy. Examples are as follows:

blog = Blog(name='My blog', tagline='Blogging is easy')
blog.save() # blog.pk == 1
#
blog.pk = None
blog.save() # blog.pk == 2

However, when using inheritance, the situation becomes complicated, if there is a subclass of Blog:

class ThemeBlog(Blog):
    theme = models.CharField(max_length=200)

django_blog = ThemeBlog(name='Django', tagline='Django is easy', theme='python')
django_blog.save() # django_blog.pk == 3

Based on the inheritance mechanism, you must set both pk and id to None:

django_blog.pk = None
django_blog.id = None
django_blog.save() # django_blog.pk == 4

For foreign keys and many-to-many relationships, further processing is needed. For example, Entry has a ManyToManyField to Author. After copying entries, you must set up many-to-many relationships for new entries, as follows:

entry = Entry.objects.all()[0] # some previous entry
old_authors = entry.authors.all()
entry.pk = None
entry.save()
entry.authors.set(old_authors)

For OneToOneField, you also copy the related objects and assign them to the fields of the new objects to avoid violating one-to-one unique constraints. For example, suppose entry has repeated as described above:

detail = EntryDetail.objects.all()[0]
detail.pk = None
detail.entry = entry
detail.save

Batch Update Object

Using the update() method, all objects in QuerySet can be updated in batches.

# Update all entry headline s published in 2007
Entry.objects.filter(pub_date__year=2007).update(headline='Everything is the same')

You can use this method only for normal fields and ForeignKey fields. To update a common field, you only need to provide a new constant value. To update the ForeignKey field, you need to set the new value to the new model instance you want to point to. For example:

b = Blog.objects.get(pk=1)
# Modify all Entry so that they belong to b
Entry.objects.all().update(blog=b)

The update method is executed immediately and returns the number of rows to which the operation matches (possibly not equal to the number of rows to be updated, because some rows may already have this new value). The only constraint is that only one database table can be accessed. You can filter by relational fields, but you can only update the fields in the main table of the model. For example:

b = Blog.objects.get(pk=1)
# Update all the headlines belonging to this Blog.
Entry.objects.select_related().filter(blog=b).update(headline='Everything is the same')
#select_related() method queries all the related objects in one time and puts them into the objects. When querying again, it does not need to connect to the database, which saves the number and time of querying the database later.

Note that the update() method converts directly into an SQL statement and executes in batches immediately. The update method can match the F expression. This is especially useful for batch updates of a field in the same model. For example, increase the number of pingback s per Entry in a Blog.

Entry.objects.all().update(n_pingbacks=F('n_pingbacks') + 1)

However, unlike F() objects in filter and exclude clauses, you can't use F() objects for cross-table operations in update. You can only refer to the fields of the model being updated. If you try to use F() objects to introduce fields of another table, you will throw a FieldError exception.

Objects of Relations

from django.db import models

class Blog(models.Model):
    name = models.CharField(max_length=100)
    tagline = models.TextField()

    def __str__(self):              # __unicode__ on Python 2
        return self.name

class Author(models.Model):
    name = models.CharField(max_length=200)
    email = models.EmailField()

    def __str__(self):              # __unicode__ on Python 2
        return self.name

class Entry(models.Model):
    blog = models.ForeignKey(Blog, on_delete=models.CASCADE)
    headline = models.CharField(max_length=255)
    body_text = models.TextField()
    pub_date = models.DateField()
    mod_date = models.DateField()
    authors = models.ManyToManyField(Author)
    n_comments = models.IntegerField()
    n_pingbacks = models.IntegerField()
    rating = models.IntegerField()

    def __str__(self):              # __unicode__ on Python 2
        return self.headline

One-to-many (foreign key)

 e = Entry.objects.get(id=2)
 e.blog # Returns the associated Blog object
 #It should be noted that changes to keys must be saved by calling save methods, such as:
e = Entry.objects.get(id=2)
e.blog = some_blog
e.save()
#If a foreign key field has a null=True attribute, the foreign key value can be removed by assigning the field a value of None:
e = Entry.objects.get(id=2)
e.blog = None
e.save() # "UPDATE blog_entry SET blog_id = NULL ...;"
#The relational object is cached the first time a foreign key relationship is accessed forward. This cache is then used for subsequent access to the same foreign key relational object, such as:
e = Entry.objects.get(id=2)
print(e.blog)  # Access the database to get the actual data
print(e.blog)  # Do not access the database, use the cached version directly

Note that the select_related() method of QuerySet recursively prepopulates all one-to-many relationships in the cache. For example:

e = Entry.objects.select_related().get(id=2)
print(e.blog)  # Do not access the database, use caching directly
print(e.blog)  # Do not access the database, use caching directly

• Reverse query: If a model has ForeignKey, the instance of the foreign key model that ForeignKey refers to can be retrieved through a manager to return all instances of the source model. By default, the name of this manager is FOO_set, where FOO is the lowercase name of the source model. The query set returned by the manager can be filtered and manipulated in the way mentioned above.

b = Blog.objects.get(id=1)
b.entry_set.all() # Returns all Entry objects related to Blog.
# b.entry_set is a Manager that returns QuerySets.
b.entry_set.filter(headline__contains='Lennon')
b.entry_set.count()

You can override the name of FOO_set by setting related_name in the definition of the ForeignKey field. For example, if you modify the Entry model blog = ForeignKey(Blog, on_delete=models.CASCADE, related_name='entries'), the above example will look like the following:

b = Blog.objects.get(id=1)
b.entries.all() # Returns all Entry objects related to Blog.
# b.entries is a Manager that returns QuerySets.
b.entries.filter(headline__contains='Lennon')
b.entries.count()

• Use a custom reverse manager: By default, the ReitedManager for reverse association is a subclass of the default manager for the model. If you want to specify a different manager for a query, you can use the following syntax:

from django.db import models
class Entry(models.Model):
    #...
    objects = models.Manager()  # Default Manager
    entries = EntryManager()    # Custom Manager
b = Blog.objects.get(id=1)
b.entry_set(manager='entries').all()

• Other methods of dealing with associated objects: In addition to the QuerySet method defined earlier, the ForeignKey manager has other methods for dealing with the associated collection of objects. The following is a summary of each method. add(obj1, obj2,...): Add the specified model object to the associated object set. create(**kwargs): Create a new object, save it and place it in the associated object set. Returns the newly created object. remove(obj1, obj2,...): Delete the specified model object from the associated object set. clear(): Clear the associated object set. set(objs): Resets the associated set of objects. To assign a value to an associated set of objects at once, use the set() method and assign it an iterative set of objects or a list of primary key values. For example:

b = Blog.objects.get(id=1)
b.entry_set.set([e1, e2])

Each reverse operation in this section will be performed immediately in the database. All add, create, and delete operations will also be automatically saved to the database immediately.

Many to many

Both ends of the many-to-many relationship automatically get APIs to access the other end. These APIs work in the same way as the "reverse" one-to-many relationship mentioned earlier.

The only difference is the name of the attribute: the model defining ManyToManyField uses the attribute name of the field, while the "reverse" model uses the lowercase name of the source model plus'_set'(as in a one-to-many relationship).

e = Entry.objects.get(id=3)
e.authors.all() # Returns all Author objects for this Entry.
e.authors.count()
e.authors.filter(name__contains='John')
#
a = Author.objects.get(id=5)
a.entry_set.all() # Returns all Entry objects for this Author

As in foreign key fields, you can also specify the related_name name name in many-to-many fields.

(Note: In a model, if there are multiple foreign keys or many-to-many relationships pointing to the same external model, they must be added different related_name s for reverse query.)

class Person(models.Model);
    name = models.CharField(verbose_name='Author name', max_length=10)
    age = models.IntegerField(verbose_name='Author age')


class Book(models.Model):
    person = models.ForeignKey(Person, related_name='person_book')
    title = models.CharField(verbose_name='Titles of books', max_length=10)
    pubtime = models.DateField(verbose_name='Publishing time')
	
	
person.person_book.all()

One-on-one

One-to-one is very similar to many-to-one relationship, and the associated model can be accessed simply through the attributes of the model.

class EntryDetail(models.Model):
    entry = models.OneToOneField(Entry, on_delete=models.CASCADE)
    details = models.TextField()

ed = EntryDetail.objects.get(id=2)
ed.entry # Returns the related Entry object.

The difference lies in the reverse query. The association model in a one-to-one relationship also has a manager object, but the manager represents a single object rather than a collection of objects:

e = Entry.objects.get(id=2)
e.entrydetail # Returns the associated EntryDetail object

Original sql

Person.objects.raw('SELECT id, first_name, last_name, birth_date FROM myapp_person')
Person.objects.raw('SELECT last_name, birth_date, first_name, id FROM myapp_person')
#You can map the fields in the query to the model field raw() using the translations parameter to. This is a dictionary that maps the name of the field in the query to the name of the field on the model. For example, the above query can also be written as:
name_map = {'first': 'first_name', 'last': 'last_name', 'bd': 'birth_date', 'pk': 'id'}
Person.objects.raw('SELECT * FROM some_other_table', translations=name_map)
Person.objects.raw('''SELECT first AS first_name,
                             last AS last_name,
                             bd AS birth_date,
                             pk AS id,
                      FROM some_other_table''')

API that does not return QuerySets

The following methods do not return QuerySets, but they are very powerful, especially for bold display methods, which need to be memorized.

Method Name Explanation
 get() Gets a single object
 create() Create objects without saving ()
get_or_create() Queries for objects and creates new objects if they are not found
 update_or_create() Updates the object and creates it if it is not found
 bulk_create() batch creation of objects
 count() Number of statistical objects
 in_bulk() Batch returns objects based on a list of primary key values
 iterator() Gets an iterator containing objects
 latest() Gets the nearest object
 earliest() Gets the earliest object
 first() Gets the first object
 last() Gets the last object
 aggregate() aggregate operation
 exists() Determines whether there are objects in queryset
 update() batch update object
 delete() bulk deletion of objects
 as_manager() Get the manager

get()

get(**kwargs)

Returns a single object that matches the query parameters in a format that meets the requirements of Field lookups.

Trigger the MultipleObjects Return exception if there is more than one matched object

If the object cannot be matched according to the given parameters, the DoesNotExist exception is triggered. For example:

Entry.objects.get(id='foo') # raises Entry.DoesNotExist
from django.core.exceptions import ObjectDoesNotExist
try:
    e = Entry.objects.get(id=3)
    b = Blog.objects.get(id=1)
except ObjectDoesNotExist:
    print("Either the entry or blog doesn't exist.")

If you want the query to return only one row, you can use get() instead of any parameter to return the object of that row:

entry = Entry.objects.filter(...).exclude(...).get()

create()

p = Person.objects.create(first_name="Bruce", last_name="Springsteen")
#Equivalent to
p = Person(first_name="Bruce", last_name="Springsteen")
p.save(force_insert=True)

get_or_create()

get_or_create(defaults=None, **kwargs)

A convenient way to query an object through kwargs (if all fields in the model have default values, they can be null), and if the object does not exist, create a new object.

This method returns a tuple consisting of (object, created). The object in the tuple is a queried or created object. Created is a Boolean value indicating whether a new object has been created.

For the following code:

try:
    obj = Person.objects.get(first_name='John', last_name='Lennon')
except Person.DoesNotExist:
    obj = Person(first_name='John', last_name='Lennon', birthday=date(1940, 10, 9))
    obj.save()

If the number of fields in the model is large, this pattern becomes very difficult to use. The above example can be rewritten with get_or_create():

obj, created = Person.objects.get_or_create(
    first_name='John',
    last_name='Lennon',
    defaults={'birthday': date(1940, 10, 9)},
)

Any keyword parameter passed to get_or_create(), except for an optional defaults, is passed to get() call. If an object is found, a tuple containing the matched object and False is returned. If more than one object is found, MultipleObjects Return is triggered. If no object is found, get_or_create() instantiates and saves a new object, returning a tuple of new objects and True. The new object will be created according to the following logic:

update_or_create()

update_or_create(defaults=None, **kwargs)

Similar to the previous get_or_create().

A convenient way to update an object by giving kwargs is to create a new object if no object is found. Defaults is a dictionary consisting of (field, value) pairs for updating objects. The values in defaults can be callable objects (that is, functions, etc.).

This method returns a tuple consisting of (object, created). The object in the tuple is a created or updated object. Created is a Boolean value indicating whether a new object has been created.

The update_or_create method attempts to retrieve matching objects from the database by giving kwargs. If a matching object is found, it updates the field based on the values given by the defaults dictionary.

defaults = {'first_name': 'Bob'}
try:
    obj = Person.objects.get(first_name='John', last_name='Lennon')
    for key, value in defaults.items():
        setattr(obj, key, value)
    obj.save()
except Person.DoesNotExist:
    new_values = {'first_name': 'John', 'last_name': 'Lennon'}
    new_values.update(defaults)
    obj = Person(**new_values)
    obj.save()

If the number of fields in the model is large, this pattern becomes very difficult to use. The above example can be overridden with update_or_create():

obj, created = Person.objects.update_or_create(
    first_name='John', last_name='Lennon',
    defaults={'first_name': 'Bob'},
)

count()

count()

Returns the number of corresponding QuerySet objects in the database. count() never raises an exception.

# Returns the total number.
Entry.objects.count()
# Returns the total number of objects containing'Lennon'
Entry.objects.filter(headline__contains='Lennon').count()

aggregate()

aggregate(args, *kwargs)

Returns a dictionary of summary values (average, sum, etc.) and calculates it through QuerySet. Each parameter specifies the value to be included in the returned dictionary.

Aggregation specified with keyword parameters uses the name of keyword parameters as the name of Annotation. The name of the anonymous parameter is generated based on the name of the aggregation function and the model field. Anonymous parameters cannot be used in complex aggregation. A keyword parameter must be specified as an alias.

For example, you want to know the number of Blog Entry:

from django.db.models import Count
q = Blog.objects.aggregate(Count('entry'))
{'entry__count': 16}

exists()

exists()

If QuerySet contains any results, return True or False.

The most efficient way to find out whether a model with unique fields (such as primary_key) is in a QuerySet is to:

entry = Entry.objects.get(pk=123)
if some_queryset.filter(pk=entry.pk).exists():
    print("Entry contained in queryset")

It will be much faster than the following method, which requires evaluating the QuerySet and iterating over the entire QuerySet:

if entry in some_queryset:
   print("Entry contained in QuerySet")

To find out if a QuerySet contains any elements:

if some_queryset.exists():
    print("There is at least one object in some_queryset")

Will be faster than:

if some_queryset:
    print("There is at least one object in some_queryset")

update()

update(**kwargs)

Perform batch update operations on specified fields and return the number of matching rows (which may not be equal to the number of updated rows if some rows already have new values).

Entry.objects.filter(pub_date__year=2010).update(comments_on=False)

Multiple fields can be updated at the same time (with few field restrictions). For example, update comments_on and headline fields at the same time:

Entry.objects.filter(pub_date__year=2010).update(comments_on=False, headline='This is old')

update() method does not require save operation. The only limitation is that it can only update the columns in the main table of the model, not the associated model, for example, it can't do this:

Entry.objects.update(blog__name='foo') # Won't work!

It can still be filtered according to the relevant fields:

Entry.objects.filter(blog__id=1).update(comments_on=True)

The update() method returns the number of rows affected:

Entry.objects.filter(id=64).update(comments_on=True)
1
Entry.objects.filter(slug='nonexistent-slug').update(comments_on=True)
0
Entry.objects.filter(pub_date__year=2010).update(comments_on=False)
132

If you just update the object and don't need to do anything else for it, the most effective way is to call update(), instead of loading the model object into memory. For example, do not do this:

e = Entry.objects.get(id=10)
e.comments_on = False
e.save()

The following actions are recommended:

Entry.objects.filter(id=10).update(comments_on=False)

Using update() also prevents competition conditions that may change some content in the database within a short period of time between loading the object and calling save().

If you want to update the records of a model with a custom save() method, iterate through them and call save(), as follows

for e in Entry.objects.filter(pub_date__year=2010):
    e.comments_on = False
    e.save()

delete()

delete()

Batch deletion of all objects in QuerySet and return a dictionary of the number of objects deleted and the number of deletions per object type.

delete() action is executed immediately.

delete() cannot be called on QuerySet.

For example, delete all entries in a particular blog:

b = Blog.objects.get(pk=1)
# Delete all the entries belonging to this Blog.
Entry.objects.filter(blog=b).delete()
(4, {'weblog.Entry': 2, 'weblog.Entry_authors': 2})

By default, Django's ForeignKey uses the SQL constraint ON DELETE CASCADE, and any objects with foreign keys pointing to objects to be deleted will be deleted along with them. Like this:

blogs = Blog.objects.all()
# This will delete all Blogs and all of their Entry objects.
blogs.delete()
(5, {'weblog.Blog': 1, 'weblog.Entry': 2, 'weblog.Entry_authors': 2})