执行原生 SQL 查询

Django 允许你用两种方式执行原生 SQL 查询:你可以使用 Manager.raw()执行原生查询并返回模型实例,或者完全不用模型层 直接执行自定义 SQL

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

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

使用原生 SQL 之前,探索一下 the ORM。在 django-users#django IRC channel 上提问,看看 ORM 是否支持你的使用场景。

警告

无论何时编写原生 SQL 都要万分小心。使用时,你要用 params 将任何用户传入的参数进行安全转义,避免 SQL 注入攻击。阅读 SQL 注入保护 了解更多。

执行原生查询

若管理器方法 raw() 能用于执行原生 SQL 查询,就会返回模型实例:

Manager.``raw(raw_query, params=None, 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. John Smith
  4. 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. ...
  3. >>> Person.objects.raw('SELECT last_name, birth_date, first_name, id FROM myapp_person')
  4. ...

匹配是根据名字来的。这意味着你可以使用 SQL 的 AS 子句将查询语句中的字段映射至模型中的字段。所以,若你还有一些数据表包含了 Person 数据,你可以很方便的将其映射至 Person 实例:

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

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

或者,你可以用 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 层面使用 limit 子句:

  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(p.first_name, # This will be retrieved by the original query
  3. ... p.last_name) # This will be retrieved on demand
  4. ...
  5. John Smith
  6. Jane Jones

表面上,看起来该查询同时检出了 first name 和 last name。然而,这个例子实际上执行了三次查询。只有 first names 是由 raw() 查询检出的 —— last names 是在它们被打印时按需检出。

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

添加注释

你可以执行带有模型中未定义字段的查询语句。例如,我们能用 PostgreSQL 的 age() 函数 获取用户列表,他们的年龄已由数据库计算:

  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. John is 37.
  5. Jane is 42.
  6. ...

你总是可以用 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 injection protection 介绍的,使用 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...

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

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

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

  1. from collections import namedtuple
  2. def namedtuplefetchall(cursor):
  3. "Return all rows from a cursor as a namedtuple"
  4. desc = cursor.description
  5. nt_result = namedtuple('Result', [col[0] for col in desc])
  6. return [nt_result(*row) for row in cursor.fetchall()]

这有个例子,介绍了三者之间的不同:

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