Лучшая практика для SQL-запросов в Python

Благодаря обязательному интерфейсу для коннекторов баз данных, Python Database API Specification v2.0, PEP249, все существующие коннекторы разработаны таким образом, чтобы соединения с базами данных и SQL (для получения данных и транзакций с данными) можно было запускать с помощью одних и тех же команд. Результаты везде отдаются в более или менее одинаковом формате.

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

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

И прежде чем начнем, отметим, что эта заметка базируется на вольном переводе вот этой статьи. Далее Грег Хварвестер, автор оригинальной статьи,  рассказывает о своем собственном опыте работы с Dynamic SQL в Python.

---

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

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

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

1. Самый наивный и опасный подход: конкатенация строк

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

Однако я могу гарантировать безошибочное выполнение примеров только начиная с Python 3.x.

import sqlite3 
db = sqlite3.connect(':memory:') 
db.execute("CREATE TABLE staff (person_id int, lastname CHAR); ") 
db.execute("INSERT INTO staff (person_id, lastname) VALUES (1, 'Pavlov') ") 
db.execute("INSERT INTO staff (person_id, lastname) VALUES (2, 'Skinner') ")

В коде примера безымянная база данных была инициализирована в памяти путем ввода ‘:memory:‘ в качестве места хранения в команде connect после импорта модуля.

Затем создается электронная таблица сотрудников с именем staff и заполняется первым набором данных.

Пока все хорошо. Но мы не хотим писать отдельную команду insert для каждого сотрудника, которого мы собираемся добавить в таблицу. Если имена сотрудников уже доступны в виде списка, то это идеально подходит для использования цикла.

Первая попытка:

db.execute("CREATE TABLE staff (person_id int, lastname CHAR); ") 
for person_id, lastname in enumerate(staff_names): 
    db.execute("INSERT INTO staff (person_id, lastname) VALUES (" + person_id + ", '" + lastname + "') ")

Несмотря на все благие намерения, это не удалось. Появилось уведомление об ошибке «TypeError: Can't convert 'int' object to str implicitly», что означает, что мы забыли привести тип данных person_id из целого в str. Хотя Python гибок практически во всем, это все еще очень сильно типизированный язык, и неизменяемые строки не могут быть объединены с целыми числами.

Следующая попытка с учетом этой правки:

db.execute("CREATE TABLE staff (person_id int, lastname CHAR);") 
for person_id, lastname in enumerate(staff_names): 
    db.execute("INSERT INTO staff (person_id, lastname) VALUES (" + str(person_id) + ", '" + lastname + "') ")

Это выполняется, но выглядит это совсем некрасиво. Особенно когда в коде используется большое количество таких конкатенаций, тогда выражение получается очень фрагментированным. Более того, мне всегда приходится самому заботиться о приведении типов.

2. Старый подход: шаблоны строк с %s

Листая литературу по Python, даже самую современную, и читая различные записи на форумах в Stack Overflow или где-либо еще, можно увидеть приемы, которые в нашем примере выглядят следующим образом:

db.execute("CREATE TABLE staff (person_id int, lastname CHAR); ") 
for person_id, lastname in enumerate(staff_names): 
    db.execute("INSERT INTO staff (person_id, lastname) VALUES (%d, '%s') " % (person_id, lastname))

Работает как по маслу. «%d» — это подстановка для цифры, а маска «%s» — подстановка для строки.

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

sql = """SELECT lastname , CASE WHEN %d > 10 THEN 'greater' WHEN %d = 10 THEN 'equal' WHEN %d < 10 THEN 'lesser' END vergleich FROM staff WHERE lastname <> '%s' and %d > 0 """ % (person_id, person_id, person_id, lastname, person_id) 

Как только мы вводим здесь еще один плейсхолдер, риск ошибки возрастает, и нам приходится каждый раз пересчитывать позиции в коде. Сложность зашкаливает, внимание на пределе.

Здесь лучше выбрать назначенные плейсхолдеры, чтобы разгрузить исходную сложность выражения:

sql = """SELECT lastname , CASE WHEN %(person_id)d > 10 THEN 'greater' WHEN %(person_id)d = 10 THEN 'equal' WHEN %(person_id)d < 10 THEN 'lesser' END vergleich FROM staff WHERE lastname <> '%(lastname)d' and %(person_id)d > 0 """ % {'person_id': person_id, 'lastname': lastname) 

Замечательно! Теперь код намного легче читать, потому что мы сразу видим, что и куда мы вставляем.

Эта нотация создает только одну небольшую проблему, а именно: она считается устаревшей и, по крайней мере в Python 3, была заменена на более совершенную. Это означает, что ее больше не следует использовать, поскольку ее сохранение в последующих версиях Python не гарантировано. Однако в наши дни от нее еще не отказались — предположительно потому, что она все еще очень широко распространена в модулях.

3. Новый подход: шаблоны строк с {}

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

Давайте сначала рассмотрим простую версию:

 db.execute("CREATE TABLE staff (person_id int, lastname CHAR); ") 
for person_id, lastname in enumerate(staff_names): 
    db.execute("INSERT INTO staff (person_id, lastname) VALUES ({}, '{}') ".format(person_id, lastname))

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

