执行原生 SQL 查询

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

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

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

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

警告

在编写原生 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 通过将模型的“应用标签”（您在 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.
...

通常，您可以避免使用原生 SQL 来计算注释，而是使用 Func() 表达式。

将参数传递给 raw()

如果您需要执行参数化查询，则可以使用 raw() 的 params 参数。

>>> 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.
    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"])
管理器
数据库事务
返回顶部