Django model layer

Time:2021-6-10

Django model layer

1、 ORM operation (16 must know and must know)

1. Create () # add data and return the value to the queryset object
2. All () # query all data and return the value to the queryset object
3. Filter () # query the data conditionally and return the value to the queryset object
4. Update () # update data, return the number of affected rows
5. Delete () # delete data. The returned value is the number of tables and rows affected
6. First () # take the first element and return the value to the data object
7. Last () # takes the last element and returns the value to the data object
8. Get () # value. The return value directly gets the object itself. It is not recommended. When the query condition does not exist, an error will be reported directly
9. Value () # value, return value to queryset object [{}, {}, {}, {}, {}, {},...] list set dictionary
10. value_ List () # value, return the value to the query set object [(), (), (), (),...] list tuple
11. order_ Sort by () # and return the value to the queryset object
12. Count () # count, the return value is the number of data
13. Exclude () # returns the value of the queryset object except what is excluded
14. Exists () # determines whether it exists and returns a Boolean value
15. Reverse () # reverses, but not reverses, and returns the value to the queryset object
The as like as two peas, 16. distinct (), the key to the data is to be exactly the same (including the primary key ID) and return the Boolean value.

app01_ The data in the movie data table (ORM) is shown in app01_ Take movie table data as an example:

Django model layerDjango model layer

class Movie(models.Model):
    name = models.CharField(max_length=32)
    price = models.DecimalField(max_digits=8, decimal_places=2)
    publish_time = models.DateField()
    '''
    auto_ Now: every time the data is modified, the latest update will be recorded automatically
    auto_ now_ Add: the creation time will be recorded automatically only when creating data, and will not be changed automatically afterwards
    '''
    # publish_time = models.DateTimeField()

    def __str__(self):
        return self.name

Movie table creation

 

1. Viewing native SQL statements through ORM operation

① As long as the returned result is a queryset object, you can directly. Query to view the SQL statements inside the current queryset object

#Example: view all the data in the movie table from app01 import models


data = models.Movie.objects.all()
print(data.query)

Print results:

SELECT `app01_movie`.`id`, `app01_movie`.`name`, `app01_movie`.`price`, `app01_movie`.`publish_time` FROM `app01_movie`

 

②   Check all SQL statements in ORM operation, Django terminal will automatically print SQL statements (fixed log file configuration, copy to settings.py configuration file)

Django model layerDjango model layer

LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'handlers': {
        'console': {
            'level': 'DEBUG',
            'class': 'logging.StreamHandler',
        },
    },
    'loggers': {
        'django.db.backends': {
            'handlers': ['console'],
            'propagate': True,
            'level': 'DEBUG',
        },
    }
}

SQL statement log configuration code

#Example: view all the data in the movie table
from app01 import models


data = models.Movie.objects.all()
print(data)

 

2、create()

Add data and return the value to the queryset object

Models. Movie. Objects. Create (name ='sheriff black cat ', price = 1451.14, publish)_ The first way to add date data
from datetime import datetime
ntime = datetime.today()
Models. Movie. Objects. Create (name ='journey to the West ', price = 1000.11, publish)_ Time = ntime) # the second way to add a date is to directly transfer the date object

 

3、all()

Query all the data and return the value to the queryset object

data = models.Movie.objects.all()
print(data)

 

4、filter()

Where condition queries data and returns the value to the queryset object. Multiple conditions can be placed in brackets. The relationship is and

#Data = models. Movie. Objects. Filter (name ='journey to the West ') [0]
# data = models.Movie.objects.filter(id=1)[0]
Data = models. Movie. Objects. Filter (PK = 1) [0] # PK refers to the primary key name of the current field
print(data)
print(data.name)

 

5、update()

Update the data and return the number of rows affected

Data = models. Movie. Objects. Filter (PK = 2). Update (name ='Four apprentices', price ='1547.21 ')
print(data)

 

