模型层聚合查询与分组查询
聚合查询与分组查询
聚合
aggregate(*args, **kwargs)
aggregate()是QuerySet 的一个终止子句，意思是说，它返回一个包含一些键值对的字典。键的名称是聚合值的标识符，值是计算出来的聚合值。键的名称是按照字段和聚合函数的名称自动生成出来的。如果你想要为聚合值指定一个名称，可以向聚合子句提供它。用到的内置函数：
from django.db.models import Avg, Sum, Max, Min, Count
示例：
res = models.Book.objects.all().aggregate(Avg('price'))#平均值
res1 = models.Book.objects.all().aggregate(Max('price'))#最大值
res2 = models.Book.objects.all().aggregate(Min('price'))#最小值
res3 = models.Book.objects.all().aggregate(Sum('price'))#求和
res4 = models.Book.objects.all().aggregate(Count('title'))#求总数，个数
根据最大值求其他的字段
res = models.Book.objects.all().aggregate(Max('price'))
print(res,type(res))
print(res.get('price__max'),type(res.get('price__max')))
res = models.Book.objects.filter(price=res.get('price__max'))
print(res.first().title)
如果你想要为聚合值指定一个名称，可以向聚合子句提供它。
res = models.Book.objects.all().aggregate(Min('price'),Max('price'),Count('price'),sum=Sum('price'),ave=Avg('price'))
#给定名称的结果先输出
{'sum': Decimal('810.44'), 'ave': 162.088, 'price__min': Decimal('10.22'), 'price__max': Decimal('250.00'), 'price__count': 5}
如果你希望生成不止一个聚合，你可以向aggregate()子句中添加另一个参数。所以，如果你也想知道所有图书价格的最大值和最小值，可以这样查询：
res5 = models.Book.objects.all().aggregate(Avg('price'),Max('price'),Min('price'),Sum('price'),Count('title'))
分组
annotate()为调用的QuerySet中每一个对象都生成一个独立的统计值（统计方法用聚合函数）。可以跨表查询，使用双下划线方法。
总结 ：跨表分组查询本质就是将关联表join成一张表，再按单表的思路进行分组查询。 分组循环涉及到安全问题，因此有些查询需要设置非安全模式。
我这里是将mysql中的mode，注释就行了。
#sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
查询练习
练习1：统计每一本书作者个数
from django.db.models import Avg, Max, Sum, Min, Max, Count
res=models.Book.objects.annotate(author_num=Count('author')).values('author_num')
res1=models.Book.objects.all().annotate(author_num=Count('author')).values('author_num')
print(res)
print(res1)
###################################################################
< QuerySet[{'author_num': 3}, {'author_num': 1}, {'author_num': 0},{'author_num': 0}, {'author_num': 0}] >
< QuerySet[{'author_num': 3}, {'author_num': 1}, {'author_num': 0}, {'author_num': 0}, {'author_num': 0}] >
################################################################
book_list = models.Book.objects.all().annotate(author_num = Count('author'))
print(book_list)
for book_obj in book_list:
print(book_obj.title)
################################################################
<QuerySet [<Book: linux>, <Book: math>, <Book: python>, <Book: english>, <Book: english>]>
linux
math
python
english
english
################################################################
book_list=models.Book.objects.all().annotate(author_num=Count('author')).values('title','author_num')
print(book_list)
################################################################
<QuerySet [{'title': 'linux', 'author_num': 3}, {'title': 'math', 'author_num': 1}, {'title': 'python', 'author_num': 0}, {'title': 'english', 'author_num': 0}, {'title': 'english', 'author_num': 0}]>
练习2：统计每一个出版社的最便宜的书的价格，
publish=models.Publish.objects.all().annotate(book_price=Min('book__price')).values("name",'book_price')
print(publish)
################################################################
<QuerySet [{'name': '北极出版社', 'book_price': Decimal('10.22')}, {'name': '南极出版社', 'book_price': Decimal('100.22')}]>
################################################################
book_min = models.Book.objects.values('publish__name').annotate(book_min = Min('price'))
print(book_min)
################################################################
<QuerySet [{'publish__name': '北极出版社', 'book_min': Decimal('10.22')}, {'publish__name': '南极出版社', 'book_min': Decimal('100.22')}]>
annotate的返回值是querySet，如果不想遍历对象，可以用上valuelist：返回列表套元组
book_min=models.Publish.objects.all().annotate(book_price=Min('book__price')).values_list("name",'book_price')
print(book_min)
################################################################
<QuerySet [('北极出版社', Decimal('10.22')), ('南极出版社', Decimal('100.22'))]>
练习3：统计不止一个作者的图书：（作者数量大于一）
- 统计每本书对应的作者个数
- 基于上面的结果 筛选出作者个数大于1 的
res = models.Book.objects.all().annotate(author_count = Count('author')).filter(author_count__gt=1).values('title','author_count')
print(res)
################################################################
<QuerySet [{'title': 'linux', 'author_count': 3}]>
练习4：统计每一本以py开头的书籍的作者个数：
book_queryresult = models.Book.objects.filter(title__startswith='m').annotate(author_count=Count('author')).values('title','author__name','author_count')
print(book_queryresult)
################################################################
<QuerySet [{'title': 'math', 'author__name': 'sky', 'author_count': 2}]>
练习5：根据一本图书作者数量的多少对查询集 QuerySet进行排序:
res = models.Book.objects.all().annotate(author_count = Count('author')).order_by('author_count').values('title','author_count')
print(res)
################################################################
<QuerySet [{'title': 'english', 'author_count': 0}, {'title': 'english', 'author_count': 0}, {'title': 'python', 'author_count': 0}, {'title': 'math', 'author_count': 2}, {'title': 'linux', 'author_count': 3}]>
练习6：查询各个作者出的书的总价格:
res = models.Author.objects.all().annotate(book_price = Sum('book__price')).values_list('name','book_price')
print(res)
################################################################
<QuerySet [('ocean', Decimal('10.22')), ('sky', Decimal('110.44')), ('rock', Decimal('110.44')), ('json', None)]>
练习7：查询每个出版社的名称和书籍个数
res = models.Publish.objects.all().annotate(book_count =Count('book')).values('name','book_count')
print(res)
################################################################
<QuerySet [{'name': '北极出版社', 'book_count': 1}, {'name': '南极出版社', 'book_count': 4}]>
总结
value里面的参数对应的是sql语句中的select要查找显示的字段，
filter里面的参数相当于where或者having里面的筛选条件
annotate本身表示group by的作用，前面找寻分组依据，内部放置显示可能用到的聚合运算式，后面跟filter来增加限制条件，最后的value来表示分组后想要查找的字段值
F查询与Q查询
- 我们之前在查询数据库的时候条件都是我们自己手写的
- 但是现在出现了条件是从数据库里面获取的
from django.db.models import F,Q
F查询
在上面所有的例子中，我们构造的过滤器都只是将字段值与某个常量做比较。如果我们要对两个字段的值做比较，那该怎么做呢？
Django 提供 F() 来做这样的比较。F() 的实例可以在查询中引用字段，来比较同一个 model 实例中两个不同字段的值。
示例1：
查询出卖出数大于库存数的商品
res = models.Book.objects.filter(maichu__gt=F('kucun'))
print(res)
################################################################
<QuerySet [<Book: math>, <Book: english>, <Book: english>]>
F可以帮我们取到表中某个字段对应的值来当作我的筛选条件，而不是我认为自定义常量的条件了，实现了动态比较的效果
Django 支持 F() 对象之间以及 F() 对象和常数之间的加减乘除和取模的操作。基于此可以对表中的数值类型进行数学运算
示例2:
将每个商品的价格提高50块
res = models.Book.objects.update(price=F('price')+100)
print(res)
#################################
5
返回更新的数量
引申：
如果要修改char字段咋办(千万不能用上面对数值类型的操作！！！)？
如：把所有书名后面加上'新款',(这个时候需要对字符串进行拼接Concat操作，并且要加上拼接值Value)
from django.db.models.functions import Concat
from django.db.models import Value
models.Book.objects.filter(pk=2).update(title=Concat(F('title'),Value('新款')))
Concat表示进行字符串的拼接操作，参数位置决定了拼接是在头部拼接还是尾部拼接，Value里面是要新增的拼接值
Q查询
filter() 等方法中的关键字参数查询都是一起进行“AND” 的。 如果你需要执行更复杂的查询（例如OR 语句），你可以使用Q 对象。
示例1：
查询 卖出数大于100 或者 价格小于100块的
from django.db.models import Q
res=models.Book.objects.filter(Q(maichu__gt=100)|Q(price__lt=100))
print(res)
##############################################
<QuerySet [<Book: math新款>, <Book: python>, <Book: english>]>
对条件包裹一层Q时候，filter即可支持交叉并的比较符
示例2：
查询 库存数是100 并且 卖出数不是0 的产品
res = models.Book.objects.filter(Q(kucun=100)&~Q(maichu=0))
print(res)
#####################
<QuerySet [<Book: linux>]>
Q 对象可以使用& 和| 操作符组合起来。当一个操作符在两个Q 对象上使用时，它产生一个新的Q 对象。
我们可以组合& 和| 操作符以及使用括号进行分组来编写任意复杂的Q 对象。
同时，Q 对象可以使用~ 操作符取反，这允许组合正常的查询和取反(NOT) 查询。
查询产品名包含新款， 并且库存数大于60的
models.Product.objects.filter(Q(kucun__gt=60), name__contains="新款")
查询函数可以混合使用Q 对象和关键字参数。所有提供给查询函数的参数（关键字参数或Q 对象）都将"AND”在一起。但是，如果出现Q 对象，它必须位于所有关键字参数的前面。例如：
bookList=Book.objects.filter(Q(publishDate__year=2016) | Q(publishDate__year=2017), title__icontains="python")
Q查询进阶用法
# Q查询进阶用法 用Q产生对象 然后再使用
q = Q()
q.connector='or'
q.children.append(('title__icontains','m'))
q.children.append(('kucun',100))
res = models.Book.objects.filter(q)
print(res)
##################################
<QuerySet [<Book: linux>, <Book: math新款>]>
"""
字符串的左边 跟你的变量名条件书写一模一样
"""
事务
事务的定义:将多个sql语句操作变成原子性操作，要么同时成功，有一个失败则里面回滚到原来的状态，保证数据的完整性和一致性(NoSQL数据库对于事务则是部分支持)
ACID
原子性
一致性
隔离性
持久性
# django中如何开启事务
from django.db import transaction
with transaction.atomic():
# 在该代码块中所写的orm语句 同属于一个事务
# 缩进出来之后自动结束
commit
rollback
要么同时成功要么同时失败
# 事务
# 买一本 跟老男孩学Linux 书
# 在数据库层面要做的事儿
# 1. 创建一条订单数据
# 2. 去产品表 将卖出数+1， 库存数-1
from django.db.models import F
from django.db import transaction
# 开启事务处理
try:
with transaction.atomic():
# 创建一条订单数据
models.Order.objects.create(num="110110111", product_id=1, count=1)
# 能执行成功
models.Product.objects.filter(id=1).update(kucun=F("kucun")-1, maichu=F("maichu")+1)
except Exception as e:
print(e)
其他鲜为人知的操作
Django ORM执行原生SQL
条件假设:就拿博客园举例，我们写的博客并不是按照年月日来分档，而是按照年月来分的，而我们的DateField时间格式是年月日形式，也就是说我们需要对从数据库拿到的时间格式的数据再进行一次处理拿到我们想要的时间格式，这样的需求，Django是没有给我们提供方法的，需要我们自己去写处理语句了
ORM 执行原生SQL的方法
事务
事务的定义:将多个sql语句操作变成原子性操作，要么同时成功，有一个失败则里面回滚到原来的状态，保证数据的完整性和一致性(NoSQL数据库对于事务则是部分支持)
复制代码
# 事务
# 买一本 跟老男孩学Linux 书
# 在数据库层面要做的事儿
# 1. 创建一条订单数据
# 2. 去产品表 将卖出数+1， 库存数-1
from django.db.models import F
from django.db import transaction
# 开启事务处理
try:
with transaction.atomic():
# 创建一条订单数据
models.Order.objects.create(num="110110111", product_id=1, count=1)
# 能执行成功
models.Product.objects.filter(id=1).update(kucun=F("kucun")-1, maichu=F("maichu")+1)
except Exception as e:
print(e)
复制代码
其他鲜为人知的操作
Django ORM执行原生SQL
条件假设:就拿博客园举例，我们写的博客并不是按照年月日来分档，而是按照年月来分的，而我们的DateField时间格式是年月日形式，也就是说我们需要对从数据库拿到的时间格式的数据再进行一次处理拿到我们想要的时间格式，这样的需求，Django是没有给我们提供方法的，需要我们自己去写处理语句了
ORM 执行原生SQL的方法
QuerySet方法大全
几个比较重要的方法：
update()与save()的区别
两者都是对数据的修改保存操作，但是save()函数是将数据列的全部数据项全部重新写一遍，而update()则是针对修改的项进行针对的更新效率高耗时少
所以以后对数据的修改保存用update()
select_related和prefetch_related
复制代码
def select_related(self, *fields)
性能相关：表之间进行join连表操作，一次性获取关联的数据。
总结：
1. select_related主要针一对一和多对一关系进行优化。
2. select_related使用SQL的JOIN语句进行优化，通过减少SQL查询的次数来进行优化、提高性能。
def prefetch_related(self, *lookups)
性能相关：多表连表操作时速度会慢，使用其执行多次SQL查询在Python代码中实现连表操作。
总结：
1. 对于多对多字段（ManyToManyField）和一对多字段，可以使用prefetch_related()来进行优化。
2. prefetch_related()的优化方式是分别查询每个表，然后用Python处理他们之间的关系。
复制代码
bulk_create批量插入数据
要求：一次性插入多条数据
data = ["".join([str(random.randint(65, 99)) for i in range(4)]) for j in range(100)]
obj_list = [models.A(name=i) for i in data]
models.A.objects.bulk_create(obj_list)
几个比较重要的方法：
查询优化
select_related和prefetch_related
区别
-
select_related内部自动连表 消耗的资源就在连表上 但是走数据库的次数较少
-
prefetch_related 内部不做连表 消耗的资源就在查询次数上 但是给用户的感觉跟连表操作一样，
select_related
只针对一对多的表格，和一对一的表格，返回的对象
res = models.Book.objects.select_related('publish')
print(res)
res1 = models.Book.objects.all()
res2 = models.Author.objects.select_related('author_detail')
for r in res2:
print(r.author_detail)
"""
select_related 会自动帮你做连表操作 然后将连表之后的数据全部查询出来封装给对象
select_related括号内只能放外键字段
并且多对多字段不能放
如果括号内外键字段所关联的表中还有外键字段 还可以继续连表
select_related(外键字段__外键字段__外键字段...)
"""
prefetch_related
res = models.Book.objects.prefetch_related('publish')
# print(res)
for r in res:
print(r.publish.name)
"""
prefetch_related 看似连表操作 其实是类似于子查询
prefetch_related括号内只能放外键字段
并且多对多字段不能放
如果括号内外键字段所关联的表中还有外键字段 还可以继续连表
select_related(外键字段__外键字段__外键字段...)
"""
def select_related(self, *fields)
性能相关：表之间进行join连表操作，一次性获取关联的数据。
总结：
1. select_related主要针一对一和多对一关系进行优化。
2. select_related使用SQL的JOIN语句进行优化，通过减少SQL查询的次数来进行优化、提高性能。
def prefetch_related(self, *lookups)
性能相关：多表连表操作时速度会慢，使用其执行多次SQL查询在Python代码中实现连表操作。
总结：
1. 对于多对多字段（ManyToManyField）和一对多字段，可以使用prefetch_related()来进行优化。
2. prefetch_related()的优化方式是分别查询每个表，然后用Python处理他们之间的关系。
defer和only
only
- only会将括号内的字段对应的值 直接封装到返回给你的对象中 点该字段 不需要再走数据库
- 一旦你点了不是括号内的字段 就会频繁的去走数据库查询
res = models.Book.objects.all()
res = models.Book.objects.values('title')
res = models.Book.objects.only('title')
for r in res:
print(r.title)
print(r.price)#查询不在的字段，会再次查询数据库，造成数据库压力大,要不断的重复查找
defer
- defer会将括号内的字段排除之外将其他字段对应的值 直接封装到返回给你的对象中 点该其他字段 不需要再走数据库
- 一旦你点了括号内的字段 就会频繁的去走数据库查询
res = models.Book.objects.defer('title') # defer和only互为反关系
for r in res:
print(r.author_detail)
print(r.title)# 查询在的字段，会再次查询数据库，造成数据库压力大
bulk_create批量插入数据
要求：一次性插入多条数据
data = ["".join([str(random.randint(65, 99)) for i in range(4)]) for j in range(100)]
obj_list = [models.A(name=i) for i in data]
models.A.objects.bulk_create(obj_list)
Querset方法大全
update()与save()的区别
两者都是对数据的修改保存操作，但是save()函数是将数据列的全部数据项全部重新写一遍，而update()则是针对修改的项进行针对的更新效率高耗时少
所以以后对数据的修改保存用update()
##################################################################
# PUBLIC METHODS THAT ALTER ATTRIBUTES AND RETURN A NEW QUERYSET #
##################################################################
def all(self)
# 获取所有的数据对象
def filter(self, *args, **kwargs)
# 条件查询
# 条件可以是：参数，字典，Q
def exclude(self, *args, **kwargs)
# 条件查询
# 条件可以是：参数，字典，Q
def select_related(self, *fields)
性能相关：表之间进行join连表操作，一次性获取关联的数据。
总结：
1. select_related主要针一对一和多对一关系进行优化。
2. select_related使用SQL的JOIN语句进行优化，通过减少SQL查询的次数来进行优化、提高性能。
def prefetch_related(self, *lookups)
性能相关：多表连表操作时速度会慢，使用其执行多次SQL查询在Python代码中实现连表操作。
总结：
1. 对于多对多字段（ManyToManyField）和一对多字段，可以使用prefetch_related()来进行优化。
2. prefetch_related()的优化方式是分别查询每个表，然后用Python处理他们之间的关系。
def annotate(self, *args, **kwargs)
# 用于实现聚合group by查询
from django.db.models import Count, Avg, Max, Min, Sum
v = models.UserInfo.objects.values('u_id').annotate(uid=Count('u_id'))
# SELECT u_id, COUNT(ui) AS `uid` FROM UserInfo GROUP BY u_id
v = models.UserInfo.objects.values('u_id').annotate(uid=Count('u_id')).filter(uid__gt=1)
# SELECT u_id, COUNT(ui_id) AS `uid` FROM UserInfo GROUP BY u_id having count(u_id) > 1
v = models.UserInfo.objects.values('u_id').annotate(uid=Count('u_id',distinct=True)).filter(uid__gt=1)
# SELECT u_id, COUNT( DISTINCT ui_id) AS `uid` FROM UserInfo GROUP BY u_id having count(u_id) > 1
def distinct(self, *field_names)
# 用于distinct去重
models.UserInfo.objects.values('nid').distinct()
# select distinct nid from userinfo
注：只有在PostgreSQL中才能使用distinct进行去重
def order_by(self, *field_names)
# 用于排序
models.UserInfo.objects.all().order_by('-id','age')
def extra(self, select=None, where=None, params=None, tables=None, order_by=None, select_params=None)
# 构造额外的查询条件或者映射，如：子查询
Entry.objects.extra(select={'new_id': "select col from sometable where othercol > %s"}, select_params=(1,))
Entry.objects.extra(where=['headline=%s'], params=['Lennon'])
Entry.objects.extra(where=["foo='a' OR bar = 'a'", "baz = 'a'"])
Entry.objects.extra(select={'new_id': "select id from tb where id > %s"}, select_params=(1,), order_by=['-nid'])
def reverse(self):
# 倒序
models.UserInfo.objects.all().order_by('-nid').reverse()
# 注：如果存在order_by，reverse则是倒序，如果多个排序则一一倒序
def defer(self, *fields):
models.UserInfo.objects.defer('username','id')
或
models.UserInfo.objects.filter(...).defer('username','id')
#映射中排除某列数据
def only(self, *fields):
#仅取某个表中的数据
models.UserInfo.objects.only('username','id')
或
models.UserInfo.objects.filter(...).only('username','id')
def using(self, alias):
指定使用的数据库，参数为别名（setting中的设置）
##################################################
# PUBLIC METHODS THAT RETURN A QUERYSET SUBCLASS #
##################################################
def raw(self, raw_query, params=None, translations=None, using=None):
# 执行原生SQL
models.UserInfo.objects.raw('select * from userinfo')
# 如果SQL是其他表时，必须将名字设置为当前UserInfo对象的主键列名
models.UserInfo.objects.raw('select id as nid from 其他表')
# 为原生SQL设置参数
models.UserInfo.objects.raw('select id as nid from userinfo where nid>%s', params=[12,])
# 将获取的到列名转换为指定列名
name_map = {'first': 'first_name', 'last': 'last_name', 'bd': 'birth_date', 'pk': 'id'}
Person.objects.raw('SELECT * FROM some_other_table', translations=name_map)
# 指定数据库
models.UserInfo.objects.raw('select * from userinfo', using="default")
################### 原生SQL ###################
from django.db import connection, connections
cursor = connection.cursor() # cursor = connections['default'].cursor()
cursor.execute("""SELECT * from auth_user where id = %s""", [1])
row = cursor.fetchone() # fetchall()/fetchmany(..)
def values(self, *fields):
# 获取每行数据为字典格式
def values_list(self, *fields, **kwargs):
# 获取每行数据为元祖
def dates(self, field_name, kind, order='ASC'):
# 根据时间进行某一部分进行去重查找并截取指定内容
# kind只能是："year"（年）, "month"（年-月）, "day"（年-月-日）
# order只能是："ASC" "DESC"
# 并获取转换后的时间
- year : 年-01-01
- month: 年-月-01
- day : 年-月-日
models.DatePlus.objects.dates('ctime','day','DESC')
def datetimes(self, field_name, kind, order='ASC', tzinfo=None):
# 根据时间进行某一部分进行去重查找并截取指定内容，将时间转换为指定时区时间
# kind只能是 "year", "month", "day", "hour", "minute", "second"
# order只能是："ASC" "DESC"
# tzinfo时区对象
models.DDD.objects.datetimes('ctime','hour',tzinfo=pytz.UTC)
models.DDD.objects.datetimes('ctime','hour',tzinfo=pytz.timezone('Asia/Shanghai'))
"""
pip3 install pytz
import pytz
pytz.all_timezones
pytz.timezone(‘Asia/Shanghai’)
"""
def none(self):
# 空QuerySet对象
####################################
# METHODS THAT DO DATABASE QUERIES #
####################################
def aggregate(self, *args, **kwargs):
# 聚合函数，获取字典类型聚合结果
from django.db.models import Count, Avg, Max, Min, Sum
result = models.UserInfo.objects.aggregate(k=Count('u_id', distinct=True), n=Count('nid'))
===> {'k': 3, 'n': 4}
def count(self):
# 获取个数
def get(self, *args, **kwargs):
# 获取单个对象
def create(self, **kwargs):
# 创建对象
def bulk_create(self, objs, batch_size=None):
# 批量插入
# batch_size表示一次插入的个数
objs = [
models.DDD(name='r11'),
models.DDD(name='r22')
]
models.DDD.objects.bulk_create(objs, 10)
def get_or_create(self, defaults=None, **kwargs):
# 如果存在，则获取，否则，创建
# defaults 指定创建时，其他字段的值
obj, created = models.UserInfo.objects.get_or_create(username='root1', defaults={'email': '1111111','u_id': 2, 't_id': 2})
def update_or_create(self, defaults=None, **kwargs):
# 如果存在，则更新，否则，创建
# defaults 指定创建时或更新时的其他字段
obj, created = models.UserInfo.objects.update_or_create(username='root1', defaults={'email': '1111111','u_id': 2, 't_id': 1})
def first(self):
# 获取第一个
def last(self):
# 获取最后一个
def in_bulk(self, id_list=None):
# 根据主键ID进行查找
id_list = [11,21,31]
models.DDD.objects.in_bulk(id_list)
def delete(self):
# 删除
def update(self, **kwargs):
# 更新
def exists(self):
# 是否有结果
