Использование чистого SQL

Django gives you two ways of performing raw SQL queries: you can use Manager.raw() to `perform raw queries and return model instances`__, or you can avoid the model layer entirely and `execute custom SQL directly`__.

Explore the ORM before using raw SQL!

The Django ORM provides many tools to express queries without writing raw SQL. For example:

Before using raw SQL, explore the ORM. Ask on django-users or the #django IRC channel to see if the ORM supports your use case.

Предупреждение

Вы должны быть предельно осторожны, используя SQL. Экранируйте все параметры, контролируемые пользователем, используя params, чтобы защитится от SQL инъекции. Больше о защите от SQL инъекций.

Выполнение чистых запросов

Метод raw() менеджера может использоваться для выполнения чистых SQL и возврата объектов модели:

Manager.raw(raw_query, params=None, translations=None)

This method takes a raw SQL query, executes it, and returns a django.db.models.query.RawQuerySet instance. This RawQuerySet instance can be iterated over like a normal QuerySet to provide object instances.

Лучше посмотрим на пример. Предположим у нас есть такая модель:

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() принимает ряд дополнительных параметров, что делает его очень полезным.

Названия таблиц для моделей

Откуда берется название таблицы для модели Person в этом примере?

По умолчанию Django создает название таблицы объединив название приложения(название которое вы использовали при вызове manage.py startapp), в котором находиться модель, и название модели с нижним подчеркиванием между ними. В нашем примере модель Person предположительно находится в приложение myapp, поэтому название таблицы в базе данных будет myapp_person.

Подробности смотрите в описании настройки модели db_table, которая также позволяет явно указать название таблицы.

Предупреждение

SQL Запрос переданный в .raw() не проверяется. Django ожидает что он вернет набор записей с результатом, но никак не принуждает к этому. Если запрос возвращает не набор записей, вы получите ошибку.

Предупреждение

Выполняя запрос в MySQL, обратите внимание на преобразование типов. Если вы выполняете запрос по текстовому полю, но используете числовое значение, MySQL преобразует все значения поля в число перед сравнением. Например, если таблица содержит значения 'abc', 'def' и в запросе WHERE mycolumn=0, обе строки попадут в результат. Чтобы избежать этого, используйте значение правильного типа в запросе.

Соответствие полей ответа и полей модели

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]

Однако, индексирование и «slicing» выполняется не на уровне базы данных. Если база данных содержит большое количество записей 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, возвращенные этим запросом будут «отложенными»(пер. - deferred) экземплярами модели (смотрите 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

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

You can often avoid using raw SQL to compute annotations by instead using a Func() expression.

Передача параметров в raw()

Если вам необходимо выполнить запрос с параметрами, используйте аргумент params:

>>> lname = 'Doe'
>>> Person.objects.raw('SELECT * FROM myapp_person WHERE last_name = %s', [lname])

params – это список параметров. Используйте %s в строке с запросом для списка параметров, или %(key)s для словаря(где key будет заменен на значение ключа из словаря), независимо от базы данных. При выполнении запроса будут подставлены значения из списка params.

Примечание

Параметры из словаря не поддерживаются SQLite, необходимо использовать список.

Предупреждение

Не используйте форматирование строк в SQL запросе!

Очень заманчиво написать запрос следующим образом:

>>> query = 'SELECT * FROM myapp_person WHERE last_name = %s' % lname
>>> Person.objects.raw(query)

Или таким (со скобками вокруг %s):

>>> query = "SELECT * FROM myapp_person WHERE last_name = '%s'"

Не делайте этих ошибок.

Как уже обсуждалось в Защита от внедрения SQL, используя params вы полностью защищены от Атак с внедрением SQL-кода. При вставке параметров непосредственно в строку запроса, рано или поздно вы станете жертвой атаки. Используя аргумент params вы обезопасите себя от этого.

Непосредственное выполнение 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 вернет результат без названий полей, то есть вы получите список значений, а не словарь. За счет небольшой потери производительности вы можете получить словарь следующим образом:

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