Django所有ORM总结

1. Django ORM操作
# 1.增删改查
# 2. 一般：
# 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__contains='xxxx')
# models.UserInfo.objects.exclude(id=1)
# 3. 排序
user_list = models.UserInfo.objects.all().order_by('-id','name')

# 4. 分组
from django.db.models import Count,Sum,Max,Min
# v =models.UserInfo.objects.values('ut_id').annotate(xxxx=Count('id'))
# print(v.query)
# v =models.UserInfo.objects.values('ut_id').annotate(xxxx=Count('id')).filter(xxxx__gt=2)
# print(v.query)
# v =models.UserInfo.objects.filter(id__gt=2).values('ut_id').annotate(xxxx=Count('id')).filter(xxxx__gt=2)
# print(v.query)



# 5. F，更新时用于获取原来的值
# from django.db.models import F,Q
# models.UserInfo.objects.all().update(age=F("age")+1)

# 6. Q，用于构造复杂查询条件
# 应用一：
# models.UserInfo.objects.filter(Q(id__gt=1))
# models.UserInfo.objects.filter(Q(id=8) | Q(id=2))
# models.UserInfo.objects.filter(Q(id=8) & Q(id=2))
# 应用二：
# q1 = Q()
# q1.connector = 'OR'
# q1.children.append(('id__gt', 1))
# q1.children.append(('id', 10))
# q1.children.append(('id', 9))
#
#
# q2 = Q()
# q2.connector = 'OR'
# q2.children.append(('c1', 1))
# q2.children.append(('c1', 10))
# q2.children.append(('c1', 9))
#
# q3 = Q()
# q3.connector = 'AND'
# q3.children.append(('id', 1))
# q3.children.append(('id', 2))
# q2.add(q3,'OR')
#
# con = Q()
# con.add(q1, 'AND')
# con.add(q2, 'AND')

# models.UserInfo.objects.filter(con)

# 7. extra, 额外查询条件以及相关表，排序

models.UserInfo.objects.filter(id__gt=1)
models.UserInfo.objects.all()
# id name age ut_id


models.UserInfo.objects.extra(self, select=None, where=None, params=None, tables=None, order_by=None, select_params=None)
# a. 映射
# select
# select_params=None
# select 此处 from 表

# b. 条件
# where=None
# params=None,
# select * from 表 where 此处

# c. 表
# tables
# select * from 表,此处

# c. 排序
# order_by=None
# select * from 表 order by 此处


models.UserInfo.objects.extra(
select={'newid':'select count(1) from app01_usertype where id>%s'},
select_params=[1,],
where = ['age>%s'],
params=[18,],
order_by=['-age'],
tables=['app01_usertype']
)
"""
select
app01_userinfo.id,
(select count(1) from app01_usertype where id>1) as newid
from app01_userinfo,app01_usertype
where
app01_userinfo.age > 18
order by
app01_userinfo.age desc
"""

result = models.UserInfo.objects.filter(id__gt=1).extra(
where=['app01_userinfo.id < %s'],
params=[100,],
tables=['app01_usertype'],
order_by=['-app01_userinfo.id'],
select={'uid':1,'sw':"select count(1) from app01_userinfo"}
)
print(result.query)
# SELECT (1) AS "uid", (select count(1) from app01_userinfo) AS "sw", "app01_userinfo"."id", "app01_userinfo"."name", "app01_userinfo"."age", "app01_userinfo"."ut_id" FROM "app01_userinfo" , "app01_usertype" WHERE ("app01_userinfo"."id" > 1 AND (app01_userinfo.id < 100)) ORDER BY ("app01_userinfo".id) DESC

# 8. 原生SQL语句

from django.db import connection, connections

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()


- extra
- 原生SQL语句
- raw
result = models.UserInfo.objects.raw('select * from userinfo')
[obj(UserInfo),obj,]
result = models.UserInfo.objects.raw('select id,1 as name,2 as age,4 as ut_id from usertype')
[obj(UserInfo),obj,]

v1 = models.UserInfo.objects.raw('SELECT id,title FROM app01_usertype',translations=name_map)

# 9. 简单的操作
http://www.cnblogs.com/wupeiqi/articles/6216618.html