一、数据库表关系
1、单表操作:
Book
id title price publish email addr
1 php 100 人民出版社 111 北京
2 python 200 沙河出版社 222 沙河
3 go 100 人民出版社 111 北京
4 java 300 人民出版社 111 北京
总结:重复内容过多,浪费大量存储空间,资源浪费。
2、表关系之一对多:
Book
id title price publish_id
1 php 100 1
2 python 200 1
3 go 100 2
4 java 300 1
Pulish
id name email addr
1 人民出版社 111 北京
2 沙河出版社 222 沙河
一个出版社可以对应多本书,但是一本书对应不了多个出版社。
总结:一旦确定表关系是一对多时,在多对应的表中创建关联字段。
3、表关系之多对多:
Book
id title price publish_id
1 php 100 1
2 python 200 1
3 go 100 2
4 java 300 1
Author
id name age addr
1 alex 34 beijing
2 egon 55 nanjing
Book2Author
id book_id author_id
1 2 1
2 2 2
3 3 2
总结:一旦确定表关系是多对多,创建第三张关系表:id 和 另外两个表的关联字段。
# alex出版过的书籍名称(子查询)
select id from Author where name='alex';
select book_id from Book2Author where author_id=1;
select title from Book where id = book_id;
4、表关系之一对一
Author
id name age ad_id(UNIQUE)
1 alex 34 1
2 egon 55 2
AuthorDetail
id addr gender tel gf_name author_id(UNIQUE)
1 beijing male 110 小花 1
2 nanjing male 911 杠娘 2
总结:一旦确定是一对一关系,在两张表中的任意一张表中建立关联字段+ UNIQUE。
5、表关系之关联字段和外键约束
创建关联字段和约束不是必然关系,但是不建立约束的话,从引擎的角度来说两个表之间没有任何关联,因此删除的时候,再查找时会找不到数据。
创建关联字段是为了进行查询,建立约束是为了防止出现脏数据。
6、表关系之sql创建关联表
GREATE TABLE publish(
id INT PRIMARY KEY auto_increment,
name VARCHAR (20)
);
GREATE TABLE book(
id INT PRIMARY KEY auto_increment,
title VARCHAR (20),
price DECIMAL (8,2),
pub_date DATE,
publish_id INT, # 关联字段
FOREIGN KEY (publish_id) REFERENCES publish(id) # 关联字段约束
);
GREATE TABLE authordetail (
id INT PRIMARY KEY auto_increment,
tel VARCHAR (20)
);
GREATE TABLE author (
id INT PRIMARY KEY auto_increment,
name VARCHAR (20),
age INT,
authordetail_id INT UNIQUE, # 一对一约束
FOREIGN KEY (authordetail_id) REFERENCES authordetail(id)
);
GREATE TABLE book2author ( # 多对多
id INT PRIMARY KEY auto_increment,
book_id INT,
author_id INT,
FOREIGN KEY (book_id) REFERENCES book(id),
FOREIGN KEY (author_id) REFERENCES author(id)
);
二、创建模型
实例:我们来假定下面这些概念,字段和关系
作者模型:一个作者有姓名和年龄。
作者详细模型:把作者的详情放到详情表,包含生日,手机号,家庭住址等信息。作者详情模型和作者模型之间是一对一的关系(one-to-one)
出版商模型:出版商有名称,所在城市以及email。
书籍模型: 书籍有书名和出版日期,一本书可能会有多个作者,一个作者也可以写多本书,所以作者和书籍的关系就是多对多的关联关系(many-to-many);一本书只应该由一个出版商出版,所以出版商和书籍是一对多关联关系(one-to-many)。
1、模型建立如下:
from django.db import models
# 出版社表
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 AuthorDetail(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()
# 与AuthorDetail建立一对一的关系,一对一的关系建立在任意一边都可以
# to="AuthorDetail",加了引号之后是在全局中寻找不会因为位置关系找不到AuthorDetail
authorDetail=models.OneToOneField(to="AuthorDetail", to_field="nid", on_delete=models.CASCADE)
""" 上面语句代表含义为下面sql语句
authordetail_id INT UNIQUE, # 一对一约束
FOREIGN KEY (authordetail_id) REFERENCES authordetail(id)
"""
class Book(models.Model):
nid = models.AutoField(primary_key=True)
title = models.CharField(max_length=32)
publishDate = models.DateField()
price = models.DecimalField(max_digits=5, decimal_places=2)
#与publish建立 一对多关系 ,外键字段建立在多的一方
publish=models.ForeignKey(to="Publish", to_field="nid",on_delete=models.CASCADE)
""" 上面语句代表含义为下面的sql语句
publish_id INT, # 关联字段
FOREIGN KEY (publish_id) REFERENCES publish(id) # 关联字段约束
"""
# 多对多 与Author表建立多对多的关系,ManyToManyField可以建在两个模型中的任意一个,自动创建第三张表
authors = models.ManyToManyField(to="Author")
""" 上面这个语句含义为下面的sql语句
GREATE TABLE book2author (
id INT PRIMARY KEY auto_increment,
book_id INT,
author_id INT,
FOREIGN KEY (book_id) REFERENCES book(id),
FOREIGN KEY (author_id) REFERENCES author(id)
);
"""
# 这种多对多写法可以由 authors = models.ManyToManyField(to="Author") 替代
# class Book2Author(models.Model):
# nid = models.AutoField(primary_key=True)
# book = models.ForeignKey(to="Book")
# author = models.ForeignKey(to="Author")
2、参数解析:
ForeignKey:一对多与多对一
ManyToManyField:多对多
OneToOneField:一对一
ForeignKey.to_field:指定当前关系与被关联对象中的哪个字段关联。默认情况下,to_field 指向被关联对象的主键。
ForeignKey.on_delete:当一个model对象的ForeignKey关联的对象被删除时,默认情况下此对象也会一起被级联删除的。
CASCADE:默认值,model对象会和ForeignKey关联对象一起被删除。
3、数据库配置
(1)配置mysql数据库连接(可选)
INSTALLED_APPS = [
'django.contrib.admin',
'django.contrib.auth',
'django.contrib.contenttypes',
'django.contrib.sessions',
'django.contrib.messages',
'django.contrib.staticfiles',
'app01',
]
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME':'orm2', # 要连接的数据库,连接前需要创建好
'USER':'root', # 连接数据库的用户名
'PASSWORD':'1234', # 连接数据库的密码
'HOST':'127.0.0.1', # 连接主机,默认本级
'PORT': 3306, # 端口 默认3306
}
}
(2)使用PyMysql连接mysql数据库实例(可选)
在Python3.x以后,由于Python统一了数据库连接的接口,开始使用pymysql,pymysql和MySQLdb 在使用方式上是类似的:
因此,python3.x链接mysql数据库应安装Python,使用pip安装方法是:
pip install PyMySQL
在django项目中配置ORM2/__init__.py文件:
import pymysql
pymysql.install_as_MySQLdb()
(3)配置logging查看翻译成sql语句
在settings.py中添加:
LOGGING = {
'version': 1,
'disable_existing_loggers': False,
'handlers': {
'console':{
'level':'DEBUG',
'class':'logging.StreamHandler',
},
},
'loggers': {
'django.db.backends': {
'handlers': ['console'],
'propagate': True,
'level':'DEBUG',
},
}
}
4、数据迁移
通过两条数据库迁移命令即可在指定的数据库中创建表:
$ python3 manage.py makemigrations
$ python3 manage.py migrate
生成如下表:
5、注意事项
- 表的名称
myapp_modelName
,是根据 模型中的元数据自动生成的,也可以覆写为别的名称 id
字段是自动添加的(没有设置id时会自动添加id字段)- 对于外键字段,Django 会在字段名上添加"_id" 来创建数据库中的列名
- 这个例子中的
CREATE TABLE
SQL 语句使用PostgreSQL 语法格式,要注意的是Django 会根据settings 中指定的数据库类型来使用相应的SQL 语句。 - 定义好模型之后,你需要告诉Django _使用_这些模型。你要做的就是修改配置文件中的INSTALL_APPSZ中设置,在其中添加
models.py
所在应用的名称。(见前面settings设置) - 外键字段 ForeignKey 有一个 null=True 的设置(它允许外键接受空值 NULL),你可以赋给它空值 None 。
6、字段选项
每个字段有一些特有的参数,例如,CharField需要max_length参数来指定VARCHAR数据库字段的大小。
还有一些适用于所有字段的通用参数。这些参数在文档中有详细定义,常用参数如下所示:
(1)null
如果为True,Django 将用NULL 来在数据库中存储空值。 默认值是 False.
(1)blank
如果为True,该字段允许不填。默认为False。
要注意,这与 null 不同。null纯粹是数据库范畴的,而 blank 是数据验证范畴的。
如果一个字段的blank=True,表单的验证将允许该字段是空值。如果字段的blank=False,该字段就是必填的。
(2)default
字段的默认值。可以是一个值或者可调用对象。如果可调用 ,每有新对象被创建它都会被调用。
(3)primary_key
如果为True,那么这个字段就是模型的主键。如果你没有指定任何一个字段的primary_key=True,
Django 就会自动添加一个IntegerField字段做为主键,所以除非你想覆盖默认的主键行为,
否则没必要设置任何一个字段的primary_key=True。
(4)unique
如果该值设置为 True, 这个数据字段的值在整张表中必须是唯一的
(5)choices
由二元组组成的一个可迭代对象(例如,列表或元组),用来给字段提供选择项。 如果设置了choices ,默认的表单将是一个选择框而不是标准的文本框,而且这个选择框的选项就是choices 中的选项。
这是一个关于 choices 列表的例子:
YEAR_IN_SCHOOL_CHOICES = (
('FR', 'Freshman'),
('SO', 'Sophomore'),
('JR', 'Junior'),
('SR', 'Senior'),
('GR', 'Graduate'),
)
每个元组中的第一个元素,是存储在数据库中的值;第二个元素是在管理界面或 ModelChoiceField 中用作显示的内容。 在一个给定的 model 类的实例中,想得到某个 choices 字段的显示值,就调用 get_FOO_display 方法(这里的 FOO 就是 choices 字段的名称 )。例如:
from django.db import models
class Person(models.Model):
SHIRT_SIZES = (
('S', 'Small'),
('M', 'Medium'),
('L', 'Large'),
)
name = models.CharField(max_length=60)
shirt_size = models.CharField(max_length=1, choices=SHIRT_SIZES)
>>> p = Person(name="Fred Flintstone", shirt_size="L")
>>> p.save()
>>> p.shirt_size
'L'
>>> p.get_shirt_size_display()
'Large'
更多详见:字段选项(Field options)
三、添加表记录
操作前先简单录入一些数据:
author表:
authordetail表:
1、添加普通字段(示例)
# 方式1 publish_obj=Publish(name="人民出版社",city="北京",email="renMin@163.com") publish_obj.save() # 将数据保存到数据库 # 方式2 返回值publish_obj是添加的记录对象 publish_obj=Publish.objects.create(name="人民出版社",city="北京",email="renMin@163.com") # 方式3 表.objects.create(**request.POST.dict())
2、添加一对多关系(外键字段)
# 方式1:
# 为book表绑定出版社:一对多绑定关系 book publish
book_obj = Book.objects.create(title="西游记", price=100, publishDate="2012-12-1", publish_id=1)
print(book_obj.title) # 返回值是添加的书籍对象属性
# 方式2:
pub_obj = Publish.objects.filter(nid=1).first()
book_obj = Book.objects.create(title="红楼梦", price=100, publishDate="2012-12-1", publish=pub_obj)
print(book_obj.title) # 红楼梦
print(book_obj.price) # 100
print(book_obj.publishDate) # 2012-12-1
print(book_obj.publish) # 与这本书关联的出版社对象:Publish object (1),设置__str__后,打印 人民出版社
# 查询西游记出版社对应的邮箱
book_obj = Book.objects.filter(title="西游记").first()
print(book_obj.publish.email) # 123@qq.com
book表:
核心:book_obj.publish与book_obj.publish_id是什么?
book_obj.publish是与这本书关联的出版社对象,book_obj.publish_id是出版社id号。
3、添加多对多关系
# 书籍和作者 多对多关系
# 当前生成的书籍对象
book_obj = Book.objects.create(title="金瓶*梅", price=100, publishDate="2012-12-1", publish_id=1)
# 添加普通字段:书籍绑定的作者对象
egon = Author.objects.get(name="egon", age=23, authorDetail_id=2)
alex = Author.objects.get(name="alex", age=33, authorDetail_id=1)
# 绑定多对多关系的API接口,即向关系表book_authors中添加记录
book_obj.authors.add(egon, alex) # 将某个特定的 model 对象添加到被关联对象集合中。 ======= book_obj.authors.add(*[])
# 另一种写法是写入author的主键值
# book_obj.authors.add(1,2,3)
# 另一种写法是传入一个列表,*是函数传列表的时候如果等效位置参数的时候需要加一个*号
# book_obj.authors.add(*[1,2,3])
#创建并保存一个新对象,然后将这个对象加被关联对象的集合中,然后返回这个新对象。
book_obj.authors.create()
(1)数据库表纪录生成如下
book表:
book_author表:
(2)核心:book_obj.authors.all()是什么?
book = Book.objects.filter(nid=6).first()
print(book.authors.all()) # <QuerySet [<Author: alex>]> 与这本书关联的所有作者对象的集合
答案:与这本书关联的所有作者对象的集合。queryset数据类型,列表里面放着的都是这本书关联作者对象。
(3)如何拿到书籍所有作者的名字?
# 查询ID为6的书籍的所有作者的名字
ret = book.authors.all().values("name")
print(ret) # <QuerySet [{'name': 'alex'}]>
通过book.authors.all().values就可以取到书籍对应作者的属性。
(4)解除多对多关系——将某个特定的对象从被关联对象集合中去除
# 解除多对多关系
book = Book.objects.filter(nid=6).first()
book.authors.remove(2) # 将book_authors中对应的6-2这条记录删除
# 类似的写法还有:
# book.authors.remove(*[1,2])
book_authors表:
(5)解除多对多关系——清空被关联对象集合
book_obj.authors.clear()
(6)先清空再设置
book_obj.authors.
set
()
4、关联管理器(RelatedManager)
"关联管理器"是在一对多或者多对多的关联上下文中使用的管理器。它存在于下面两种情况:
(1)ForeignKey关系的“另一边”。像这样:
from django.db import models
class Reporter(models.Model):
# ...
pass
class Article(models.Model):
reporter = models.ForeignKey(Reporter)
在上面的例子中,管理器reporter.article_set拥有下面的方法。
(2)ManyToManyField关系的两边:
class Topping(models.Model):
# ...
pass
class Pizza(models.Model):
toppings = models.ManyToManyField(Topping)
这个例子中,topping.pizza_set 和pizza.toppings都拥有下面的方法。
5、关联管理器拥有的方法
add(obj1[, obj2, ...]):把指定的模型对象添加到关联对象集中。
把指定的模型对象添加到关联对象集中。
例如:
>>> b = Blog.objects.get(id=1)
>>> e = Entry.objects.get(id=234)
>>> b.entry_set.add(e) # Associates Entry e with Blog b.
在上面的例子中,对于ForeignKey关系,e.save()由关联管理器调用,执行更新操作。然而,在多对多关系中使用add()并不会调用任何 save()方法,而是由QuerySet.bulk_create()创建关系。
延伸:
# 1 *[]的使用
>>> book_obj = Book.objects.get(id=1)
>>> author_list = Author.objects.filter(id__gt=2)
>>> book_obj.authors.add(*author_list)
# 2 直接绑定主键
book_obj.authors.add(*[1,3]) # 将id=1和id=3的作者对象添加到这本书的作者集合中
# 应用: 添加或者编辑时,提交作者信息时可以用到.
create(**kwargs):创建一个新的对象,保存对象,并将它添加到关联对象集之中。返回新创建的对象。
创建一个新的对象,保存对象,并将它添加到关联对象集之中。返回新创建的对象:
>>> b = Blog.objects.get(id=1)
>>> e = b.entry_set.create(
... headline='Hello',
... body_text='Hi',
... pub_date=datetime.date(2005, 1, 1)
... )
# No need to call e.save() at this point -- it's already been saved.
这完全等价于(不过更加简洁于):
>>> b = Blog.objects.get(id=1)
>>> e = Entry(
... blog=b,
... headline='Hello',
... body_text='Hi',
... pub_date=datetime.date(2005, 1, 1)
... )
>>> e.save(force_insert=True)
要注意我们并不需要指定模型中用于定义关系的关键词参数。在上面的例子中,我们并没有传入blog参数给create()。Django会明白新的 Entry对象blog 应该添加到b中。
remove(obj1[, obj2, ...]):从关联对象集中移除执行的模型对象
从关联对象集中移除执行的模型对象:
>>> b = Blog.objects.get(id=1)
>>> e = Entry.objects.get(id=234)
>>> b.entry_set.remove(e) # Disassociates Entry e from Blog b.
对于ForeignKey对象,这个方法仅在null=True时存在。
clear():从关联对象集中移除一切对象
从关联对象集中移除一切对象。
>>> b = Blog.objects.get(id=1)
>>> b.entry_set.clear()
注意这样不会删除对象 —— 只会删除他们之间的关联。
就像 remove() 方法一样,clear()只能在 null=True的ForeignKey上被调用。
set()方法:先清空,再设置
def change_book(request,edit_book_id):
edit_book_obj = Book.objects.filter(pk=edit_book_id).first()
if request.method=="POST":
title = request.POST.get("title")
price = request.POST.get("price")
pub_date = request.POST.get("pub_date")
publish_id = request.POST.get("publish_id")
authors_id_list = request.POST.getlist("authors_id_list") # checkbox,select传多个值的时候用getlist()
# 更新为修改的新内容
Book.objects.filter(pk=edit_book_id).update(title=title,price=price,publishDate=pub_date,publish_id=publish_id)
# 更新书的作者(覆盖原记录)
# 方法一:
# edit_book_obj.authors.clear()
# edit_book_obj.authors.add(*authors_id_list)
# 方法二:set()方法先清空再设置
edit_book_obj.authors.set(authors_id_list)
return redirect("/books/")
publish_list = Publish.objects.all()
author_list = Author.objects.all()
return render(request, "editbook.html", {"edit_book_obj": edit_book_obj,"publish_list":publish_list, "author_list":author_list})
方法使用注意
对于所有类型的关联字段,add()、create()、remove()和clear(),set()都会马上更新数据库。换句话说,在关联的任何一端,都不需要再调用save()方法。
直接赋值:
通过赋值一个新的可迭代的对象,关联对象集可以被整体替换掉。
>>> new_list = [obj1, obj2, obj3]
>>> e.related_set = new_list
如果外键关系满足null=True,关联管理器会在添加new_list中的内容之前,首先调用clear()方法来解除关联集中一切已存在对象的关联。否则, new_list中的对象会在已存在的关联的基础上被添加。
四、基于对象的跨表查询(子查询)
子查询概念:子查询是将一个查询语句嵌套在另一个查询语句中。
# 一对多查询的正向查询:查询金瓶*梅这本书的出版社的名字
book_obj = Book.objects.filter(title="金瓶*梅").first()
print(book_obj.publish) # 与这本书关联出版社对象
print(book_obj.publish.name)
# 等同于下列sql语句形式
# select publish_id from Book where title = "金瓶*梅";
# select name from Publish where id = (select publish_id from Book where title = "金瓶*梅");
1、一对多查询(publish与book)
正向查询:按字段
# 一对多查询的正向查询:查询金瓶*梅这本书的出版社的名字
book_obj = Book.objects.filter(title="金瓶*梅").first()
print(book_obj.publish) # 与这本书关联出版社对象
print(book_obj.publish.name)
反向查询:按表名小写_set.all()
# 一对多查询的反向查询:查询人民出版社出版过的书籍名称
publish_obj = Publish.objects.filter(name="人民出版社").first() # 出版社对象
ret = publish_obj.book_set.all()
print(ret) # <QuerySet [<Book: 红楼梦>, <Book: 西游记>, <Book: 金瓶*梅>]>
总结:
(1)假如A、B两个表有关系,关联属性在A表中。正向查询——A去查B对象;反向查询——B去查A对象。
(2)Book(关联属性:publish) ,用book去找关联对象出版社Publish:book_obj.publish;
(3)用publish去找关联对象书籍book:publish_obj.book_set.all() ,且数据类型是queryset。
2、一对一查询(author 与 authordetail)
正向查询:按字段
# 一对一查询的正向查询:查询alex的手机号
alex = Author.objects.filter(name="alex").first()
print(alex.authordetail.telephone)
反向查询:按表名小写
# 一对一查询的反向查询:查询手机号为110的作者名字和年龄
ad = AuthorDetail.objects.filter(telephone="110").first()
print(ad.author.name)
print(ad.author.age)
3、多对多查询(author 与 book)
正向查询(按字段:authors):
# 金瓶眉所有作者的名字以及手机号
book_obj=Book.objects.filter(title="金瓶眉").first()
authors=book_obj.authors.all()
for author_obj in authors:
print(author_obj.name,author_obj.authorDetail.telephone)
反向查询(按表名:book_set):
# 查询egon出过的所有书籍的名字
author_obj=Author.objects.get(name="egon")
book_list=author_obj.book_set.all() #与egon作者相关的所有书籍
for book_obj in book_list:
print(book_obj.title)
注意:
可以通过在 ForeignKey() 和ManyToManyField的定义中设置 related_name 的值来覆写 FOO_set 的名称。例如,如果 Article model 中做一下更改:
publish = ForeignKey(Book, related_name='bookList')
那么接下来就会如我们看到这般:
# 查询 人民出版社出版过的所有书籍
publish=Publish.objects.get(name="人民出版社")
book_list=publish.bookList.all() # 与人民出版社关联的所有书籍对象集合
五、基于双下划线的跨表查询(join查询)
Django 还提供了一种直观而高效的方式在查询(lookups)中表示关联关系,它能自动确认 SQL JOIN 联系。要做跨关系查询,就使用两个下划线来链接模型(model)间关联字段的名称,直到最终链接到你想要的model 为止。
正向查询按字段,反向查询按表名小写用来告诉ORM引擎join哪张表。
1、一对多查询
# 一对多查询:查询金瓶*梅这本书的出版社的名字
"""
SELECT
app01_publish.name
FROM
app01_book
Inner join
app01_publish
ON app01_book.publish_id = app01_publish.nid
WHERE app01_book.title='金瓶*梅';
"""
# 方式一:正向查询按字段
# ret = Book.objects.filter(title="金瓶*梅").values("publish__name") # 正向查询按字段
# print(ret) # <QuerySet [{'publish__name': '人民出版社'}]>
# 方式二:反向查询按表名小写
# SELECT app01_publish.name FROM app01_publish INNER JOIN app01_book....仅仅是顺序不同
ret = Publish.objects.filter(book__title="金瓶*梅").values("name") # 反向查询按表名小写
# publish去找book因此是反向查询,通过表名小写的方式通知ORM引擎去join book表,然后使用book的属性title
print(ret)
总结:正向查询按字段,反向查询按表名小写用来告诉ORM引擎join哪张表。
2、多对多查询
value等同于sql语句中的select,filter等同于sql语句中的where。
# 多对多查询:查询金瓶*梅这本书所有作者的名字
"""
SELECT app01_author.name FROM
app01_book
INNER JOIN
app01_book_authors
ON app01_book.nid = app01_book_authors.book_id
INNER JOIN
app01_author
ON app01_book_authors.author_id = app01_author.nid
WHERE app01_book.title="金瓶*梅";
"""
# 方式一:
# 需求:通过Book表join与其关联的Author表,属于正向查询:按字段Book模型中字段authors = models.ManyToManyField(to="Author"),来通知ORM引擎join book_authors 和 author
ret = Book.objects.filter(title="金瓶*梅").values("authors__name")
print(ret) # <QuerySet [{'authors__name': 'alex'}]>
# 方式二:
# 需求:通过Author表join与其关联的Book表,属于反向查询:按表名小写book通知ORM引擎join book_authors与book表
ret = Author.objects.filter(book__title="金瓶*梅").values("name")
print(ret) # <QuerySet [{'name': 'alex'}]>
3、一对一查询
# 一对一查询:查询alex的手机号
# 方式一:
# 需求:通过Author表join与其关联的AuthorDetail表,属于正向查询:按Author表中字段authorDetail通知ORM引擎join Authordetail表
ret = Author.objects.filter(name="alex").values("authorDetail__telephone")
print(ret) # <QuerySet [{'authorDetail__telephone': 110}]>
# 方式二:
# 需求:通过AuthorDetail表join与其关联的Author表,属于反向查询:按表名小写author通知ORM引擎join Author表
ret = AuthorDetail.objects.filter(author__name="alex").values("telephone")
print(ret) # <QuerySet [{'telephone': 110}]>
4、进阶练习(连续跨表)
# 练习:手机号以110开头的作者出版过得所有书籍名称及书籍出版社名称
# 方式一:
# 需求:通过Book表join AuthorDetail表,Book与AuthorDetail无关联,因此必须连续跨表
# book,book_authors,author,authordetail,publish五张表join
ret = Book.objects.filter(authors__authorDetail__telephone__startswith="110").values("title", "publish")
print(ret) # <QuerySet [{'title': '金瓶*梅', 'publish': 1}]>
# 方式二:
# 需求:以作者为基表,Author表join与其关联的authorDetail,属于正向查询,找到authorDetail下telephone属性,查看110开头的电话的作者。
ret = Author.objects.filter(authorDetail__telephone__startwith="110").values("book__title", "book__publish__name")
print(ret)
5、related_name
反向查询时,如果定义了related_name ,则用related_name替换表名,例如:
publish = ForeignKey(Blog, related_name='bookList')
练习: 查询人民出版社出版过的所有书籍的名字与价格(一对多)
# 反向查询 不再按表名:book,而是related_name:bookList
queryResult=Publish.objects
.filter(name="人民出版社")
.values_list("bookList__title","bookList__price")
六、聚合查询和分组查询
先了解sql中的聚合与分组概念。
1、聚合:aggregate(*args, **kwargs)
# 查询所有书籍的平均价格
from django.db.models import Avg,Max,Min,Count
ret = Book.objects.all().aggregate(Avg("price"))
print(ret) # {'price__avg': 100.0} 返回值是一个字典,键自动由字段和聚合函数拼接组成
aggregate()是QuerySet 的一个终止子句,意思是说,它返回一个包含一些键值对的字典。聚合 aggregate:返回值是一个字典,不再是一个queryset。
键的名称是聚合值的标识符,值是计算出来的聚合值。键的名称是按照字段和聚合函数的名称自动生成出来的。
如果你想要为聚合值指定一个名称,可以向聚合子句提供它:
# 自定义键:
ret = Book.objects.all().aggregate(avg_price=Avg("price"))
print(ret) # {'avg_price': 100.0}
如果你希望生成不止一个聚合,你可以向aggregate()子句中添加另一个参数。所以,如果你也想知道所有图书价格的最大值和最小值,可以这样查询:
ret = Book.objects.all().aggregate(avg_price=Avg("price"),max_price=Max("price"))
print(ret) # {'avg_price': 100.0, 'max_price': Decimal('100.00')}
2、分组:annotate()
为调用的QuerySet中每一个对象都生成一个独立的统计值(统计方法用聚合函数)。
前置准备创建Emp表:
class Emp(models.Model):
name=models.CharField(max_length=32)
age=models.IntegerField()
salary=models.DecimalField(max_digits=8,decimal_places=2)
dep=models.CharField(max_length=32)
province=models.CharField(max_length=32)
添加数据如下:
(1)单表分组查询
单表分组查询的ORM语法:单表模型.objects.values("group by的字段").annotate(聚合函数("统计字段"))
# --------------------------->分组查询 annotate:返回值依然是queryset
# 单表分组查询
# 示例一:查询每个部门的名称及员工平均薪水
# select dep,Avg(salary) from emp group by dep;
ret = Emp.objects.values("dep").annotate(avg_salary=Avg('salary'))
print(ret) # <QuerySet [{'dep': '教学部', 'avg_salary': 51000.0}, {'dep': '保安部', 'avg_salary': 5000.0}]>
# 单表分组查询的ORM语法:单表模型.objects.values("group by的字段").annotate(聚合函数("统计字段"))
# 实例二:查询每个省份的名称和员工数
ret = Emp.objects.values("province").annotate(c=Count("id"))
print(ret) # <QuerySet [{'province': '山东省', 'c': 2}, {'province': '河北省', 'c': 1}]>
注意:在单表分组下,按照主键进行group by 是没有任何意义的。
# annotate是按照前面显示的字段group by。
ret = Emp.objects.all() # select * from emp;
print(ret)
ret = Emp.objects.values("name") # select name from emp;
print(ret)
# 下面代表的是对所有字段进行group by
Emp.objects.all().annotate(avg_salary=Avg("salary"))
(2)多表分组查询
多表分组查询示例:
Book表
id title date price publish_id
1 红楼梦 2012-12-12 101 1
2 西游记 2012-12-12 101 1
3 三国演绎 2012-12-12 101 1
4 金瓶*梅 2012-12-12 301 2
Publish表
id name addr email
1 人民出版社 北京 123@qq.com
2 南京出版社 南京 345@163.com
1 查询每一个出版社出版的书籍个数
Book.objects.values("publish_id").annotate(Count("id")) # 单表查询即可完成
2 示例 查询每一个出版社的名称以及出版的书籍个数
join sql : select * from Book inner join Publish on book.publish_id=publish.id
两张表合并为一张大表:
id title date price publish_id publish.id publish.name publish.addr publish.email
1 红楼梦 2012-12-12 101 1 1 人民出版社 北京 123@qq.com
2 西游记 2012-12-12 101 1 1 人民出版社 北京 123@qq.com
3 三国演绎 2012-12-12 101 1 1 人民出版社 北京 123@qq.com
4 金瓶*梅 2012-12-12 301 2 2 南京出版社 南京 345@163.com
分组查询sql:
select publish.name,Count("title") from Book inner join Publish on book.publish_id=publish.id
group by publish.id;
(3)思考:如何用ORM语法进行跨表分组查询?
在单表分组下,按照主键进行group by 是没有任何意义的;但是在多表下对主键进行group by是有意义的。
# 查询每一个出版社的名称以及出版的书籍个数
ret = Publish.objects.values("nid").annotate(c=Count("book__title")) # 与单表分组查询的区别就是统计的字段跨表了
print(ret) # <QuerySet [{'nid': 1, 'c': 2}, {'nid': 2, 'c': 1}, {'nid': 3, 'c': 0}]>
# 方式一:由于nid不是符合需求,改用name分组:
ret = Publish.objects.values("name").annotate(c=Count("book__title")) # 与单表分组查询的区别就是统计的字段跨表了
print(ret) # <QuerySet [{'name': '人民出版社', 'c': 2}, {'name': '苹果出版社', 'c': 1}, {'name': '橘子出版社', 'c': 0}]>
# 方式二:用nid分组,用values去取,也能查询到名称信息
ret = Publish.objects.values("nid").annotate(c=Count("book__title")).values("name","c")
print(ret) # <QuerySet [{'name': '人民出版社', 'c': 2}, {'name': '苹果出版社', 'c': 1}, {'name': '橘子出版社', 'c': 0}]>
用一个新例子帮助理解:
# 查询每一个作者的名字以及出版过得书籍的最高价格
"""
SELECT app01_author.name,Max(app01_book.price) FROM app01_book
INNER JOIN app01_book_authors ON app01_book.nid = app01_book_authors.book_id
INNER JOIN app01_author ON app01_author.nid=app01_book_authors.author_id
GROUP BY app01_author.nid;
"""
# pk可以指代id或nid,join book表反向查询
ret = Author.objects.values("pk").annotate(max_price=Max("book__price")).values("name","max_price")
print(ret) # <QuerySet [{'name': 'alex', 'max_price': Decimal('100.00')}, {'name': 'egon', 'max_price': None}]>
另一个示例:
# 示例:查询查询每一个书籍的名称以及对应的作者个数
"""
SELECT app01_book.title,COUNT(app01_author.name) FROM app01_book
INNER JOIN app01_book_authors ON app01_book.nid = app01_book_authors.book_id
INNER JOIN app01_author ON app01_author.nid=app01_book_authors.author_id
GROUP BY app01_book.nid;
"""
ret=Book.objects.values("pk").annotate(c=Count("authors__name")).values("title","c")
print(ret) # <QuerySet [{'title': '金瓶*梅', 'c': 1}, {'title': '红楼梦', 'c': 0}, {'title': '西游记', 'c': 0}]>
跨表分组查询另一种写法:
# 每一个后的表模型.objects.annotate(聚合函数(关联表__统计字段)).values("表模型的所有字段以及统计字段")
# 示例:查询每一个出版社的名称及出版社书籍个数
ret = Publish.objects.all().annotate(c=Count("book__title")).values("name","email","c")
# 省略all()的简略写法
ret = Publish.objects.annotate(c=Count("book__title")).values("name", "email", "c")
print(ret) # <QuerySet [{'name': '人民出版社', 'email': '123@qq.com', 'c': 2}, {'name': '苹果出版社', 'email': 'yuan@163.com', 'c': 1}, {'name': '橘子出版社', 'email': 'egon@qq.com', 'c': 0}]>
总结跨表的分组查询的模型:
每一个后的表模型.objects.values("pk").annotate(聚合函数(关联表__统计字段)).values("表模型的所有字段以及统计字段")
每一个后的表模型.objects.annotate(聚合函数(关联表__统计字段)).values("表模型的所有字段以及统计字段")
(4)查询练习
# 练习1:统计每一个出版社的最便宜的书
# 模板:每一个后的表模型.objects.annotate(聚合函数(关联表__统计字段)).values("表模型的所有字段以及统计字段")
publishList = Publish.objects.annotate(MinPrice=Min("book__price"))
for publish_obj in publishList:
print(publish_obj.name,publish_obj.MinPrice)
"""
人民出版社 100.00
苹果出版社 100.00
橘子出版社 None
"""
# annotate的返回值是querySet,如果不想遍历对象,可以用上valuelist:
queryResult = Publish.objects.annotate(MinPrice=Min("book__price")).values_list("name", "MinPrice")
print(queryResult) # <QuerySet [('人民出版社', Decimal('100.00')), ('苹果出版社', Decimal('100.00')), ('橘子出版社', None)]>
# 练习3:统计每一本以py开头的书籍的作者个数
# 每一个后的表模型.objects.values("pk").annotate(聚合函数(关联表__统计字段)).values("表模型的所有字段以及统计字段")
ret = Book.objects.filter(title__startswith="py").values("pk").annotate(c=Count("authors__name")).values("title","c")
print(ret) # <QuerySet []>
# 练习4:统计不止一个作者的图书 gt:大于
ret = Book.objects.values("pk").annotate(c=Count("authors__name")).filter(c__gt=1).values("title", "c")
print(ret) # <QuerySet []>
# 练习5:根据一本图书作者数量的多少对查询集queryset进行排序
Book.objects.annotate(num_authors=Count("authors")).order_by("num_authors")
# 练习6:查询各个作者出的书的总价格
# 按author表的所有字段group by
from django.db.models import Sum
queryResult = Author.objects.annotate(sum_price=Sum("book__price")).values_list("name", "sum_price")
print(queryResult) # <QuerySet [('alex', Decimal('100.00')), ('egon', None)]>
七、F查询和Q查询
1、F查询
Django 提供 F() 来对两个字段的值做比较比较。F() 的实例可以在查询中引用字段,来比较同一个 model 实例中两个不同字段的值。
修改models.py内的book类,添加评论数和已读数:
class Book(models.Model):
nid = models.AutoField(primary_key=True)
title = models.CharField(max_length=32)
publishDate = models.DateField()
price = models.DecimalField(max_digits=5, decimal_places=2)
# 临时更改表结构:
# read_num = models.IntegerField()
# comment_num = models.IntegerField()
""" 由于临时更改没有添加默认值,会提示如下报错:
Please select a fix:
1) Provide a one-off default now (will be set on all existing rows with a null value for this column)
2) Quit, and let me add a default in models.py
"""
read_num = models.IntegerField(default=0)
comment_num = models.IntegerField(default=0)
然后在book表内添加修改评论数和已读数:
from django.db.models import F
# 临时更改表结构后,需要查看书籍评论次数大于已读次数
ret = Book.objects.filter(comment_num__gt=F("read_num"))
print(ret) # <QuerySet [<Book: 红楼梦>, <Book: 金瓶*梅>]>
Django 还支持 F() 对象之间以及 F() 对象和常数之间的加减乘除和取模的操作。
# 查询已读数大于评论数2倍的书籍
ret = Book.objects.filter(read_num__gt=F("comment_num")*2)
print(ret) # <QuerySet [<Book: 西游记>]>
修改操作也可以使用F函数,比如将每一本书的价格提高10元:
# 所有书籍的价格提升10元
Book.objects.all().update(price=F("price")+10)
2、Q查询
filter() 等方法中的关键字参数查询都是一起进行“AND” 的。 如果需要执行更复杂的查询(例如OR 语句)可以使用Q对象。
from django.db.models import Q
# 书籍的名称为红楼梦或者书的价格为110 Q表示或
ret = Book.objects.filter(Q(title="红楼梦")|Q(price=110))
print(ret) # <QuerySet [<Book: 红楼梦>, <Book: 西游记>, <Book: 金瓶*梅>]>
Q 对象可以使用& 和| 操作符组合起来。当一个操作符在两个Q 对象上使用时,它产生一个新的Q 对象。等同于下面的语句:
WHERE title ="红楼梦" OR price > 110
可以组合& 和| 操作符以及使用括号进行分组来编写任意复杂的Q 对象。
同时,Q 对象可以使用~ 操作符取反,这允许组合正常的查询和取反(NOT) 查询:
# 书籍名称不为红楼梦或者书的价格为110 ~表示非
ret=Book.objects.filter(~Q(title="红楼梦")|Q(price=110))
print(ret) # <QuerySet [<Book: 红楼梦>, <Book: 西游记>, <Book: 金瓶*梅>]>
查询函数可以混合使用Q对象和关键字参数。所有提供给查询函数的参数(关键字参数或Q 对象)都将"AND”在一起。但是,如果出现Q 对象,它必须位于所有关键字参数的前面。例如:
# 在2012年或2013年出版的名称包含"红"
bookList=Book.objects.filter(Q(publishDate__year=2012) | Q(publishDate__year=2014),title__icontains="红")
print(bookList) # <QuerySet [<Book: 红楼梦>]>
还有一种特别的Q对象用法,查询条件可以不用是字段名称,可以使用字符串来完成查询。
def test(request):
from django.db.models import Q
# Q查询普通写法:
ret = Book.objects.all().filter(Q(title="go")|Q(price=103))
print("ret", ret) # ret <QuerySet [<Book: go>]>
# Q查询特殊用法:
q = Q()
q.connectiion = "or"
q.children.append(("title", "go"))
q.children.append(("price", 103))
print("q", q) # q (AND: ('title', 'yuan'), ('price', 123))
return HttpResponse(ret, q) # q (AND: ('title', 'go'), ('price', 103))