Data preparation
# Build the table structure
from import models
# table app01_publish
class Publish():
name = (max_length=20)
addr = (max_length=20)
# Table app01_author_detail
class Author_Detail():
tel = (max_length=20)
# table app01_author
class Author():
name = (max_length=20)
age = ()
# table app01_author one-to-one table app01_authordetail
detail = (to='Author_Detail',to_field='id',unique=True,on_delete=)
# table app01_book
class Book():
title = (max_length=20)
price = (max_digits=8, decimal_places=2)
pub_date = (auto_now_add=True)
# table app01_book many to one table app01_publish, parameter to specifies the model name, parameter to_field specifies the field to be associated with
publish = (to='Publish',to_field='id',on_delete=)
# When we write our own sql, for the multiple pairs of relationship between the books table and the authors table, we need to create a new table by ourselves, and based on django's orm, the following line of code can help us automatically create that relationship table
authors=(to='Author')
# If the variable is named authors, the new table is named app01_book_authors, if the variable is named xxx, the new table is named app01_book_xxx
# Add data
import os
if __name__ == "__main__":
("DJANGO_SETTINGS_MODULE", "book_sys.settings")
import django
()
from import *
# 1, first add the author_detail table pubulish table with no foreign key fields.
Author_Detail.(tel='123456789')
Author_Detail.(tel='987654321')
Author_Detail.(tel='000000000')
(name='Northern Publishing House',addr='Beijing')
(name='Southern Publishing House',addr='Nanjing')
(name='Oriental Publishing House',addr='Shanghai')
(name='Western Publishing House',addr='Xi'an')
# 2. add author table book table
(name='frank',age=31 ,detail_id=1)
(name='lili',age=29 ,detail_id=2)
(name='tank',age=42 ,detail_id=3)
(title='Romance of the Three Kingdoms',price=200 ,publish_id=1)
(title='Three Kingdoms',price=198.5 ,publish_id=2)
(title='Dream of the Red Chamber',price=255.43 ,publish_id=2)
(title='Journey to the West',price=300.5 ,publish_id=3)
(title='The West Wing',price=213.4 ,publish_id=4)
(title='Water Margin',price=199 ,publish_id=1)
# 3. Finally, manipulate the author_book table. Since the ManyToMany field is used to automatically generate the author_book table, manipulate the author_book table based on the table where the foreign key is located.
book_obj1=(pk=1).first()
book_obj1.(1,2)
book_obj2 = (pk=2).first()
book_obj2.(1)
book_obj3 = (pk=3).first()
author_obj1 = (pk=1).first()
author_obj2 = (pk=2).first()
book_obj3.(author_obj1,author_obj2)
book_obj4 = (pk=4).first()
book_obj4.(3,2)
book_obj5 = (pk=5).first()
book_obj5.(3)
book_obj6 = (pk=6).first()
book_obj6.(1,3)Forward and Reverse Queries
A publisher and books as an example, the book table contains the foreign key field of the publisher table
Positive Query>>> Book Query Publisher
Reverse Lookup>>> Publisher Lookup Books
To summarize: whether the current query object contains a foreign key field, yes is a forward query, no is a reverse query
Forward Query by Field, Reverse Query by Table Name
Object-based cross-table queries
Equivalent to a subquery in MySQL: the results of a table query are enclosed in parentheses and used as a condition for another SQL statement .
forward looking (computing)
many-to-one (game)
Query the name of the publisher whose primary key is 5
1. Check the book object whose primary key is 5.
2. publish gets the publisher object based on the foreign key field of the book object.
3. Getting the name from the publisher's object
book_obj = (pk=5).first() res = book_obj.publish print(res) # Publish object print() # Occidental Press
many-to-many
Query the author name of a book with a primary key of 3
1. Query the book object whose primary key is 3.
2. foreign key field in the book table, the same is the forward query, then just according to the field autjors query can be
3. Get the name of the author object
book_obj = (pk=3).first() res = book_obj.authors print(res) #
Note: Since the authors field is a many-to-many foreign key field, further manipulation of the object is required.
book_obj = (pk=3).first() res = book_obj.authors res1 = book_obj.() print(res1) # <QuerySet [<Author: Author object>, <Author: Author object>]>
Then just for loop the respective names
one-to-one
Look up the author's lili's number
1. Query author object
2, foreign key field in the author table, the same is a forward query, then just follow the dictionary detail query can be
3. Getting the tel of the detail object
author_obj = (name='lili').first() res = author_obj.detail print(res) print()
Reverse Query One-to-Many
Search for books published by Oriental Publishing House
1. First get the object of the Oriental Publishing House
2. Publishing house does not have a foreign key field, to find books is the reverse query
3. Table name lowercase_set.all()
4. Access to book titles
publish_obj=(name='Oriental Publishing House').first() print(publish_obj) # Publish object res = publish_obj.book_set print(res) # res1 = () print(res1) # <QuerySet [<Book: Book object>]> for obj in res1: print()
many-to-many
Search for books written by author lili
1. Get the author object
2. the author table does not have a foreign key to the books table, so it is a reverse query
3. .book_set.all() get book object
4. Get the name of the book object again
author_obj = (name='lili').first() res = author_obj.book_set print(res) # res1 = () print(res1) # <QuerySet [<Book: Book object>, <Book: Book object>, <Book: Book object>]> for obj in res1: print(, end='') # Three Kingdoms period (220-280) in Chinese history,The Dream of the Red Chamber (Suzhou, Jiangsu province),also called Pilgrimage the West or Monkey,
one-to-one
Author with query number 0000000
1. Search for author object details
2. foreign key field in the author table, the same is the testimony of Ang query, then only according to the field detail query can be
3. Getting the tel of the detail object
detail_obj = Author_Detail.(tel='000000000').first() print(detail_obj) # Author_Detail object res = detail_obj.author print(res) # Author object print() # tank
Methodological summary
forward looking (computing)
One-to-one and one-to-many are the same, you can find the object directly by querying the fields --- author_obj.detail
One-to-many , according to the field query will return a .None object, need to add .all()>>> book_obj.() after the field.
reverse inquiry
one-to-many, many-to-many method is the same, according to the table name lowercase, in accordance with the follow_set.all()>>>author_obj.book_set.all()
One to one, then directly according to the indication of lowercase, can directly get >> > detail_obj.author
When a query ends in .None, you need to follow the original query method with .all() to get the desired result.
Cross-table query based on double underlining
Equivalent to a concatenated table query in MySQL: concatenate two tables or reconciled tables into a single table for querying.
forward looking (computing)
Forward query, according to the associated field + double underline >>> .values('associated field ___ in the associated table'), the return is a QuerySet object
one-to-one
Check author frank's cell phone number
res = (name='frank').values('detail__tel')
print(res)
# <QuerySet [{'detail__tel': '123456789'}]>
print(())
# {'detail__tel': '123456789'}many-to-one (game)
Search for the name of the publisher of the Three Kingdoms
res = (title='Romance of the Three Kingdoms').values('publish__name')
print(res)
print(())
# <QuerySet [{'publish__name': 'Northern Publishing House'}]>
# {'publish__name': 'Northern Publishing House'}many-to-many
Query all authors of Three Kingdoms
res = (title='Romance of the Three Kingdoms').values('authors__name')
print(res)
# <QuerySet [{'authors__name': 'frank'}, {'authors__name': 'lili'}]>reverse inquiry
By model name (lowercase)+double underline>>> .values('Table name lowercase __ fields in the associated table'), returns a QuerySet object.
one-to-one
Query author name with cell phone number '123456789'
res = Author_Detail.(tel='123456789').values('author__name')
print(res)
# <QuerySet [{'author__name': 'frank'}]>many-to-one (game)
Check the names of all books published by Northern Publishing
res = (name='Northern Publishing House').values('book__title')
print(res)
# <QuerySet [{'book__title': 'Romance of the Three Kingdoms'}, {'book__title': 'Water Margin'}]>many-to-many
Search all books published by lili
res = (name='lili').values('book__title')
print(res)
# <QuerySet [{'book__title': 'Romance of the Three Kingdoms'}, {'book__title': 'Dream of the Red Chamber'}, {'book__title': 'Journey to the West'}]>Summary of methodology.
Forward query, by associated field: .values('associated field __ field in associated table'), returns a QuerySet object
By model name (lowercase) + double underline: .values('Table name lowercase __ fields in the associated table'), the return is a QuerySet object, the object holds a dictionary type of data
Double Down Higher Order Forward and Reverse Queries
Double underlined queries using filter()
The first thing you need to consider is whether it is a forward or reverse query, determine the method to be used inside the parentheses, but outside the parentheses instead of using values, use filters!!!!
Note: fields cannot be quoted using the filter method, values need to be quoted.
Query book primary key is reversed, use .filter('Table name lowercase __ field in the table being associated with')
res = (book__pk= 4) print(res) # <QuerySet [<Publish: Publish object>]> res = (book__title= 'Journey to the West') print(res) # Same results
Consecutive queries across multiple tables
Routine and the above case are the same, you can practice n double underscores, just double underline each time, to determine whether each double underline is followed by a forward query or reverse query can be
# Requirement 1: Search for the names of all books published by Beijing Publishing House, as well as the names and cell phone numbers of the authors.
# Mode 1: Base table is Publish
res=(name='Northern Publishing House').values_list('book__title','book__authors__name','book__authors__author_detail__tel')
# Mode 2: Base table as Book
res=(publish__name='Northern Publishing House').values_list('title','authors__name','authors__author_detail__tel')
# The cyclic printouts are all
for obj in res:
print(obj)
# Requirement 2: Search for the names of all books published by authors whose cell phone numbers begin with 186 and the name of the publisher.
# Mode 1: base table is AuthorDetail
res=(tel__startswith='186').values_list('author__book__title','author__book__publish__name')
# Mode 2: Base table as Book
res=(authors__author_detail__tel__startswith='186').values_list('title','publish__name')
# Mode 3: base table is Publish
res=(book__authors__author_detail__tel__startswith='186').values_list('book__title','name')
# The cyclic printouts are all
for obj in res:
print(obj)
This article on Django ORM multi-table query is introduced to this article, more related Django ORM multi-table query content please search for my previous posts or continue to browse the following related articles I hope you will support me in the future more!