Django的F查询和Q查询,事务,ORM执行原生SQL

太过爱你忘了你带给我的痛 2023-08-17 15:21 108阅读 0赞

# [F查询和Q查询，事务及其他][F_Q] #

# F查询和Q查询 #

## F查询 ##

在上面所有的例子中，我们构造的过滤器都只是将字段值与某个我们自己设定的常量做比较。如果我们要对两个字段的值做比较，那该怎么做呢？

Django 提供 F() 来做这样的比较。F() 的实例可以在查询中引用字段，来比较同一个 model 实例中两个不同字段的值。

示例1：

查询出卖出数大于库存数的商品

from django.db.models import F
    ret1=models.Product.objects.filter(maichu__gt=F('kucun'))
    print(ret1)

F可以帮我们取到表中某个字段对应的值来当作我的筛选条件，而不是我认为自定义常量的条件了，实现了动态比较的效果

Django 支持 F() 对象之间以及 F() 对象和常数之间的加减乘除和取模的操作。基于此可以对表中的数值类型进行数学运算

将每个商品的价格提高50块

models.Product.objects.update(price=F('price')+50)

**引申：**

如果要修改char字段咋办(千万不能用上面对数值类型的操作！！！)？

如：把所有书名后面加上'新款',(这个时候需要对字符串进行拼接Concat操作，并且要加上拼接值Value)

from django.db.models.functions import Concat
    from django.db.models import Value
    ret3=models.Product.objects.update(name=Concat(F('name'),Value('新款')))

Concat表示进行字符串的拼接操作，参数位置决定了拼接是在头部拼接还是尾部拼接，Value里面是要新增的拼接值

## Q查询 ##

filter() 等方法中逗号隔开的条件是与的关系。 如果你需要执行更复杂的查询（例如OR语句），你可以使用Q对象。

示例1：

查询 卖出数大于100 或者 价格小于100块的

from django.db.models import Q
    models.Product.objects.filter(Q(maichu__gt=100)|Q(price__lt=100))

对条件包裹一层Q时候，filter即可支持交叉并的比较符

示例2：

查询 库存数是100 并且 卖出数不是0 的产品

models.Product.objects.filter(Q(kucun=100)&~Q(maichu=0))

**我们可以组合& 和| 操作符以及使用括号进行分组来编写任意复杂的Q 对象。**

**同时，Q 对象可以使用~ 操作符取反，这允许组合正常的查询和取反(NOT) 查询。**

示例3：

查询产品名包含新款， 并且库存数大于60的

models.Product.objects.filter(Q(kucun__gt=60), name__contains="新款")

**查询函数可以混合使用Q 对象和关键字参数。所有提供给查询函数的参数（关键字参数或Q 对象）都将"AND”在一起。但是，如果出现Q 对象，它必须位于所有关键字参数的前面。**

# 事务 #

事务的定义:将多个sql语句操作变成原子性操作，要么同时成功，有一个失败则里面回滚到原来的状态，保证数据的完整性和一致性(NoSQL数据库对于事务则是部分支持)

![复制代码][copycode.gif]

# 事务
        # 买一本 跟老男孩学Linux 书
        # 在数据库层面要做的事儿
        # 1. 创建一条订单数据
        # 2. 去产品表 将卖出数+1， 库存数-1
        from django.db.models import F
        from django.db import transaction
        # 开启事务处理
        try:
            with transaction.atomic():
                # 创建一条订单数据
                models.Order.objects.create(num="110110111", product_id=1, count=1)
                # 能执行成功
                models.Product.objects.filter(id=1).update(kucun=F("kucun")-1, maichu=F("maichu")+1)
        except Exception as e:
            print(e)

![复制代码][copycode.gif]

# 其他鲜为人知的操作 #

## Django ORM执行原生SQL ##

条件假设:就拿博客园举例，我们写的博客并不是按照年月日来分档，而是按照年月来分的，而我们的DateField时间格式是年月日形式，也就是说我们需要对从数据库拿到的时间格式的数据再进行一次处理拿到我们想要的时间格式，这样的需求，Django是没有给我们提供方法的，需要我们自己去写处理语句了

