Если вам не хватает возможностей API ORM Django, вы можете использовать чистый SQL. Django предоставляет два способа для выполнения чистых SQL запросов: вы можете использовать Manager.raw() для для выполнения запроса и возврата объектов модели, или вообще не использовать модели и напрямую выполнить SQL запрос.
Предупреждение
Вы должны быть предельно осторожны, используя SQL. Экранируйте все параметры, контролируемые пользователем, используя params, чтобы защитится от SQL инъекции. Больше о защите от SQL инъекций.
Метод raw() менеджера может использоваться для выполнения чистых SQL и возврата объектов модели:
Этот метод принимает чистый 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.
...
Если вам необходимо выполнить запрос с параметрами, используйте аргумент 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 вы обезопасите себя от этого.
Иногда и 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 вернет результат без названий полей, то есть вы получите список значений, а не словарь. За счет небольшой потери производительности вы можете получить словарь следующим образом:
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()
Mar 31, 2016