Django之ORM(二)
必知必会13条
准备数据
from django.db import models
# Create your models here.
class MyCharField(models.Field):
"""
自定义的char类型的字段类
"""
def __init__(self, max_length, *args, **kwargs):
self.max_length = max_length
super(MyCharField, self).__init__(max_length=max_length, *args, **kwargs)
def db_type(self, connection):
"""
限定生成数据库表的字段类型为char，长度为max_length指定的值
"""
return 'char(%s)' % self.max_length
class Person(models.Model):
id = models.AutoField(primary_key=True)
name = models.CharField(max_length=32, db_column='myname', verbose_name='姓名')
age = models.IntegerField(null=True, blank=True)
birth = models.DateTimeField(auto_now=True)
phone = MyCharField(max_length=11)
sex = models.IntegerField(choices=((1, '男'), (2, '女')))
class Meta:
# 数据库中生成的表名称 默认 app名称 + 下划线 + 类名
db_table = "person"
# 在admin中的显示, 一般直接加verbose_name_plural
verbose_name = '个人信息' # 显示 个人信息s
verbose_name_plural = '所有用户信息' # 显示所有用户信息
# 联合索引
index_together = [('name', 'age')]
# 联合唯一索引
# unique_together = (("name", "age"))
# 排序
# ordering = ('age', )
def __str__(self):
return 'Person <{}:{}>'.format(self.id, self.name)
<1> all():
--->> 查询所有结果 --->> 对象列表
ret = models.Person.objects.all()
print(ret)
输出结果
<QuerySet [<Person: Person <1:魏新雨咋软>>, <Person: Person <2:陈骏啊, 萨宁啊>>]>
SQL语句
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.001) SELECT `person`.`id`, `person`.`myname`, `person`.`age`, `person`.`birth`, `person`.`phone`, `person`.`sex` FROM `person` LIMIT 21; args=()
<2> filter(**kwargs):
--->> 查询出所有满足条件的对象 ---->> 对象列表
ret = models.Person.objects.filter(sex=1)
print(ret)
输出结果
<QuerySet [<Person: Person <1:魏新雨咋软>>, <Person: Person <2:陈骏啊, 萨宁啊>>]>
SQL语句
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.001) SELECT `person`.`id`, `person`.`myname`, `person`.`age`, `person`.`birth`, `person`.`phone`, `person`.`sex` FROM `person` LIMIT 21; args=()
<3> get(**kwargs):
返回与所给筛选条件相匹配的对象，返回结果有且只有一个，如果符合筛选条件的对象超过一个或者没有都会抛出错误
ret = models.Person.objects.get(id=1)
print(ret)
输出结果
Person <1:魏新雨咋软>
SQL语句
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.000) SELECT VERSION(); args=None
(0.001) SELECT `person`.`id`, `person`.`myname`, `person`.`age`, `person`.`birth`, `person`.`phone`, `person`.`sex` FROM `person` WHERE `person`.`id` = 1; args=(1,)
<4> exclude(**kwargs):
--->> 查询出所有不满足条件的对象 ------>> 对象列表
ret = models.Person.objects.exclude(id=1)
print(ret)
输出结果
<QuerySet [<Person: Person <2:陈骏啊, 萨宁啊>>]>
SQL语句
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.000) SELECT VERSION(); args=None
(0.000) SELECT `person`.`id`, `person`.`myname`, `person`.`age`, `person`.`birth`, `person`.`phone`, `person`.`sex` FROM `person` WHERE NOT (`person`.`id` = 1) LIMIT 21; args=(1,)
<5> values(*field):
---->> 取具体的数据 ------> 对象列表 ---->>> 元素 {字段: 值}
ret = models.Person.objects.all().values('id', 'name')
for i in ret:
print(i, type(i))
输出结果
{'id': 2, 'name': '陈骏啊, 萨宁啊'} <class 'dict'>
{'id': 1, 'name': '魏新雨咋软'} <class 'dict'>
SQL语句
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.000) SELECT `person`.`id`, `person`.`myname` FROM `person`; args=()
<6> values_list(*field):
---->> 取具体的数据 ------> 对象列表 ---->>> 元素 (字段, 值)
ret = models.Person.objects.all().values_list('id', 'name')
for i in ret:
print(i, type(i))
输出结果
(2, '陈骏啊, 萨宁啊') <class 'tuple'>
(1, '魏新雨咋软') <class 'tuple'>
SQL语句
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.000) SELECT `person`.`id`, `person`.`myname` FROM `person`; args=()
<7> order_by(*field):
可以指定多个字段 (默认升序, 在字段前加 - 降序)
ret = models.Person.objects.all().order_by('-age')
print(ret)
输出结果
<QuerySet [<Person: Person <1:魏新雨咋软>>, <Person: Person <2:陈骏啊, 萨宁啊>>]>
SQL语句
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.001) SELECT `person`.`id`, `person`.`myname`, `person`.`age`, `person`.`birth`, `person`.`phone`, `person`.`sex` FROM `person` ORDER BY `person`.`age` DESC LIMIT 21; args=()
<8> reverse():
对查询结果反向排序，请注意reverse()通常只能在具有已定义顺序的QuerySet上调用(在model类的Meta中指定ordering或调用order_by()方法)
ret = models.Person.objects.all().order_by('age').reverse()
print(ret)
输出结果
<QuerySet [<Person: Person <1:魏新雨咋软>>, <Person: Person <2:陈骏啊, 萨宁啊>>]>
SQL语句
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.001) SELECT `person`.`id`, `person`.`myname`, `person`.`age`, `person`.`birth`, `person`.`phone`, `person`.`sex` FROM `person` ORDER BY `person`.`age` DESC LIMIT 21; args=()
<9> distinct():
从返回结果中剔除重复纪录(如果你查询跨越多个表，可能在计算QuerySet时得到重复的结果。此时可以使用distinct()，注意只有在PostgreSQL中支持按字段去重)
<10> count():
返回数据库中匹配查询(QuerySet)的对象数量
ret = models.Person.objects.all().count()
print(ret)
输出结果
2
SQL语句
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.000) SELECT COUNT(*) AS `__count` FROM `person`; args=()
<11> first(): 返回第一条记录
ret = models.Person.objects.first()
print(ret)
输出结果
Person <1:魏新雨咋软>
SQL语句
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.001) SELECT `person`.`id`, `person`.`myname`, `person`.`age`, `person`.`birth`, `person`.`phone`, `person`.`sex` FROM `person` ORDER BY `person`.`id` ASC LIMIT 1; args=()
<12> last(): 返回最后一条记录
ret = models.Person.objects.last()
print(ret)
输出结果
Person <2:陈骏啊, 萨宁啊>
SQL语句
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.001) SELECT `person`.`id`, `person`.`myname`, `person`.`age`, `person`.`birth`, `person`.`phone`, `person`.`sex` FROM `person` ORDER BY `person`.`id` DESC LIMIT 1; args=()
<13> exists():
如果QuerySet包含数据，就返回True，否则返回False
ret = models.Person.objects.filter(id=100).exists()
print(ret)
输出结果
False
SQL语句
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.000) SELECT VERSION(); args=None
(0.000) SELECT (1) AS `a` FROM `person` WHERE `person`.`id` = 100 LIMIT 1; args=(100,)
单表查询之神奇的双下划线
ret = models.Person.objects.filter(id__gt=1) # greater than 大于
ret = models.Person.objects.filter(id__lt=4) # less than 小于
ret = models.Person.objects.filter(id__gte=1) # greater than equal 大于等于
ret = models.Person.objects.filter(id__lte=4) # greater than equal 小于等于
ret = models.Person.objects.filter(id__in=[1,3]) # 获取id是1 和 id是3的数据
ret = models.Person.objects.filter(id__range=[1,3]) # 获取id是1 和 id是3的数据
ret = models.Person.objects.filter(id__in=[1,3]) # 获取id是1 和 id是3的数据
ret = models.Person.objects.filter(id__gte=1, id__lt=3) # 获取id大于1小于3的数据
ret = models.Person.objects.filter(id__range=[1,3]) # 获取id在1到3之间的数据
ret = models.Person.objects.filter(name__contains='新') # 获取name字段中包含新的数据
ret = models.Person.objects.filter(name__icontains='sb') # 获取name字段中包含sb的数据, 忽略大小写
ret = models.Person.objects.filter(name__startswith='l') # 获取name字段中以小写l开头的数据
ret = models.Person.objects.filter(name__istartswith='l') # 获取name字段中以l开头的数据, 忽略大小写
ForeignKey操作
准备表格
from django.db import models
# 出版社表
class Press(models.Model):
id = models.AutoField(primary_key=True) # id主键
name = models.CharField(max_length=32) # 出版社名称
def __str__(self):
return '<这是一个出版社对象，它的名字是:{}>'.format(self.name)
# 书
class Book(models.Model):
id = models.AutoField(primary_key=True) # 自增id主键
title = models.CharField(max_length=30) # 书名
price = models.IntegerField(null=True)
# Django 1.11 默认就是级联删除， Django 2.0之后必须指定on_delete
# to=关联的表名
press = models.ForeignKey(to='Press', on_delete=models.CASCADE)
# 作者
class Author(models.Model):
id = models.AutoField(primary_key=True) # 自增id主键
name = models.CharField(max_length=32) # 作者名字
books = models.ManyToManyField(to='Book') # 只是ORM层面建立的一个多对多关系，不是作者表的一个字段
def __str__(self):
return self.name
正向查找
对象查找（跨表）
语法：
对象.关联字段.字段
示例：
book_obj = models.Book.objects.first() # 第一本书对象
print(book_obj.press) # 得到这本书关联的出版社对象
print(book_obj.press.name) # 得到出版社对象的名称
字段查找（跨表）
语法：关联字段__字段
示例：
print(models.Book.objects.values_list('press__book__title'))
反向操作
对象查找
语法：
obj.表名_set
示例：
press_obj = models.Press.objects.get(id=1) # 找到第一个出版社对象
books = press_obj.book_set.all() # 找到第一个出版社出版的所有书
title = books.values_list("title") # 找到第一个出版社出版的所有书的书名
字段查找
语法：
表名__字段
示例：
title = models.Press.objects.values_list('book__title').filter(id=1)
方法
set()
press_obj = models.Press.objects.get(id=1)
press_obj.book_set.set(models.Book.objects.filter(id__in=[1, 2, 3]))
SQL语句
(0.000) SELECT `app01_press`.`id`, `app01_press`.`name` FROM `app01_press` WHERE `app01_press`.`id` = 1; args=(1,)
(0.000) SELECT `app01_book`.`id`, `app01_book`.`title`, `app01_book`.`price`, `app01_book`.`press_id` FROM `app01_book` WHERE `app01_book`.`id` IN (1, 2, 3); args=(1, 2, 3)
(0.000) UPDATE `app01_book` SET `press_id` = 1 WHERE `app01_book`.`id` IN (1, 2, 3); args=(1, 1, 2, 3)
add()
press_obj = models.Press.objects.get(id=1)
press_obj.book_set.add(*models.Book.objects.filter(id__in=[1, 2, 3]))
SQL语句
(0.000) SELECT `app01_press`.`id`, `app01_press`.`name` FROM `app01_press` WHERE `app01_press`.`id` = 1; args=(1,)
(0.000) SELECT `app01_book`.`id`, `app01_book`.`title`, `app01_book`.`price`, `app01_book`.`press_id` FROM `app01_book` WHERE `app01_book`.`id` IN (1, 2, 3); args=(1, 2, 3)
(0.000) UPDATE `app01_book` SET `press_id` = 1 WHERE `app01_book`.`id` IN (1, 2, 3); args=(1, 1, 2, 3)
注意：
- 对于ForeignKey对象，clear()和remove()方法仅在null=True时存在
- 对于所有类型的关联字段，add()、create()、remove()和clear(),set()都会马上更新数据库。换句话说，在关联的任何一端，都不需要再调用save()方法。
举个例子：
ForeignKey字段没设置null=True时，
class Book(models.Model):
id = models.AutoField(primary_key=True) # 自增id主键
title = models.CharField(max_length=30) # 书名
price = models.IntegerField(null=True)
# Django 1.11 默认就是级联删除， Django 2.0之后必须指定on_delete
# to=关联的表名
press = models.ForeignKey(to='Press', on_delete=models.CASCADE)
没有clear()和remove()方法：
Traceback (most recent call last):
File "/Users/cjw/Desktop/码云项目/orm_practice/orm_test/4. 多对多查询.py", line 30, in <module>
press_obj.book_set.clear()
AttributeError: 'RelatedManager' object has no attribute 'clear'
当ForeignKey字段设置null=True时，
class Book(models.Model):
id = models.AutoField(primary_key=True) # 自增id主键
title = models.CharField(max_length=30) # 书名
price = models.IntegerField(null=True)
# Django 1.11 默认就是级联删除， Django 2.0之后必须指定on_delete
# to=关联的表名
press = models.ForeignKey(to='Press', on_delete=models.CASCADE, null=True)
此时就有clear()和remove()方法：
press_obj = models.Press.objects.get(id=1)
press_obj.book_set.add(*models.Book.objects.filter(id__in=[1, 2, 3]))
press_obj.book_set.clear()
SQL语句
(0.000) SELECT `app01_press`.`id`, `app01_press`.`name` FROM `app01_press` WHERE `app01_press`.`id` = 1; args=(1,)
(0.001) UPDATE `app01_book` SET `press_id` = NULL WHERE `app01_book`.`press_id` = 1; args=(1,)
press_obj = models.Press.objects.get(id=1)
press_obj.book_set.add(*models.Book.objects.filter(id__in=[1, 2, 3]))
press_obj.book_set.remove(*models.Book.objects.filter(id__in=[1, 2]))
SQL语句
(0.000) SELECT `app01_press`.`id`, `app01_press`.`name` FROM `app01_press` WHERE `app01_press`.`id` = 1; args=(1,)
(0.001) SELECT `app01_book`.`id`, `app01_book`.`title`, `app01_book`.`price`, `app01_book`.`press_id` FROM `app01_book` WHERE `app01_book`.`id` IN (1, 2, 3); args=(1, 2, 3)
(0.000) UPDATE `app01_book` SET `press_id` = 1 WHERE `app01_book`.`id` IN (1, 2, 3); args=(1, 1, 2, 3)
(0.001) SELECT `app01_book`.`id`, `app01_book`.`title`, `app01_book`.`price`, `app01_book`.`press_id` FROM `app01_book` WHERE `app01_book`.`id` IN (1, 2); args=(1, 2)
(0.000) UPDATE `app01_book` SET `press_id` = NULL WHERE (`app01_book`.`press_id` = 1 AND `app01_book`.`id` IN (1, 2)); args=(1, 1, 2)
ManyToManyField
class RelatedManager
"关联管理器"是在一对多或者多对多的关联上下文中使用的管理器。
它存在于下面两种情况：
- 外键关系的反向查询
- 多对多关联关系
简单来说就是当 点后面的对象 可能存在多个的时候就可以使用以下的方法。
方法
create()
创建一个新的对象，保存对象，并将它添加到关联对象集之中，返回新创建的对象
models.Author.objects.first().books.create(title='偷塔秘籍', press_id=1)
SQL语句
(0.000) SELECT `app01_author`.`id`, `app01_author`.`name` FROM `app01_author` ORDER BY `app01_author`.`id` ASC LIMIT 1; args=()
(0.000) INSERT INTO `app01_book` (`title`, `price`, `press_id`) VALUES ('偷塔秘籍', NULL, 1); args=['偷塔秘籍', None, 1]
(0.001) SELECT `app01_author_books`.`book_id` FROM `app01_author_books` WHERE (`app01_author_books`.`author_id` = 1 AND `app01_author_books`.`book_id` IN (6)); args=(1, 6)
(0.000) INSERT INTO `app01_author_books` (`author_id`, `book_id`) VALUES (1, 6); args=(1, 6)
add()
把指定的model对象追加添加到关联对象集中。
添加对象
author_obj = models.Author.objects.first()
book_obj = models.Book.objects.filter(id__lte=3)
author_obj.books.add(*book_obj)
SQL语句
(0.000) SELECT `app01_author`.`id`, `app01_author`.`name` FROM `app01_author` ORDER BY `app01_author`.`id` ASC LIMIT 1; args=()
(0.001) SELECT `app01_book`.`id`, `app01_book`.`title`, `app01_book`.`price`, `app01_book`.`press_id` FROM `app01_book` WHERE `app01_book`.`id` <= 3; args=(3,)
(0.000) SELECT `app01_author_books`.`book_id` FROM `app01_author_books` WHERE (`app01_author_books`.`author_id` = 1 AND `app01_author_books`.`book_id` IN (1, 2, 3)); args=(1, 1, 2, 3)
(0.000) INSERT INTO `app01_author_books` (`author_id`, `book_id`) VALUES (1, 1), (1, 2), (1, 3); args=(1, 1, 1, 2, 1, 3)
添加id
author_obj = models.Author.objects.first()
author_obj.books.add(*[1, 2])
SQL语句
(0.000) SELECT `app01_author`.`id`, `app01_author`.`name` FROM `app01_author` ORDER BY `app01_author`.`id` ASC LIMIT 1; args=()
(0.000) SELECT `app01_author_books`.`book_id` FROM `app01_author_books` WHERE (`app01_author_books`.`author_id` = 1 AND `app01_author_books`.`book_id` IN (1, 2)); args=(1, 1, 2)
(0.000) INSERT INTO `app01_author_books` (`author_id`, `book_id`) VALUES (1, 1), (1, 2); args=(1, 1, 1, 2)
set()
更新某个对象在第三张表中的关联对象。不同于上面的add是添加,set相当于重置
author_obj = models.Author.objects.first()
author_obj.books.set([1, 2])
SQL语句
(0.000) SELECT `app01_author`.`id`, `app01_author`.`name` FROM `app01_author` ORDER BY `app01_author`.`id` ASC LIMIT 1; args=()
(0.001) SELECT `app01_book`.`id` FROM `app01_book` INNER JOIN `app01_author_books` ON (`app01_book`.`id` = `app01_author_books`.`book_id`) WHERE `app01_author_books`.`author_id` = 1; args=(1,)
(0.001) DELETE FROM `app01_author_books` WHERE (`app01_author_books`.`author_id` = 1 AND `app01_author_books`.`book_id` IN (6)); args=(1, 6)
(0.001) SELECT `app01_author_books`.`book_id` FROM `app01_author_books` WHERE (`app01_author_books`.`author_id` = 1 AND `app01_author_books`.`book_id` IN (1, 2)); args=(1, 1, 2)
(0.005) INSERT INTO `app01_author_books` (`author_id`, `book_id`) VALUES (1, 1), (1, 2); args=(1, 1, 1, 2)
清空多对都关系
author_obj.books.set([])
SQL语句
(0.000) SELECT `app01_author`.`id`, `app01_author`.`name` FROM `app01_author` ORDER BY `app01_author`.`id` ASC LIMIT 1; args=()
(0.001) SELECT `app01_book`.`id` FROM `app01_book` INNER JOIN `app01_author_books` ON (`app01_book`.`id` = `app01_author_books`.`book_id`) WHERE `app01_author_books`.`author_id` = 1; args=(1,)
(0.000) DELETE FROM `app01_author_books` WHERE (`app01_author_books`.`author_id` = 1 AND `app01_author_books`.`book_id` IN (1, 2)); args=(1, 1, 2)
remove()
从关联对象集中移除执行的model对象(移除对象在第三张表中与某个关联对象的关系)
author_obj = models.Author.objects.first()
author_obj.books.set([2,3,4,5])
author_obj.books.remove(2,3)
SQL语句
(0.000) SELECT `app01_author`.`id`, `app01_author`.`name` FROM `app01_author` ORDER BY `app01_author`.`id` ASC LIMIT 1; args=()
(0.000) SELECT `app01_book`.`id` FROM `app01_book` INNER JOIN `app01_author_books` ON (`app01_book`.`id` = `app01_author_books`.`book_id`) WHERE `app01_author_books`.`author_id` = 1; args=(1,)
(0.000) DELETE FROM `app01_author_books` WHERE (`app01_author_books`.`author_id` = 1 AND `app01_author_books`.`book_id` IN (2, 3)); args=(1, 2, 3)
clear()
从关联对象集中移除一切对象。(移除所有与对象相关的关系信息)
author_obj = models.Author.objects.first()
author_obj.books.set([2,3,4,5])
author_obj.books.clear()
SQL语句
(0.000) SELECT `app01_author`.`id`, `app01_author`.`name` FROM `app01_author` ORDER BY `app01_author`.`id` ASC LIMIT 1; args=()
(0.001) SELECT `app01_book`.`id` FROM `app01_book` INNER JOIN `app01_author_books` ON (`app01_book`.`id` = `app01_author_books`.`book_id`) WHERE `app01_author_books`.`author_id` = 1; args=(1,)
(0.000) SELECT `app01_author_books`.`book_id` FROM `app01_author_books` WHERE (`app01_author_books`.`author_id` = 1 AND `app01_author_books`.`book_id` IN (2, 3)); args=(1, 2, 3)
(0.000) INSERT INTO `app01_author_books` (`author_id`, `book_id`) VALUES (1, 2), (1, 3); args=(1, 2, 1, 3)
(0.000) DELETE FROM `app01_author_books` WHERE `app01_author_books`.`author_id` = 1; args=(1,)
反向查询
book_obj = models.Book.objects.get(id=2)
print(book_obj.author_set.all())
SQL语句
(0.000) SELECT `app01_book`.`id`, `app01_book`.`title`, `app01_book`.`price`, `app01_book`.`press_id` FROM `app01_book` WHERE `app01_book`.`id` = 2; args=(2,)
(0.001) SELECT `app01_author`.`id`, `app01_author`.`name` FROM `app01_author` INNER JOIN `app01_author_books` ON (`app01_author`.`id` = `app01_author_books`.`author_id`) WHERE `app01_author_books`.`book_id` = 2 LIMIT 21; args=(2,)
注意!!! 如果在author表中的多对多字段中指定 related_name='author', 则反向查询就不需要使用book_obj.author_set.all()
book_obj = models.Book.objects.get(id=2)
print(book_obj.author.all())
SQL语句
(0.000) SELECT `app01_book`.`id`, `app01_book`.`title`, `app01_book`.`price`, `app01_book`.`press_id` FROM `app01_book` WHERE `app01_book`.`id` = 2; args=(2,)
(0.001) SELECT `app01_author`.`id`, `app01_author`.`name` FROM `app01_author` INNER JOIN `app01_author_books` ON (`app01_author`.`id` = `app01_author_books`.`author_id`) WHERE `app01_author_books`.`book_id` = 2 LIMIT 21; args=(2,)
聚合查询和分组查询
聚合(利用聚合函数)
aggregate()实现
就是实现mysql里的聚合函数功能
导入模块 from django.db.models import Max,Min,Count,Avg
>>> from app01 import models
>>> from django.db.models import Max, Min, Sum, Avg, Count
>>> models.Book.objects.aggregate(Max('price'), avg=Avg('price'))
{'avg': 188.6, 'price__max': 444}
SQL语句
(0.000) SELECT AVG(`app01_book`.`price`) AS `avg`, MAX(`app01_book`.`price`) AS `price__max` FROM `app01_book`; args=()
分组
分组一般都需要一个聚合的结果
示例1 查询 每本书的作者数量
from app01 import models
from django.db.models import Max, Min, Sum, Avg, Count
ret = models.Book.objects.annotate(count = Count('author')).values('title', 'count')
for i in ret:
print(i)
SQL语句
(0.001) SELECT `app01_book`.`title`, COUNT(`app01_author_books`.`author_id`) AS `count` FROM `app01_book` LEFT OUTER JOIN `app01_author_books` ON (`app01_book`.`id` = `app01_author_books`.`book_id`) GROUP BY `app01_book`.`id` ORDER BY NULL; args=()
示例2 查询每个出版社的最便宜的书
方法1
from app01 import models
from django.db.models import Max, Min, Sum, Avg, Count
ret = models.Press.objects.annotate(min = Min('book__price')).values('name', 'min')
for i in ret:
print(i)
SQL语句
(0.001) SELECT `app01_press`.`name`, MIN(`app01_book`.`price`) AS `min` FROM `app01_press` LEFT OUTER JOIN `app01_book` ON (`app01_press`.`id` = `app01_book`.`press_id`) GROUP BY `app01_press`.`id` ORDER BY NULL; args=()
方法2
from app01 import models
from django.db.models import Max, Min, Sum, Avg, Count
ret = models.Book.objects.values('press_id').annotate(min = Min('price')).values('press__name', 'title', 'min')
for i in ret:
print(i)
SQL语句
(0.001) SELECT `app01_press`.`name`, `app01_book`.`title`, MIN(`app01_book`.`price`) AS `min` FROM `app01_book` LEFT OUTER JOIN `app01_press` ON (`app01_book`.`press_id` = `app01_press`.`id`) GROUP BY `app01_book`.`press_id`, `app01_press`.`name`, `app01_book`.`title` ORDER BY NULL; args=()
示例3 统计不止一个作者的图书
from app01 import models
from django.db.models import Max, Min, Sum, Avg, Count
ret = models.Book.objects.annotate(author_num=Count('author')).filter(author__gt=1)
for i in ret:
print(i)
SQL语句
(0.001) SELECT `app01_book`.`id`, `app01_book`.`title`, `app01_book`.`price`, `app01_book`.`press_id`, COUNT(`app01_author_books`.`author_id`) AS `author_num` FROM `app01_book` LEFT OUTER JOIN `app01_author_books` ON (`app01_book`.`id` = `app01_author_books`.`book_id`) INNER JOIN `app01_author_books` T4 ON (`app01_book`.`id` = T4.`book_id`) WHERE T4.`author_id` > 1 GROUP BY `app01_book`.`id` ORDER BY NULL; args=(1,)
示例4 根据一本图书作者数量的多少对查询集 QuerySet进行排序
from app01 import models
from django.db.models import Max, Min, Sum, Avg, Count
ret = models.Book.objects.annotate(author_num=Count('author')).order_by('author_num').values('author_num', 'title')
for i in ret:
print(i)
SQL语句
(0.000) SELECT `app01_book`.`title`, COUNT(`app01_author_books`.`author_id`) AS `author_num` FROM `app01_book` LEFT OUTER JOIN `app01_author_books` ON (`app01_book`.`id` = `app01_author_books`.`book_id`) GROUP BY `app01_book`.`id` ORDER BY `author_num` ASC; args=()
示例5：查询各个作者出的书的总价格
from app01 import models
from django.db.models import Max, Min, Sum, Avg, Count
ret = models.Author.objects.annotate(sum_price = Sum('books__price')).values('sum_price', 'name')
for i in ret:
print(i)
SQL语句
(0.002) SELECT `app01_author`.`name`, SUM(`app01_book`.`price`) AS `sum_price` FROM `app01_author` LEFT OUTER JOIN `app01_author_books` ON (`app01_author`.`id` = `app01_author_books`.`author_id`) LEFT OUTER JOIN `app01_book` ON (`app01_author_books`.`book_id` = `app01_book`.`id`) GROUP BY `app01_author`.`id` ORDER BY NULL; args=()
总结
value里面的参数对应的是sql语句中的select要查找显示的字段，
filter里面的参数相当于where或者having里面的筛选条件
annotate本身表示group by的作用，前面找寻分组依据，内部放置显示可能用到的聚合运算式，后面跟filter来增加限制条件，最后的value来表示分组后想要查找的字段值
