Если вам не хватает возможностей API ORM Django, вы можете использовать чистый SQL. Django предоставляет два способа для выполнения чистых SQL запросов: вы можете использовать Manager.raw() для `для выполнения запроса и возврата объектов модели`__, или вообще не использовать модели и `напрямую выполнить SQL запрос`__.
Метод raw() менеджера может использовать для выполнения чистых SQL и возврата объектов модели:
Этот метод принимает чистый SQL запрос, выполняет его, и возвращает экземпляр 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() принимает ряд дополнительных параметров, что делает его очень полезным.
Model table names
Откуда берется название таблицы для модели Person?
По умолчанию Django создает название таблицы объединив название приложения(название которое вы использовали при вызове manage.py startapp), в котором находиться модель, и название модели с нижним подчеркиванием между ними. В нашем примере модель Person предположительно находится в приложение myapp, по этому название таблицы в базе данных будет myapp_person.
Подробности смотрите в описании настройки модели db_table, которая так же позволяет явно указать название таблицы.
Предупреждение
SQL Запрос переданный в .raw() не проверяется. Django ожидает что он вернет набор записей с результатом, но никак не принуждает к этому. Если запрос возвращает не набор записей, вы получите ошибку.
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
... print 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.
...
Если вам необходимо выполнить запрос с параметрами, используйте аргумент params:
>>> lname = 'Doe'
>>> Person.objects.raw('SELECT * FROM myapp_person WHERE last_name = %s', [lname])
params – это список параметров. Используйте %s в строке с запросом (независимо от базы данных); при выполнении запросу будут подставлены значения из списка params.
Предупреждение
Не используйте форматирование строк в запросе!
Очень заманчиво написать запрос следующим образом:
>>> 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 представляет подключение к базе данных по умолчанию, django.db.transaction представляет транзакции базы данных по умолчанию. Для использования подключения к базе данных вызовите connection.cursor() что бы получить объект “указателя”. Затем вызовите cursor.execute(sql, [params]) для выполнения SQL и cursor.fetchone() или cursor.fetchall() для получения результата. При выполнении запросов изменяющих данные, необходимо вызвать transaction.commit_unless_managed() что бы убедиться, что изменения были применены к базе данных. Если запрос просто возвращает данные, в этом нет необходимости. Например:
def my_custom_sql():
from django.db import connection, transaction
cursor = connection.cursor()
# Data modifying operation - commit required
cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])
transaction.commit_unless_managed()
# Data retrieval operation - no commit required
cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
row = cursor.fetchone()
return row
Если вы используете несколько баз данных, вы можете использовать django.db.connections для получения подключения к конкретной базе данных. django.db.connections – объект-словарь позволяющий получить подключение по псевдониму базы данных:
from django.db import connections
cursor = connections['my_db_alias'].cursor()
# Your code here...
transaction.commit_unless_managed(using='my_db_alias')
По умолчанию 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}]
При выполнении SQL запроса напрямую, Django автоматически пометит текущую транзакцию как “грязную”. Вы должны убедиться, что транзакция содержащая эти вызовы, была правильно завершена. Подробности смотрите в заметках о требованиях к работе с транзакциями в Django.
До Django 1.3 необходимо было самостоятельно помечать транзакцию как “грязную” используя transaction.set_dirty() при непосредственном выполнении SQL запросов.
connection и cursor почти полностью реализуют Python DB-API описанный в PEP 249 (кроме работы с тразакциями). Если вы не знакомы с Python DB-API, заметьте что SQL запрос переданный cursor.execute() использует подстановку параметром, "%s", вместо непосредственного добавления их в строку SQL запроса. При использовании такого подхода, библиотека для работы с текущей базой данной автоматически экранирует параметры. (Так же заметим что Django принимает "%s", не "?", который использует библиотекой для SQLite в Python. Так сделано для последовательности и удобства.)
Mar 30, 2016