Выполнение произвольных 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)

Выполнение произвольных 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

Обратите внимание, что если вы хотите включить символы процента в запрос, вы должны удвоить их в случае передачи параметров:

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", а не заполнитель "?", который используется SQLite Python-связями. Это делается для обеспечения согласованности и стабильности.

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

with connection.cursor() as c:
    c.execute(...)

эквивалентно:

c = connection.cursor()
try:
    c.execute(...)
finally:
    c.close()