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)

装载与：https://www.cnblogs.com/duhong0520/p/11416230.html