Shulou(Shulou.com)06/02 Report--

This article shows you how to do ORM multi-table operation, the content is concise and easy to understand, can definitely brighten your eyes, through the detailed introduction of this article, I hope you can get something.

1. Create the table from django.db import models# Create your models here.class AuthorDtail (models.Model): nid = models.AutoField (primary_key=True) birthday = models.DateField () telephone = models.BigIntegerField () addr = models.CharField (max_length=64) class Author (models.Model): nid = models.AutoField (primary_key=True) name = models.CharField (max_length=32) age = models.IntegerField () # OneToOneField can be built in two models Any authorDetail = models.OneToOneField (to= "AuthorDtail") To_field= "nid", on_delete=models.CASCADE) class Publish (models.Model): nid = models.AutoField (primary_key=True) name = models.CharField (max_length=32) city = models.CharField (max_length=32) email = models.EmailField () class Book (models.Model): nid = models.AutoField (primary_key=True) title = models.CharField (max_length=32) publishDate = models.DateField () price = models.DecimalField (max_digits=9 Decimal_places=2) # establish an one-to-many relationship with Publish. Foreign key fields are established on the side of many publish = models.ForeignKey (to= "Publish", to_field= "nid", on_delete=models.CASCADE) # many-to-many ManyToManyField can be built on either of the two models, automatically creating a third table authors = models.ManyToManyField (to= "Author")

MySQL View Table creation statement

