执行原始 SQL 查询

Django 为你提供了两种执行原始 SQL 查询的方法：你可以使用 Manager.raw() 来执行原始查询并返回模型实例，或者你可以完全避免模型层和 [ X198X]直接执行自定义SQL。

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

Django ORM 提供了许多工具来表达查询，而无需编写原始 SQL。例如：

QuerySet API 非常广泛。
您可以使用许多内置的数据库函数注释和聚合。除此之外，您还可以创建自定义查询表达式。

在使用原始 SQL 之前，探索 ORM 。询问支持频道之一，看看 ORM 是否支持您的用例。

警告

无论何时编写原始 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 通过将模型的“应用程序标签”（您在 manage.py startapp 中使用的名称）连接到模型的类名，并在它们之间使用下划线来计算出数据库表名称。在示例中，我们假设 Person 模型位于名为 myapp 的应用程序中，因此其表将为 myapp_person。

有关更多详细信息，请查看 db_table 选项的文档，该选项还允许您手动设置数据库表名称。

警告

不检查传入 .raw() 的 SQL 语句。 Django 期望该语句会从数据库中返回一组行，但不执行任何操作。如果查询不返回行，则会导致（可能是隐晦的）错误。

警告

如果您在 MySQL 上执行查询，请注意 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')

此查询返回的 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

从表面上看，这看起来像是查询检索到了名字和姓氏。然而，这个例子实际上发出了 3 个查询。 raw() 查询只检索了名字——姓氏都是在打印时按需检索的。

只有一个字段不能遗漏——主键字段。 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()

如果需要执行参数化查询，可以使用 params 参数到 raw()：

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

params 是参数列表或字典。您将在查询字符串中使用 %s 占位符作为列表，或使用 %(key)s 占位符作为字典（其中 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'])

执行原始 SQL 查询 — Django 文档

目录