6、delete()

Delete data and return the number of tables and rows affected

data = models.Movie.objects.filter(pk=1).delete()
print(data)

 

7、first()

Take the first element and return the value to the data object

data = models.Movie.objects.filter().first()
print(data)

 

8、last()

Take the last element and return the value to the data object

data = models.Movie.objects.filter().last()
print(data)

 

9、get()

Value, return value directly get the object itself, not recommended, when the query condition does not exist, direct error

Data = models. Movie. Objects. Get (PK = 2) # PK = 2
print(data)

Data = models. Movie. Objects. Get (PK = 1) # PK = 1 does not exist
print(data)

 

10、values()

Value, return the value to the queryset object   [{}, {}, {}, {}, …] List set dictionary

#Get the data of the specified field in the form of dictionary
data = models.Movie.objects.values('name', 'publish_time')
print(data)

 

11、values_list()

Value, return the value to the queryset object   [(), (), (), (), …] List nested tuple

#Value in the form of tuples to obtain the data of the specified field
data = models.Movie.objects.values_list('name', 'publish_time')
print(data)

 

12、order_by()

Sort and return the value to the queryset object

data = models.Movie.objects.order_ By ('price ') ා the default is ascending
data2 = models.Movie.objects.order_ By ('- price'), plus - is the reverse order
print(data)
print(data2)

 

13、count()

Count, the return value is the number of data

data = models.Movie.objects.count()
print(data)

 

14、exclude()

Excluding what, return the value to the queryset object

data = models.Movie.objects.exclude(pk=2)
print(data)

 

15、exists()

To determine whether it exists, Boolean value is returned

Data = models. Movie. Objects. Filter (PK = 2). Exists () # PK = 2 exists
print(data)

Data = models. Movie. Objects. Filter (PK = 1). Exists () # PK = 1 does not exist
print(data)

 

16、reverse()

Reverses, but not reverses, the value returned to the queryset object

data = models.Movie.objects.order_ By ('price ') ා the default is ascending
data2 = models.Movie.objects.order_ By ('price '). Reverse () #, but not in reverse order
print(data)
print(data2)

 

17、distinct()

The premise of de duplication isThe data are as like as two peas (including the primary key ID)., returns a Boolean value

