执行原生 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 一样被迭代获取对象实例。

最好用例子来解释。假设你有以下模型:

  1. class Person(models.Model):
  2. first_name = models.CharField(...)
  3. last_name = models.CharField(...)
  4. birth_date = models.DateField(...)

然后,您可以执行自定义 SQL,如下所示:

  1. >>> for p in Person.objects.raw("SELECT * FROM myapp_person"):
  2. ... print(p)
  3. ...
  4. John Smith
  5. 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() 字段将查询语句中的字段映射至模型中的字段。

查询中字段的顺序无关紧要。换句话说,以下两个查询的效果相同:

  1. >>> Person.objects.raw("SELECT id, first_name, last_name, birth_date FROM myapp_person")
  2. >>> Person.objects.raw("SELECT last_name, birth_date, first_name, id FROM myapp_person")

匹配是通过名称完成的。这意味着您可以使用 SQL 的 AS 子句将查询中的字段映射到模型字段。因此,如果您有另一个包含 Person 数据的表,您可以轻松地将其映射为 Person 实例:

  1. >>> Person.objects.raw(
  2. ... """
  3. ... SELECT first AS first_name,
  4. ... last AS last_name,
  5. ... bd AS birth_date,
  6. ... pk AS id,
  7. ... FROM some_other_table
  8. ... """
  9. ... )

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

或者,您可以使用 raw()translations 参数将查询中的字段映射到模型字段。这是一个将查询中字段的名称映射到模型字段名称的字典。例如,上面的查询也可以这样写:

  1. >>> name_map = {"first": "first_name", "last": "last_name", "bd": "birth_date", "pk": "id"}
  2. >>> Person.objects.raw("SELECT * FROM some_other_table", translations=name_map)

索引查询

raw() 支持索引,因此如果只需要第一个结果,可以这样写:

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

但是,索引和切片不是在数据库级别执行的。如果您的数据库中有大量的 Person 对象,限制 SQL 级别的查询更加高效:

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

延迟模型字段

也可以省略字段:

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

这个查询返回的 Person 对象将是延迟加载的模型实例(参见 defer())。这意味着从查询中省略的字段将按需加载。例如:

  1. >>> for p in Person.objects.raw("SELECT id, first_name FROM myapp_person"):
  2. ... print(
  3. ... p.first_name, # This will be retrieved by the original query
  4. ... p.last_name, # This will be retrieved on demand
  5. ... )
  6. ...
  7. John Smith
  8. Jane Jones

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

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

添加注释

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

  1. >>> people = Person.objects.raw("SELECT *, age(birth_date) AS age FROM myapp_person")
  2. >>> for p in people:
  3. ... print("%s is %s." % (p.first_name, p.age))
  4. ...
  5. John is 37.
  6. Jane is 42.
  7. ...

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

将参数传给 raw()

如果需要执行参数化查询,可以使用 raw()params 参数:

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

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

备注

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

警告

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

将上述查询写成以下形式可能很诱人:

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

您可能也认为应该这样编写查询(在 %s 周围加上引号):

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

不要犯其它错误。

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

直接执行自定义 SQL

有时候,甚至 Manager.raw() 都无法满足需求:你可能要执行不明确映射至模型的查询语句,或者就是直接执行 UPDATEINSERTDELETE 语句。

这些情况下,你总是能直接访问数据库,完全绕过模型层。

对象 django.db.connection 代表默认数据库连接。要使用这个数据库连接,调用 connection.cursor() 来获取一个指针对象。然后,调用 cursor.execute(sql, [params]) 来执行该 SQL 和 cursor.fetchone(),或 cursor.fetchall() 获取结果数据。

例如:

  1. from django.db import connection
  2. def my_custom_sql(self):
  3. with connection.cursor() as cursor:
  4. cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])
  5. cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
  6. row = cursor.fetchone()
  7. return row

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

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

  1. cursor.execute("SELECT foo FROM bar WHERE baz = '30%'")
  2. cursor.execute("SELECT foo FROM bar WHERE baz = '30%%' AND id = %s", [self.id])

若你同时使用 不止一个数据库,你可以使用 django.db.connections 获取指定数据库的连接(和指针)。 django.db.connections 是一个类字典对象,它允许你通过连接别名获取指定连接:

  1. from django.db import connections
  2. with connections["my_db_alias"].cursor() as cursor:
  3. # Your code here
  4. ...

默认情况下,Python DB API 返回的结果不会包含字段名,这意味着你最终会收到一个 list,而不是一个 dict。要追求较少的运算和内存消耗,你可以以 dict 返回结果,通过使用如下的玩意:

  1. def dictfetchall(cursor):
  2. """
  3. Return all rows from a cursor as a dict.
  4. Assume the column names are unique.
  5. """
  6. columns = [col[0] for col in cursor.description]
  7. return [dict(zip(columns, row)) for row in cursor.fetchall()]

另一个选项是使用来自 Python 标准库的 collections.namedtuple()namedtuple 是一个类元组对象,可以通过属性查找来访问其包含的字段;也能通过索引和迭代。结果都是不可变的,但能通过字段名或索引访问,这很实用:

  1. from collections import namedtuple
  2. def namedtuplefetchall(cursor):
  3. """
  4. Return all rows from a cursor as a namedtuple.
  5. Assume the column names are unique.
  6. """
  7. desc = cursor.description
  8. nt_result = namedtuple("Result", [col[0] for col in desc])
  9. return [nt_result(*row) for row in cursor.fetchall()]

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

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

  1. >>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2")
  2. >>> cursor.fetchall()
  3. ((54360982, None), (54360880, None))
  4. >>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2")
  5. >>> dictfetchall(cursor)
  6. [{'parent_id': None, 'id': 54360982}, {'parent_id': None, 'id': 54360880}]
  7. >>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2")
  8. >>> results = namedtuplefetchall(cursor)
  9. >>> results
  10. [Result(id=54360982, parent_id=None), Result(id=54360880, parent_id=None)]
  11. >>> results[0].id
  12. 54360982
  13. >>> results[0][0]
  14. 54360982

连接和指针

connectioncursor 实现了 PEP 249 中介绍的大部分标准 Python DB-API —— 除了 事务处理 之外。

若你并不熟悉 Python DB-API,要注意 cursor.execute() 中的 SQL 语句使用了占位符 "%s",而不是直接在 SQL 中添加参数。若你使用这个技巧,潜在的数据库库会自动在需要时转义参数。

也要注意,Django 期望 "%s" 占位符,而 不是 "?" 占位符,后者由 SQLite Python 绑定使用。这是为了一致性和正确性。

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

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

相当于:

  1. c = connection.cursor()
  2. try:
  3. c.execute(...)
  4. finally:
  5. c.close()

调用存储流程

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

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

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

  1. CREATE PROCEDURE "TEST_PROCEDURE"(v_i INTEGER, v_text NVARCHAR2(10)) AS
  2. p_i INTEGER;
  3. p_text NVARCHAR2(10);
  4. BEGIN
  5. p_i := v_i;
  6. p_text := v_text;
  7. ...
  8. END;

这将调用该存储流程:

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