Database query optimization

orm statement features: lazy query

If it's just writing orm Statement. If the parameters queried by the statement are not used later, then orm It will be recognized automatically and will not be executed

give an example:

res = models.Book.objects.all()
# Executing this statement alone, Django will not use the database and reduce the pressure on the database

res = models.Book.objects.all()
print(res)
# Only when the data is used will the database be used

Next, try to get the names of all the books in the data table.

res = models.Book.objects.values('name')
print(res)  # Get the form and object of the list set dictionary
for i in res:
  print(i.get('name')) # Get the book name through the for circular dictionary value

So how to get a data object, and then you can get the book title through the title without other fields.

only and defer

only

res1 = models.Book.objects.only('name') # The object has only the name attribute
for b in res1:
    print(b.name) # If (.) only some fields in parentheses do not go to the database
    	print(b.title) # If (.) only fields not in parentheses need to go to the database repeatedly to query data and return one by one, while all() does not need to

Conclusion: all() gets all the data objects, while only() only gets the data objects specified in brackets.

defer

res = models.Book.objects.defer('name')  # The object has no name attribute except for others
for i in res:
    print(i.price)

Conclusion: defer is just the opposite of only. The field in defer brackets is no longer in the queried object. Querying this field needs to go to the database again. If the fields queried are not in parentheses, the database will not be used.

select_related and prefetch_related: related to cross table operation

select_related

Example: check the publisher name of each book

res = models.Book;.objects.all()
for i in res:
  print(i.publish.name)
# When using the all method to query, each object will query the data in the database

res = models.Book.objects.select_related()
for i in res:
  print(i.publish.name) # The database was accessed only once, and the internal join operation was used

Summary: select_related directly connects book and publish, and then encapsulates all the data in the large table to the queried object at one time. select_ Only foreign key fields can be placed in related brackets, and they are one to many, one to one.

prefetch_related

# Related to cross table operation

res = models.Book.objects.prefetch_related('publish')  # Subquery
for i in res:
    print(i.publish.name)
    
"""
    prefetch_related This method is actually a sub query
        Encapsulate all the results of the sub query into objects
        It feels like it's done at one time
"""

Conclusion: prefetch_related this method is actually a sub query, which encapsulates all the results of the sub query into objects.

Summary: select_related and prefetch_related，select_related is a table join operation, prefetch_related is a sub query, which has advantages and disadvantages. If the table is large, select is used_ Connected tables take a long time, while select_ Although the related sub query is queried twice, the operation time of two tables is very short, and the efficiency will be better than that of the associated table query prefetch_related

choices parameter (common in database field design)

All scenarios can be summarized for information sources. For example: gender, education, work experience, information source How should this be stored?

As long as the possibility of a field can be enumerated completely, the choices parameter is generally used

The data type created by the field is based on the type of an ancestor element in the field you want to specify. The gender field is still a number, but if the stored number is within the range listed by Yuanzu above, the real content corresponding to the number can be obtained.

give an example:

# If the number stored in the gender field is no longer in the range listed by the above Yuanzu
# If yes, how to get the corresponding Chinese information?

Create table:

class Client(models.Model):
    username = models.CharField(max_length=32)
    age = models.IntegerField()
    # Gender
    gender_choices = {
        (1, 'male'),
        (2, 'female'),
        (3, 'other'),
    }
    """
    The data type created by the field is based on the type of an ancestor element in the field you want to specify.
    Should gender The field is still a number, but if the number is within the range listed by Yuanzu above
    Then you can get the real content corresponding to the number
    
    """
    gender = models.IntegerField(choices=gender_choices)

Create data, tests py:

from django.test import TestCase

# Create your tests here.
import os
import sys

if __name__ == "__main__":
    os.environ.setdefault("DJANGO_SETTINGS_MODULE", "day06.settings")
    import django
    django.setup()

    from app01 import models
    models.Client.objects.create(username='junjie',age=18,gender=1)
    models.Client.objects.create(username='nn',age=1,gender=2)
    models.Client.objects.create(username='jason',age=58,gender=3)
    models.Client.objects.create(username='tom',age=99,gender=4)

It can be seen that there is no problem in creating data, and numbers that are not created can be saved.

So what?

# take
user_obj = models.Client.objects.filter(pk=1).first()
print(user_obj.gender)
# Output: 1

At this time, the output should not be the real gender corresponding to pk=1. How to obtain the data of 1?

As long as it is the field of the choices parameter, if you want to obtain the corresponding information, the fixed writing method is get_ Field name_ display()

user_obj = models.Client.objects.filter(pk=1).first()
print(user_obj.get_gender_display())
# Output: Male

Then there is another user name named tom, which has no fixed relationship. What will he return?

user_obj = models.Client.objects.filter(pk=4).first()
print(user_obj.get_gender_display())
# Output: 4

If there is no corresponding relationship, what is the field or what is displayed.

The same is true for string format:

Create field first:

score_choices = (
    ('A','excellent'),
    ('B','pass'),
    ('C','fail,'),
)
# To add a new field to the created table, you need to set null=True to indicate that the field is empty, or you can set the default value
score = models.CharField(max_length=32,choices=score_choices,null=True)

Add data:

models.Client.objects.filter(pk=1).update(score='A')

Get data:

res = models.Client.objects.filter(pk=1).first()
print(res.get_score_display())
# Output: excellent

MVC and MTV models

• MTV: Django is known as the MTV model, and its essence is also the MVC model
  M: models
  T: templates
  V: views

• MVC
  M: models
  V: views
  C: Controller

There are three creation methods: many to many

• fully automatic
  • Advantages: the code does not need to be written, which is convenient. It supports orm and provides a method to operate the third relational table
  • Disadvantages: the scalability of the third relational table is very poor (there is no way to add additional fields)
• Pure manual
  • Advantage: the third table is entirely up to you with additional extensions
  • Disadvantages: you need to write more code. You can't use the simple methods provided by orm, forward and reverse queries and so on
• semi-automatic

fully automatic

Using orm to create the third relation table automatically

class Book(models.Model):
    name = models.CharField(max_length=32)
    authors = models.ManyToManyField('Author')


class Author(models.Model):
    name = models.CharField(max_length=32)

Pure manual

class Book(models.Model):
    name = models.CharField(max_length=32)
    


class Author(models.Model):
    name = models.CharField(max_length=32)
    
    

class Book2Author(models.Model):
    authors_id = models.ForeignKey(to='Author')
    books_id = models.ForeignKey(to='Book')

semi-automatic

through_fields field order, the essence of judgment: query the corresponding table through the third table. If you need to use that field, which field is in front of the bar, and who the current table is, put the corresponding associated word in front.

You can use the reverse method, but you can't use the reverse method

class Book(models.Model):
    name = models.CharField(max_length=32)
    authors = models.ManyToManyField(to='Author',
                                     # Declare the specified table as the third relationship table
                                     through='Book2Author',
                                     # The fields declared in the third relationship table represent relationships
                                     through_fields=('Book','Author')
                                     )


class Author(models.Model):
    name = models.CharField(max_length=32)



class Book2Author(models.Model):
    authors = models.ForeignKey(to='Author')
    books = models.ForeignKey(to='Book')
    ...

Summary: fully automatic and semi-automatic need to be mastered. In order to have higher expansibility, semi-automatic is generally adopted