Django默认ORM(五)：常用操作数据的方法
1、我们平时用到的数据库的基本增删改查：
models.UserInfo.objects.all()
models.UserInfo.objects.filter(id=1,id=2)
models.UserInfo.objects.all().first()
models.UserInfo.objects.all().count()
models.UserInfo.objects.all().update()
models.UserInfo.objects.all().delete()
models.UserInfo.objects.all()[1:19] #取第一条到第18条数据
跨表：实质上是先进行连表操作，再进行的where筛选，最后显示的列名
正向：
xxxx.filter(ut__title='值').values('id','name','ut__title')
反向：
xxxx.filter(小写表名称__列名='值').values('id','name','小写表名称__列名')
- 示例：打印原sql语句看看，查看QuerySet里取出的字典
#正向先过滤后取值
obj = models.UserInfo.objects.filter(ut__title="白银用户").values("id","name","ut__title")
print(obj.query)
print(obj)
"""
SELECT "app01_userinfo"."id", "app01_userinfo"."name", "app01_usertype"."title" FROM "app01_userinfo"
INNER JOIN "app01_usertype" ON("app01_userinfo"."ut_id" = "app01_usertype"."id")
WHERE "app01_usertype"."title" = 白银用户
< QuerySet[{'id': 6, 'name': 'ada1', 'ut__title': '白银用户'}] >
"""
#反向先过滤后取值
obj = models.UserType.objects.filter(userinfo="3").values("userinfo__name","userinfo__age","title",)
print(obj.query)
print(obj)
"""SELECT "app01_userinfo"."name", "app01_userinfo"."age", "app01_usertype"."title" FROM "app01_usertype"
INNER JOIN "app01_userinfo" ON ("app01_usertype"."id" = "app01_userinfo"."ut_id")
WHERE "app01_userinfo"."id" = 3
<QuerySet [{'userinfo__name': 'ada2', 'userinfo__age': 19, 'title': '黄金用户'}]>
"""
2、过滤/条件判断<等同于 where>：
__gt --->大于
__gte ---> 大于等于
__lt ---> 小于
__lte ---> 小于等于
__in = [] ----> 获取 xx 在列表中的值 相当于in 例：id__in = [1,2,3,4,5]
__range=[] ----> 范围查找 相当于between and
__contains="xxx" -----> 包含
__startswith = "xxxxx" ----->起始值
__endswith = "xxxxx" ----->结尾
.exclude(列名__in=[列表值]) 获取不在列表内的值，相当于 not in
- 示例：
models.UserInfo.objects.filter(id__gt=1)
models.UserInfo.objects.filter(id__lt=1)
models.UserInfo.objects.filter(id__lte=1)
models.UserInfo.objects.filter(id__gte=1)
models.UserInfo.objects.filter(id__in=[1,2,3])
models.UserInfo.objects.filter(id__range=[1,2])
models.UserInfo.objects.filter(name__startswith='xxxx')
models.UserInfo.objects.filter(name__endswith='xxxx')
models.UserInfo.objects.filter(name__contains='xxxx')
models.UserInfo.objects.exclude(id=1)
3、排序：.order_by("列名") <等同于order by 列名 asc or desc> 可以通过多个列名排序！
- 语法：
对象.order_by("id") #默认排序是从小到大
对象.order_by("-id") #从大到小排序
- 示例：
user_list = models.UserInfo.objects.values("id","name","age").order_by("id") #从小到大排
user_list = models.UserInfo.objects.values("id","name","age").order_by("-id") #从大到小排
user_list = models.UserInfo.objects.values("id","name","age").order_by("id","name")
#先按照id从小到大排,如果有重复的再按照name从小到大排列
print(user_list)
4、分组：.annotate(别名=聚合函数(列名)) <等同于 group by > annotate的本意是注释,此处是分组
- 语法：
from django.db.models import Count,max,min,Sum,…… #使用前先导入聚合函数
.annotate(别名=Count(列名))
二次筛选----->在分组之后.filter(别名的条件)<__gt:大于；__lt:小于>
- 示例：
from django.db.models import Count
v = models.UserInfo.objects.values("ut_id").annotate(othername = Count("id"))
#用于查看sql语句 上条语句的实质是以values中的列作为分组的列，此处values和annotate存在关系
# 如果annotate 中不写任何语句，则只是查看数据包中对应的这列的数据！
print(v.query)
print(v)
v1 = models.UserInfo.objects.values("ut_id").filter(ut_id__gte=2).annotate(othername =
Count("id")).filter(othername__gt=1)
#查看sql语句发现，第一个filter等同于where，第二个filter等同于 having
print(v1.query)
print(v1)
5、更新F:
用于获取表中某列的原始数据 常用于更新操作.update()
from django.db.models import F #导入模块
models.UserInfo.objects.all().update(age=F("age")+1)
6、Q查询：
#补充：查询条件是字典类型，将字典作为条件进行筛选
condition = {
'id':1,
'name': 'root'
}
v = models.UserInfo.objects.filter(**condition) #结果为obj对象
- 常用于数据的筛选.filter() 用于构造复杂的查询条件
- Q使用有两种方式：对象方式，方法方式***
from django.db.models import Q #导入模块
应用一：对象 组合判断
from django.db.models import Q
user_list = models.UserInfo.objects.values("id","name").filter(Q(id__gt=1)) #单个Q对象
user_list = models.UserInfo.objects.values("id","name").filter(Q(id=2)| Q(id=3)) # 多个Q对象 或关系关联
user_list = models.UserInfo.objects.values("id","name").filter(Q(id=2) & Q(name="rain1")) # 多个Q对象 与关系关联
print(user_list)
应用二：先创建方法，然后添加条件
q1 = Q() #生成Q对象
q1.connector = "OR" #指定多个对象间连接的关系
q1.children.append(("id__gt",1))
#添加的数据：必须是元组类型，第一个是条件，第二个是值
q1.children.append(("id__lt",5))
q2 = Q()
q2.connector = "AND"
q2.children.append(("id",1))
q2.children.append(("id",2))
conn = Q() #生成一个Q对象
conn.add(q1,"AND") #以某种关系，添加其他的Q对象
conn.add(q2,"AND")
v = models.UserInfo.objects.all().filter(conn) #通过条件去查找对应数据
print(v)
- 若是查询信息是字典格式，想转成Q对象的方法：
condition_dict = {
'k1': [1, 2, 3, 4],
'k2': [1, ],
}
con = Q()
for k, v in condition_dict.items():
q = Q()
q.connector = 'OR'
for i in v:
q.children.append(('id', i))
con.add(q, 'AND')
user_list = models.UserInfo.objects.filter(con)
7、extra额外查询条件以:
***当django定义的规则无法完成或是无法满足需求的时候，就需要用到以下两种方式，进行对数据库的操作！
作用：(额外查询条件以及相关表，排序)用于子查询，再从别处获取点数据 除filter外可以在外边添加extra，这是同时生效的。
语法：.extra(self, select=None, where=None, params=None, tables=None, order_by=None, select_params=None)
使用分类：
a. 映射
select = {"别名":"带占位符的原生sql语句"}
select_params=[按顺序要传递的值]
#select 此处 from 表
b. 条件
where=["字符串类型的条件(有占位符)",]
params=[传值],
# select * from 表 where 此处
c. 表
tables = ["表"]
# select * from 表,此处
d. 排序
order_by=["列名"]
# select * from 表 order by 此处
- 示例：
v = models.UserInfo.objects.all().extra(
select={
'n':"select count(1) from app01_usertype where id=%s or id=%s",
'm':"select count(1) from app01_usertype where id=%s or id=%s",
},
select_params=[1,2,3,4])
for obj in v:
print(obj.name,obj.id,obj.n,obj.m)
v = models.UserInfo.objects.extra(
where=["id=1","name='alex'"]
)
v = models.UserInfo.objects.extra(
where=["id=1 or id=%s ","name=%s"],
params=[1,"alex"]
)
v = models.UserInfo.objects.extra(
tables=['app01_usertype'],
)
# sql语句"""select * from app01_userinfo,app01_usertype""" 查询两个表，以笛卡尔基的方式显示多条数据
models.UserInfo.objects.extra(
tables=['app01_usertype'],
where = ["app01_usertype.id = app01_userinfo.ut_id"],
order_by = ["-app01_userinfo.id"],
)
# sql语句"""select * from app01_userinfo,app01_usertype where app01_usertype.id = app01_userinfo.ut_id""" 连表操作，where判断
print(v.query) #查看对应的sql语句
- 所有方法操作：
v = models.UserInfo.objects.filter(id__gt=3).extra(
tables=["app01_usertype"],
select={"title":"select title from app01_usertype where id >%s"},
select_params=[1,],
where=["app01_userinfo.age>%s","app01_userinfo.ut_id = app01_usertype.id",],
params=[16,],
order_by=["-app01_userinfo.id"]
)
print(v.query)
"""
SELECT (select title from app01_usertype where id >1) AS "title",
"app01_userinfo"."id", "app01_userinfo"."name", "app01_userinfo"."age", "app01_userinfo"."ut_id"
FROM "app01_userinfo" , "app01_usertype"
WHERE ("app01_userinfo"."id" > 3 AND (app01_userinfo.age>16) AND (app01_userinfo.ut_id = app01_usertype.id))
ORDER BY ("app01_userinfo".id) DESC
"""
8、原生sql语句：
- 当ORM操作无法完成的时候，我们就需要写原生的sql语句来实现这个操作数据库的功能
from django.db import connection, connections #首先先导入模块
connection 默认连接settings文件中对应的 default 库
connections["数据库"] ----> 选择连接settings中设置的不同数据库
#注意：django的机制会连接上数据库，我们就利用现成的连接，访问数据库，创建游标，写原生sql语句并提交。不需要考虑关闭连接的问题。
#谨慎一些可以把创建的游标关闭，数据库连接由django去做判断
#创建不同类型的游标
cursor = connection.cursor() # connection=default数据
cursor = connections['db2'].cursor()
cursor.execute("""SELECT * from auth_user where id = %s""", [1]) #向数据库提交数据
row = cursor.fetchone() #接收查到的单个信息
row = cursor.fetchall() #接收查到的所有信息
总结：
可以写sql语句的方法：
1、原生sql语句，
2、extra
3、raw
9、其他操作：
1 ################################################################## 2 # PUBLIC METHODS THAT ALTER ATTRIBUTES AND RETURN A NEW QUERYSET # 3 ################################################################## 4 5 def all(self) 6 # 获取所有的数据对象 7 8 def filter(self, *args, **kwargs) 9 # 条件查询 10 # 条件可以是：参数，字典，Q 11 12 def exclude(self, *args, **kwargs) 13 # 条件查询 14 # 条件可以是：参数，字典，Q 15 16 def select_related(self, *fields) 17 #性能相关：表之间进行join连表操作，一次性获取关联的数据。 18 model.tb.objects.all().select_related() 19 model.tb.objects.all().select_related('外键字段') 20 model.tb.objects.all().select_related('外键字段__外键字段') 21 22 def prefetch_related(self, *lookups) 23 #性能相关：多表连表操作时速度会慢，使用其执行多次SQL查询在Python代码中实现连表操作。 24 # 获取所有用户表 25 # 获取用户类型表where id in (用户表中的查到的所有用户ID) 26 models.UserInfo.objects.prefetch_related('外键字段') 27 28 29 from django.db.models import Count, Case, When, IntegerField 30 Article.objects.annotate( 31 numviews=Count(Case( 32 When(readership__what_time__lt=treshold, then=1), 33 output_field=CharField(), 34 )) 35 ) 36 37 students = Student.objects.all().annotate(num_excused_absences=models.Sum( 38 models.Case( 39 models.When(absence__type='Excused', then=1), 40 default=0, 41 output_field=models.IntegerField() 42 ))) 43 - 以上两种连表操作方法总结： 44 优点：约束，节省磁盘空间 45 缺点：性能不高，连表越多，性能越差 46 47 对于连表，操作少量的数据表显现不出性能的缺点， 48 但是对于大数据的表，或者是访问量巨大，同时追求响应速度的网站，查看搜索数据过大，再加上连表操作的时候，缺点就会显现。 49 针对这种情况，大公司都是舍弃磁盘，把数据都放在一个表里操作以提高性能！ 50 51 #select_related: 查询主动做连表。<性能相对的会降低> 52 # 操作机制：通过外键名，先做连表操作把数据都放在一个对象里，对象再通过外键名.列名去获取数据，而不再做二次查询。 53 v = models.UserInfo.objects.all().select_related("ut",……) #括号内可以写多个外键名，前提是当前表内有多个 54 for row in v: 55 print(row.id,row.name,row.age,row.ut.title) 56 57 #prefetch_related: 不做连表，做多次查询。 58 <避免影响性能，django自己通过外键[过滤之后的]作为条件，对多表都先查询，然后django内部对查询到的结果做连表操作> 59 q = models.UserInfo.objects.all().prefetch_related("ut",……) #括号内可以写多个外键名，前提是当前表内有多个 60 for row in q: 61 print(row.id,row.name,row.age,row.ut.title) 62 63 64 def annotate(self, *args, **kwargs) 65 # 用于实现聚合group by查询 66 67 from django.db.models import Count, Avg, Max, Min, Sum 68 69 v = models.UserInfo.objects.values('u_id').annotate(uid=Count('u_id')) 70 # SELECT u_id, COUNT(ui) AS `uid` FROM UserInfo GROUP BY u_id 71 72 v = models.UserInfo.objects.values('u_id').annotate(uid=Count('u_id')).filter(uid__gt=1) 73 # SELECT u_id, COUNT(ui_id) AS `uid` FROM UserInfo GROUP BY u_id having count(u_id) > 1 74 75 v = models.UserInfo.objects.values('u_id').annotate(uid=Count('u_id',distinct=True)).filter(uid__gt=1) 76 # SELECT u_id, COUNT( DISTINCT ui_id) AS `uid` FROM UserInfo GROUP BY u_id having count(u_id) > 1 77 78 def distinct(self, *field_names) 79 # 用于distinct去重 80 models.UserInfo.objects.values('nid').distinct() 81 # select distinct nid from userinfo 82 83 注：只有在PostgreSQL中才能使用distinct进行去重 84 85 def order_by(self, *field_names) 86 # 用于排序 87 models.UserInfo.objects.all().order_by('-id','age') 88 89 def extra(self, select=None, where=None, params=None, tables=None, order_by=None, select_params=None) 90 # 构造额外的查询条件或者映射，如：子查询 91 92 Entry.objects.extra(select={'new_id': "select col from sometable where othercol > %s"}, select_params=(1,)) 93 Entry.objects.extra(where=['headline=%s'], params=['Lennon']) 94 Entry.objects.extra(where=["foo='a' OR bar = 'a'", "baz = 'a'"]) 95 Entry.objects.extra(select={'new_id': "select id from tb where id > %s"}, select_params=(1,), order_by=['-nid']) 96 97 def reverse(self): 98 # 倒序 99 models.UserInfo.objects.all().order_by('-nid').reverse() 100 # 注：如果存在order_by，reverse则是倒序，如果多个排序则一一倒序 101 #倒序：只有前边有order_by操作的时候才起作用，否则不起任何作用！倒序只是反转 按照 某列分组 的排序规则 102 103 104 def defer(self, *fields): 105 models.UserInfo.objects.defer('username','id') 106 或 107 models.UserInfo.objects.filter(...).defer('username','id') 108 #映射中排除某列数据 109 #defer 获取除了条件列外所有的数据 110 111 def only(self, *fields): 112 #仅取某个表中的数据 113 models.UserInfo.objects.only('username','id') 114 或 115 models.UserInfo.objects.filter(...).only('username','id') 116 117 #only 只获取条件内的列所对应的数据 118 v = models.UserInfo.objects.all().only("id","name") 119 #操作之后的结果还是obj对象，只是对象内的数据只有id和name，当然也可以通过这个对象去获取其他列的信息，就会再往数据库发送一次查询请求！ 120 #所以说，取了哪个就获取哪个，不要再获取额外的数据！ 121 122 def using(self, alias): 123 #指定使用的数据库，参数为别名（setting中的设置） 124 #多个数据库的情况下：using用于指定去某个数据库中取数据，不写默认是default。当然，前提是数据库里得有要操作的表。 125 models.UserInfo.objects.all().using("数据库名") 126 #注意点：数据库操作拿到的对象相同，就能一直调用这个对象对应的方法。obj对象就可以。 127 128 ################################################## 129 # PUBLIC METHODS THAT RETURN A QUERYSET SUBCLASS # 130 ################################################## 131 132 def raw(self, raw_query, params=None, translations=None, using=None): 133 # 执行原生SQL 134 models.UserInfo.objects.raw('select * from userinfo') 135 136 # 如果SQL是其他表时，必须将名字设置为当前UserInfo对象的主键列名 137 models.UserInfo.objects.raw('select id as nid from 其他表') 138 139 # 为原生SQL设置参数 140 models.UserInfo.objects.raw('select id as nid from userinfo where nid>%s', params=[12,]) 141 142 # 将获取的到列名转换为指定列名 143 name_map = {'first': 'first_name', 'last': 'last_name', 'bd': 'birth_date', 'pk': 'id'} 144 Person.objects.raw('SELECT * FROM some_other_table', translations=name_map) 145 146 # 指定数据库 147 models.UserInfo.objects.raw('select * from userinfo', using="default") 148 149 ################### 原生SQL ################### 150 from django.db import connection, connections 151 cursor = connection.cursor() # cursor = connections['default'].cursor() 152 cursor.execute("""SELECT * from auth_user where id = %s""", [1]) 153 row = cursor.fetchone() # fetchall()/fetchmany(..) 154 - 示例： 155 #sql语句，查询本表 156 v = models.UserInfo.objects.raw("select * from app01_userinfo") 157 print(v) 158 print(v.query) 159 for i in v: 160 print(i.id,i.name) 161 """ 162 <RawQuerySet: select * from app01_userinfo> 163 select * from app01_userinfo 164 """ 165 #查询另一张表，由于两张表的id名相同，Userinfo在生成对象的时候，会根据id再对自己的表进行一次查询。拿到自己表对应的数据。 166 #而sql语句中要找的title列信息，不会存在Userinfo的对象中。 167 v = models.UserInfo.objects.raw("select id,title from app01_usertype") 168 print(v) 169 print(v.query) 170 for i in v: 171 print(i.id,i.name) 172 #SQL是其他表，将名字设置为当前UserInfo表中相对应的列名<id名相同>, 173 # UserInfo生成对象的时候，就会把sql语句中查到的数据，按照列名存放<对应的自己表的数据不取，此时就是对sql语句起一个包装的作用> 174 175 176 177 v = models.UserInfo.objects.raw("select id,title as name from app01_usertype") 178 print(v) 179 print(v.query) 180 for i in v: 181 print(i.id,i.name) 182 #将获取到的列名，转换成指定列名 自定义名 = {"获取的列名":"指定列名",.....} 183 name_map = {"id":"id","title":"name"} 184 v = models.UserInfo.objects.raw("select * from app01_usertype",translations=name_map).using("default") 185 print(v) 186 print(v.query) 187 for i in v: 188 print(i.id, i.name) 189 190 191 def values(self, *fields): 192 # 获取每行数据为字典格式 193 194 def values_list(self, *fields, **kwargs): 195 # 获取每行数据为元祖 196 #对数据库存放的时间进行截取操作，通过关键字去截取。 197 def dates(self, field_name, kind, order='ASC'): 198 # 根据时间进行某一部分去重查找并截取指定内容 199 # kind只能是："year"（年）, "month"（年-月）, "day"（年-月-日） 200 # order只能是："ASC" "DESC" 201 # 并获取转换后的时间 202 - year : 年-01-01 203 - month: 年-月-01 204 - day : 年-月-日 205 206 models.DatePlus.objects.dates('ctime','day','DESC') 207 208 209 def datetimes(self, field_name, kind, order='ASC', tzinfo=None): 210 # 根据时间进行某一部分进行去重查找并截取指定内容，将时间转换为指定时区时间 211 # kind只能是 "year", "month", "day", "hour", "minute", "second" 212 # order只能是："ASC" "DESC" 213 # tzinfo时区对象 214 models.DDD.objects.datetimes('ctime','hour',tzinfo=pytz.UTC) 215 models.DDD.objects.datetimes('ctime','hour',tzinfo=pytz.timezone('Asia/Shanghai')) 216 217 """ 218 pip3 install pytz 219 import pytz 220 pytz.all_timezones 221 pytz.timezone(‘Asia/Shanghai’) 222 """ 223 224 def none(self): 225 # 空QuerySet对象，什么都不取 226 227 #################################### 228 # METHODS THAT DO DATABASE QUERIES # 229 #################################### 230 231 232 233 234 235 def aggregate(self, *args, **kwargs): 236 # 聚合函数，获取字典类型聚合结果 237 #聚合不分组，把整个表看成一个组进行计算，返回相关聚合函数操作次数的结果。 238 from django.db.models import Count, Avg, Max, Min, Sum 239 240 result = models.UserInfo.objects.aggregate(k=Count('u_id',), n=Count('nid')) #不对数据进行去重 241 ===>{"k":6,"n":6} 242 result = models.UserInfo.objects.aggregate(k=Count('u_id', distinct=True), n=Count('nid')) #对数据进行去重 243 ===> {'k': 3, 'n': 6} 244 245 def count(self): 246 # 获取个数 247 248 def get(self, *args, **kwargs): 249 # 获取单个对象 250 #get(条件) 能找到数据正常，找不到对应的数据就会报错；找到一条数据正常，找到多条数据会报错； 251 def create(self, **kwargs): 252 # 创建对象 253 #增加会有返回值，表示当前增加的数据，通过obj.id 获取新增的id。 254 obj = models.UserInfo.objects.create(XXXXX) 255 print(obj.id) 256 257 258 259 260 261 262 263 def bulk_create(self, objs, batch_size=None): 264 # 批量插入 265 # batch_size表示一次插入的个数 266 objs = [ 267 models.UserInfo(name='r11'), #注意插入数据的类型，没有object 268 models.UserInfo(name='r22') 269 ] 270 models.UserInfo.objects.bulk_create(objs, 10) #batch_size：批量增加，每次最多提交对象的个数，最多不要操作999。 271 272 def get_or_create(self, defaults=None, **kwargs): 273 # 如果存在，则获取，否则，创建 274 #查找的条件可以有多个，若是存在，则defaults内的字段全部作废，否则创建一条新的数据 275 # defaults 指定创建时，其他字段的值 276 obj, created = models.UserInfo.objects.get_or_create(username='root1', defaults={'email': '1111111','u_id': 2, 't_id': 2}) 277 278 def update_or_create(self, defaults=None, **kwargs): 279 # 如果存在，则更新，否则，创建 280 # defaults 指定创建时或更新时的其他字段 281 obj, created = models.UserInfo.objects.update_or_create(username='root1', defaults={'email': '1111111','u_id': 2, 't_id': 1}) 282 283 def first(self): 284 # 获取第一个 285 286 def last(self): 287 # 获取最后一个 288 289 290 def in_bulk(self, id_list=None): 291 # 根据主键ID进行查找 292 #根据主键进行查询，相当于是in操作，判断主键在不在条件中。 293 id_list = [11,21,31] 294 models.UserInfo.objects.in_bulk(id_list) 295 296 def delete(self): 297 # 删除 298 299 def update(self, **kwargs): 300 # 更新 301 302 def exists(self): 303 # 是否有结果 304 #查看新增之后的数据存不存在，返回的是布尔值。