Да, можно даже использовать кортежи, например, так:

db.execute("SELECT * FROM staff WHERE person_id in {}".format((1,3,4))) 

Функция формата строки вызывает метод __str__ каждого объекта. Это соответствует каждому str(object).

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

 db.execute("CREATE TABLE staff (person_id int, lastname CHAR); ") 
for person_id, lastname in enumerate(staff_names): 
    db.execute("INSERT INTO staff (person_id, lastname) VALUES ({person_id}, '{lastname}') ".format(person_id=person_id, lastname=lastname))

Все, кому лень писать, могут также использовать упаковку и распаковку кортежей вот так:

db.execute("CREATE TABLE staff (person_id int, lastname CHAR); ") 
for row in enumerate(staff_names): 
    db.execute("INSERT INTO staff (person_id, lastname) VALUES ({}, '{}') ".format(*row))

row является кортежем в каждом такте цикла, потому что enumerate() возвращает два значения в виде кортежа. Затем они принудительно помещаются в переменную row. С помощью обозначения «.format(*row)» кортеж можно снова распаковать и вызвать значения в соответствующем порядке.

То же самое работает со словарями.

 data = {'lastname': 'Mustermann', 'person_id': 12345} 
db.execute("INSERT INTO staff (person_id, lastname) VALUES ({person_id}, '{lastname}') ".format(**data))

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

def insert_staff(person_id, lastname, other_parameter1, other_parameter2): 
   db.execute("INSERT INTO staff (person_id, lastname) VALUES ({person_id}, '{lastname}') ".format(**globals())

Здесь команда format просто извлекает данные из переменных, ранее определенных в пространстве имен вызова функции, в данном случае функциональных параметров. Я сам использовал этот пример в Python 2.7. Однако в Python 3.x это уже не работает, и я считаю, что так будет всем лучше.

Теперь представим, что к нам приходит новый сотрудник, пятидесятый по счету, по фамилии OʼReilly. Имя добавляется быстро:

db.execute("INSERT INTO staff (person_id, lastname) VALUES ({person_id}, '{lastname}') ".format(person_id=50, lastname="O'Reilly"))

SQLite жалуется здесь «sqlite3.OperationalError: near "Reilly": syntax error». Что же это такое? Совершенно очевидно, что имя добавляется к оператору SQL, но апостроф в имени интерпретируется как конец строки имени, то есть « , 'O'Reilly')».

Здесь можно найти облегчение в середине безумия кодирования, удалив апостроф. Это зависит от базы данных. В SQLite апостроф должен быть удвоен.

db.execute("INSERT INTO staff (person_id, lastname) VALUES ({person_id}, '{lastname}') ".format(person_id=50, lastname="O'Reilly".replace("'", "''")))

Ну, пока это работает, но это всего лишь дешевый обходной путь.

Итак, теперь мы получаем еще одного нового сотрудника, пятьдесят первого, по имени «');DROP TABLE staff;». Странное имя, но если пользователь ввел его именно так, то оно должно быть правильным.

Если бы мы не удаляли различные символы из события с мистером OʼReilly и если бы мы не работали с SQLite, программой, которая не позволяет использовать два оператора в строке выполнения, то строка запроса выглядела бы следующим образом.

 INSERT INTO staff (person_id, lastname) VALUES 
(51, '');DROP TABLE staff;

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

4. Лучшая практика: параметризованные запросы

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

Однако наилучшей практикой, безусловно, является использование «параметризованных запросов». Независимо от языка программирования, каждый коннектор базы данных должен поддерживать такой тип передачи запросов. В Python, по крайней мере, я знаю из личного опыта, что это работает для Oracle, MySql, SQLite и PostgreSQL. Скорее всего, будет работать и во всех остальных БД.

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

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

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

К сожалению, тип параметризации в различных платформах баз данных не отличается единообразием (хотя его пытаются сделать таковым). Драйвер базы данных Python имеет по крайней мере один атрибут paramstyle, который определяет, какую технику следует использовать.

 print(sqlite.paramstyle) 
# Ausgabe ist 'qmark' 
# also 
db.execute("INSERT INTO staff (person_id, lastname) VALUES (?, ?)", (51, "Mc'Donald"))

В примере SQLite мы сначала проверяем, какой параметр является важным. Это ‘qmark‘, то есть вопросительные знаки. Оператор insert демонстрирует его использование. Вопросительные знаки добавляются для каждой позиции.

В правильном порядке значения должны быть переданы в виде кортежа как второй параметр в вызове функции. Преобразование типов выполняется автоматически, даже для объектов даты.

В PostgreSQL, например, существует другой формат, который также допускает назначенные параметры

 cursor.execute('SELECT * FROM staff WHERE person_id = %d', (99,)) 
# oder 
cursor.execute('SELECT * FROM staff WHERE person_id = %(person_id)d', {'person_id': 99})

 

5. Лучшая практика = только практика?

Название «Лучшая практика» на самом деле вводит в заблуждение. На самом деле оно должно звучать как «Единственная практика». На первый взгляд, параметризованные запросы кажутся громоздкими.

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

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

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

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

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

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