Путаница между подготовленным оператором и параметризованным запросом в Python

Насколько я понимаю, подготовленные заявления (в основном) - это функция базы данных, которая позволяет отделять параметры от кода, который использует такие параметры. Пример:

PREPARE fooplan (int, text, bool, numeric) AS
    INSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);

Параметрированный запрос заменяет интерполяцию ручной строки, поэтому вместо выполнения

cursor.execute("SELECT FROM tablename WHERE fieldname = %s" % value)

мы можем сделать

cursor.execute("SELECT FROM tablename WHERE fieldname = %s", [value])

Теперь кажется, что подготовленные заявления, по большей части, используются на языке базы данных, а параметризованные запросы в основном используются на языке программирования, подключающемся к базе данных, хотя я видел исключения из этого правила.

Проблема заключается в том, что вопрос о различии между подготовленным оператором и параметризованным запросом вызывает много путаницы. Их цель, по общему признанию, та же, но их методология кажется отличной. Тем не менее, есть источники , указывающие на то, что оба они одинаковы. MySQLdb и Psycopg2, похоже, поддерживают параметризованные запросы, но не поддерживают подготовленные инструкции (например, здесь для MySQLdb и TODO для драйверов postgres или этот ответ в группе sqlalchemy). На самом деле существует gist, реализующий курсор psycopg2, поддерживающий подготовленные инструкции и минимальное пояснение об этом. Существует также предложение подкласса объекта курсора в psycopg2 для предоставления подготовленного оператора вручную.

Я хотел бы получить авторитетный ответ на следующие вопросы:

Есть ли значимая разница между подготовленным оператором и параметризованным запросом? Это на практике? Если вы используете параметризованные запросы, вам нужно беспокоиться о подготовленных операторах?
Если есть разница, каков текущий статус подготовленных операторов в экосистеме Python? Какие адаптеры баз данных поддерживают подготовленные инструкции?

Ответ 1

Подготовленный оператор: ссылка на предварительно интерпретированную процедуру запроса в базе данных, готовую принимать параметры
Параметрированный запрос: запрос, сделанный вашим кодом, таким образом, что вы передаете значения рядом с некоторым SQL, который имеет значения-заполнители, обычно ? или %s или что-то в этом аромате.

Путаница здесь, по-видимому, связана с (очевидным) отсутствием различия между способностью напрямую получать подготовленный объект утверждения и возможностью передавать значения в метод параметризованного запроса, который действует очень как один... потому что это один или, по крайней мере, делает его для вас.

Например: интерфейс C библиотеки SQLite3 имеет множество инструментов для работы с готовыми объектами оператора, но Python api почти не упоминает о них. Вы не можете подготовить выражение и использовать его несколько раз, когда захотите. Вместо этого вы можете использовать sqlite3.executemany(sql, params), который берет код SQL, создает подготовленный оператор внутри, а затем использует этот оператор в цикле для обработки каждого из ваших кортежей параметров в указанном вами итерабельном файле.

Многие другие библиотеки SQL в Python ведут себя одинаково. Работа с готовыми объектами утверждения может быть настоящей болью и может привести к двусмысленности, а на языке, таком как Python, который имеет такую наклонность к ясности и легкости по сравнению с необработанной скоростью выполнения, это не самый лучший вариант. По сути, если вам приходится делать сотни тысяч или миллионы вызовов сложного SQL-запроса, который каждый раз пересматривается, вы, вероятно, должны делать что-то по-другому. Независимо от того, иногда люди хотят, чтобы у них был прямой доступ к этим объектам, потому что, если вы сохраняете один и тот же подготовленный оператор вокруг сервера базы данных, ему не придется продолжать интерпретировать один и тот же код SQL снова и снова; большую часть времени это будет приближаться к проблеме из-за неправильного направления, и вы получите гораздо большую экономию в другом месте или путем изменения вашего кода. *

Возможно, что более важно, в общем, то, как подготовленные операторы и параметризованные запросы сохраняют ваши данные в санитарных условиях и отдельно от вашего кода SQL. Это гораздо предпочтительнее форматирования строк!. Вы должны думать о параметризованных запросах и подготовленных операциях в той или иной форме, как , единственный способ передать переменные данные из вашего приложения в базу данных. Если вы попытаетесь создать инструкцию SQL иначе, она будет работать не только значительно медленнее, но вы будете уязвимы для других проблем.

