Django model layer (2) multi-table operation

Create a model

Relationships between tables and tables

One-to-one, many-to-one, many-to-many

# The information commonly used in the author's table is placed in this table.
class Author(models.Model):
    name=models.CharField(max_length=32)
    age=models.IntegerField()
    authorDetail=models.OneToOneField(to='AuthorDetail')
    #and AuthorDetail To establish a one-to-one relationship, the field of one-to-one relationship can be written in either table of the two tables. models.OneToOneField(to='AuthorDetail')Namely foreignkey+unique,We just don't need to write our own parameters, and orm It will automatically spell the name of this field for you._id，The field name in the database is authorDetail_id It can also be written like this.# AuthorDetail = models. OneToOneField (to= AuthorDetail, to_field= id, on_delete = models. CASCADE) on_delete = models. CASCADE cascade relationship
    def __str__(self):
        return self.name
    
    
# Author information, which is not commonly used in author information tables, is placed in this table.
class AuthorDetail(models.Model):
    brithday=models.DateField()
    telephone=models.BigIntegerField()
    addr=models.CharField(max_length=64)
    def __str__(self):
        return self.addr
# Books Publishing List
class Publish(models.Model):
    name=models.CharField(max_length=32)
    city=models.CharField(max_length=32)
    email=models.EmailField()
    def __str__(self):
        return self.name
    
# Booklist
class Book(models.Model):
    title=models.CharField(max_length=32)
    publishDate=models.DateField()
    price=models.DecimalField(max_digits=5,decimal_palces=2)
    # Establish a one-to-many relationship with Publish. The foreign key field is built on one side of the many. If the field publish is a foreign key field, it is automatically an int type.
    # There are many parameters in foreignkey, which need us to learn. Slowly, to point to the table, to_field point to the fields you associate with. If you don't write this, it will automatically associate the primary key fields by default. on_delete cascade deletion field names do not need to be written as publish_id. orm will automatically give you this when translating foreignkey. The field is spelled with a _id, which automatically becomes publish_id in the database.
    publish=models.Foreignkey(to='publish')
    
    # To establish a many-to-many relationship with the Author table, ManyToManyField can build any one of the two models, automatically create the third table, and note that when you look at the book table, you can't see the field, because this field is the meaning of creating the third table, not the meaning of creating the field, so you can only say This book class has the author field attribute
    authors=models.ManyToManyField(to='Author')
    def __str__(self):
        return self.title
    
# Many-to-many table relationship, how do we build it when we learn mysql? Is it possible to create a third table manually and write two fields on it? Each field foreign key is related to the other two tables of many-to-many relationship. manytomany of orm automatically creates a third table for us. Both ways can establish a relationship. Later Learn from the third table we created automatically with orm for the time being, because the third table we created manually can't be used in many of the orm statements between tables with many-to-many relationships when we do orm operations.
# If you want to delete a table, you just need to cancel the table, and then execute the two database synchronization instructions, automatically delete.

#Note that whether it's one-to-many or many-to-many, when you write to, the last value is a string, otherwise you need to put the table you want to associate on top of it.

Addition, deletion and alteration checking among multiple tables

Add Table Records - Add

Simply enter some data before operation: create and save. The difference between the two methods and the table is to see how to add the data of the associated fields.

One-on-one
    //Mode 1:
    new_author_detail=models.AuthorDetail.objects.create(
        birthday='1965-10-10',
        telephone='18335267641',
        addr='Datong, Shanxi'
    )
    models.Author.objects.create(
        name='ll',
        age='53',
        authorDetail=new_author_detail,
    )

    //Mode 2
    obj=models.AuthorDetail.objects.filter(addr='Datong, Shanxi').last()
    print(obj.id)
    models.Author.objects.create(
        name='mx',
        age='52',
        authorDetail_id=obj.id
    )

//One-to-many
    obj=models.Publish.objects.get(id=3)
    models.Book.objects.create(
        title='Stories will be newly compiled',
        publishDate='2019-9-10',
        price=30,
        # Mode I
        publish=obj
        # Mode 2
        publish_id=obj.id
    )

//Many-to-many
    //Way I Commonly Used
    book_obj=models.Book.objects.get(id=1)
    book_obj.authors.add(*[1,2])  #Scatter
    //Mode 2
    author1=models.Author.objects.get(id=3)
    author2=models.Author.objects.get(id=4)
    book_obj=models.Book.objects.get(id=4)
    book_obj.authors.add(*[author1,author2])

Delete table records - Delete

One-on-one
    models.AuthorDetail.objects.get(id=6).delete()
    models.Author.objects.get(id=5).delete()

