Django提供了两种执行原始SQL查询的方法:可以使用 Manager.raw()
到 `perform raw queries and return model instances`_ _或者您可以完全避免模型层,并且 `execute custom SQL directly`_ _.
在使用原始SQL之前浏览ORM!
DjangoORM提供了许多工具来表示查询,而不需要编写原始SQL。例如:
这个 QuerySet API 是广泛的。
你可以 annotate
和 aggregate 使用许多内置 database functions . 除此之外,您还可以创建 custom query expressions .
在使用原始SQL之前,请先浏览 the ORM 。询问以下问题之一 the support channels 查看ORM是否支持您的用例。
警告
编写原始SQL时应该非常小心。每次使用它时,都应该正确地转义用户可以使用 params
为了防止SQL注入攻击。请阅读更多关于 SQL injection protection .
这个 raw()
Manager方法可用于执行返回模型实例的原始SQL查询:
此方法获取原始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
选项,它还允许您手动设置数据库表名。
警告
未对传入的SQL语句执行任何检查 .raw()
. 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
... """
... )
只要名称匹配,将正确创建模型实例。
属性将查询中的字段映射到模型字段。 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()
支持索引,所以如果只需要第一个结果,可以写:
>>> 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使用主键来标识模型实例,因此它必须始终包含在原始查询中。A FieldDoesNotExist
如果忘记包含主键,将引发异常。
您还可以执行包含模型上未定义的字段的查询。例如,我们可以使用 `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.
...
通常可以避免使用原始SQL来计算注释,方法是使用 Func() expression .
raw()
¶如果需要执行参数化查询,可以使用 params
参数为 raw()
:
>>> 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 injection attacks`_ _这是一种常见的漏洞,攻击者会将任意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注入,不能在 %s
SQL字符串中的占位符。
请注意,如果要在查询中包含文字百分比符号,则在传递参数的情况下,必须将它们加倍:
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
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()]
另一个选择是使用 collections.namedtuple()
来自Python标准库。一 namedtuple
是一个类似于元组的对象,它具有可通过属性查找访问的字段;它也是可索引和可Iterable的。结果是不可变的,可以通过字段名或索引访问,这可能有用:
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
主要实现中描述的标准python db-api PEP 249 -除非涉及到 transaction handling .
如果您不熟悉python db-api,请注意 cursor.execute()
使用占位符, "%s"
而不是直接在SQL中添加参数。如果使用这种技术,底层数据库库将根据需要自动转义参数。
还请注意,Django预计 "%s"
占位符, not 这个 "?"
占位符,由sqlite python绑定使用。这是为了保持一致性和理智。
将光标用作上下文管理器::
with connection.cursor() as c:
c.execute(...)
等于:
c = connection.cursor()
try:
c.execute(...)
finally:
c.close()
使用给定的名称调用数据库存储过程。序列 (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"])
12月 18, 2023