day22-Model数据库操作补充之QuerySet方法详细
一、前言
我们之前学习了model中查询返回的数据都是queryset对象，所以一直可以使用queryset的属性中的方法，比如我们下面这个：
Blog.objects.filter(name="root").filter("id=1").all()
我们一直都可以使用，因为它返回的一直是一个queryset对象，queryset对象有哪些方法呐？下面我们就来研究一下。
二、queryset对象
我返回的永远是一个queryset对象，而且一直可以使用对象里面的方法，这边是直接返回一个queryset对象，也就是支持你个类似于上面的链式变成：
Blog.objects.filter(name="root").filter("id=1").all()
所以如下方法支持这种链式编程：
def all(self) # 获取所有的数据对象 def filter(self, *args, **kwargs) # 条件查询 # 条件可以是：参数，字典，Q def exclude(self, *args, **kwargs) # 条件查询 # 条件可以是：参数，字典，Q def select_related(self, *fields) 性能相关：表之间进行join连表操作，一次性获取关联的数据。 model.tb.objects.all().select_related() model.tb.objects.all().select_related('外键字段') model.tb.objects.all().select_related('外键字段__外键字段') def prefetch_related(self, *lookups) 性能相关：多表连表操作时速度会慢，使用其执行多次SQL查询在Python代码中实现连表操作。 # 获取所有用户表 # 获取用户类型表where id in (用户表中的查到的所有用户ID) models.UserInfo.objects.prefetch_related('外键字段') from django.db.models import Count, Case, When, IntegerField Article.objects.annotate( numviews=Count(Case( When(readership__what_time__lt=treshold, then=1), output_field=CharField(), )) ) students = Student.objects.all().annotate(num_excused_absences=models.Sum( models.Case( models.When(absence__type='Excused', then=1), default=0, output_field=models.IntegerField() ))) 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中的设置）
补充：
1、分组查询支持having子句
v = models.UserInfo.objects.values('u_id').annotate(uid=Count('u_id')).filter(uid__gt=1) #支持having子句，放在最开始的地方就是where子句，放在这边就是having子句
# 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) #distinct=True去重
# SELECT u_id, COUNT( DISTINCT ui_id) AS `uid` FROM UserInfo GROUP BY u_id having count(u_id) > 1
2、 reverse
说明：这个需要跟order_by使用
3、only、defer
说明：only表示第一次帮你拿了两列放到对象中(username,sex)，但是如果你想拿另外一列(password)也是能拿到的，django会再次去执行sql语句，去拿这个password，这样的话效率就低了很多
models.UserInfo.objects.filter(...).only('username','id')
defer是跟only相反，原理都是一样的，它获取的是排除你筛选的字段。
4、using
说明：指定使用的数据库，参数是别名(settings中设置的)，使用场景：比如数据库的读写分离，你读的是A库，写的是B库
Blog.objects.filter(name="root").filter("id=1").all().using(alias="default1") #这个看你settings里面的设置
5、extra
说明：我们在原生的sql语句中会遇到一些复杂的sql，用orm是处理的不了的，比如以下sql：
select id,
name,
1, #使这一列值为1
func(id), #调用函数
select name from tb2 where nid=id，#使用子查询
from
tb;
所以我们该怎么办呐？所以django帮我支持了另外一种，extra，意思是：构造额外的查询或者映射，如：子查询
def extra(self, select=None, where=None, params=None, tables=None, order_by=None, select_params=None)
# 构造额外的查询条件或者映射，如：子查询
#加额外字段
Entry.objects.extra(select={'new_id': "%s"}, select_params=(1,))
#select *,1 as cid from Entry
Entry.objects.extra(select={'new_id': "select col from sometable where othercol > %s"}, select_params=(1,)) #select的参数，%s 就会替换成 1，当然这边可以有多个参数，也可以没有参数，直接传入实参
#select *,(select col from sometable where nid = id) as new_id from Entry
Entry.objects.extra(select={'new_id': "func(1)"}) #可以直接放函数
#加额外的条件语句
Entry.objects.extra(where=['headline=%s'], params=['Lennon'])#加参数，参数可以是元组，只要是可迭代的序列就行
Entry.objects.extra(where=["foo='a' OR bar = 'a'", "baz = 'a'"]) #中间逗号隔开表示 and
Entry.objects.extra(select={'new_id': "select id from tb where id > %s"}, select_params=(1,), order_by=['-nid'])
6、select_related
说明：我们在利用反射或者说是外键查询另外一张表里面的数据的时候，django会想数据库请求两次sql，如下：
users = User.objects.all()
for row in users:
#仅仅是用户表的数据,如果这边10条，那么一共请求11次
print(row.name,row.sex)
#这边需要再次向user_type表再次发送请求，去获取type_name，这边是1条数据
print(row.ut.type_name)
但是如果是这种的话，就请求一次：
users = User.objects.all().values("name","sex","ut__type_name")
但是我们要获取的是queryset对象，所以以上的肯定是不行的，所以我们用select_related：
#这边需要加1到多个外键，这边只能写foreign_key或者one_to_one，因为你没有必要一次性吧所有的表都加载进来，只需要加载自己需要的表就可以了
users = User.objects.all().select_related("ut","tu")
有了select_related，那么与之关联的表，django会一次给你拿过来，所以我们代码改成这样：
#只拿ut对应的user_type表一次性拿过来
users = User.objects.all().select_related("ut") #只关联ut这张表，取出来，其他的表都不关联，因为一张表里面可以有多个外键
for row in users:
print(row.name,row.sex)
print(row.ut.type_name)
print(row.tu.name) #tu没有加进去，所以这边会再次django发送数据库请求
7、prefetch_related
说明：其实我们在生产上，很少链表查询，因为这样效率很低，我们一般都是空间换时间，所以这个就会用到prefetch_related，用到这个，它就不会做链表查询，会做多次查询，保证每次都是单表查询。
users = User.objects.filter(ut_id__gt=30).prefetch_related('ut') #这边也可以多加几个外键
#解析步骤
## select * from users where id > 30 当然这边不会用*的，一般会把所有字段都打印出来，效率更高
## 获取上一步中所有ut_id = [1,2]的数据
## select * from user_type where id in [1,2]
这边django已经通过以上步骤，把你要查询的数据放到内存中，你下次获取的数据的时候，不用在想数据库发送请求了，就可以直接获取了。
三、queryset子类
说明：也就是只有queryset对象可以调用，但是返回的不再是queryset对象了，所以不能再调用queset对象中的方法了
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对象
补充：
1、dates
说明：根据时间进行某一部分进去去重查找并截取指定内容，这个只支持year，month， day
models.DatePlus.objects.dates('ctime','day','DESC') #一直截取到day，默认是asc
2、datetimes
说明：使用方式跟dates差不多，只是维度更小了，他到second级别了，但是要安装 pytz，是为了设置时区用的
models.DDD.objects.datetimes('ctime','hour',tzinfo=pytz.timezone('Asia/Shanghai'))
3、raw
说明：这边还可以支持写原生sql，但是它拿到的是一个一个对象
obj = User.objects.raw('select id,name,password from tb ') #拿到的是一个一个对象
那如果把tb2里面的数据强制tb中：
obj = User.objects.raw('select nid as id,username as name,email as password from tb2 ')
也可以设置一个对应关系，在内部帮你设置一个as操作：
dic = {'id':'nid','name':'username','email':'password'}
obj = User.objects.raw('select nid,username,email from tb2 ',dic)
四、数据库查询操作
这个是直接对数据操作的，包括插入数据库，批量插入数据库，对整个数据做聚合操作等等
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): # 是否有结果
1、aggregate
说明：之前我们只是单个字段做分组聚合，这个是对整张表做聚合
from django.db.models import Count,Avg,Max,Min,Sum
result = User.objects.aggregate(n=Count('nid',distinct=True)) #先去重，再执行聚合
result = User.objects.aggregate(n=Count('nid')) #相当于sql如下： #select count(nid) as n from user
2、bulk_create
说明：批量插入数据
objs = [
models.DDD(name='r11'),
models.DDD(name='r22')
]
models.DDD.objects.bulk_create(objs, 10) #表示分批插入数据，一次最多插入10条
3、get_or_create
说明：如果存在，则获取，否则，创建数据，并且帮你创建了数据所在的对象再获取到
#先根据username='root1'先去差，如果有获取到obj对象，created=True或者False，defaults只有创建数据的时候才会起作用
obj, created = models.UserInfo.objects.get_or_create(username='root1', defaults={'email': '1111111','u_id': 2, 't_id': 2})
