Django的Models(三)详解大数据

################################################################## 
# PUBLIC METHODS THAT ALTER ATTRIBUTES AND RETURN A NEW QUERYSET # 
################################################################## 
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中的设置） 
################################################## 
# PUBLIC METHODS THAT RETURN A QUERYSET SUBCLASS # 
################################################## 
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对象 
#################################### 
# METHODS THAT DO DATABASE QUERIES # 
#################################### 
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): 
# 是否有结果