执行原生SQL，场景：复杂SQL语句
1 from django.db import connection, connections 2 3 #配置选择哪个数据库 cursor = connections['db1'].cursor() 4 cursor = connection.cursor() 5 cursor.execute("""SELECT * from auth_user where id = %s""", [1,]) 6 7 # row = cursor.fetchall() # 获取符合条件的所有数据，models.User.objects.all() 8 row = cursor.fetchone() # 获取符合条件的第一条数据,models.User.objects.all().first()
ORM自带的操作
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 UserInfo.objects.extra(where=['headline ? %s'], params=['Lennon']) 70 # select * from userinfo where headline > 'Lennon' 71 72 UserInfo.objects.extra(where=["foo='a' OR bar = 'a'", "baz = 'a'"]) 73 # select * from userinfo where (foo='a' OR bar = 'a') and baz = 'a' 74 75 UserInfo.objects.extra(select={'new_id': "select col from sometable where othercol > %s"}, select_params=(1,)) 76 """ 77 select 78 id, 79 name, 80 (select col from sometable where othercol > 1) as new_id 81 """ 82 UserInfo.objects.extra(select={'new_id': "select id from tb where id > %s"}, select_params=(1,), order_by=['-nid']) 83 84 def reverse(self): 85 # 倒序 86 models.UserInfo.objects.all().order_by('-nid').reverse() 87 # 注：如果存在order_by，reverse则是倒序，如果多个排序则一一倒序 88 89 90 def defer(self, *fields): 91 models.UserInfo.objects.defer('username','id') 92 或 93 models.UserInfo.objects.filter(...).defer('username','id') 94 #映射中排除某列数据 95 96 def only(self, *fields): 97 #仅取某个表中的数据 98 models.UserInfo.objects.only('username','id') 99 或 100 models.UserInfo.objects.filter(...).only('username','id') 101 102 def using(self, alias): 103 指定使用的数据库，参数为别名（setting中的设置） 104 105 models.UserInfo.objects.filter(id=5).using('db1') 106 107 108 ################################################## 109 # PUBLIC METHODS THAT RETURN A QUERYSET SUBCLASS # 110 ################################################## 111 112 def raw(self, raw_query, params=None, translations=None, using=None): 113 # 执行原生SQL 114 models.UserInfo.objects.raw('select * from userinfo where id > 10 ') 115 116 # 如果SQL是其他表时，必须将名字设置为当前UserInfo对象的主键列名 117 models.UserInfo.objects.raw('select id as nid from 其他表') 118 119 # 为原生SQL设置参数 120 models.UserInfo.objects.raw('select id as nid from userinfo where nid>%s', params=[12,]) 121 122 # 将获取的到列名转换为指定列名 123 name_map = {'first': 'first_name', 'last': 'last_name', 'bd': 'birth_date', 'pk': 'id'} 124 Person.objects.raw('SELECT * FROM some_other_table', translations=name_map) 125 126 # 指定数据库 127 models.UserInfo.objects.raw('select * from userinfo', using="default") 128 129 ################### 原生SQL ################### 130 from django.db import connection, connections 131 cursor = connection.cursor() # cursor = connections['default'].cursor() 132 cursor.execute("""SELECT * from auth_user where id = %s""", [1]) 133 row = cursor.fetchone() # fetchall()/fetchmany(..) 134 135 136 def values(self, *fields): 137 # 获取每行数据为字典格式 138 139 def values_list(self, *fields, **kwargs): 140 # 获取每行数据为元祖 141 142 def dates(self, field_name, kind, order='ASC'): 143 # 根据时间进行某一部分进行去重查找并截取指定内容 144 # kind只能是："year"（年）, "month"（年-月）, "day"（年-月-日） 145 # order只能是："ASC" "DESC" 146 # 并获取转换后的时间 147 - year : 年-01-01 148 - month: 年-月-01 149 - day : 年-月-日 150 151 models.DatePlus.objects.dates('ctime','day','DESC') 152 153 def datetimes(self, field_name, kind, order='ASC', tzinfo=None): 154 # 根据时间进行某一部分进行去重查找并截取指定内容，将时间转换为指定时区时间 155 # kind只能是 "year", "month", "day", "hour", "minute", "second" 156 # order只能是："ASC" "DESC" 157 # tzinfo时区对象 158 models.DDD.objects.datetimes('ctime','hour',tzinfo=pytz.UTC) 159 models.DDD.objects.datetimes('ctime','hour',tzinfo=pytz.timezone('Asia/Shanghai')) 160 161 """ 162 pip3 install pytz 163 import pytz 164 pytz.all_timezones 165 pytz.timezone(‘Asia/Shanghai’) 166 """ 167 168 def none(self): 169 # 空QuerySet对象 170 171 172 #################################### 173 # METHODS THAT DO DATABASE QUERIES # 174 #################################### 175 176 def aggregate(self, *args, **kwargs): 177 # 聚合函数，获取字典类型聚合结果 178 from django.db.models import Count, Avg, Max, Min, Sum 179 result = models.UserInfo.objects.aggregate(k=Count('u_id', distinct=True), n=Count('nid')) 180 ===> {'k': 3, 'n': 4} 181 182 def count(self): 183 # 获取个数 184 185 def get(self, *args, **kwargs): 186 # 获取单个对象 187 188 def create(self, **kwargs): 189 # 创建对象 190 191 def bulk_create(self, objs, batch_size=None): 192 # 批量插入 193 # batch_size表示一次插入的个数 194 objs = [ 195 models.DDD(name='r11'), 196 models.DDD(name='r22') 197 ] 198 models.DDD.objects.bulk_create(objs, 10) 199 200 def get_or_create(self, defaults=None, **kwargs): 201 # 如果存在，则获取，否则，创建 202 # defaults 指定创建时，其他字段的值 203 obj, created = models.UserInfo.objects.get_or_create(username='root1', defaults={'email': '1111111','u_id': 2, 't_id': 2}) 204 205 def update_or_create(self, defaults=None, **kwargs): 206 # 如果存在，则更新，否则，创建 207 # defaults 指定创建时或更新时的其他字段 208 obj, created = models.UserInfo.objects.update_or_create(username='root1', defaults={'email': '1111111','u_id': 2, 't_id': 1}) 209 210 def first(self): 211 # 获取第一个 212 213 def last(self): 214 # 获取最后一个 215 216 def in_bulk(self, id_list=None): 217 # 根据主键ID进行查找 218 id_list = [11,21,31] 219 models.DDD.objects.in_bulk(id_list) 220 221 models.User.objects.filter(id__in=[11,21,31]) 222 223 def delete(self): 224 # 删除 225 226 def update(self, **kwargs): 227 # 更新 228 229 def exists(self): 230 # 是否有结果