Data = models. Movie. Objects. All (). Distinct () ා cannot de duplicate because the primary key ID is different
print(data)
Models. Movie. Objects. Create (name ='sheriff black cat ', price ='1000.11', publish)_ Add a piece of data with the same name and price fields (different ID)
Print (models. Movie. Objects. Filter (name ='sheriff black cat ') # view Sheriff black cat's data
Data2 = models. Movie. Objects. Values ('name ','price'). Distinct() # can selectively de duplicate, and the result does not include the primary key ID
print(data2)

 

 

2、 Double underline query (single table query)

1. Greater than, less than, greater than or equal to, less than or equal to

#Query movies with price more than 500
data = models.Movie.objects.filter(price__gt=500)
print(data)

#Query movies with price less than 500
data = models.Movie.objects.filter(price__lt=500)
print(data)

#Query movies with movie price greater than or equal to 500
data = models.Movie.objects.filter(price__gte=500)
print(data)

#Query movies whose price is less than or equal to 500
data = models.Movie.objects.filter(price__gte=500)
print(data)

 

2. And relationship (what and what)

#Find movies with movie prices of 100, 200 and 300
data = models.Movie.objects.filter(price__in=[100, 200, 300])
print(data)

 

3. In what range

#Query the movie price between 200 ~ 500
data = models.Movie.objects.filter(price__range=(200, 500))
print(data)

 

4. What characters are included in the data

#Search for movies with the letter X in the movie name
# data = models.Movie.objects.filter(name__ By default, contains is case sensitive
data = models.Movie.objects.filter(name__ Icontains ='x ') # icontains are case insensitive
print(data)

 

5. Query the data corresponding to year, month and day in the time field

#Inquire about Films published in 2020
data = models.Movie.objects.filter(publish_time__year='2020')
print(data)

#Search for Films published in December (regardless of date)
data = models.Movie.objects.filter(publish_time__month='12')
print(data)

#Inquire about the film published on the 8th (regardless of the year and year)
data = models.Movie.objects.filter(publish_time__day='8')
print(data)

 

 

3、 Multi table query

1. Library management system table creation and manual data entry

Django model layerDjango model layer

class Book(models.Model):
    name = models.CharField(max_length=32)
    price = models.DecimalField(max_digits=8, decimal_places=2)
    publish_ time = models.DateField(auto_ now_ Add = true) # the newly added data of this field is automatically added, without considering

    #Press one to many foreign key fields on the side of many
    publish = models.ForeignKey(to='Publish')
    #The author's many to many foreign key fields can be built at any end, and it's better to build on the side with high query frequency
    author = models.ManyToManyField(to='Author')

    #Inventory
    kucun = models.BigIntegerField(default=20000)

    #Sales
    sale = models.BigIntegerField(default=20000)

    def __str__(self):
        return self.name


class Publish(models.Model):
    name = models.CharField(max_length=32)
    addr = models.CharField(max_length=64)

    def __str__(self):
        return self.name


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

    #The one-to-one foreign key field of author details can be set up on either side, and it is better to set up on the side with high query frequency
    author_detail = models.OneToOneField(to='AuthorDetail')

    def __str__(self):
        return self.name


class AuthorDetail(models.Model):
    phone = models.BigIntegerField()
    addr = models.CharField(max_length=64)

Establishment of table relation in library management system

Table relation model of library management system

 

2. Adding, deleting, modifying and querying foreign key fields

  PS: in version 1. X, the default foreign key field is cascade update and cascade delete; In version 2. X, you need to specify it manually and add the attribute: on to ForeignKey_ delete=models.SET_ Null

1) One to many

Add create ()

#First, write the actual table field publish directly_ Id = number
Models. Book. Objects. Create (name ='journey to the West ', price ='200.99', publish)_ id=2)
#The second is to add through virtual fields
publish_obj = models.Publish.objects.get(pk=1)
Models. Book. Objects. Create (name ='dream of Red Mansions', price ='180.50 ', publish = publish)_ obj)

Update ()

#The first is to directly change the actual table field publish_ Id = number
models.Book.objects.filter(pk=1).update(publish_id=3)
#The second is to modify through virtual field
publish_obj = models.Publish.objects.get(pk=3)
models.Book.objects.filter(pk=1).update(publish=publish_obj)

 

2) Many to many

Add () to add data to the third relational table. Both numbers and objects can be transferred in brackets, and multiple data can be transferred

#The first one is digital transmission
book_obj = models.Book.objects.filter(pk=1).first()
book_obj.author.add(1)
#The second is the target of transmission
book_obj = models.Book.objects.filter(pk=1).first()
author_obj1 = models.Author.objects.get(pk=2)
author_obj2 = models.Author.objects.get(pk=3)
book_obj.author.add(author_obj1, author_obj2)

Remove ()

#The first one is digital transmission
book_obj = models.Book.objects.filter(pk=1).first()
book_obj.author.remove(1, 2)
#The second is the target of transmission
book_obj = models.Book.objects.filter(pk=1).first()
author_obj = models.Author.objects.get(pk=3)

Change set () to support transferring numbers and objects in brackets, but it needs to be an iterative object

#The first one is digital transmission
book_obj = models.Book.objects.filter(pk=2).first()
book_obj.author.set([1, 2])
#The second is the target of transmission
book_obj = models.Book.objects.filter(pk=2).first()
author_obj = models.Author.objects.get(pk=3)
book_obj.author.set((author_obj, ))

Clear () clears the object data in the relation table in Chapter 3 without any parameters

