Чистый SQL
Django предоставляет вам два способа выполнения необработанных SQL-запросов: вы можете использовать Manager.raw() для `выполнения необработанных запросов и возврата экземпляров модели`__, или вы можете полностью отказаться от уровня модели и` напрямую выполнять пользовательский SQL `__.
Изучите ORM перед использованием чистого SQL!
Django ORM предоставляет множество инструментов для выражения запросов без написания необработанного SQL. Например:
Запросы QuerySet API обширны.
Вы можете annotate, используя множество встроенных функций базы данных. Помимо этого, вы можете создавать пользовательские выражения запросов.
Прежде чем использовать необработанный SQL, изучите the ORM. Обратитесь к одному из the support channels, чтобы узнать, поддерживает ли ORM ваш вариант использования.
Предупреждение
Вы должны быть очень осторожны, когда пишете необработанный SQL. Каждый раз, когда вы его используете, вы должны правильно избегать любых параметров, которыми пользователь может управлять с помощью params, чтобы защитить себя от атак с использованием SQL-инъекций. Пожалуйста, прочтите больше о Защита от 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() есть множество других опций, которые делают его очень мощным.
Имена таблиц моделей
Откуда в этом примере взялось название таблицы Person?
По умолчанию Django определяет имя таблицы базы данных, присоединяя «метку приложения» модели - имя, которое вы использовали в manage.py startapp - к имени класса модели, с подчеркиванием между ними. В этом примере мы предположили, что модель Person находится в приложении с именем myapp, поэтому ее таблица будет myapp_person.
Для получения дополнительной информации ознакомьтесь с документацией по параметру db_table, который также позволяет вам вручную установить имя таблицы базы данных.
Предупреждение
Для оператора SQL, переданного в .raw(), проверка не выполняется. Django ожидает, что оператор вернет набор строк из базы данных, но ничего не делает для этого. Если запрос не возвращает строки, это приведет к (возможно, загадочной) ошибке.
Предупреждение
Если вы выполняете запросы к MySQL, обратите внимание, что принудительное приведение типов в MySQL может привести к неожиданным результатам при смешивании типов. Если вы запрашиваете столбец строкового типа, но с целочисленным значением, MySQL перед выполнением сравнения преобразует типы всех значений в таблице в целое число. Например, если ваша таблица содержит значения 'abc', 'def' и вы запрашиваете WHERE mycolumn=0, обе строки будут совпадать. Чтобы предотвратить это, выполните правильное приведение типов перед использованием значения в запросе.
Changed in Django 3.2:
Значение по умолчанию аргумента params было изменено с None на пустой кортеж.
Сопоставление полей запроса с полями модели
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 использует первичный ключ для идентификации экземпляров модели, поэтому он всегда должен быть включен в необработанный запрос. Исключение FieldDoesNotExist будет вызвано, если вы забудете включить первичный ключ.
Добавление аннотаций
Вы также можете выполнять запросы, содержащие поля, которые не определены в модели. Например, мы могли бы использовать функцию `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.
...
Часто можно избежать использования необработанного 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.
Примечание
Параметры словаря не поддерживаются серверной частью 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-инъекций`__, распространенного эксплойта, при котором злоумышленники вводят произвольный SQL в вашу базу данных. Если вы используете строковую интерполяцию или цитируете заполнитель, вы подвергаетесь риску внедрения SQL.
Выполнение пользовательского 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 возвращает результаты без имен полей, что означает, что вы получаете список значений, а не 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'])
