This article is participating in Python Theme Month. See the link for details

Rest cannot be enjoyed by lazy people.~

preface

Continuing with the previous article, this article introduces multi-table operations. Djangos ORM allows you to create multiple table relationships with djangos ORM, and also supports multiple table operations. Take author, book, publishing house and author information tables as examples to illustrate.

Create table relationships

Note: in practice, it is not recommended to use foreign keys to establish table relationships, i.e. cascading updates and deletes are not used. Instead, logical foreign key relationships are recommended to establish table relationships.

In the above four tables, the relationship between books and publishing houses belongs to one-to-many relationship, and the foreign key is established in the party with high query frequency. The author and author details table belong to one-to-one relationship, and the foreign key is established in the party with high query frequency. The author and book belong to many-to-many relationship, and the third table is needed to store the relationship. Therefore, it is recommended to build the foreign key in the party with high query frequency. Be sure to execute database migration commands when creating tables

When creating a table relationship, you can create the table model first and then add a foreign key field. In addition, when creating a foreign key relationship using Django ORM, the associated foreign key field is automatically appended to the field_idBy default, the relationship between tables uses the primary key as the associated field. In addition, when creating table relationships, it is not recommended to use physical foreign keys for association, but to specify table relationships by using logical relationships.

class Book(models.Model) :
    name = models.CharField(max_length=60, verbose_name='Book name')
    Max_digits indicates 8 digits and decimal_place indicates 2 digits
    price = models.DecimalField(max_digits=8, decimal_places=2, verbose_name='Book prices')
    inventory_num = models.IntegerField(verbose_name='Stock quantity')
    sell_num = models.IntegerField(verbose_name='Quantity sold')
    Db_constraint =Flase ForeigenKey(to=' table name '), db_constraint=Flase
    publish = models.ForeignKey(to='Publish', on_delete=models.DO_NOTHING, db_constraint=False, verbose_name='Foreign key Associated Publisher')
    # many-to-many relationships, using ManyToManyField(to=' table name '), author is a virtual field, mainly used to tell ORM that the book and author are many-to-many relationships, and ORM automatically creates a third many-to-many relationship
    author = models.ManyToManyField(to='Author', on_delete = models. DO_NOTHING db_constraint =False, verbose_name='Foreign key association author')


class Publish(models.Model) :
    name = models.CharField(max_length=12, verbose_name='Publisher name')


class Author(models.Model) :
    name = models.CharField(max_length=10, verbose_name='Author name')
    # OneToOneField(to=' table name ')
    author_detail = models.OneToOneField(to='AuthorDetail', on_delete=models.DO_NOTHING, db_constraint=False, verbose_name='Details of External Associated Authors')


class AuthorDetail(models.Model) :
    age = models.IntegerField(verbose_name='age')
    phone = models.CharField(max_length=11, verbose_name='Mobile phone Number')
Copy the code

Many-to-many table relationships can be automatically created, semi-automatically created, and manually created.

# Automatic creation - ManyToManyField, generally this way can meet the requirements
The advantage of using automatic many-to-many relationships is that you don't need to manually create a third table, which is very convenient. The disadvantage of django ORM is that it doesn't extend the third table.
class Book(models.Model) :
	name = models.CharField(max_length=32)
    authors = models.ManyToManyField(to='Author')
    
class Author(models.Model) :
    name = models.CharField(max_length=32)
    
    
    
# pure manual create - ForeignKey Manually create the third table
The third table depends entirely on manual extensions, but requires more code and cannot use the simple methods provided by ORM.
class Book(models.Model) :
    name = models.CharField(max_length=32)
    
class Author(models.Model) :
	name = models.CharField(max_length=32)
	
class Book2Author(models.Model) :
    book_id = models.ForeignKey(to='Book')
    author_id = models.ForeignKey(to='Author')
    
# Semi-automatic creation, controls the third table with ManyToManyField parameters
class Book(models.Model) :
    name = models.CharField(max_length=32)
    authors = models.ManyToManyField(
        to='Author'.Tell the ORM that it does not need to automatically help create a third relational table
        through='Book2Author'.Tell ORM the foreign key field of the third relational table
        through_fields=('book'.'author')  Select * from table_name where table_name (); select * from table_name ()
                                     )
