Выполнение прямых 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')
...

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

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

Вы также можете выполнять запросы, содержащие поля, которые не определены в модели. Например, мы можем использовать функцию age() PostgreSQL для получения списка людей с вычисленным возрастом в базе данных:

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