django系列5.5--分组查询,聚合查询,F查询,Q查询,脚本中调用django环境

一.聚合查询

aggregate(*args, **args)

先引入需要的包,再使用聚合查询

#计算所有图书的平均价格
from django.db.models import Avg
Book.objects.all().aggregate(Avg('price'))

aggregate()是QuerySet 的一个终止子句，意思是说，它返回一个包含一些键值对的字典。键的名称是聚合值的标识符，值是计算出来的聚合值。键的名称是按照字段和聚合函数的名称自动生成出来的。如果你想要为聚合值指定一个名称，可以向聚合子句提供它。

Book.objects.aggregate(average_price=Avg('price'))
#{'average_price': 34.35}

如果你希望生成不止一个聚合，你可以向aggregate()子句中添加另一个参数。所以，如果你也想知道所有图书价格的最大值和最小值，可以这样查询：

from django.db.models import Avg, Max, Min
Book.objects.aggregate(Avg('price'), Max('price'), Min('price')) 
{'price__avg': 34.35, 'price__max': Decimal('81.20'), 'price__min': Decimal('12.99')}

count('id'),count(1)也可以统计个数,Book.objects.all().aggregete和Book.objects.aggregate()，都可以

二.分组查询

annotate()

1.annotate() 为调用的Queryset中每一个对象都生成一个独立的统计值(统计方法用聚合函数)

2.本质是将关联表join成一张表,再进行单表分组查询

#查询每一个部门的id以及对应部门员工的平均薪水
models.Emp.objects.values('dep_id').annotate(s = Avg('salary'))		

3.annotate()的返回值是queryset,如果不想遍历对象, 可以用上valuelist

#统计每一个出版社的最便宜的书
publishList=Publish.objects.annotate(MinPrice=Min("book__price")) 
for publish_obj in publishList:
    print(publish_obj.name,publish_obj.MinPrice)

如果没有使用objects后面values或者values_list，得到的结果是queryset类型，里面是Publish的model对象，并且是对所有记
录进行的统计，统计的Minprice也成了这些model对象里面的一个属性，这种连表分组统计的写法最常用，思路也比较清晰

ret=Publish.objects.annotate(MinPrice=Min("book__price")).values_list("name","MinPrice")
print(ret)

执行分组查询时,务必要将mysql中的ONLY_FULL_GROUP_BY模式关闭,否则分组查询会有很大几率报错

1.在mysql目录下my.ini文件中将sql_mode中的only_full_group_by去掉

2.在mysql中设置sql_mode

#在mysql中查看sql_mode
mysql> select @@global.sql_mode;

#设置sql_mode为如下操作(去掉ONLY_FULL_GROUP_BY)
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

原因:

ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。

三.F查询

1.F()的实例可以在查询中引用字段,来比较同一个model实例中两个不同字段的值

#查询评论数大于收藏数的书籍
from django.db.models import F
Book.objects.filter(commentNum__lt = F('keepNum'))

2.支持F()对象之间以及F()对象和常数之间的加减乘除和取模的操作

Book.objects.filter(commentNum__lt = F('keepNum')*2)

3.修改操作亦可以使用F函数,比如将每一本书的价格提高30元

Book.objects.all().update(price=F("price")+30)

四.Q查询

1.filter()等方法中的关键字参数都是一起进行"AND"的.如果需要OR条件,就可以使用Q对象

from django.db.models import Q
Q(title__startswith='Py')

2.q对象可以使用&(与), |(或), ~(非) 操作符组合,当一个操作符在两个Q对象上使用时,产生一个新的Q对象

bookList=Book.objects.filter(Q(authors__name="川端康成")|Q(authors__name="太宰治"))
等同于sql语句:
    WHERE name ="太宰治" OR name ="村上春树"

3.可以组合& 和| 操作符以及使用括号进行分组来编写任意复杂的Q 对象。同时，Q 对象可以使用~ 操作符取反，这允许组合正常的查询和取反(NOT) 查询：

bookList=Book.objects.filter(Q(authors__name="太宰治") & ~Q(publishDate__year=2017)).values_list("title")
bookList=Book.objects.filter(Q(Q(authors__name="太宰治") & ~Q(publishDate__year=2017))&Q(id__gt=6)).values_list("title") #可以进行Q嵌套，多层Q嵌套等，其实工作中比较常用

4.查询函数可以混合使用Q对象和关键字参数.所有提供给查询函数的参数（关键字参数或Q 对象）都将"AND”在一起。但是，如果出现Q 对象，它必须位于所有关键字参数的前面。例如：

bookList=Book.objects.filter(
	Q(publishDate__year=2016) | Q(publishDate__year=2017),
    title__icontains="python"  #也是and的关系，但是Q必须写在前面
)

五.ORM执行原生sql语句

在模型查询API不够用的情况下,还可以使用原始的SQL语句进行查询

Django提供两种方法使用原始SQL进行查询:

1. 使用raw()方法,进行原始SQL查询并返回模型实例

2. 完全避开模型层,直接执行自定义的SQL语句

1.执行原生查询

1. raw()管理器方法用于原始的SQL查询,并返回模型的实例
2. raw()语法查询必须包含主键
3. raw()方法自动将查询字段映射到模型字段.还可以通过translations参数指定一个把查询的字段名和ORM对象实例的字段名互相对应的字典

这个方法执行原始的SQL查询，并返回一个django.db.models.query.RawQuerySet 实例。 这个RawQuerySet 实例可以像一般的QuerySet那样，通过迭代来提供对象实例

def query(request):
	for p in models.Book.objects.raw("select * from app01_Book;"):
		print(p) # 这里的p为Book object对象
	return HttpResponse('OK')

2.直接执行自定义SQL

需要执行DELETE, INSERT或UPDATE时,可以直接访问数据库,完全避开模型层

可以直接从django提供的接口中获取数据库连接,然后像使用pymysql模块一样操作数据库

from django.db import connection, connections
cursor = connection.cursor()
cursor.execute("select * from auth_user where id = %s ", [1])
ret = cursor.fetchone()		

六.Python脚本中调用Django环境(django外部脚本使用models)

如果你想通过自己创建的python文件在django项目中使用django的models,那么就需要调用django环境才能运行

import os
if __name__== '__main__':
os.environ.setdefault("DJANGO_SETTINGS_MODULE","BMS.settings")
import django
django.setup()
			
from app01 import models
			
books = models.Book.objects.all()
print(books)