Skip to main content

执行原始SQL查询

模型查询API 不够远,你可以回到写原始SQL。 Django给你两种执行原始SQL查询的方法:你可以使用 Manager.raw()perform raw queries and return model instances,或者你可以完全避免模型层和 execute custom SQL directly

警告

每当你写原始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通过将模型的“app label”(manage.py startapp 中使用的名称)连接到模型的类名称,在它们之间加上下划线来计算数据库表名称。在该示例中,我们假设 Person 模型存在于名为 myapp 的应用程序中,因此其表将是 myapp_person

有关更多详细信息,请参阅 db_table 选项的文档,还可以手动设置数据库表名称。

警告

不会对传递到 .raw() 的SQL语句进行检查。 Django期望语句将从数据库返回一组行,但不会强制执行。如果查询不返回行,将导致(可能是神秘的)错误。

警告

如果您正在对MySQL执行查询,请注意,MySQL的静默类型强制可能会在混合类型时导致意外的结果。如果查询字符串类型列,但使用整数值,MySQL将在执行比较之前将表中所有值的类型强制为整数。例如,如果表包含值 'abc''def' 并且您查询 WHERE mycolumn=0,则两行都将匹配。为了防止出现这种情况,请在使用查询中的值之前执行正确的类型转换。

警告

虽然 RawQuerySet 实例可以像正常的 QuerySet 一样迭代,但是 RawQuerySet 并不实现所有可以与 QuerySet 一起使用的方法。例如,__bool__()__len__() 没有在 RawQuerySet 中定义,因此所有 RawQuerySet 实例被认为是 True。这些方法在 RawQuerySet 中未实现的原因是,在没有内部缓存的情况下实现它们将是性能缺点,并且添加这样的缓存将是向后不兼容的。

将查询字段映射到模型字段

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使用主键来识别模型实例,因此它必须始终包含在原始查询中。如果您忘记包含主键,将会引发 InvalidQuery 异常。

添加注释

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

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

将参数传递给 raw()

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

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

params 是参数的列表或字典。您将在查询字符串中使用 %s 占位符作为列表,或者在字典中使用 %(key)s 占位符(当然,其中 key 由字典键替换),而不考虑数据库引擎。这样的占位符将被替换为 params 参数的参数。

注解

SQLite后端不支持字典参数;使用此后端,必须将参数作为列表传递。

警告

不要对原始查询使用字符串格式化!

将上面的查询写为是诱人的:

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

别。

使用 params 参数完全保护您不受 SQL injection attacks 攻击,这是攻击者将任意SQL插入数据库的常见漏洞。如果使用字符串插值,迟早会成为SQL注入的牺牲品。只要你记得总是使用 params 参数,你就会受到保护。

直接执行自定义SQL

有时甚至 Manager.raw() 不够:您可能需要执行不完全映射到模型的查询,或直接执行 UPDATEINSERTDELETE 查询。

在这些情况下,您可以始终直接访问数据库,完全绕过模型层。

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

请注意,如果要在查询中包含文字百分号,则在传递参数的情况下,必须将它们加倍:

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
cursor = connections['my_db_alias'].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

连接和光标

connectioncursor 大多数实现 PEP 249 中描述的标准的Python DB-API - 除非涉及 事务处理

如果您不熟悉Python DB-API,请注意 cursor.execute() 中的SQL语句使用占位符 "%s",而不是直接在SQL中添加参数。如果使用此技术,基础数据库库将根据需要自动转义您的参数。

还要注意,Django需要 "%s" 占位符,not"?" 占位符,它由SQLite Python绑定使用。这是为了一致性和理智的缘故。

使用游标作为上下文管理器:

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

相当于:

c = connection.cursor()
try:
    c.execute(...)
finally:
    c.close()