Запуск сырых SQL-запросов

Django предлагает два способа выполнения необработанных SQL-запросов: вы можете использовать Manager.raw() для выполнения необработанных запросов и возврата экземпляров модели или вы можете полностью обойти уровень моделей и напрямую выполнить собственный код SQL .

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

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

Прежде чем использовать необработанный SQL, изучите ORM . Спросите у django-users или IRC-канала #django, чтобы узнать, поддерживает ли ORM ваш вариант использования.

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

Вы должны быть очень осторожны при написании необработанных операторов 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 macolonne=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 запроса. По запросу были получены только имена (first_name), raw() каждое из имен (last_name) было получено по запросу при отображении.

Есть только одно поле, которое нельзя пропустить - поле первичного ключа. 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'"

Не делайте ни одной из этих ошибок.

Как обсуждалось в разделе Защитаparams от внедрения SQL , использование параметра и оставление заполнителей без кавычек защищает вас от атак с использованием SQL-инъекций - распространенной уязвимости, когда злоумышленник вводит произвольный код SQL в вашу базу данных. , Если вы используете строковую интерполяцию или заключаете заполнители в кавычки, вы подвергаетесь инъекциям SQL.

Прямое выполнение кода SQL

В некоторых случаях даже Manager.raw() этого недостаточно: вам может потребоваться выполнять запросы без надлежащего соответствия шаблонов, или вы можете выполнять запросы напрямую UPDATE , INSERT или DELETE .

В этих ситуациях вы по-прежнему можете получить доступ к базе данных напрямую, полностью минуя уровень модели.

Объект django.db.connection представляет соединение с базой данных по умолчанию. Чтобы использовать соединение с базой данных, вызовите, connection.cursor() чтобы получить объект курсора. Затем вызовите, чтобы запустить код SQL и / или получить строки результатов.cursor.execute(sql, [params]) 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...

По умолчанию API базы данных Python возвращает результаты без имен полей, что означает, что вы получаете список значений, а не словарь. Из-за низкой стоимости производительности и памяти вы можете получить результаты в виде словаря, написав что-то вроде:

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. A 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 основном реализовать стандартный API базы данных Python, описанный вPEP 249 , за исключением управления транзакциями .

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

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

Использование курсора в качестве диспетчера контекста:

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

Например, с этой хранимой процедурой в базе данных 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'])

Copyright ©2021 All rights reserved