//One-to-many
    models.Publish.objects.get(id=1).delete()
    models.Book.objects.get(id=1).delete()

//Many-to-many
    book_obj = models.Book.objects.get(id=1)
    book_obj.authors.remove(*[1, 2]) #delete
    book_obj.authors.clear()  #empty
    book_obj.authors.add(2,)  #Add to
    book_obj.authors.set(['1','2']) #Delete and update

Update Table Records - Change

One-on-one
    models.Author.objects.filter(id=1).update(
        name='An Wen',
        age=24,
    //Mode I
        # authorDetail_id=5,
    //Mode 2
        authorDetail=models.AuthorDetail.objects.get(id=3)
    )

//One-to-many
    models.Book.objects.filter(id=4).update(
    title='java core',
    //Mode I
    publish_id=4,
    //Mode 2
    publish=models.Publish.objects.get(id=2)
    )

//Many-to-many
    book_obj.authors.set(['1','2']) #Delete and update

Query Table Records - Check

Object-based cross-table queries -- similar to sub-queries

Forward and reverse queries

| Relational attributes (fields) are written in that table. To query the data of the current table (class) from the data of its associated table (class) is called forward query, and vice versa is called reverse query.|

# One-to-one
 Forward Query
 # Query yage's phone number
author_obj=models.Author.objects.filter(name='yage').first()
print(author_obj.authorDetail)
print(author_obj.authorDetail.telepbone)
inverse query
 # To find out who is calling 145523669874
author_detail_obj=models.AuthorDertail.objects.get(telrphone=145523669872)
print(author_detail_obj.author)
print(author_detail_obj.author.name)
"""
        Forward Query Author_obj.authorDateil, Object. Associated Properties
    Author------------------------->AuthorDateil
    Author<-------------------------AuthorDateil
        Reverse query: AuthorDateil.author, object. lowercase class name

"""

# One-to-many
 Forward Query
 # Query which publisher is the core java publisher of a Book
 book_obj=models.Book.objects.get(title='java core')
print(book_obj.publish)
print(book_obj.publish.name)
inverse query
 # Those Books Published by Tsinghua Publishing House
 pub_pbj=models.Publish.objects.get(name ='Tsinghua Publishing House')
print(pub_obj.book_set.all())

"""
        Forward query book_obj.publishs object. Attribute
    Book------------------------------>>>Publish
    Book<<<------------------------------Publish
        Reverse query pub_obj.book_set.all() object. Table name lowercase_set
    
"""

# Many-to-many
 Forward Query
 # Query who publishes the java core of a Book
 book_obj=models.Book.objects.get(title='java core')
print(book_obj.authors.all())
inverse query
 # Query what books yage has written
author_obj=models.Author.object.get(name='yage')
print(author_obj.book_set.all())
"""
        Forward query book_obj.authors.all() object. attribute
    book --------------->author
    book <---------------author
        Reverse query author_obj.book_set.all() object. Table name lowercase_set
    
"""

Cross-table Query Based on Double Underlines--Joint Table Query

Django also provides an intuitive and efficient way to represent association relationships in lookups, which can automatically confirm SQL JOIN connections. To do Cross-Relational queries, you use two underscores to link the names of the associated fields between models until you finally link to the model you want.

'''
    //Queries based on double underscores have one sentence: forward queries are in fields, reverse queries are in lowercase by table names to tell ORM engine which table to join. One-to-one, one-to-many, and many-to-many are all written in one way. Note that when we write an ORM query, it's no problem which table is in the front and which table is in the back, because join join table operation is used.
'''

# One-on-one
#1. Call yage
# Mode One Forward Query
obj=models.Author.object.filter(name='yage').values('authorDetail__telepbone')
print(obj)
# Mode 2 Reverse Query
obj=models.AuthorDetail.objects.filter(author__name='yage').values('telephone')
print(obj)

#2. Whose telephone number is 14553669874
obj=models.AuthorDetail.object.filter(telephon='145523669874').values('authors__name')
print(obj)
obj=models.Author.objects.filter(authorDetail__telephon='145523669874').values('name')
print(obj)

# One-to-many
# Query which publisher is the core java publisher of a Book
obj=models.Book.objects.filter(name='java core').values('publish__name')
obj=models.Publish.objects.filter(book__title='java core').values('name')

# Those Books Published by Tsinghua Publishing House
obj=models.Publish.objects.filter(name='Tsinghua Publishing House').values('book__title')
print(obj)
obj=models.Book.object.filter(publish__name='Tsinghua Publishing House').values('title')
print(obj)


# Many-to-many
# Query who publishes the java core of a Book
obj=models.Book.objects.filter(title='java core').values('authors__name')
print(obj)
obj=models.Author.objects.filter(book__title='java core').values('name')
print(obj)

