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

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

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

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

Сопоставление происходит по имени. Это означает, что вы можете использовать AS-запросы SQL, чтобы сопоставить поля в запросе с полями модели. Таким образом, если у вас есть другая таблица, содержащая данные 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 использует первичный ключ для идентификации экземпляров модели, поэтому он всегда должен включаться в необработанный запрос. Если вы забудете включить первичный ключ, будет поднята исключительная ситуация InvalidQuery.

Добавление аннотаций

Вы также можете выполнять запросы, содержащие поля, которые не определены в модели. Например, мы можем использовать PostgreSQL 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().

Передача параметров в 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"
    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"
    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 и 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'])