執行原始 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 一樣進行迭代，以提供物件實例。

最好用範例說明。假設您有以下模型

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 會在執行比較之前，將資料表中所有值的類型強制轉換為整數。例如，如果您的資料表包含值 '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
...     """
... )

只要名稱相符，就會正確建立模型實例。

或者，您可以使用 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 使用主索引鍵來識別模型實例，因此它必須始終包含在原始查詢中。如果您忘記包含主索引鍵，則會引發 FieldDoesNotExist 例外。

新增註解

您也可以執行包含模型上未定義欄位的查詢。例如，我們可以利用 PostgreSQL 的 age() 函式來取得人員清單，並由資料庫計算他們的年齡

>>> 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() 表達式。

將參數傳遞到 raw()

如果您需要執行參數化查詢，您可以使用 raw() 的 params 引數

>>> 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 注入攻擊，這是一種常見的漏洞，攻擊者會將任意 SQL 注入到您的資料庫中。如果您使用字串內插或將預留位置加上引號，您將面臨 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 注入攻擊，您在 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])

如果您正在使用一個以上的資料庫，您可以使用 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()]

另一種選擇是使用 Python 標準函式庫中的 collections.namedtuple()。namedtuple 是一個類似 tuple 的物件，其欄位可透過屬性查找存取；它也可以透過索引存取和迭代。結果是不可變的，並且可以透過欄位名稱或索引來存取，這可能很有用。

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 主要實作了 PEP 249 中描述的標準 Python DB-API — 除了在交易處理方面。

如果您不熟悉 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.callproc(procname, params=None, kparams=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"])
管理器
資料庫交易
返回頂部