![]()
1 # 增
2 #
3 # models.Tb1.objects.create(c1='xx', c2='oo') 增加一条数据，可以接受字典类型数据 **kwargs
4
5 # obj = models.Tb1(c1='xx', c2='oo')
6 # obj.save()
7
8 # 查
9 #
10 # models.Tb1.objects.get(id=123) # 获取单条数据，不存在则报错（不建议）
11 # models.Tb1.objects.all() # 获取全部
12 # models.Tb1.objects.filter(name='seven') # 获取指定条件的数据
13 # models.Tb1.objects.exclude(name='seven') # 获取指定条件的数据
14
15 # 删
16 #
17 # models.Tb1.objects.filter(name='seven').delete() # 删除指定条件的数据
18
19 # 改
20 # models.Tb1.objects.filter(name='seven').update(gender='0') # 将指定条件的数据更新，均支持 **kwargs
21 # obj = models.Tb1.objects.get(id=1)
22 # obj.c1 = '111'
23 # obj.save() # 修改单条数据
![]()
1 # 获取个数
2 #
3 # models.Tb1.objects.filter(name='seven').count()
4
5 # 大于，小于
6 #
7 # models.Tb1.objects.filter(id__gt=1) # 获取id大于1的值
8 # models.Tb1.objects.filter(id__gte=1) # 获取id大于等于1的值
9 # models.Tb1.objects.filter(id__lt=10) # 获取id小于10的值
10 # models.Tb1.objects.filter(id__lte=10) # 获取id小于10的值
11 # models.Tb1.objects.filter(id__lt=10, id__gt=1) # 获取id大于1 且 小于10的值
12
13 # in
14 #
15 # models.Tb1.objects.filter(id__in=[11, 22, 33]) # 获取id等于11、22、33的数据
16 # models.Tb1.objects.exclude(id__in=[11, 22, 33]) # not in
17
18 # isnull
19 # Entry.objects.filter(pub_date__isnull=True)
20
21 # contains
22 #
23 # models.Tb1.objects.filter(name__contains="ven")
24 # models.Tb1.objects.filter(name__icontains="ven") # icontains大小写不敏感
25 # models.Tb1.objects.exclude(name__icontains="ven")
26
27 # range
28 #
29 # models.Tb1.objects.filter(id__range=[1, 2]) # 范围bettwen and
30
31 # 其他类似
32 #
33 # startswith，istartswith, endswith, iendswith,
34
35 # order by
36 #
37 # models.Tb1.objects.filter(name='seven').order_by('id') # asc
38 # models.Tb1.objects.filter(name='seven').order_by('-id') # desc
39
40 # group by
41 #
42 # from django.db.models import Count, Min, Max, Sum
43 # models.Tb1.objects.filter(c1=1).values('id').annotate(c=Count('num'))
44 # SELECT "app01_tb1"."id", COUNT("app01_tb1"."num") AS "c" FROM "app01_tb1" WHERE "app01_tb1"."c1" = 1 GROUP BY "app01_tb1"."id"
45
46 # limit 、offset
47 #
48 # models.Tb1.objects.all()[10:20]
49
50 # regex正则匹配，iregex 不区分大小写
51 #
52 # Entry.objects.get(title__regex=r'^(An?|The) +')
53 # Entry.objects.get(title__iregex=r'^(an?|the) +')
54
55 # date
56 #
57 # Entry.objects.filter(pub_date__date=datetime.date(2005, 1, 1))
58 # Entry.objects.filter(pub_date__date__gt=datetime.date(2005, 1, 1))
59
60 # year
61 #
62 # Entry.objects.filter(pub_date__year=2005)
63 # Entry.objects.filter(pub_date__year__gte=2005)
64
65 # month
66 #
67 # Entry.objects.filter(pub_date__month=12)
68 # Entry.objects.filter(pub_date__month__gte=6)
69
70 # day
71 #
72 # Entry.objects.filter(pub_date__day=3)
73 # Entry.objects.filter(pub_date__day__gte=3)
74
75 # week_day
76 #
77 # Entry.objects.filter(pub_date__week_day=2)
78 # Entry.objects.filter(pub_date__week_day__gte=2)
79
80 # hour
81 #
82 # Event.objects.filter(timestamp__hour=23)
83 # Event.objects.filter(time__hour=5)
84 # Event.objects.filter(timestamp__hour__gte=12)
85
86 # minute
87 #
88 # Event.objects.filter(timestamp__minute=29)
89 # Event.objects.filter(time__minute=46)
90 # Event.objects.filter(timestamp__minute__gte=29)
91
92 # second
93 #
94 # Event.objects.filter(timestamp__second=31)
95 # Event.objects.filter(time__second=2)
96 # Event.objects.filter(timestamp__second__gte=31)
![]()
1 # extra
2 #
3 # extra(self, select=None, where=None, params=None, tables=None, order_by=None, select_params=None)
4 # Entry.objects.extra(select={'new_id': "select col from sometable where othercol > %s"}, select_params=(1,))
5 # Entry.objects.extra(where=['headline=%s'], params=['Lennon'])
6 # Entry.objects.extra(where=["foo='a' OR bar = 'a'", "baz = 'a'"])
7 # Entry.objects.extra(select={'new_id': "select id from tb where id > %s"}, select_params=(1,), order_by=['-nid'])
8
9 # F
10 #
11 # from django.db.models import F
12 # models.Tb1.objects.update(num=F('num')+1)
13
14
15 # Q
16 #
17 # 方式一：
18 # Q(nid__gt=10)
19 # Q(nid=8) | Q(nid__gt=10)
20 # Q(Q(nid=8) | Q(nid__gt=10)) & Q(caption='root')
21
22 # 方式二：
23 # con = Q()
24 # q1 = Q()
25 # q1.connector = 'OR'
26 # q1.children.append(('id', 1))
27 # q1.children.append(('id', 10))
28 # q1.children.append(('id', 9))
29 # q2 = Q()
30 # q2.connector = 'OR'
31 # q2.children.append(('c1', 1))
32 # q2.children.append(('c1', 10))
33 # q2.children.append(('c1', 9))
34 # con.add(q1, 'AND')
35 # con.add(q2, 'AND')
36 #
37 # models.Tb1.objects.filter(con)
38
39
40 # 执行原生SQL
41 #
42 # from django.db import connection, connections
43 # cursor = connection.cursor() # cursor = connections['default'].cursor()
44 # cursor.execute("""SELECT * from auth_user where id = %s""", [1])
45 # row = cursor.fetchone()
![]()
1 def all(self)
2 # 获取所有的数据对象
3
4 def filter(self, *args, **kwargs)
5 # 条件查询
6 # 条件可以是：参数，字典，Q
7
8 def exclude(self, *args, **kwargs)
9 # 条件查询
10 # 条件可以是：参数，字典，Q
11
12 def select_related(self, *fields)
13 性能相关：表之间进行join连表操作，一次性获取关联的数据。
14 model.tb.objects.all().select_related()
15 model.tb.objects.all().select_related('外键字段')
16 model.tb.objects.all().select_related('外键字段__外键字段')
17
18 def prefetch_related(self, *lookups)
19 性能相关：多表连表操作时速度会慢，使用其执行多次SQL查询在Python代码中实现连表操作。
20 # 获取所有用户表
21 # 获取用户类型表where id in (用户表中的查到的所有用户ID)
22 models.UserInfo.objects.prefetch_related('外键字段')
23
24
25
26 from django.db.models import Count, Case, When, IntegerField
27 Article.objects.annotate(
28 numviews=Count(Case(
29 When(readership__what_time__lt=treshold, then=1),
30 output_field=CharField(),
31 ))
32 )
33
34 students = Student.objects.all().annotate(num_excused_absences=models.Sum(
35 models.Case(
36 models.When(absence__type='Excused', then=1),
37 default=0,
38 output_field=models.IntegerField()
39 )))
40
41 def annotate(self, *args, **kwargs)
42 # 用于实现聚合group by查询
43
44 from django.db.models import Count, Avg, Max, Min, Sum
45
46 v = models.UserInfo.objects.values('u_id').annotate(uid=Count('u_id'))
47 # SELECT u_id, COUNT(ui) AS `uid` FROM UserInfo GROUP BY u_id
48
49 v = models.UserInfo.objects.values('u_id').annotate(uid=Count('u_id')).filter(uid__gt=1)
50 # SELECT u_id, COUNT(ui_id) AS `uid` FROM UserInfo GROUP BY u_id having count(u_id) > 1
51
52 v = models.UserInfo.objects.values('u_id').annotate(uid=Count('u_id',distinct=True)).filter(uid__gt=1)
53 # SELECT u_id, COUNT( DISTINCT ui_id) AS `uid` FROM UserInfo GROUP BY u_id having count(u_id) > 1
54
55 def distinct(self, *field_names)
56 # 用于distinct去重
57 models.UserInfo.objects.values('nid').distinct()
58 # select distinct nid from userinfo
59
60 注：只有在PostgreSQL中才能使用distinct进行去重
61
62 def order_by(self, *field_names)
63 # 用于排序
64 models.UserInfo.objects.all().order_by('-id','age')
65
66 def extra(self, select=None, where=None, params=None, tables=None, order_by=None, select_params=None)
67 # 构造额外的查询条件或者映射，如：子查询
68
69 Entry.objects.extra(select={'new_id': "select col from sometable where othercol > %s"}, select_params=(1,))
70 Entry.objects.extra(where=['headline=%s'], params=['Lennon'])
71 Entry.objects.extra(where=["foo='a' OR bar = 'a'", "baz = 'a'"])
72 Entry.objects.extra(select={'new_id': "select id from tb where id > %s"}, select_params=(1,), order_by=['-nid'])
73
74 def reverse(self):
75 # 倒序
76 models.UserInfo.objects.all().order_by('-nid').reverse()
77 # 注：如果存在order_by，reverse则是倒序，如果多个排序则一一倒序
78
79
80 def defer(self, *fields):
81 models.UserInfo.objects.defer('username','id')
82 或
83 models.UserInfo.objects.filter(...).defer('username','id')
84 #映射中排除某列数据
85
86 def only(self, *fields):
87 #仅取某个表中的数据
88 models.UserInfo.objects.only('username','id')
89 或
90 models.UserInfo.objects.filter(...).only('username','id')
91
92 def using(self, alias):
93 指定使用的数据库，参数为别名（setting中的设置）
94
95
96 ##################################################
97 # PUBLIC METHODS THAT RETURN A QUERYSET SUBCLASS #
98 ##################################################
99
100 def raw(self, raw_query, params=None, translations=None, using=None):
101 # 执行原生SQL
102 models.UserInfo.objects.raw('select * from userinfo')
103
104 # 如果SQL是其他表时，必须将名字设置为当前UserInfo对象的主键列名
105 models.UserInfo.objects.raw('select id as nid from 其他表')
106
107 # 为原生SQL设置参数
108 models.UserInfo.objects.raw('select id as nid from userinfo where nid>%s', params=[12,])
109
110 # 将获取的到列名转换为指定列名
111 name_map = {'first': 'first_name', 'last': 'last_name', 'bd': 'birth_date', 'pk': 'id'}
112 Person.objects.raw('SELECT * FROM some_other_table', translations=name_map)
113
114 # 指定数据库
115 models.UserInfo.objects.raw('select * from userinfo', using="default")
116
117 ################### 原生SQL ###################
118 from django.db import connection, connections
119 cursor = connection.cursor() # cursor = connections['default'].cursor()
120 cursor.execute("""SELECT * from auth_user where id = %s""", [1])
121 row = cursor.fetchone() # fetchall()/fetchmany(..)
122
123
124 def values(self, *fields):
125 # 获取每行数据为字典格式
126
127 def values_list(self, *fields, **kwargs):
128 # 获取每行数据为元祖
129
130 def dates(self, field_name, kind, order='ASC'):
131 # 根据时间进行某一部分进行去重查找并截取指定内容
132 # kind只能是："year"（年）, "month"（年-月）, "day"（年-月-日）
133 # order只能是："ASC" "DESC"
134 # 并获取转换后的时间
135 - year : 年-01-01
136 - month: 年-月-01
137 - day : 年-月-日
138
139 models.DatePlus.objects.dates('ctime','day','DESC')
140
141 def datetimes(self, field_name, kind, order='ASC', tzinfo=None):
142 # 根据时间进行某一部分进行去重查找并截取指定内容，将时间转换为指定时区时间
143 # kind只能是 "year", "month", "day", "hour", "minute", "second"
144 # order只能是："ASC" "DESC"
145 # tzinfo时区对象
146 models.DDD.objects.datetimes('ctime','hour',tzinfo=pytz.UTC)
147 models.DDD.objects.datetimes('ctime','hour',tzinfo=pytz.timezone('Asia/Shanghai'))
148
149 """
150 pip3 install pytz
151 import pytz
152 pytz.all_timezones
153 pytz.timezone(‘Asia/Shanghai’)
154 """
155
156 def none(self):
157 # 空QuerySet对象
158
159
160 ####################################
161 # METHODS THAT DO DATABASE QUERIES #
162 ####################################
163
164 def aggregate(self, *args, **kwargs):
165 # 聚合函数，获取字典类型聚合结果
166 from django.db.models import Count, Avg, Max, Min, Sum
167 result = models.UserInfo.objects.aggregate(k=Count('u_id', distinct=True), n=Count('nid'))
168 ===> {'k': 3, 'n': 4}
169
170 def count(self):
171 # 获取个数
172
173 def get(self, *args, **kwargs):
174 # 获取单个对象
175
176 def create(self, **kwargs):
177 # 创建对象
178
179 def bulk_create(self, objs, batch_size=None):
180 # 批量插入
181 # batch_size表示一次插入的个数
182 objs = [
183 models.DDD(name='r11'),
184 models.DDD(name='r22')
185 ]
186 models.DDD.objects.bulk_create(objs, 10)
187
188 def get_or_create(self, defaults=None, **kwargs):
189 # 如果存在，则获取，否则，创建
190 # defaults 指定创建时，其他字段的值
191 obj, created = models.UserInfo.objects.get_or_create(username='root1', defaults={'email': '1111111','u_id': 2, 't_id': 2})
192
193 def update_or_create(self, defaults=None, **kwargs):
194 # 如果存在，则更新，否则，创建
195 # defaults 指定创建时或更新时的其他字段
196 obj, created = models.UserInfo.objects.update_or_create(username='root1', defaults={'email': '1111111','u_id': 2, 't_id': 1})
197
198 def first(self):
199 # 获取第一个
200
201 def last(self):
202 # 获取最后一个
203
204 def in_bulk(self, id_list=None):
205 # 根据主键ID进行查找
206 id_list = [11,21,31]
207 models.DDD.objects.in_bulk(id_list)
208
209 def delete(self):
210 # 删除
211
212 def update(self, **kwargs):
213 # 更新
214
215 def exists(self):
216 # 是否有结果