class Author(models.Model) :
    name = models.CharField(max_length=32) 
    
class Book2Author(models.Model) :
    book = models.ForeignKey(to='Book')
    author = models.ForeignKey(to='Author')
Copy the code

Multi-table data operation – add, delete and change

First of all, it introduces the operation of adding, deleting and changing multiple tables. Because the operation of query data of multiple tables is a little more troublesome, it is separate and separate.

One-to-many & one-to-one relationship – add, delete, change

One-to-one and one-to-many add, delete and modify operations are basically the same.

Increase the data

There are two ways to add data, one is through actual fields and the other is through virtual field object assignment.

# Method 1: Pass the actual field
book_obj = models.Book.objects.create(name='Harry Potter', price=10.2, publish_id=1)

# Method 2: First obtain the publisher object, and then associate the book with the publisher through the publisher object
publis_obj = models.Publish.objects.filter(pk=1).first()
book_obj = models.Book.objects.create(name='Harry Potter', price=10.2, publish=publis_obj)
Copy the code

Delete the data

It is important to note that deleting data in a real project is not actually deleting it, but rather using a Boolean field to indicate whether the data is being deleted.

If on_delete=models.DO_NOTHING is not specified when data is deleted, the data is deleted in cascade mode by default.

models.Publish.objects.filter(pk=1).delete()
Copy the code

Modify the data

There are two ways to modify data as well as to add data.

Way # 1
models.Book.objects.filter(pk=1).update(publish_id=1)

Way # 2
pub_obj = models.Publish.objects.filter(pk=2).first()
models.Book.objects.filter(pk=1).update(publish=pub_obj)
Copy the code

Many-to-many relationships – add, subtract, change

To be clear, many-to-many additions and deletions are working on the third table, but the third table is created automatically by Django. How do you code into the third table? The many-to-many foreign key relationship is built into the book table, and the many-to-many foreign key field is the author field, so the third table can be manipulated with book_obj.author.

For many-to-many relationships in the third table that Django automatically creates, Django provides additional ways to manipulate the data.

Add many-to-many relationships – add()

The add() method adds data to the third relational table, passing both numbers and objects in parentheses, and supporting multiple simultaneous operations.

Method 1: Add id directly
book_obj = models.Book.objects.filter(pk=1).first()
book_obj.author.add(1)		# add 1, 1, 1, 1, 1, 1
book_obj.author.add(2.3)   Add two records, 1, 2 and 1, 3

# Method 2, add relationships through objects
book_obj = models.Book.objects.filter(pk=2).first()
author_obj1 = models.Author.objects.filter(pk=1).first()
author_obj2 = models.Author.objects.filter(pk=2).first()
author_obj3 = models.Author.objects.filter(pk=3).first()
book_obj.author.add(author_obj1)				Add 1 record
book_obj.author.add(author_obj2, author_obj3)	# add 2 items
Copy the code

Remove many-to-many relationships – remove()

The remove() method is used to remove data from the third table. Similarly, parentheses can be used to pass both numbers and objects, and multiple data operations can be performed simultaneously.

# Method 1: Delete the value directly
book_obj = models.Book.objects.filter(pk=1).first()
book_obj.author.remove(2)  # delete book_id = 1 and author_id = 2
book_obj.authors.remove(1.3)  # delete multiple

# Method 2: Delete by object
author_obj1 = models.Author.objects.filter(pk=2).first()
author_obj2 = models.Author.objects.filter(pk=3).first()
book_obj.authors.remove(author_obj1, author_obj2)
Copy the code

Modify many-to-many relationships – set()

The set() method is used to modify the third table, which is an override operation that overwrites the previous relation with a new relation. The argument to this method must be a list or tuple, indicating a number or object. Simultaneous manipulation of multiple data is also supported.

# Method 1: Modify directly by value
book_obj = models.Book.objects.filter(pk=1).first()
book_obj.author.set([2])  # change author_id where book_id = 1 to 2
book_obj.authors.set([1.2])  Set the author of the book to the author whose id=1 and id=2

