在 模型查询APIs 不能满足使用时可以使用原生SQL查询.
Django提供了两种执行原生SQL查询的方式: 一是使用 Manager.raw()
执行原生查询返回模型实例,
二是不使用模型层 直接执行自定义SQL.
警告
使用原生SQL时要格外小心. 确保将 params
中用户传入的参数进行转义, 避免遭到SQL注入攻击.
详见 SQL注入保护.
管理器方法 raw()
用于执行原生SQL查询返回模型实例:
Manager.
raw
(raw_query, params=None, translations=None)¶该方法接收原生SQL语句并执行, 返回一个
django.db.models.query.RawQuerySet
实例. 该 RawQuerySet
实例可以像
QuerySet
那样通过迭代获取实例对象.
下面用具体例子来解释下. 假设有如下模型:
class Person(models.Model):
first_name = models.CharField(...)
last_name = models.CharField(...)
birth_date = models.DateField(...)
可以像这样执行自定义SQL语句:
>>> for p in Person.objects.raw('SELECT * FROM myapp_person'):
... print(p)
John Smith
Jane Jones
当然这个例子并不是很贴切, 因为它和 Person.objects.all()
功能一样. 但是 raw()
有很多额外选项使得它非常强大.
模型表名
上面例子中 Person
表的名称从何而来?
默认情况下, Django通过将模型的”app label”(即在 manage.py startapp
中使用的名称)和模型类名用下划线拼接来推算模型表名称.
在本例中, 我们假定 Person
模型位于一个叫做 myapp
的应用中, 这样模型的表名就是 myapp_person
.
更多信息详见 db_table
选项文档, 它可以自定义表名.
警告
传入 .raw()
的SQL语句不会被检查. Django默认语句会返回一组行, 但没有强制要求.
但如果该查询没有返回这些数据, 则会导致一些(神秘)错误.
警告
在MySQL上执行查询时, 要小心在类型不一样时MySQL的静默强制类型可能导致意想不到的事情发生.
比如在一个字符串类型的字段上查询一个整数类型的值. MySQL会在比较前强制把每个值的类型转成整数.
例如, 字段包含值 'abc'
和 'def'
, 在查询 WHERE mycolumn=0
时这两行都会匹配上.
要防止这种情况, 在查询中使用值之前要做好正确的类型转换.
警告
虽然 RawQuerySet
实例可以像 QuerySet
一样被迭代,
但 RawQuerySet
并没有实现 QuerySet
上的所有方法. 比如,
__bool__()
和 __len__()
方法 RawQuerySet
就没有实现,
所以 RawQuerySet
实例的布尔值始终为 True
. RawQuerySet
没有实现它们的原因是, 在没有内部缓存的情况下会导致性能下降, 而且增加内部缓存不向后兼容.
raw()
方法会自动将查询字段映射到模型字段.
这不受查询字段顺序的影响. 换句话说, 下面两种查询结果是相同的:
>>> Person.objects.raw('SELECT id, first_name, last_name, birth_date FROM myapp_person')
...
>>> Person.objects.raw('SELECT last_name, birth_date, first_name, id FROM myapp_person')
...
匹配是根据名字来的. 这意味着你可以通过SQL的 AS
子句来将查询字段映射到模型字段.
因此如果在其他表中存在 Person
数据, 可以通过该访问来将其转换成 Person
实例:
>>> Person.objects.raw('''SELECT first AS first_name,
... last AS last_name,
... bd AS birth_date,
... pk AS id,
... FROM some_other_table''')
只要名字能对应上模型的实例就会被正确创建.
也可以使用 raw()
的 translations
参数来将查询的字段名字映射到模型字段名字, 该参数是一个字典.
例如, 上面的查询可以写成这样:
>>> name_map = {'first': 'first_name', 'last': 'last_name', 'bd': 'birth_date', 'pk': 'id'}
>>> Person.objects.raw('SELECT * FROM some_other_table', translations=name_map)
raw()
方法支持索引访问, 比如查询第一条记录可以这样写:
>>> first_person = Person.objects.raw('SELECT * FROM myapp_person')[0]
然而, 索引和切片并不是在数据库层级实现的. 如果 Person
在数据库中的数据量非常大, 最好将此类操作放在SQL中:
>>> first_person = Person.objects.raw('SELECT * FROM myapp_person LIMIT 1')[0]
查询时也可以省略部分字段:
>>> people = Person.objects.raw('SELECT id, first_name FROM myapp_person')
该查询返回的 Person
对象是一个延迟模型实例
(见 defer()
). 这样查询时被省略的字段会在使用时加载. 例如:
>>> for p in Person.objects.raw('SELECT id, first_name FROM myapp_person'):
... print(p.first_name, # 这是开始时查询得到的
... p.last_name) # 这是被使用时延迟加载的
...
John Smith
Jane Jones
表面上看起来这个查询同时查出了first name和last name. 然而这个例子中实际上执行了3次查询. 只有first names是通过raw()查询出来的 – last names 是在被print时延迟加载的.
除了主键字段其他字段都是可以省略的. 主键字段是Django用来识别模型实例的, 因此原生查询必须包含主键.
如果没有则会引发 InvalidQuery
异常.
查询时也可以使用模型中没有的字段. 例如, 使用 PostgreSQL的 age() 函数 查询数据库中一组人并带有age字段:
>>> people = Person.objects.raw('SELECT *, age(birth_date) AS age FROM myapp_person')
>>> for p in people:
... print("%s is %s." % (p.first_name, p.age))
John is 37.
Jane is 42.
...
raw()
¶通过给 raw()
传递 params
参数, 执行参数化查询:
>>> lname = 'Doe'
>>> Person.objects.raw('SELECT * FROM myapp_person WHERE last_name = %s', [lname])
params
是一组参数列表或字典. 列表参数被用于替换查询语句中的 %s
占位符,
字典参数被用于替换查询语句中的 %(key)s
占位符(key
替换成字典中对应key的值),
不论使用哪个数据库引擎这些占位符会被 params
参数的值替换.
注解
SQLite不支持字典参数; 使用SQLite只能使用列表参数.
警告
不要在原生查询语句上使用字符串格式化!
它类似于这种样子:
>>> query = 'SELECT * FROM myapp_person WHERE last_name = %s' % lname
>>> Person.objects.raw(query)
千万不要这样做.
使用 params
参数可以完全防止 SQL注入攻击, 这是一个攻击者常用的漏洞,
它可以向你的数据库中注入任何SQL语句, 如果使用字符串格式化则不能避免SQL注入攻击,
记住一定要使用 params
参数让你避免此风险.
有时候 Manager.raw()
也不能满足需求: 我们可能不需要将查询结果映射成模型,
或者我们需要执行
UPDATE
INSERT
和 DELETE
查询.
这种情况下, 可以直接访问数据库绕开模型层.
django.db.connection
提供了默认数据库的连接. 要使用这个数据库连接, 先调用 connection.cursor()
方法来获取一个游标对象.
然后调用 cursor.execute(sql, [params])
来执行SQL, 调用 cursor.fetchone()
或者 cursor.fetchall()
来获取返回行.
例如:
from django.db import connection
def my_custom_sql(self):
with connection.cursor() as cursor:
cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])
cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
row = cursor.fetchone()
return row
注意, 如果查询中包含百分号, 你需要写成两个百分号:
cursor.execute("SELECT foo FROM bar WHERE baz = '30%'")
cursor.execute("SELECT foo FROM bar WHERE baz = '30%%' AND id = %s", [self.id])
使用了 多个数据库 时, 可以使用 django.db.connections
获取指定的数据库连接(或游标)对象.
django.db.connections
是一个类似字典的对象, 它允许你通过数据库的别名获取指定的连接:
from django.db import connections
cursor = connections['my_db_alias'].cursor()
# Your code here...
默认情况下, Python DB API返回的结果不包含字段名, 也就是说你拿到的是一组 list
的值而不是 dict
.
在追求较少的运算和内存消耗下, 可以通过以下代码来返回 dict
结果:
def dictfetchall(cursor):
"Return all rows from a cursor as a dict"
columns = [col[0] for col in cursor.description]
return [
dict(zip(columns, row))
for row in cursor.fetchall()
]
另一个方法是使用Python标准库中的 collections.namedtuple()
.
namedtuple
是一个类元组对象, 可以通过属性访问字段. 它是可索引和可迭代的.
结果是不可变的, 可以通过字段名称和索引访问, 这在某些场景下非常实用:
from collections import namedtuple
def namedtuplefetchall(cursor):
"Return all rows from a cursor as a namedtuple"
desc = cursor.description
nt_result = namedtuple('Result', [col[0] for col in desc])
return [nt_result(*row) for row in cursor.fetchall()]
下面例子演示了这三者的区别:
>>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2");
>>> cursor.fetchall()
((54360982, None), (54360880, None))
>>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2");
>>> dictfetchall(cursor)
[{'parent_id': None, 'id': 54360982}, {'parent_id': None, 'id': 54360880}]
>>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2");
>>> results = namedtuplefetchall(cursor)
>>> results
[Result(id=54360982, parent_id=None), Result(id=54360880, parent_id=None)]
>>> results[0].id
54360982
>>> results[0][0]
54360982
connection
和 cursor
实现了 PEP 249 中大部分的 Python DB-API — 除非它涉及 事务处理.
如果你不熟悉Python DB-API, 请注意 cursor.execute()
中SQL语句使用 "%s"
占位符,
而不是直接在SQL中添加参数. 如果你使用这种方法, 底层数据库的库会在必要时自动转义你的参数.
还需要注意Django中是 "%s"
占位符, 不是 "?"
占位符, 后者由 SQLite 和 Python 绑定使用. 这是为了一致性和正确性.
将cursor作为上下文管理器使用:
with connection.cursor() as c:
c.execute(...)
它等价于:
c = connection.cursor()
try:
c.execute(...)
finally:
c.close()
10月 29, 2021