# Query what books yage has written
#Method 1
obj=models.Author.objects.filter(name='yage').values('book__title')
print(obj)
#Method 2
obj=models.Book.objects.filter(authors__name='yage').values('title')
print(obj)

#Advanced Query I
#Books Published by Tsinghua Publishing House and Name of Author
#Method 1
obj=models.Publish.objects.filter(name='Tsinghua Publishing House').values('book__title','book__authors__name')
print(obj)
#Method 2
obj=models.Book.objects.filter(publish__name='Tsinghua Publishing House').values('title','authors__name')
print(obj)
#Method 3
obj=models.Author.objects.filter(book__publish__name='Tsinghua Publishing House').values('name','book__title')
print(obj)


//Advanced Query II
#The title of the book and the name of the Publishing House published by the author whose mobile number begins at 14552
#Method 1
obj=models.AuthorDetail.objects.filter(telephone__startswith='14552').values('author__book__title' , 'author__book__publish__name')
print(obj)
#Method 2
obj=models.Author.objects.filter(authorDetail__telephone__startswith='14552').values('book__title','book__publish__name')
print(obj)
#Method 3
obj=models.Book.objects.filter(authors__authorDetail__telephone__startswith='14552').values('authors__book__title','authors__book__publish__name')
print(obj)

related_name

In reverse query, if related_name is defined, the table name is replaced with related_name, for example:

publish = ForeignKey(Book, related_name='bookList')

# Exercise: Query the names and prices of all books published by People's Publishing House (one-to-many)
# Reverse queries are no longer by table name: book, but related_name:bookList
obj=models.Publish.objects.filter(name="Tsinghua Publishing House").values("bookList__title","bookList__price") 

Aggregation function

from django.db.models import Avg,Min,Max ,Sum,Count

# Calculate the average price of all books. Maximum book price
#aggregate() is a termination clause of QuerySet, meaning that it returns a dictionary containing some key-value pairs. The name of the key is the identifier of the aggregate value, and the value is the calculated aggregate value. The name of the key is automatically generated by the name of the field and the aggregation function. If you want to specify a name for the aggregation value, you can provide it to the aggregation clause.
obj=models.Book.objects.all().aggregate(a=Avg('price'),m=Max('price'))
    print(obj)  #{'a': 47.5, 'm': Decimal('78.00')}
    print(obj['m']-2)   #76.00

Group query

# Statistics on the average price of books published by each publishing house
ret=models.Book.objects.values('publish_id').annotate(a=Avg('price'))
 print(ret) #<QuerySet [{'publish_id': 2, 'a': 69.0}, {'publish_id': 3, 'a': 30.0}, {'publish_id': 4, 'a': 22.0}]>

ret=models.Publish.objects.annotate(a=Avg('book__price'))
print(ret)  #<Query Set [<Publish: Shanxi Publishing House>, <Publish: Tsinghua Publishing House>, <Publish: Jiangsu Publishing House>]>

ret = models.Publish.objects.annotate(a=Avg('book__price')).values('name', 'a')
print(ret)  #<QuerySet [{name':'Shanxi Publishing House','a': 22.0}, {name':'Tsinghua Publishing House','a': 69.0}, {name':'Jiangsu Publishing House','a': 30.0}]>

F Query

In all the examples above, the filters we constructed simply compare the field values with a constant. If we want to compare the values of the two fields, what should we do? We add two fields to the book table: number of comment s, number of collections: good

#Books with more favourable query points than reviews
ret=models.Book.objects.filter(good__gt=F('comment'))

# Books with more favourable query points than reviews + 20     
#Django supports addition, subtraction, multiplication, division and modular operations between F() objects and between F() objects and constants.
ret=models.Book.objects.filter(good__gt=F('comment')+20)
print(ret)  #<QuerySet [<Book: Today is a good day>, <Book: Java Core>]>

#Price of all books + 20
models.Book.objects.all().update(price=F('price')+20)

#The number of comments is more than 100, and the number of comments is more than 100.
ret=models.Book.objects.filter(good__gt=100,comment__gt=100)
print(ret)  #<QuerySet [<Book: Java Core>, <Book: Story will be New >]>

Q Query

Q objects can be combined with & (and), | (or) and ~ (non) operators. When an operator is used on two Q objects, it generates a new Q object.

You can compose arbitrarily complex Q objects by combining & and | operators and grouping them in parentheses. At the same time, Q objects can use the ~operator inversion, which allows the combination of normal queries and inversion (NOT) queries:

