django orm 原生sql queryset

由于一些极其复杂的查询，orm搞不定的，有三种方式实现原生sql查询

 

extra：提供额外查询参数的机制，一定程度上可以实现sql注入

1 where 设置查询条件，params 如果where设置了字符串

In [2]: s=Vocation.objects.extra(where=["job=%s or 1=1","baz = 'a'"],params=["设计"])

In [3]: s
Out[3]: <QuerySet [<Vocation: 3>]>
 

新增查询字段

In [2]:  ss=Infos.objects.extra(select={"seat":"%s"},select_params=["seatInfo"])#select_params为 select的%s提供参数

In [3]: print(ss.query)
SELECT (seatInfo) AS `seat`, `Infos`.`id`, `Infos`.`job`, `Infos`.`title`, `Infos`.`payment`, `Infos`.`name_id` FROM `Infos`

链接数据表

In [4]: s=Vocation.objects.extra(tables=["test_personinfo"], order_by=['-nid'])

In [5]: s
Out[5]: s<QuerySet [<Vocation: 5>, <Vocation: 4>, <Vocation: 3>, <Vocation: 2>, <Vocation: 1>, <Vocation: 5>, <Vocation: 4>, <Vocation: 3>, <Vocation: 2>, <Vocation: 1>, <Vocation: 5>, <Vocation: 4>, <Vocation: 3>, <Vocation: 2>, <Vocation: 1>, <Vocation: 5>, <Vocation: 4>, <Vocation: 3>, <Vocation: 2>, <Vocation: 1>, '...(remaining elements truncated)...']>

In [6]: print(s.query)
SELECT `test_vocation`.`id`, `test_vocation`.`job`, `test_vocation`.`title`, `test_vocation`.`payment`, `test_vocation`.`name_id` FROM `test_vocation` , `test_personinfo`
order_by :设置排序方式

raw，返回RawQueryset对象

d={"name":"call"}

In [18]: s=Vocation.objects.raw("select * from %s",params=['vocation']，using=”配置文件 默认为default“，translations=d #dict对象)#translations为查询的字段设置别名

for i in s：

    s.call

In [19]: s
Out[19]: <RawQuerySet: select * from index_vocation>
 

execute 不走orm 完全原生

from django.db import connection

cursor = connection.cursor()

cursor.execute(sql)

#ret = cursor.fetchone() 查一条

ret = cursor.fetchall() 查所有

 

返回QuerySet对象的方法有

all()、filter()、exclude()、order_by()、reverse()、distinct()