猿变实验室之猿变开发手册-模型与数据库-执行原生 SQL 查询

执行原生 SQL 查询

Django 允许你用两种方式执行原生 SQL 查询：你可以使用 Manager.raw() 来执行原生查询并返回模型实例，或者完全不用模型层直接执行自定义 SQL。

在使用原生 SQL 之前探索 ORM！

Django ORM 提供了很多工具，允许你在不编写原生 SQL 的情况下表达查询。例如：

QuerySet API 覆盖面很广。
你可以用很多内置的数据库函数进行 annotate 和 aggregate。除此之外，你还可以创建查询表达式。

Before using raw SQL, explore the ORM. Ask on one of the support channels to see if the ORM supports your use case.

警告

无论何时编写原生 SQL 都要万分小心。使用时，你要用 params 将任何用户传入的参数进行安全转义，避免 SQL 注入攻击。阅读 SQL 注入保护了解更多。

执行原生查询

若管理器方法 raw() 能用于执行原生 SQL 查询，就会返回模型实例：

Manager.raw(raw_query, params=(), 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 会执行比较前将表中所有数据强制转为整数。例如，若数据表包含的值有 'abc' 和 'def'，而查询语句为 WHERE mycolumn=0，这两行都会匹配上。要避免这种情况，在将值传给查询语句前进行合适的类型转换。

将查询字段映射为模型字段

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 层面使用 limit 子句:

>>> 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, # This will be retrieved by the original query
...           p.last_name) # This will be retrieved on demand
...
John Smith
Jane Jones

From outward appearances, this looks like the query has retrieved both the first name and last name. However, this example actually issued 3 queries. Only the first names were retrieved by the raw() query -- the last names were both retrieved on demand when they were printed.

只有一个字段你不能省略 —— 主键字段。Django 用主键来区分模型实例，所以必须在原生查询语句中包含主键。若你忘了包含主键会抛出 FieldDoesNotExist 异常。

添加注释

你可以执行带有模型中未定义字段的查询语句。例如，我们能用 PostgreSQL 的 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.
...

你总是可以用 Func() 表达式避免使用原生 SQL 去计算注释。

将参数传给 `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 后端时不支持字典参数；使用此后端时，你必须以列表形式传入参数。

警告

不要对原生查询或 SQL 字符串中的引号占位符使用字符串格式化！

临时将上述查询写作:

>>> query = 'SELECT * FROM myapp_person WHERE last_name = %s' % lname
>>> Person.objects.raw(query)

你可能认为你需要将查询写成这样（用单引号包裹 %s）:

>>> query = "SELECT * FROM myapp_person WHERE last_name = '%s'"

不要犯其它错误。

正如防御 SQL 注入介绍的，使用 params 参数和不用引号包裹占位符使你免受 SQL 注入攻击，这是一个攻击者常用的漏洞，将任意 SQL 注入你的数据库。若你使用了字符串插入或用引号包裹占位符，你正处于 SQL 注入的风险中。

直接执行自定义 SQL

有时候，甚至 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

要避免 SQL 注入，你绝对不能在 SQL 字符串中用引号包裹 %s 占位符。

注意，若要在查询中包含文本的百分号，你需要在传入参数使用两个百分号:

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
with connections['my_db_alias'].cursor() as 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 绑定使用。这是为了一致性和正确性。

将指针作为上下文的管理器:

with connection.cursor() as c:
    c.execute(...)

相当于:

c = connection.cursor()
try:
    c.execute(...)
finally:
    c.close()

调用存储流程

CursorWrapper.callproc(procname, params=None, kparams=None)

以给定名称调用数据库存储流程。要提供一个序列 (params) 或字典 (kparams) 作为输入参数。大多数数据库不支持 kparams。对于 Django 内置后端来说，只有 Oracle 支持。

例如，在一个 Oracle 数据库中指定存储流程：

CREATE PROCEDURE "TEST_PROCEDURE"(v_i INTEGER, v_text NVARCHAR2(10)) AS
    p_i INTEGER;
    p_text NVARCHAR2(10);
BEGIN
    p_i := v_i;
    p_text := v_text;
    ...
END;

这将调用该存储流程:

with connection.cursor() as cursor:
    cursor.callproc('test_procedure', [1, 'test'])