CREATE TABLE `PRIMARY KEY authordetail` (`nid` int (11) NOT NULL AUTO_INCREMENT, `telephone` date NOT NULL, `telephone` bigint (20) NOT NULL, `addr` varchar (64) NOT NULL, PRIMARY KEY (`nid`) ENGINE=InnoDB DEFAULT CHARSET=utf8;#UNIQUE KEY `authorDetail_ id` (`authorDetail_ id`), which is created by OneToOneField. CREATE TABLE `app1_ author` (`nid` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (32) NOT NULL, `age` int (11) NOT NULL, `authorDetail_ id` int (11) NOT NULL, PRIMARY KEY (`nid`), UNIQUE KEY `authorDetail_ id` (`authorDetail_ id`), CONSTRAINT `app1_author_authorDetail_id_d894fd2a_fk_app1_authordetail_ nid` FOREIGN KEY (`authorDetail_ id`) REFERENCES `app1_ detail` (`nid`) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `app1_ publish` (`nid` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (32) NOT NULL, `city` varchar (32) NOT NULL, `email` varchar (254) NOT NULL, PRIMARY KEY (`nid`) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `app1_ book` (`nid` int (11) NOT NULL AUTO_INCREMENT, `title` varchar (32) NOT NULL, `publishDate` date NOT NULL, `price` decimal (9) NOT NULL, `publish_ id`NOT NULL, PRIMARY KEY (`nid`), KEY `app1_book_publish_id_ e41ee7e4` (`publish_ id`), CONSTRAINT `app1_book_publish_id_e41ee7e4_fk_app1_publish_ nid` FOREIGN KEY (`publish_ id`) REFERENCES `app1_ publish` (`nid`) ENGINE=InnoDB DEFAULT CHARSET=utf8) # third-party tables created by ManyToManyField: CREATE TABLE `app1_book_ authors` (`id` int (11) NOT NULL AUTO_INCREMENT, `book_ id` int (11) NOT NULL, `author_ id` int (11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `app1_book_authors_book_id_author_id_ce887e61_ uniq` (`book_ id`, `author_ id`), KEY `app1_book_authors_author_id_89b9ee26_fk_app1_author_ nid` (`author_ id`) CONSTRAINT `app1_book_authors_author_id_89b9ee26_fk_app1_author_ nid` FOREIGN KEY (`author_ id`) REFERENCES `app1_ author` (`nid`), CONSTRAINT `app1_book_authors_book_id_75b281cd_fk_app1_book_ nid` FOREIGN KEY (`book_ id`) REFERENCES `app1_ book` (`nid`) ENGINE=InnoDB DEFAULT CHARSET=utf8 1. The name of the table is app_modelName, which is automatically generated based on the metadata in the model, or it can be rewritten to another name. two。 If AutoField () is not defined, the id field is created by default as the primary key. 3. For foreign key fields, Django adds "_ id" to the field to set the column name. 4.Django uses the appropriate SQL statement based on the database type specified in settings. 5. To set the application name of models.py in INSTALL_APPS 6. 0. The foreign key field ForeignKey has a null=True setting (it allows foreign keys to accept null NULL), and you can assign it null None. two。 Addition, deletion, modification and query of multi-table ORM 2.1 insert data

2.1.1 insert data from django.shortcuts import renderfrom app1.models import * def index (request): # Note: this must be a model object, not a queryset object # method 1: set publish, it is a Model object. Publish_obj = Publish.objects.get (nid=1) aa_book_obj = Book.objects.create (title= "AA_title", publishDate= "2012-12-12", price=100,publish=publish_obj) # method 2: set publish_id bb_book_obj = Book.objects.create (title= "BB_title", publishDate= "2012-12-12", price=100 Publish_id=2) print (aa_book_obj.title) print (aa_book_obj.publish.name) print (bb_book_obj.title) print (bb_book_obj.publish.name) # the key here is the difference between publish and publish_id. # as you can see, # publish is an object You can call the field data in the corresponding Publish table through publish # publish_id is just a field value in the Book table print (bb_book_obj.publish.nid) print (bb_book_obj.publish_id) return render (request, "index.html") query the publisher mailbox book_obj=Book.objects.filter (title= "AA_title"). First () print (book_obj.publish.email) whose title is AA_title.

2.1.2 many-to-many insert data from django.shortcuts import renderfrom app1.models import * def index (request): # bind many-to-many relationship cc_book_obj = Book.objects.create (title= "CC_title", publishDate= "2018-12-12", price=120 Publish_id=1) vita = Author.objects.get (name= "vita") lili = Author.objects.get (name= "lili") # method 1: add-author object cc_book_obj.authors.add (vita,lili) # method 2: add-author_id dd_book_obj = Book.objects.create (title= "DD_title", publishDate= "2018-12-12", price=120 Publish_id=1) dd_book_obj.authors.add (1d2) # method 3: add-* [1je 2je 3] ee_book_obj = Book.objects.create (title= "EE_title", publishDate= "2018-12-12", price=120, publish_id=1) ee_book_obj.authors.add (* [1jue 2]) return render (request, "index.html")

2.1.3 many-to-many key points from django.shortcuts import renderfrom app1.models import * def index (request): ee_book_obj = Book.objects.filter (title= "EE_title"). First () # key: ee_book_obj.authors.all () # the collection of all author information associated with this book is the queryset object # print (ee_book_obj.authors.all ()) return render (request, "index.html")

2.1.4 many-to-many unbinding book_obj.authors.remove () # removes a particular object from the collection of associated objects. = book_obj.authors.remove (* []) book_obj.authors.clear () # empty the collection of associated objects book_obj.authors.set () # empty and then set from django.shortcuts import renderfrom app1.models import * def index (request): vita = Author.objects.filter (name= "vita"). First () aa_book_obj = Book.objects.filter (title= "CC_title"). First () # method 1: remove-author object aa_book_obj.authors.remove (vita) # method 2: remove-author 's nid aa_book_obj.authors.remove (2) bb_book_obj = Book.objects.filter (title= "DD_title"). First () # method 3: remove-* [1 bb_book_obj.authors.remove 2] bb_book_obj.authors.remove (* [1 minute 2]) return render (request) "index.html")

2.2 Cross-table query 1 based on object query 2 based on double underscore query 3 aggregation and grouping query 4 F and Q query 2.2.1 forward query and reverse query one-to-many relationship, models.ForeignKey is set in book to query author information according to book table, forward query queries book information according to author table, reverse query many-to-many relationship, models.ManyToMany is set in book table to query author information according to book table Is the forward query according to the author table to query book information, is the reverse query one-to-one relationship, models.OneToOne is set in the author table, query authoDetail information according to the author table, is the forward query according to the authorDetail table query quthor information, is the reverse query code: want to query by field reverse query by table name: forward query by field, the associated fields set in models.py, that is, publish and author in the authorDetail,Book class in the Author class. (one-to-many, many-to-many) reverse query lowercase _ set---- by table name because it is multiple data, so use set, meaning collection. (one-to-one) reverse queries are lowercase by table name-because there is only one corresponding piece of data, there is no set. 2.2.2 query based on object-subquery rules: you want to query by field reverse query by table name: forward query by field, the associated fields set in models.py, that is, publish and author in authorDetail,Book class in Author class. (one-to-many, many-to-many) reverse query lowercase _ set---- by table name because it is multiple data, so use set, meaning collection. (one-to-one) reverse queries are lowercase by table name-because there is only one corresponding piece of data, there is no set. 2.2.2.1 prepare data

2.2.2.2 object-based query-one-to-many query from django.shortcuts import renderfrom app1.models import * def index (request): # one-to-many forward query: query the name of the publisher of the book Romance of the three Kingdoms # A book corresponds to only one publisher The return value is a model object publish_obj = Book.objects.filter (title= "SanGuo"). First (). Publish print (publish_obj) # Publish object (1) print (publish_obj.name) # AA Publishing House # SQL sentence: # one-to-many reverse query: query the names of books published by AA_ publishers # one publishing house corresponds to multiple books The return value is a queryset collection book_list = Publish.objects.filter (name= "AA_publish") .first () .book_set.all () print (book_list) # # SQL statement: return render (request, "index.html")

2.2.2.3 object-based query-many-to-many query from django.shortcuts import renderfrom app1.models import * def index (request): # many-to-many forward query: query all the author names of the book Sanguo. The book and the author have a many-to-many relationship, and the return value is the queryset collection. # author_list = Book.objects.filter (title= "SanGuo"). First (). Authors.all () print (author_list) # many-to-many reverse query: query the titles of all books published by vita. The book and author are many-to-many, and the return value is the queryset collection. # book_list = Author.objects.filter (name= "vita"). First (). Book_set.all () print (book_list) return render (request, "index.html")

2.2.2.4 object-based query-one-to-one query from django.shortcuts import renderfrom app1.models import * def index (request): # one-to-one forward query: query vita's mobile phone number # one-to-one relationship The return value is the model object # AuthorDetail object (1) author_detail_obj = Author.objects.filter (name= "vita"). First (). AuthorDetail print (author_detail_obj) # one-to-one reverse query: query the author # one-to-one relationship on mobile number 112233 The return value is the model object # Author object (1) author_obj = AuthorDetail.objects.filter (telephone= "112233") .first () .author print (author_obj) return render (request, "index.html")

2.2.3 Cross-table query based on double underscore-join query rules: want to query by field reverse query by table name 2.2.3.1 Cross-table query based on double underscore-one-to-many query from django.shortcuts import renderfrom app1.models import * def index (request): # one-to-many query: query SanGuo the book's publisher name # returned value is queryset object # party One forward query: ret = Book.objects.filter (title= "SanGuo"). Values ("publish__name") print (ret) # # method two reverse query: ret2 = Publish.objects.filter (book__title= "SanGuo"). Values ("name") print (ret2) # # one-to-many query: query the title of the book published by AA_publish Publishing House # the returned value is Queryset object # forward query: ret3 = Publish.objects.filter (name= "AA_publish"). Values ("book__title") print (ret3) # # reverse query: ret4 = Book.objects.filter (publish__name= "AA_publish"). Values ("title") print (ret4) # return render (request "index.html")

2.2.3.2 Cross-table query based on double underscores-many-to-many query from django.shortcuts import renderfrom app1.models import * def index (request): # many-to-many query: query the names of all authors in the book SanGuo # return value: queryset object # method 1: forward query ret1 = Book.objects.filter (title= "SanGuo"). Values ("authors__name") print (ret1) # # method 2: reverse query ret2 = Author.objects.filter (book__title= "SanGuo"). Values ("name") print (ret2) # return render (request) "index.html")

2.2.3.3 Cross-table query based on double underscores-one-to-one query from django.shortcuts import renderfrom app1.models import * def index (request): # one-to-one query: query the mobile number of vita # return value queryset object # method 1: forward query ret1 = Author.objects.filter (name= "vita"). Values ("authorDetail__telephone") print (ret1) # party Formula 2: reverse query ret2 = AuthorDetail.objects.filter (author__name= "vita"). Values ("telephone") print (ret2) # return render (request) "index.html")

2.2.3.4 query from django.shortcuts import renderfrom app1.models import * def index (request) based on double underscores across multiple tables: # the title of the book published by the author whose mobile number begins with 11 and the name of the publisher ret1 = Author.objects.filter (authorDetail__telephone__startswith= "11"). Values ("book__title") "book__publish__name") print (ret1) ret2 = AuthorDetail.objects.filter (telephone__startswith= "11"). Values ("author__book__title", "author__book__publish__name") print (ret2) ret3 = Book.objects.filter (authors__authorDetail__telephone__startswith= "11"). Values ("title", "publish__name") print (ret3) return render (request, "index.html")

2.2.4 aggregation and grouping query 2.2.4.1aggregate aggregate query returns the dictionary from django.shortcuts import renderfrom django.db.models import Avg,Max,Min,Countfrom app1.models import * def index (request): # query the average price of all books # the return value is the dictionary ret = Book.objects.filter (publishDate__year=2012) .aggregate (avg_price=Avg ("price"), max_price=Max ("price") Min_price=Min ("price") print (ret) # {'avg_price': Decimal (' 100.000000'), 'max_price': Decimal (' 100.0000'), 'min_price': Decimal (' 100.00')} return render (request, "index.html")

2.2.4.2annotate grouping query-single table grouping # annotate grouping query # ORM syntax for single table grouping query: single table model .objects.values ("group by field"). Annotate (aggregate function ("statistical field")) # return value is queryset object # supplementary knowledge point: # # ret=Emp.objects.all () # print (ret) # select * from emp # ret=Emp.objects. Values ("name") # print (ret) # select name from emp # grouped by id under a single table It doesn't make any sense, here all () contains all the fields, including the primary key id,id is unique Grouping according to this field does not make any sense # Emp.objects.all (). Annotate (avg_salary=Avg ("salary")) from django.shortcuts import renderfrom django.db.models import Avg,Max,Min,Countfrom app1.models import * def index (request): # query the price and quantity of books at each price # ret = Book.objects.values ("price") .annotate (book_count = Count ("nid"). Values ("title") # when grouped in a single table Finally, add values () to add another grouping field, instead of taking out the field value ret = Book.objects.values ("price") .annotate (book_count = Count ("nid")) print (ret) # return render (request, "index.html")

2.2.4.3annotate grouping query-multi-table grouping from django.shortcuts import renderfrom django.db.models import Avg,Max,Min Countfrom app1.models import * def index (request): # query the name of each publisher and the number of books published # reverse query ret1 = Publish.objects.values ("name") .annotate (book_count=Count ("book__nid")) print (ret1) # # forward query ret2 = Book.objects.values ("publish__name") .annotate (book_count=Count ("nid")) Print (ret2) # # forward query Finally, values takes the value ret3 = Book.objects.values ("publish__nid") .annotate (book_count=Count ("nid")) .values ("publish__name", "book_count") print (ret3) # return render (request, "index.html")

From django.shortcuts import renderfrom django.db.models import Avg,Max,Min,Countfrom app1.models import * def index (request): # summarize the model of cross-table grouping queries: # each subsequent table model. Objects.values ("contention"). Annotate (aggregate function (associated table _ _ statistical field) # contention is the primary key # 1. Query the name of each author and the highest price of published books ret1 = Author.objects.values ("competition") .annotate (max_price = Max ("book__price"). Values ("name", "max_price") print (ret1) # 2. Query the name of each book and the corresponding number of authors ret2 = Book.objects.values ("competition") .annotate (author_count = Count ("authors__nid"). Values ("title", "author_count") print (ret2) return render (request, "index.html")

From django.shortcuts import renderfrom django.db.models import Avg,Max,Min,Countfrom app1.models import * def index (request): # query the name and number of books of each publishing house # three ways: ret1=Publish.objects.values ("nid"). Annotate (c=Count ("book__title"). Values ("name", "email") "c") print (ret1) ret2=Publish.objects.all (). Annotate (c=Count ("book__title"). Values ("name", "c", "city") print (ret2) ret3 = Publish.objects.annotate (c=Count ("book__title"). Values ("name", "c", "city") print (ret3) return render (request, "index.html")

From django.shortcuts import renderfrom django.db.models import Avg,Max,Min,Countfrom app1.models import * def index (request): # count the number of authors of each book beginning with S ret1 = Book.objects.filter (title__startswith= "S"). Values ("competition") .annotate (author_count = Count ("authors__nid")). Values ("title") "author_count") print (ret1) # counts books of more than one author ret2 = Book.objects.values ("competition") .annotate (author_count = Count ("authors__nid")) .filter (author_count__gt=1) .values ("title", "author_count") print (ret2) return render (request, "index.html")

2.2.5F query and Q query 2.2.5.1F query from django.shortcuts import renderfrom django.db.models import FMagneQ from app1.models import * def index (request): # 1.F query Compare the values of the two fields ret1 = Book.objects.filter (price__gt=F ("publish_id")) print (ret1) # 2.F addition, subtraction, multiplication, division and modularization between the query and the constant ret2 = Book.objects.filter (price__gt=F ("publish_id") * 100) print (ret2) # 3. Support batch update of Book.objects.all () .update (price=F ("price") + 30) return render (request, "index.html")

2.2.5.2Q query from django.shortcuts import renderfrom django.db.models import Fleming Qfrom app1.models import * def index (request): # 1.Q query can be used for "or" query. Filter () method is "and" query ret1 = Book.objects.filter (Q (authors__name= "vita") | Q (authors__name= "lili")) print (ret1) # 2. It can be combined with & and ~ (inverted) # the author's name is Vita ret2 ret2 = Book.objects.filter (Q (authors__name= "vita") & Q (authors__age=23)) print (ret2) # 3. Query functions can mix Q objects and keyword parameters. The keyword parameter should be placed at the end of ret3 = Book.objects.filter (Q (authors__age=23) | Q (publishDate__year=2012), title__startswith= "S") print (ret3) return render (request, "index.html")

The above content is how to operate ORM multi-meter. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.