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

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

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

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

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