There are three table relationships: one-to-one, one-to-many, many-to-many.
1. Create a model
- OneToOneField () one to one
- ForeignKey() one-to-many many-to-one
- Many-to-many ManyToManyField ()
# add foreign key one-to-many, many-to-one
class Book(models.Model) :
name = models.CharField(max_length=32)
# add ForeignKey ForeignKey
publishers = models.ForeignKey(Publisher, on_delete=models.CASCADE) The default is cascading deletion
""" on_delete=None, # Delete =models.CASCADE, On_delete =models.DO_NOTHING, on_delete=models. Error ProtectedError on_delete= models.set_null, On_delete = models.set_default, On_delete =models.SET, # delete associated data, a. The associated value is SET to the specified value, SET: models.set (value) b. The associated value is SET to the return value of the executable, SET: models.set (executable) ""
Copy the codeclass Author(models.Model) :
name=models.CharField( max_length=32)
age=models.IntegerField()
authorDetail=models.OneToOneField(to="AuthorDetail",on_delete=models.CASCADE)
# One-to-one to details table
# foreign+unique tofield will be automatically associated with the primary key
# on_delete = models.CASCADE The key that is associated with it is deleted
# on_delete = models.SET_NULL After the associated delete, the associated becomes null
class AuthorDetail(models.Model) :# Put infrequently used items in this table
birthday=models.DateField()
# telephone=models.BigIntegerField()
telephone=models.CharField(max_length=32)
addr=models.CharField( max_length=64)
There is a one-to-many relationship between the publishers table and the books table
class Publish(models.Model) :
name=models.CharField( max_length=32)
city=models.CharField( max_length=32)
email=models.EmailField() # is actually CharField - but can be verified later in the format xx@xx.
class Book(models.Model) :
title = models.CharField( max_length=32)
publishDate=models.DateField()
price=models.DecimalField(max_digits=5,decimal_places=2)
Publish is an int. Publish is a foreign key. Publish is an int
publishs=models.ForeignKey(to="Publish",to_field="id",on_delete=models.CASCADE)
# foreign key(publish) references publish(id)
authors=models.ManyToManyField(to='Author'.)# author is not a field in the table, a property of the knowledge class, used to operate on the third table below, and can be written in the associated table
# manytomany automatically generates the following table, if there are other fields you can create the table manually
# class BookToAuthor(models.Model):
# book_id = models.ForeignKey(to="Book",to_field="id",on_delete=models.CASCADE)
# author_id = models.ForeignKey(to="Author",to_field="id",on_delete=models.CASCADE)
Copy the codeDjango admin –
- Create a superuser
python manage.py createsuperuser
Username (leave blank to use 'bee'): root Enter a user name. The default is bee
Email address: # Email
Password: # enter root1234
Password (again): #root1234
Superuser created successfully.
Copy the code2. Register model in admin.py of app
from django.contrib import admin
from app01 import models
# Register your models here.
admin.site.register(models.Person) The table name for the operation
admin.site.register(models.User) The table name for the operation
Copy the code- The login page at http://127.0.0.1:8000/admin/
After login, enter the admin page, and you can operate the database by entering the user root and password root1234Copy the codeViews.py operates through models
increase
from django.shortcuts import render,HttpResponse,redirect
from app01 import models
# Create your views here.
def query(request) :
# increase
# One-to-one increase
new_author_detail = models.AuthorDetail.objects.create(
birthday='1979-08-08',
telephone='13456789876',
addr='Harbin, Heilongjiang'.)Way # 1
models.Author.objects.create(
name='carter',
age=18,
authorDetail=new_author_detail,
)
# Method 2 is commonly used
obj = models.AuthorDetail.objects.filter(addr='Linfen, Shaanxi').first()
models.Author.objects.create(
name='carter',
age=18,
authorDetail_id=obj.id.)# one-to-many
Way # 1
models.Book.objects.create(
title='Zuan Quick Talker',
publishDate='2019-08-09',
price=3,
publishs=models.Publish.objects.get(id=1),# Method 2 is commonly used
models.Book.objects.create(
title='Zuan Quick Talker 2',
publishDate='2019-08-09',
price=3,
publishs_id=models.Publish.objects.get(id=1).id.)# many-to-many
# Method 1 is commonly used
book_obj = models.Book.objects.get(id=1)
book_obj.authors.add(*[1.2])
Way # 2
author1 = models.Author.objects.get(id=1)
author2 = models.Author.objects.get(id=3)
book_obj_ = models.Book.objects.get(id=5)
book_obj_.authors.add(*[author1, author2])
return HttpResponse('ok')
Copy the codedelete
# remove
The one-to-one and many-to-one cases are the same as the single-table drop
# one-to-one
# Author is also deleted by cascade
models.AuthorDetail.objects.get(id=2).delete()
Deleting author has no effect on AuthorDetail
models.Author.objects.get(id=1).delete()
# one-to-many
# Delete publishing house, all the books under this publishing house are deleted in cascade
models.Publish.objects.get(id=1).delete()
# Deleting a book will not affect the publisher
models.Book.objects.get(id=2).delete()
# many-to-many
Select * from book_author; select * from book_author; select * from book_author; select * from book_author
book_obj_1 = models.Book.objects.get(id=3)
book_obj_1.authors.remove(*[4.5])
# Method 2 is to find the author object and put it in the list
book_obj_1.authors.clear() Select * from author where id=3
book_obj_1.authors.set(['5'.'6']) Add a 5 and a 6
Copy the codechange
# update
# one-to-one
# change the author with id=2
models.Author.objects.filter(id=2).update(
name='Urgat',
age=18.# authorDetail=models.AuthorDetail.objects.get(id=5),
authorDetail_id=5.# Two ways
)
# one-to-many
# change the publisher of the book whose id=4 to 3
models.Book.objects.filter(pk=4).update( Primary key field of table pk
title='Brother B's Past'.# publishs=models.Publish.objects.get(id=3),
publishs_id=3.# Two ways
)
SQL > select * from orM; mysql > select * from orM
Copy the codecheck
def books_list(request) : Get the contents of the database
all_books = models.Book.objects.all(a)for i in all_books:
print(i.publishers.name, i.publishers.id,i.publishers_id) # nam is the publisher object associated with the book
return render(request, "books_list.html", {"all_books": all_books})
Copy the codeThe query
Object-based cross-table queries – similar to subqueries
Forward and reverse query
The query to the constrained table by setting the constraint is a forward query – the query to the child from the thigh
A reverse lookup is a reverse query
Forward query: a relational attribute (field) unloads a class (table) from the current class (table) to query its associated class (table) data.
Reverse query: The reverse is true.
# One-on-one #Is the query# query galen's address
author_obj = models.Author.objects.filter(name='galen').first()
print(author_obj.authDetail.addr) Who is the number of this AuthorDetail# reverse query # query 444
author_detail_obj = models.AuthorDetail.objects.get(telephone='444')print(author_detail_obj.author.name)# Author ---------> AuthorDetail forward queryAuthor_obj.authdetail.addr, object. Associated with the name# Author <--------- AuthorDetail Reverse queryAuthor_detail_obj.author. name, object. Small class nameLi Shuai's bedtime story is published by the publisher of this book
book_obj = models.Book.objects.get(title='Li Shuai's Bedtime Story')
print(book_obj.publishs.name) What books have been published by # Bge Press?
pub_obj = models.Publish.objects.get(name='B Brother Press ')
print(pub_obj.book_set.all()) QuerySet# Book -----------> Publish forward query book_obj.publishs.name Attribute # Book < -- -- -- -- -- -- -- -- -- -- - the Publish reverse inquiries pub_obj book_set. All () object. Who wrote this book?
book_obj = models.Book.objects.get(title='Li Shuai's Bedtime Story')
print(book_obj.authors.all())What books did Galen write?
author_obj = models.Author.objects.get(name='galen')
print(author_obj.book_set.all())# Book ---------------> Author forward query
book_obj.authors.all() object. Property# Book <--------------- Author reverse query
pub_obj.book_set.all() object. Lowercase class name _set
Copy the codeCross-table queries based on double underscores
# High efficiency of cross table query based on double underline
# one-to-one
# 1. Query Galen's phone number
Mode 1 is forward
obj = models.Author.objects.filter(name='galen').values('authorDetail__telephone')
print(obj) # QuerySet type
# Method 2 Reverse
obj = models.AuthorDetail.objects.filter(author__name='galen').values('telephone'.'author__age')
print(obj) Query which teacher's phone number is 444? Towards #
obj = models.Author.objects.filter(authorDetail__telephone='444').values('name')print(obj)
# reverse query
obj = models.AuthorDetail.objects.filter(telephone='444').values('author__name')
print(obj)Which publisher is this book?
obj = models.Book.objects.filter(title='Li Shuai's Bedtime Story').values('publishs__name')
print(obj)
obj = models.Publish.objects.filter(book__title='Li Shuai's Bedtime Story').values('name')
print(obj)
What books have been published by # B Press?
obj = models.Publish.objects.filter(name='B Brother Press ').values('book__title')
print(obj)
obj = models.Book.objects.filter(publishs__name='B Brother Press ').values('title')
print(obj)
# Many-to-many # Li Shuai's Bedtime Story Who wrote this book?
obj = models.Book.objects.filter(title='Li Shuai's Bedtime Story').values('authors__name')
print(obj)
obj = models.Author.objects.filter(book__title='Li Shuai's Bedtime Story').values('name')
print(obj)
# Galen wrote those books
obj = models.Book.objects.filter(authors__name='galen').values('title')
print(obj)
obj = models.Author.objects.filter(name='galen').values('book__title')
print(obj)
Copy the codeAdvanced queries
# advanced query
Select * from books where author's name and name are available.
obj = models.Book.objects.filter(publishs__name='B Brother Press ').values('title'.'authors__name')
print(obj)
# Author AuthorDetail Book Publish
obj = models.Author.objects.filter(authorDetail__telephone__startswith='4').values('book__title'.'book__publishs__name')
print(obj)
# models.py calss, ForeignKey alias # publishs= models.foreignkey (to="Publish",to_field="id",on_delete=models.CASCADE,related_name=' XXX ')
Mysql > alter table name (' XXX '); mysql > alter table name (' XXX '); mysql > alter table name (' XXX '
Copy the codeAggregate query aggregate
Aggregate () is a termination clause of QuerySet, meaning that it returns a dictionary of key-value pairs.
The name of the key is the identifier of the aggregate value, which is the calculated aggregate value. The key names are automatically generated from the field and aggregate function names
from django.db.models
import Max, Min, Count, Sum, Avg
# {'price__max': Decimal('999.00')}
ret = models.Book.objects.all().aggregate(Max("price"))
Get the maximum value of price in the Book table
{'price__max': Decimal('999.00'), 'price__min': Decimal('1.00')
ret = models.Book.objects.all().aggregate(Max("price"), Min("price"))
# {' Max ': Decimal('999.00'), 'min': Decimal('1.00')}
ret = models.Book.objects.all().aggregate(max=Max("price"), min=Min("price"))
# set an alias # {' Max ': a Decimal (' 999.00'), 'min' : a Decimal (' 22.00 ')}
ret = models.Book.objects.filter(id__lte=5).aggregate(max=Max("price"), min=Min("price"))
# print(ret) # print(ret)
Copy the codeGroup by annotate
Group by
Grouping is used in # annotate
# Count the number of authors per book
{'name': 'Software Engineering DSDS'.'pub_id': 1Grouping} values: what said to ret = models. Book. Objects. Annotate (Count ("authors")).values("name"."pub_id")# for i in ret:#
print(i)
# Figure out how much each publisher sells the cheapest book for # Method 1:
ret = models.Publisher.objects.annotate(Min("book__price")).values()# for i in ret:#
print(i)
# Method 2:
ret = models.Book.objects.values("pub"."pub__name").annotate(Min("price"))
# group by pub_id pub_name
# for i in ret:
# print(i)
# Count books by more than one author
ret = models.Book.objects.annotate(cont=Count("authors")).filter(cont__gt=1)
# Two underscores
# for i in ret:
# print(i)
# Sort querySets by the number of authors in a book
ret = models.Book.objects.annotate(count=Count("authors")).order_by("-count") # plus - is in descending order
# for i in ret:
# print(i)
# query the total price of a book by an author
ret = models.Author.objects.annotate(sum=Sum("books__price")).values()
# for i in ret:
# print(i)
ret = models.Book.objects.values("authors"."authors__name").annotate(sum=Sum("price"))
# for i in ret:
# print(i)For more information: https://www.cnblogs.com/maple-shaw/articles/9403501.html
Copy the codeF and Q
from django.db.models import F,Q# F
ret = models.Book.objects.filter(kucun__lt=50).values("name"."kucun") #kucun books less than 50
ret = models.Book.objects.filter(sale__gt=F("kucun")).values("name"."sale"."kucun") # Books that sell more than Kucun
# for i in ret:
# print(i)
models.Book.objects.filter(id__lte=5).update(sale=F("sale") *5+10) Less than or equal to 5 # update id, let sale quantity * 5 + 10 # Q () ' ' '| or & with ~' ' '
ret = models.Book.objects.filter(Q(id__lt=4)|Q(id__gt=5)).values("name"."id") # id < 4 or ID > 5
ret = models.Book.objects.filter(Q(Q(id__lt=3)|Q(id__gt=5)) & Q(name__startswith="Zhang"))
ret = models.Book.objects.filter(Q(Q(id__lt=3)|Q(id__gt=5)) &~ Q(name__startswith="Zhang")).values("name"."id")for i in ret: print(i)
Copy the codeForeign key operation
1. Create tables in models.py and migrate them in the database
class Publisher(models.Model) :
name = models.CharField(max_length=32,verbose_name="Name of publisher")
def __str__(self) :
return "<Publisher object:{}-{}>".format(self.id,self.name)
class Book(models.Model) :
name = models.CharField(max_length=32,verbose_name="Title")
pub = models.ForeignKey("Publisher", on_delete=models.CASCADE, related_name="books", related_query_name="book")
def __str__(self) :
return "<Book object:{}-{}>".format(self.id,self.name)
Copy the codeObject-based query
Select * from Book where publisher = publisher
book_obj = models.Book.objects.get(pk=3)
Print (book_obj) print(book_obj)
print(book_obj.pub)
Print (book_obj.pub_id) print(book_obj.pub_id)
Select id from Publisher to get the Book table
pub_obj = models.Publisher.objects.get(pk=2) Obtain table 2 from Publisher
# 1. Related_name is the class name lowercase _setprint(pub_obj)
print(pub_obj.book_set, type(pub_obj.book_set)) Print (pub_obj.book_set.all()) print(pub_obj.book_set.all()
Print (pub_obj.books.all()) print(pub_obj.books.all()) Alias. All ()
Copy the codeField based query
ret = models.Book.objects.filter(pub__name="Tsinghua Press 2")
Select 'pub' from 'Book' where 'pub' = 'pub'
ret = models.Book.objects.filter(pub__name__contains="Tsinghua Press 2")
# according to the foreign key of Book table, include the name of "Tsinghua Press 2"
# do not specify related_name="books" is the class name lowercase do not specify related_query_name
ret = models.Publisher.objects.filter(book__name="Hadoop Big Data Mining")# specify related_name="books" The specified name does not specify related_query_name
ret = models.Publisher.objects.filter(books__name="Hadoop Big Data Mining")# specified related_query_name = "book"
ret = models.Publisher.objects.filter(book__name="Hadoop Big Data Mining")
Copy the codeMany-to-many operation
# many-to-many relationships between books and authors
author_obj = models.Author.objects.get(pk=1) # author-->author_books-->bookprint(author_obj.books) #
print(author_obj.books.all()) # Associated object collection 3- Software Engineering DSDS
book_obj = models.Book.objects.get(pk=4) Print (book_obj.author_set.all()) print(book_obj.author_set ())
ret = models.Book.objects.filter(author__name="Bill") # author--> author_books --> Book 4 -- Big data Visualization
ret = models.Author.objects.filter(books__name="Big Data Visualization") Print (author_obj.books.all())# set man-to-many relationship [id,id] [object,id,id] Object]author_obj.books.set([3,4,5]) # modify id=1 representation as 3,4,5
author_obj.books.set(models.Book.objects.filter(id_in=[1.2]))# add adds many-to-many relationships
author_obj.books.add(6) Add a magnum opus 6 to id=
author_obj.objects.add(models.Book.objects.filter(id__in=[5.6])) # remove remove many-to-many relationship
author_obj.books.remove(3.4) # Delete the representative works of author 1, 3 and 4
author_obj.books.remove(*models.Book.objects.filter(id__in=[5])) # Delete 5#clear Clear many-to-many relationships
author_obj.books.clear# create Creates a new object and establishes a relationship with the current object
author_obj.books.create(name="One two three four",pub_id=2)
Copy the codeAdd shortcut keys
File --> Settings --> Editor --> Live Templates --> Django -- click the + sign on the right --> Select Live Templates --> Enter the shortcut name in the Abbreviation --> Template text: enter the contentCopy the codeORM exercises
import os
import djangoos.environ.set
default("DJANGO_SETTINGS_MODULE"."orm_homework.settings")django.setup()
from app01 import models
# 1. Find all books with Boss Kim in the title
ret = models.Book.objects.filter(title__contains=Boss Kim)
# 2. Find books with 2018 publication dates
ret = models.Book.objects.filter(publish_date__year=2018)
# 3. Find titles with publication dates of 2017
ret = models.Book.objects.values("title").filter(publish_date__year=2017)
# 4. Find books that cost more than $10
ret = models.Book.objects.filter(price__gt=10)
# 5. Find titles and prices greater than $10
ret = models.Book.objects.values("title"."price").filter(price__gt=10)
# 6. Find books with an empty memo field
from django.db.models import Q
# you can use Q when you have more than one condition
ret = models.Book.objects.filter(Q(memo__isnull=True)|Q(memo=""))
# 7. Find a publisher in Beijing
ret = models.Publisher.objects.filter(city="Beijing")
# 8. Find publishers whose names start with Sand River
ret = models.Publisher.objects.filter(name__startswith="Sha")
# 9. Find all books published by Sand River Press
ret = models.Book.objects.filter(publisher__name="Shahe Press")
# 10. Find the highest price for a book published by each publisher
from django.db.models import Avg,Max,Min,Count,Sum
ret = models.Book.objects.annotate(max=Max("price")).values("publisher"."publisher__name"."max") Find publisher from book
ret = models.Publisher.objects.annotate(max=Max("book__price")).values("name"."max") Find books from Publisher
# 11. Find the name of each publisher and the number of books published
ret = models.Publisher.objects.annotate(count=Count("book")).values("name"."count")
# 12. Find authors with "small" in their names
ret = models.Author.objects.filter(name__contains="Small")
# 13. Find authors older than 30
ret = models.Author.objects.filter(age__gt=30)
# 14. Find authors with phone numbers starting with 155
ret = models.Author.objects.filter(phone__startswith="155")# 15. To find the phone number is 155 at the beginning of the Author's name and age ret = models. The Author, objects, filter (phone__startswith = "155") values (" name ", "age") # 16. Find the most expensive book by each author
ret = models.Author.objects.annotate(max=Max("book__price")).values("name"."max")
ret = models.Book.objects.values("author"."author__name").annotate(max=Max("price"))
# 17. Find the name of each author and the number of books published
ret = models.Author.objects.annotate(count=Count("book")).values("name"."count")
ret = models.Book.objects.values("author"."author__name").annotate(count=Count("id"))
# 18. Find the publisher of a book called "Learn to Drive With Boss Kim.
ret = models.Book.objects.filter(title="Learn how to drive from Boss Kim.").values("publisher__name")
ret = models.Publisher.objects.filter(book__title="Learn how to drive from Boss Kim.")
# 19. Find the city where the book "Learn to Drive With Boss Kim" is published
ret = models.Book.objects.filter(title="Learn how to drive from Boss Kim.").values("publisher__name"."publisher__city")
ret = models.Publisher.objects.filter(book__title="Learn how to drive from Boss Kim.").values('city')
# 20. Find the publisher of a book titled "Learn to Drive With Boss Kim.
ret = models.Book.objects.filter(title="Learn how to drive from Boss Kim.").values("publisher__name")
ret = models.Publisher.objects.filter(book__title="Learn how to drive from Boss Kim.").values('name')
# 21. Find names and prices of other books published by publishers with titles like "Learn to Drive With Boss Kim.
pub_obj = models.Publisher.objects.filter(book__title="Learn how to drive from Boss Kim.").first()
ret = pub_obj.book_setret = models.Book.objects.filter(publisher__book__title="Learn how to drive from Boss Kim.").exclude(title="Learn how to drive from Boss Kim.").values("title"."price")
# 22. Find all authors of books entitled "Learning to Drive with Boss Kim"
ret = models.Author.objects.filter(book__title="Learn how to drive from Boss Kim.").values("name")
Find out the age of the author of the book "Learning to Drive with Boss Kim"
ret = models.Author.objects.filter(book__title="Learn how to drive from Boss Kim.").values("age")
Look up the cell phone number of the author of the book "Learn to Drive With Boss Kim.
ret = models.Author.objects.filter(book__title="Learn how to drive from Boss Kim.").values("phone")
# 25. Look up the names of the authors of books entitled "Learn to Drive With Boss Kim", as well as the names and prices of all books published
authors = models.Author.objects.filter(book__title="Learn how to drive from Boss Kim.")
# for author in authors:
# print(author.book_set.values("title","price"))
ret = models.Book.objects.filter(title="Learn how to drive from Boss Kim.").values("author__name"."author__book__title"."author__book__price")for i in ret: print(i)
Copy the code