# Method 2: Modify by object
author_obj2 = models.Author.objects.filter(pk=2).first()
author_obj3 = models.Author.objects.filter(pk=3).first()
book_obj.authors.set([author_obj2, author_obj3])
Copy the code

Clear the binding of an object in the third table – clear()

The clear() method clears the bindings for an object in the third relational table.

book_obj = models.Book.objects.filter(pk=1).first()
book_obj.author.clear()
Copy the code

Multi-table query

Before performing a multi-table query operation, you need to understand the concept of forward and reverse queries.

Forward query: in which table the foreign key is in, the query associated table is forward query, for example, through books to query publishing houses.

Reverse lookup: A reverse lookup is performed when the associated table looks up the foreign key field in the table, for example, a book by a publisher.

The subquery

If the query is more complex, you can use the way of sub-query, sub-query is the meaning of step by step query, the results of the first query as the condition of the query.

Is the query

If there are multiple results that require the foreign key field. All (), then how to determine whether there are multiple results? If you don’t add.all() the result is the name of the application. Model name. None such as first.author.none if the ORM statement does not contain any error, it is required to add.all(). The result is a model object if there is only one query result, or a QuerySet object if there are more than one.

Select books whose primary key is 1 from which publisher
book_obj = models.Book.objects.filter(pk=1).first()
res = book_obj.publish  # Publish object (1)
print(res.name)

Select * from authors whose primary key is 1
book_obj = models.Book.objects.filter(pk=1).first()
res = book_obj.author  # first.author.none, indicating that there are multiple results
res_many = book_obj.author.all(a)# <QuerySet [<Author: Author object (1)>, <Author: Author object (2)>]>
    
Query author lili's age
author_obj = models.Author.objects.filter(name='lili').first()
res = author_obj.author_detail
print(res.phone)
Copy the code

Reverse query

In the case of one-to-one reverse query, the table name is lowercase; in the case of one-to-many or many-to-many reverse query, the table name is lowercase — set. In addition, if multiple results are lowercase, _set.all() is added to determine whether multiple results are the same as those in the forward query. The result is a model object if there is only one query result, or a QuerySet object if there are more than one.

The query publisher is a book published by Oriental Publishing House
publish_obj = models.Publish.objects.filter(name='Oriental').first()
res = publish_obj.book_set.all(a)# <QuerySet [<Book: Book object (1)>, <Book: Book object (2)>]>

# many-to-many relationship query, query author is lili's book
author_obj = models.Author.objects.filter(name='lili').first()
res = author_obj.book_set  # first.book.none, indicating multiple results
res_many = author_obj.book_set.all(a)print(res_many)  # <QuerySet [<Book: Book object (1)>]>

Select * from authors whose phone number is 119
author_detail_obj = models.AuthorDetail.objects.filter(phone='119').first()
res = author_detail_obj.author
print(res.name)
Copy the code

League table query

A syntable query is just like a syntable query in a MySQ SQL statement, but in Djangos ORM, a syntable query is based on a double underscore (cross-table query). A table query can be queried using a single line of code, and a table query also follows a positive and negative relationship.

Is the query

Select * from books whose primary key is 1
Select values('names') from publish; select values('names') from publish; select values('names') from publish
res = models.Book.objects.filter(pk=1).values('name'.'publish__name')  # < QuerySet [{' name ':' harry potter ', 'publish__name' : 'Oriental'}] >

Select * from author where primary key = 1
res = models.Book.objects.filter(pk=1).values('author__name')  # <QuerySet [{'author__name': 'lili'}, {'author__name': 'nana'}]>

Query lili's phone number and name
res = models.Author.objects.filter(name='lili').values('name'.'author_detail__phone').first()
print(res.get('name'), res.get('author_detail__phone'))
Copy the code

Reverse query

Select * from publisher where primary key = 1 and book where primary key = 1
res = models.Publish.objects.filter(book__id=1).values('name'.'book__name')  # < QuerySet [{' name ':' Oriental ', 'book__name' : 'harry potter'}] >

Select * from many-to-many relationships where primary key = 1
res = models.Author.objects.filter(book__id=1).values('name'.'book__name')  # < QuerySet [{' name ':' lili ', 'book__name' : 'harry potter'}, {' name ':' nana ', 'book__name' : 'harry potter'}] >

