执行原生 SQL 查询

Django gives you two ways of performing raw SQL queries: you can use Manager.raw() to perform raw queries and return model instances, or you can avoid the model layer entirely and execute custom SQL directly.

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

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

• QuerySet API 覆盖面很广。

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

在使用原始 SQL 之前，请探索 ORM。在 支持渠道 中咨询，看看 ORM 是否支持您的用例。

Warning

无论何时编写原生 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 选项的文档，它也允许你手动指定数据库的表名。

Warning

不会对传给 .raw() 的 SQL 语句做任何检查。Django 期望该语句会从数据库中返回一个集合，但并不强制如此。若该查询没有返回一些记录，会导致一个（含糊）的错误。

Warning

若你在 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 级别的查询更加高效：

>>> first_person = Person.objects.raw("SELECT * FROM myapp_person LIMIT 1")[0]

延迟模型字段

也可以省略字段：

>>> people = Person.objects.raw("SELECT id, first_name FROM myapp_person")

The Person objects returned by this query will be deferred model instances (see defer()). This means that the fields that are omitted from the query will be loaded on demand. For example:

>>> 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

从外表看，这似乎是查询检索了名字和姓氏。然而，这个示例实际上发出了 3 个查询。只有 raw() 查询检索了名字 - 姓氏是在打印时按需检索的。

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

添加注释

您还可以执行包含模型未定义字段的查询。例如，我们可以使用 PostgreSQL's age() function 来获取一个由数据库计算年龄的人员列表：

>>> 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 参数的值替换。

Note

使用 SQLite 后端时不支持字典参数；使用此后端时，你必须以列表形式传入参数。

Warning

不要对原生查询或 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.
    Assume the column names are unique.
    """
    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.
    Assume the column names are unique.
    """
    desc = cursor.description
    nt_result = namedtuple("Result", [col[0] for col in desc])
    return [nt_result(*row) for row in cursor.fetchall()]

dictfetchall() 和 namedtuplefetchall() 示例假设列名是唯一的，因为游标无法区分来自不同表的列。

以下是三者之间的差异示例：

>>> 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"])