book_obj = models.Book.objects.filter(pk=2).first()
book_obj.author.clear()

 

 

4、 Cross table query of multi table query

Cross table query mode:

1. Subquery

Take the query result of one table as the query condition of another table. The normal way to solve the problem is to operate step by step

2. Joint table query

    inner join

    left join

    right join

    union

 

1. The concept of forward and backward query

Definition: when cross table query, whether the foreign key field is in the current data object. If so, query another relational table, which is called forward query; If not, query another table, called reverse query

Pithy formula: forward query by foreign key field, reverse query by lowercase table name_ set

 

2. Object based cross table query (subquery)

Example:

①   Query the name and address of the publishing house whose book PK is 1

book_obj = models.Book.objects.filter(pk=1).first()
print(book_obj.publish.name)
print(book_obj.publish.addr)

②   Query the names of all authors whose PK is 2

book_obj = models.Book.objects.filter(pk=2).first()
print(book_ Obj. Author. All ()) # get all the author objects whose primary key value is 2
for author_obj in book_obj.author.all():
    print(author_obj.name)

③   Query the phone number of author PK = 1

author_obj = models.Author.objects.filter(pk=2).first()
print(author_obj.author_detail.phone)

PS: ① ~ ③ are forward queries. When there are multiple data corresponding to the foreign key field, you need to add. All (), otherwise, you can get the corresponding data object by clicking the foreign key field

 

④   Search for books published by Oriental Publishing House

publish_ Obj = models. Publish. Objects. Filter (name ='oriental Publishing '). First ()
print(publish_ obj.book_ Set. All ()) # get the objects of all the books published by Oriental publishing house, and use the for loop to get the corresponding titles

⑤   Search for books by Tom

author_obj = models.Author.objects.filter(name='tom').first()
print(author_ obj.book_ Set. All ()) # get the objects of all the books published by Oriental publishing house, and use the for loop to get the corresponding titles

⑥   Query the author’s name with mobile number 110

author_detail_obj = models.AuthorDetail.objects.filter(phone='110').first().author.name
print(author_detail_obj)

PS: ④ ~ ⑥ is a reverse query to see if the table name is lowercase_ Set. All (), one to many and many to many need to be added, one to one need not

 

3. Cross table query based on double underscores

Example:

①   Query the name of the publishing house whose book PK is 1

