Выполнение необработанных SQL-запросов

Django предоставляет вам два способа выполнения необработанных SQL-запросов: вы можете использовать Manager.raw() для :ref:`выполнения необработанных запросов и возврата экземпляров модели <performing-raw-queries>`__, или вы можете полностью отказаться от уровня модели и :ref:`напрямую выполнять пользовательский SQL <executing custom SQL directly>`__.

Изучите ORM перед использованием чистого SQL!

Django ORM предоставляет множество инструментов для выражения запросов без написания необработанного SQL. Например:

Before using raw SQL, explore the ORM. Ask on one of the support channels to see if the ORM supports your use case.

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

Вы должны быть очень осторожны, когда пишете необработанный 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 может привести к неожиданным результатам при смешивании типов. Если вы запрашиваете столбец строкового типа, но с целочисленным значением, 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]

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

Часто можно избежать использования необработанного 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'])
Вернуться на верх