Select * from author where id = 1
res = models.AuthorDetail.objects.filter(author__id=1).values('author__name'.'phone')  # <QuerySet [{'author__name': 'lili', 'phone': '119'}]>

First, get the book object, the book is associated with the author table, author table and associated with the author details table
res = models.Book.objects.filter(pk=1).values('author__author_detail__phone')  # <QuerySet [{'author__author_detail__phone': '119'}, {'author__author_detail__phone': '120'}]>
Copy the code

Aggregation query

Models import Max, Min, Sum, Count, Avg from Django.db. models import Max, Min, Sum, Count, Avg from django.db.models import Max, Min, Sum, Count, Avg If you use aggregate functions without grouping, you need to use them in the aggregate() method.

from django.db.models import Min,Max,Sum,Count,Avg
# Count the average price of a book
res = models.Book.objects.aggregate(Avg('price'))
print(res)
These aggregate functions can be used together
res = models.Book.objects.aggregate(Max('price'),Sum('price'),Count('pk'))
print(res)
Copy the code

Grouping query

Aggregate functions are usually used with groups, and grouped queries are annotate, with models by default. Grouping based on the primary key of the table, and values() specified in the annotate() method appear before the annotate() method. Grouped queries support __ cross-table queries.

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

# 1. Count the number of authors per book
res = models.Book.objects.annotate(author_num=Count('author')).values('name'.'author_num')  # author_num = # author_num = # author_num = # author_num = # author_num = # author_num

# 2. Count the price of the cheapest book sold by each publisher
res = models.Publish.objects.annotate(min_price=Min('book__price')).values('name'.'min_price')

# 3. Count books with more than one author
# Calculate the number of authors in each book according to the group of books, and then filter the data with the number of authors greater than 1
res = models.Book.objects.annotate(author_num=Count('author')).filter(author_num__gt=1).values('name'.'author_num')

# 4. Query the total price of a book published by each author
res = models.Author.objects.annotate(sum_price=Sum('book__price')).values('name'.'sum_price')
Copy the code

F and Q query

F the query

F query can obtain the data Value of a field in the table, especially suitable for comparison between two fields in the table. When operating data of character type, F cannot directly concatenate strings, but needs to use Concat and Value.

from django.db.models import F

# 1. Query the number of books sold than the number of books in stock
res = models.Book.objects.filter(sell_num__gt=F('inventory_num'))

# Raise the price of all books by 20 yuan
res = models.Book.objects.update(price=F('price') +20)
Copy the code

F query operations on strings require two methods: Concat and Value:

Add the word hot after the title of all books
from django.db.models.functions import Concat
from django.db.models import F, Value

models.Book.objects.update(name=Concat(F('name'),Value('hot style')))
Copy the code

Q query

When filter() is used to filter conditions, logic and operation is adopted. If you want to change the relationship between multiple filtering conditions to OR or not, you need to use Q query. In query Q | says the or relationship, ~ said not relationship.

import django.db.models import Q

Select * from books where the number of books sold is greater than 100 or the price is less than 20
res = models.Book.objects.filter(~Q(sell_num__gt=100) | Q(price__lt=20))
Copy the code

Another more advanced use of Q queries is to change the left side of the query criteria to a string.

Create an instance of Q first
q = Q()
# change the connection condition of q
q.connector = 'or'
q.children.append(('sell_num__gt'.100))
q.children.append(('price__lt'.200))
res = models.Book.objects.filter(q)
# filter: Q instantiates the object. Each condition defaults to and and can be modified
print(res)
Copy the code

Django starts a transaction

MySQL has a transaction mechanism to ensure data security. Django can connect to MySQL so that Django can support MySQL transactions. The following code is used to start a transaction in Django:

from django.db import transaction

try:
    with transaction.atomic():		 All ORM operations written in the with express belong to the same transaction.except Exception as e:
    print(r)
...
Copy the code

conclusion

The article was first published in the wechat public account Program Yuan Xiaozhuang, at the same time in nuggets.

The code word is not easy, reprint please explain the source, pass by the little friends of the lovely little finger point like and then go (╹▽╹)