#Positive
book = models.Book.objects.filter(pk=1).values('publish__ If you write a foreign key field, it means that you are already in the table managed by the foreign key field
print(book)
#Reverse
book = models.Publish.objects.filter(book__ PK = 1). First (). Name # take the publishing house corresponding to the book with PK = 1
print(book)

②   Query the name and age of the author whose PK is 1

#Positive
data = models.Book.objects.filter(pk=1).values('author__name', 'author__age')
print(data)
#Reverse
data = models.Author.objects.filter(book__pk=1).values('name', 'age')
print(data)

③   Query the age and mobile phone number of the author Jack

#Positive
data = models.Author.objects.filter(name='jack').values('age', 'author_detail__phone')
print(data)
#Reverse
data = models.AuthorDetail.objects.filter(author__name='jack').values('author__age', 'phone')
print(data)

④   Query the mobile phone number of the author whose PK is 1

#Positive
data = models.Book.objects.filter(pk=1).values('author__author_detail__phone')
print(data)
#Reverse
data = models.AuthorDetail.objects.filter(author__book__pk=1).values('phone')
print(data)

PS: as long as there is a relationship in the table, continuous cross table operations can be performed through forward foreign key fields or reverse lowercase table names

 

 

5、 Aggregate function

Find the maximum, minimum, average, count, and return the value to the dictionary

Example:

#Find the maximum, minimum, average, count, sum
from django.db.models import Max, Min, Avg, Count, Sum


data = models.Movie.objects.aggregate(Max('price'), Min('price'), Avg('price'), Count('pk'), Sum('price'))
print(data)

 

 

6、 Group query

keywordannotate

Example:

①   Count the number of authors of each book

data = models.Book.objects.annotate(author_num=Count('author__pk')).values('name', 'author_num')
print(data)

②   Count out the price of the cheapest book sold by each publishing house

data = models.Publish.objects.annotate(min_price=Min('book__price')).values('name', 'min_price')
print(data)

③   Count books by more than one author

data = models.Book.objects.annotate(author_num=Count('author')).filter(author_num__gt=1).values('name')
print(data)

④   Query the total price of books published by each author

data = models.Author.objects.annotate(price_sum=Sum('book__price')).values('name', 'price_sum')
print(data)

The above examples are grouped by table. After the table, annotate is grouped by table, and after value, annotate is grouped by field. How to group by a specified field in the table?

Data = models. Book. Objects. Values ('price '). Annotate() # grouped by price
print(data)

 

 

7、 F and Q query

1. F query

It can get the corresponding value of a field in the database and take it out one by one for operation. Before operation, it needs to import a module

from django.db.models import F

Example:

①   Query books with inventory greater than sales

res = models.Book.objects.filter(kucun__gt=F('sale')).values('name')
print(res)

②   Raise the price of all books by 100 yuan

 

2. Q query

It can change the condition relation of query   and   or   Not, a module needs to be imported before operation

from django.db.models import Q

Example:

Search the name of the book is journey to the West or the price is 500 books

"""
Data = models. Book. Objects. Filter (Q (price = 500), q (name ='journey to the West '). Values ('name') # comma is and relation
Data = models. Book. Objects. Filter (~ Q (price = 500) | Q (name ='journey to the West ')). Values ('name') # ~ is not relation, negative
"""
Data = models. Book. Objects. Filter (Q (price = 500) | Q (name ='journey to the West ')). Values ('name') | is or relation
print(data)

 

3. High order usage of Q query

Application scenario: the search function of the web page, which searches according to the user’s selected content and input content, such as user input   name   Journey to the West   etc.

from django.db.models import Q

q = Q()
q. Connector ='or '# Q object is also and relation by default, and or can be changed through connector
q. Children. Append (('name ','journey to the West'))
q.children.append(('price', 500))
data = models.Book.objects.filter(q).values('name')
print(data)
Django model layerDjango model layer

from django.db.models import Q

column_ Name = input ('field name of input query: '). Strip ()
column_ Value = input ('input field value of query: '). Strip ()
q = Q()
q. Connector ='or '# Q object is also and relation by default, which can be changed to or by connector
q.children.append((column_name, column_value))
data = models.Book.objects.filter(q).values('name')
print(data)

Examples of advanced usage

 

 

8、 Common fields in models

1. AutoField(primary_ Key = true) # primary key field
2. CharField(max_length=32)     # varchar(32)
3. IntegerField()               # int
4. BigIntegerField()            # bigint
5. DecimalField()               # decimal
6. EmailField()                 # varchart(254)
7. DateField()                  # date
8. DateTimeField()              # datetime
      auto_ Now: the time of this field will be automatically updated every time the data is edited
      auto_ now_ Add: it is automatically updated when it is created, and the update field will not change
9. Boolean field() # pass a Boolean value to the field, which will correspond to the number 0 / 1
      Application scenario: is_ delete    is_ vip    is_ status
10. Textfield() # text type to store large pieces of text
11. Filefield() # from the ferry, the path is saved in the database, the file is uploaded to the specified directory, only the file path is saved
      upload_ To ='specify file path 'to transfer the file object to this field, and the file will be saved to upload automatically_ To the specified folder, and then change the field to save the file path

 

1. Custom char type field

from django.db.models import Field


Class defcharfield (field): # need to inherit field class
    def __init__(self, max_length, *args, **kwargs):
        self.max_ length = max_ Length # after intercepting the method of a parent class, use super to call the method of the parent class
        super().__init__(max_length=max_length, *args, **kwargs)

    def db_type(self, connection):
        return 'char(%s)' % self.max_length

 

2. Key parameters in fields

  null

  default

  on_delete = models.CASCADE

 

3. Options parameter

If the data can be listed for users to choose, then you can consider using the choices parameter

Django model layerDjango model layer

class UserInfo(models.Model):
    name = models.CharField(max_length=64)
    gender_choice = (
        (1,'male '),
        (2,'female '),
        (3,'Other '),
    )
    gender = models.IntegerField(choices=gender_ Choice) # the field still stores numbers and can match numbers outside the relationship
    record_ Choices = ('checked ','checked in'),
                      ('vacate ','Ask for leave'),
                      ('late ','late'),
                      ('noshow ','absence'),
                      ('leave_ Early ', "early leave"),
                      )
    Record = models. Charfield ("class record", choices = record_ choices, default="checked", max_ length=64)