*, например, создавая данные, которые должны быть переданы в БД, в функции генератора, затем используя executemany(), чтобы вставить все это сразу из генератора, а не вызывать execute() каждый раз, когда вы зацикливаете.

TL;DR

Параметрированный запрос представляет собой единую операцию, которая генерирует подготовленный оператор внутренне, затем передает ваши параметры и выполняет.

Ответ 2

Во-первых, ваши вопросы показывают очень хорошую подготовку - хорошо сделано.

Я не уверен, если я человек, чтобы дать авторитетный ответ, но я попытаюсь объяснить свои понимание ситуации.

Подготовленный оператор - это объект, созданный на стороне сервера базы данных в результате PREPARE утверждение, превращение предоставило SQL-инструкцию в виде временной процедуры с параметрами. подготовленный оператор имеет время жизни текущего сеанса базы данных и отбрасывается после завершения сеанса. Оператор SQL DEALOCATE позволяет явно уничтожить подготовленный оператор.

Клиенты базы данных могут использовать оператор SQL EXECUTE для выполнения подготовленного оператора, вызвав его имя и параметры.

Параметризированный оператор - это псевдоним для подготовленного оператора, как обычно, подготовленный оператор некоторые параметры.

Параметризованный запрос, по-видимому, менее часто используется для псевдонима для того же (24 мил Google хитов для параметризованный оператор, 14 мил для параметризованного запроса). Возможно, что некоторые люди используют этот термин для другой цели.

Преимущества подготовленных операторов:

более быстрое выполнение фактического подготовленного запроса оператора (не считая времени для PREPARE)
сопротивляемость атаке SQL-инъекций

Игроки, выполняющие SQL-запрос

Реальное приложение, вероятно, будет иметь следующих участников:

код приложения
пакет ORM (например, sqlalchemy)
Драйвер базы данных
сервер базы данных

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

Выводы

В коде приложения запрещено прямое формирование SQL-запроса, поскольку оно подвержено атаке SQL-инъекций. Для по этой причине рекомендуется использовать то, что ORM предоставляет параметризованному запросу, даже если он не приводят к использованию подготовленных операторов на стороне сервера базы данных, так как ORM-код может быть оптимизирован для предотвратить такую атаку.

Решите, если подготовленная заявка стоит по соображениям производительности. Если у вас простой запрос SQL, который выполняется всего несколько раз, это не поможет, когда-то это даже замедлит выполнение бит.

Для выполнения сложного запроса много раз и с относительно коротким временем выполнения будет эффект самый большой. В этом случае вы можете выполнить следующие действия:

проверьте, что база данных, которую вы собираетесь использовать, поддерживает оператор PREPARE. В большинстве случаев это будет присутствовать.
проверьте, что используемый вами диск поддерживает подготовленные инструкции, а если нет, попробуйте найти другое один из них поддерживает его.
Проверьте поддержку этой функции на уровне пакета ORM. Иногда он варьирует драйвер от водителя (например, sqlalchemy заявляет некоторые ограничения на подготовленные заявления с MySQL из-за того, как MySQL управляет что).

Если вы ищете реальный авторитетный ответ, я бы подошел к авторам sqlalchemy.

Ответ 3

Оператор sql не может быть выполнен немедленно: СУБД должна интерпретировать их перед выполнением.

Подготовленные операторы уже интерпретируются, параметры изменения СУБД и запрос начинаются немедленно. Это особенность некоторых СУБД, и вы можете добиться быстрого ответа (сравнимого с хранимыми процедурами).

Параметризованный оператор - это просто способ, которым вы составляете строку запроса на ваших языках программирования. Поскольку не имеет значения, как формируется строка sql, у вас медленный ответ с помощью СУБД.

Если вы измеряете время, выполняющее 3-4 раза тот же запрос (выберите с разными условиями), вы увидите то же время с параметризованными запросами, время меньше от второго выполнения подготовленного оператора (при первом использовании СУБД интерпретировать script в любом случае).