![1342004-20180622153757447-1520335198.png][]

![ContractedBlock.gif][] ORM 执行原生SQL的方法

\# extra  
\# 在QuerySet的基础上继续执行子语句  
\# extra(self, select=None, where=None, params=None, tables=None, order\_by=None, select\_params=None)

\# select和select\_params是一组，where和params是一组，tables用来设置from哪个表  
\# 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'\])

举个例子：  
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  
"""

\# 执行原生SQL  
\# 更高灵活度的方式执行原生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()

ORM 执行原生SQL的方法

## QuerySet方法大全 ##

**几个比较重要的方法：**

### update()与save()的区别 ###

两者都是对数据的修改保存操作，但是save()函数是将数据列的全部数据项全部重新写一遍，而update()则是针对修改的项进行针对的更新效率高耗时少

所以以后对数据的修改保存用update()

### select\_related和prefetch\_related ###

![复制代码][copycode.gif]

def select_related(self, *fields)
        性能相关：表之间进行join连表操作，一次性获取关联的数据。
    
        总结：
        1. select_related主要针一对一和多对一关系进行优化。
        2. select_related使用SQL的JOIN语句进行优化，通过减少SQL查询的次数来进行优化、提高性能。
    
    def prefetch_related(self, *lookups)
        性能相关：多表连表操作时速度会慢，使用其执行多次SQL查询在Python代码中实现连表操作。
    
        总结：
        1. 对于多对多字段（ManyToManyField）和一对多字段，可以使用prefetch_related()来进行优化。
        2. prefetch_related()的优化方式是分别查询每个表，然后用Python处理他们之间的关系。

![复制代码][copycode.gif]

### ![1342004-20180707190651556-702396876.png][] ###

### ![1342004-20180707192517858-1910038173.png][] ###

### bulk\_create批量插入数据 ###

要求：一次性插入多条数据

data = ["".join([str(random.randint(65, 99)) for i in range(4)]) for j in range(100)]
    obj_list = [models.A(name=i) for i in data]
    models.A.objects.bulk_create(obj_list)

### ![1342004-20180625143117993-562746753.png][] ###

### 方法大全 ###

![ContractedBlock.gif][] QuerySet方法大全

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\# 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连表操作，一次性获取关联的数据。

总结：  
1. select\_related主要针一对一和多对一关系进行优化。  
2. select\_related使用SQL的JOIN语句进行优化，通过减少SQL查询的次数来进行优化、提高性能。

def prefetch\_related(self, \*lookups)  
性能相关：多表连表操作时速度会慢，使用其执行多次SQL查询在Python代码中实现连表操作。

总结：  
1. 对于多对多字段（ManyToManyField）和一对多字段，可以使用prefetch\_related()来进行优化。  
2. prefetch\_related()的优化方式是分别查询每个表，然后用Python处理他们之间的关系。

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):  
\# 是否有结果

QuerySet方法大全

转载于:https://www.cnblogs.com/Sunbreaker/p/11551122.html

[F_Q]: https://www.cnblogs.com/Dominic-Ji/p/9213887.html
[copycode.gif]: /images/20230810/7dba9a1e5cfb4a01bb37837e5cab6c92.png
[1342004-20180622153757447-1520335198.png]: /images/20230810/4bc6bd2b085d49a1b81af497eb6dc68b.png
[ContractedBlock.gif]: /images/20230810/a1e206c0b49f4eea8f814931c68a977b.png
[1342004-20180707190651556-702396876.png]: /images/20230810/b943152b587a437dac5003414abd44be.png
[1342004-20180707192517858-1910038173.png]: /images/20230810/8ccc386839df4ed29ab8f2959fdd5136.png
[1342004-20180625143117993-562746753.png]: /images/20230810/0e788ddf40da4bf59464c52b86407df7.png