Userinfo table in models.py

① Query the gender of the person whose PK is 1

user_obj = models.UserInfo.objects.get(pk=2)
print(user_obj.gender)
#For the choices parameter field, when the value is taken, get_ Field name_ display()
print(user_obj.get_gender_display())

② Query the gender of the person whose PK is 5

user_obj = models.UserInfo.objects.get(pk=5)
print(user_obj.name, user_obj.gender, user_obj.get_gender_display())

 

 

9、 Database operation optimization

Only and defer, defer and only are anti objects to each other

 

1、only()

Function: pass the field name in brackets, the result is a list set of data objects, the object only contains the specified field properties, the object. The properties will not go to the database, but once the object. Non bracket fields, you can get the data, but it is a new database query

Data = models. Book. Objects. Only ('name ') # data object only has name attribute
print(data)
for i in data:
    Print (i.name) #
    Print (i.price) #

 

2、defer()

Function: pass the field name in brackets, the result is a list set of data object, which does not contain the field attribute. Once the object is not in brackets, the field will not go to the database, but the object. This attribute can also get the data, but it is a re database query

Data = models. Book. Objects. Defer ('name ') # the data object contains all the attributes except the name attribute
print(data)
for i in data:
    Print (i.price) #
    Print (i.name) #

 

3、select_related()

Function: Select_ Related () can only transfer foreign key fields in brackets, not many to many fields, only one to many and one to one. The internal operation is to connect all the relational tables, and then query them at one time and encapsulate them in the object. When the data object obtains the data in any table, it doesn’t need to go to the database, because all of them are encapsulated as the attributes of the object. select_ Related (‘foreign key field 1__ Foreign key field 2__ Foreign key field 3… ‘) connects all tables containing foreign key fields

data = models.Book.objects.select_related('publish')
print(data)
for i in data:
    print(i.publish.name)
    print(i.publish.addr)

 

4、prefetch_related()

Function: prefetch_ Related () is a sub query internally, but it gives us the feeling that it is a table connection operation. Internally, all the data in the foreign key association table is encapsulated into the object through the sub query, and then the fields in the current table or the foreign key association table do not need to go through the database

data = models.Book.objects.prefetch_related('publish')
print(data)
for i in data:
    print(i.publish.name)

 

5、select_ Related and prefetch_ Comparison of related advantages and disadvantages

select_ Related is a join table operation, and its advantage is that it only goes through SQL query once

Time consuming in “operation of connection table”

prefetch_ Related is a sub query, which is a SQL query twice

Time spent on “query times”

 

 

10、 Django ORM start transaction operation

1. Four characteristics of transaction (acid)

①   Atomicity

②   uniformity

③   Isolation

④ Persistence

 

2. How to open a transaction in ORM

from django.db import transaction
with transaction.atomic():
    #The ORM statements executed in the with block belong to the same transaction
    pass
    #When the code block finishes running, the transaction ends

 

3. Three paradigms of database

self-examination…