#If the number of comments is more than 100, or the number of comments is more than 100
ret=models.Book.objects.filter(Q(good__gt=100)|Q(comment__gt=100))
print(ret)  #<QuerySet [<Book: Java Core>, <Book: Story will be Newly Edited>, <Book: LINUX Learning>]>


#Comments greater than 100, or points less than or equal to 100
ret = models.Book.objects.filter(~Q(good__gt=100) | Q(comment__gt=100))
    print(ret)

#Comments more than 100, or points more than 100 and price='42'
#Comma join common query conditions are placed at the end
#Query functions can mix Q objects with keyword parameters. All parameters provided to the query function (keyword parameters or Q objects) will be "AND" together. However, if a Q object appears, it must precede all keyword parameters. For example:
ret = models.Book.objects.filter(Q(good__gt=100) | Q(comment__gt=100),price='42')
    print(ret)

#Comments more than 100, or points more than 100 and price='42'
ret = models.Book.objects.filter(Q(good__gt=100) | Q(comment__gt=100) & Q(price='42'))  #& High priority

ret = models.Book.objects.filter(Q(Q(good__gt=100) | Q(comment__gt=100)) & Q(price='42'))   #| High priority

orm executes native sql statements

When the model query API is not enough, we can also use the original SQL statement to query.

Django provides two ways to query using original SQL: one is to query original SQL using raw() method and return model instances; the other is to execute custom SQL statements directly without model layer.

Execute native queries

The raw() manager method is used for the original SQL query and returns an instance of the model:

Note: raw() grammar queries must contain primary keys.

This method executes the original SQL query and returns an instance of django.db.models.query.RawQuerySet. This RawQuerySet instance can provide object instances iteratively, just like a general QuerySet.

ret=models.Publish.objects.raw('select * from app01_publish;')
print(ret)      #<RawQuerySet: select * from app01_publish;>
for i in ret:
   print(i.name)

# Direct execution of custom SQL
# Get the database connection in the interface provided by django, and then operate the database as if using the pymysql module
from django.db import connection, connections
cursor = connection.cursor()  # cursor = connections['default'].cursor()
cursor.execute("""SELECT * from auth_user where id = %s""", [1])
ret = cursor.fetchone()

View orm to generate native sql statements

ret=models.Publish.objects.all()
print(ret)
from django.db.models import connection
print(connection.queries)

Comprehensive Query Exercises

#1 Query the name of each author and the maximum price of the book published
ret=models.Author.objects.values('name').annotate(max_price=Max('book__price'))
print(ret) #Note: Values are written in front of annotate as grouping criteria, and the values returned to you are the fields in this value (name) and the result field data of grouping statistics (max_price).
#ret=models.Author.objects.annotate(max_price=Max('book__price')).values('name','max_price')#This way of writing is grouped according to the id field of the Author table and returned to you all the model objects of the table. This object contains the attribute of max_price. The value method is written afterwards to get the values of the attributes of these objects. Of course, double underscores can be added to join the table to get the data of other related tables, but The other associated table data you get is the data corresponding to your model objects, and the associated data may not be the data corresponding to the maximum value you want.

# 2 Query the name of the author whose id is greater than 2 and the maximum price of the book published
ret=models.Author.objects.filter(id__gt=2).annotate(max_price=Max('book__price')).values('name','max_price')#Keep in mind that this value takes all the field values and max_pirce values of the table that invoked this method earlier, which is why we double-underlined the associated data.
print(ret)

#3 Query the name of a female author whose id is greater than 2 or whose age is older than or equal to 20 and the maximum price of a published book.
ret=models.Author.objects.filter(Q(id__gt=2)|Q(age__gte=20),sex='female').annotate(max_price=Max('book__price')).values('name','max_price')

#4 Query the average of the highest price for each author's book
ret=models.Author.objects.values('id').annotate(max_price=Max('book__price')).aggregate(Avg('max_price')) #{'max_price__avg': 555.0} Note that aggregate is the termination sentence of queryset and the result is a dictionary.
ret=models.Author.objects.annotate(max_price=Max('book__price')).aggregate(Avg('max_price')) #{'max_price__avg': 555.0} Note that aggregate is the termination sentence of queryset and the result is a dictionary.

supplement

url(r'^admin/', admin.site.urls) routing operation admin.py in url.py

admin.py in app application

from django.contrib import admin
from app01 import models
# Register your models here.
admin.site.register(models.Book)
admin.site.register(models.Author)
admin.site.register(models.AuthorDetail)
admin.site.register(models.Publish)

Click run management.py task in Tools to execute create superuser to create a superuser (username and password), and then access http://127.0.0.1:8000/admin/. You can directly manipulate the database to add, delete and check.

| Visit http://127.0.0.1:8000/admin/|