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

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

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

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

Передача параметров в 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
cursor = connections['my_db_alias'].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'])

Изменено в Django 2.0:

Был добавлен аргумент kparams.