执行原生 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(...)

You could then execute custom SQL like so:

>>> 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() 字段将查询语句中的字段映射至模型中的字段。

The order of fields in your query doesn’t matter. In other words, both of the following queries work identically:

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

Matching is done by name. This means that you can use SQL’s AS clauses to map fields in the query to model fields. So if you had some other table that had Person data in it, you could easily map it into Person instances:

>>> Person.objects.raw(
...     """
...     SELECT first AS first_name,
...            last AS last_name,
...            bd AS birth_date,
...            pk AS id,
...     FROM some_other_table
...     """
... )

只要名字对上了，模型实例就会被正确创建。

Alternatively, you can map fields in the query to model fields using the translations argument to raw(). This is a dictionary mapping names of fields in the query to names of fields on the model. For example, the above query could also be written:

>>> 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() supports indexing, so if you need only the first result you can write:

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

However, the indexing and slicing are not performed at the database level. If you have a large number of Person objects in your database, it is more efficient to limit the query at the SQL level:

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

延迟模型字段

Fields may also be left out:

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

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 异常。

添加注释

You can also execute queries containing fields that aren’t defined on the model. For example, we could use PostgreSQL’s age() function to get a list of people with their ages calculated by the database:

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

If you need to perform parameterized queries, you can use the params argument to raw():

>>> lname = "Doe"
>>> Person.objects.raw("SELECT * FROM myapp_person WHERE last_name = %s", [lname])

params 是一个参数字典。你将用一个列表替换查询字符串中 %s 占位符，或用字典替换 %(key)s 占位符（key 被字典 key 替换），不论你使用哪个数据库引擎。这些占位符会被 params 参数的值替换。

备注

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

警告

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

It’s tempting to write the above query as:

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

You might also think you should write your query like this (with quotes around %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()]

The dictfetchall() and namedtuplefetchall() examples assume unique column names, since a cursor cannot distinguish columns from different tables.

Here is an example of the difference between the three:

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