执行原始SQL查询

model query APIs不够用时,可以回退写入原始SQL。 Django gives you two ways of performing raw SQL queries: you can use Manager.raw() to perform raw queries and return model instances, or you can avoid the model layer entirely and execute custom SQL directly.

警告

无论何时写入原始SQL,您都应该非常小心。 每次使用它时,都应该正确地转义用户可以通过使用params控制的参数,以防止SQL注入攻击。 请阅读有关SQL injection protection的更多信息。

执行原始查询

可以使用raw()管理器方法执行返回模型实例的原始SQL查询:

经理。rawraw_queryparams = Nonetranslations = 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将强制表中所有值的类型为整数。 For example, if your table contains the values 'abc', 'def' and you query for WHERE mycolumn=0, both rows will match. 为了防止这种情况,请在查询中使用该值之前执行正确的类型转换。

警告

While a RawQuerySet instance can be iterated over like a normal QuerySet, RawQuerySet doesn’t implement all methods you can use with QuerySet. For example, __bool__() and __len__() are not defined in RawQuerySet, and thus all RawQuerySet instances are considered True. 这些方法在RawQuerySet中没有实现的原因是在没有内部缓存的情况下实现它们会是一个性能缺陷,添加这样的缓存会向后不兼容。

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

raw() automatically maps fields in the query to fields on the model.

查询中字段的顺序无关紧要。 换句话说,以下两个查询的工作原理是一样的:

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

只要名称匹配,模型实例将被正确创建。

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

>>> 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() supports indexing, so if you need only the first result you can write:

>>> 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异常。

添加注释

您还可以执行包含未在模型上定义的字段的查询。 For example, we could use PostgreSQL’s age() function to get a list of people with their ages calculated by the database:

>>> 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 is a list or dictionary of parameters. 您将在列表的查询字符串中使用%s占位符,或者在字典中使用%(key)s占位符(其中key当然,用字典键替换),而不管你的数据库引擎如何。 这些占位符将被替换为params参数的参数。

注意

Dictionary参数不支持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'"

不要犯这些错误之一。

As discussed in SQL injection protection, using the params argument and leaving the placeholders unquoted protects you from SQL injection attacks, a common exploit where attackers inject arbitrary SQL into your database. 如果使用字符串插值或引用占位符,则有可能导致SQL注入。

直接执行自定义的SQL

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

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

对象django.db.connection表示默认的数据库连接。 要使用数据库连接,请调用connection.cursor()来获取游标对象。 Then, call cursor.execute(sql, [params]) to execute the SQL and cursor.fetchone() or cursor.fetchall() to return the resulting rows.

例如:

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

如果使用more than one database,则可以使用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() A 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 and cursor mostly implement the standard Python DB-API described in PEP 249 — except when it comes to transaction handling.

如果您不熟悉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。callprocprocnameparams = Nonekparams = 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'])
在Django 2.0中更改:

添加了kparams参数。