Выполнение необработанных запросов SQL

Django предоставляет два способа выполнения необработанных запросов SQL: вы можете использовать Manager.raw(), чтобы выполнить необработанные запросы и вернуть экземпляры моделей, или же полностью обойти уровень моделей и непосредственно выполнить пользовательский 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() имеет множество других возможностей, которые делают его очень мощным.

Сопоставление полей запроса с полями модели

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 использует первичный ключ для идентификации экземпляров модели, поэтому он всегда должен включаться в необработанный запрос. Если вы забудете включить первичный ключ, будет поднято исключение 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() expression.

Передача параметров в raw()

Если вам нужно выполнять параметризованные запросы, вы можете использовать параметр params для raw():

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

params – это список или словарь параметров. Вы будете использовать %s-заменители в строке запроса для списка, или %(key)s-заменители для словаря (где key заменяется ключом словаря), независимо от вашей СУБД. Такие заменители будут заменены параметрами из параметра params.

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

>>> query = "SELECT * FROM myapp_person WHERE last_name = '%s'"

Выполнение пользовательского 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])

Если вы используете несколько баз данных, вы можете использовать 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 — это объект, похожий на кортеж, у которого поля доступны через поиск по атрибуту; он также индексируется и итерируется. Результаты неизменяемы и доступны по именам полей или индексам, что может быть полезно:

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 — за исключением обработки обработки транзакций.

Если вы не знакомы с Python DB-API, обратите внимание, что SQL-запрос в cursor.execute() использует плейсхолдеры, "%s", а не непосредственное добавление параметров в SQL. Если вы используете эту технику, базовая библиотека базы данных автоматически экранирует ваши параметры при необходимости.

Также обратите внимание, что Django ожидает "%s" плейсхолдер, а не "?" плейсхолдер, который используется связями Python с SQLite. Это для обеспечения согласованности и здравого смысла.

Использование курсора как менеджера контекста:

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