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

Если вам не хватает возможностей API ORM Django, вы можете использовать чистый SQL. Django предоставляет два способа для выполнения чистых SQL запросов: вы можете использовать Manager.raw() для для выполнения запроса и возврата объектов модели, или вообще не использовать модели и напрямую выполнить SQL запрос.

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

Вы должны быть предельно осторожны, используя SQL. Экранируйте все параметры, контролируемые пользователем, используя params, чтобы защитится от SQL инъекции. Больше о защите от 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() принимает ряд дополнительных параметров, что делает его очень полезным.

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

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

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

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

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

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

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

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

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

Хотя RawQuerySet и можно проитерировать как QuerySet, RawQuerySet не предоставляет все методы QuerySet. Например, __bool__() и __len__() не определены в RawQuerySet, все экземпляры RawQuerySet равны True. Они не определены в RawQuerySet, потому что их реализация без внутреннего кеширования может вызвать большие проблемы с производительностью, а добавление такого кеша повредило бы обратной совместимости.

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

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.
...

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

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

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

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

Примечание

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

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

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

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

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

Не делайте этого.

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

def dictfetchall(cursor):
    "Returns all rows from a cursor as a dict"
    desc = cursor.description
    return [
        dict(zip([col[0] for col in desc], row))
        for row in cursor.fetchall()
    ]

Пример, показывающий разницу между двумя методами:

>>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2");
>>> cursor.fetchall()
((54360982L, None), (54360880L, None))

>>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2");
>>> dictfetchall(cursor)
[{'parent_id': None, 'id': 54360982L}, {'parent_id': None, 'id': 54360880L}]

Подключения и указатель

connection и cursor реализуют стандартный Python DB-API описанный в PEP 249, кроме механизма работы с транзакциями.

Если вы не знакомы с Python DB-API, заметьте что SQL запрос переданный cursor.execute() использует подстановку параметром, "%s", вместо непосредственного добавления их в строку SQL запроса. При использовании такого подхода, библиотека для работы с текущей базой данной автоматически экранирует параметры.

Также заметим, что Django принимает "%s", не "?", который использует библиотекой для SQLite в Python. Так сделано для последовательности и удобства.

Изменено в Django 1.7.

PEP 249 не определяет должен ли курсор поддерживать интерфейс контекстного менеджера. До Python 2.7 курсор можно было использовать как контекстный менеджер из-за странного поведения магических методов (Python ticket #9220). Django 1.7 явно предоставляет эту возможность.

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

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

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

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