Простой подход к шаблонным SQL-запросам в Python
Существует множество ситуаций, когда нужно вставить параметры в запрос SQL, и существует множество способов реализации шаблонных запросов SQL в python. Не вдаваясь в сравнение различных подходов, этот пост объясняет простой и эффективный метод параметризации SQL с использованием JinjaSql. Помимо множества мощных функций Jinja2, таких как условные операторы и циклы, JinjaSql предлагает чистый и простой способ параметризации не только значений подставляемых в where и in, но и многое другое, подробнее ниже в статье.
Подстановка основных параметров
Предположим, у нас есть таблица transactions с записями о финансовых транзакциях. Столбцы в этой таблице могут быть такие transaction_id, user_id, transaction_date и amount. Чтобы вычислить количество транзакций и общую сумму для данного пользователя в заданный день, запрос непосредственно в базу данных может выглядеть примерно так
select user_id , count(*) as num_transactions , sum(amount) as total_amount from transactions where user_id = 1234 and transaction_date = '2019-03-02'
Здесь мы предполагаем, что база данных автоматически преобразует формат строкового представления даты YYYY-MM-DD в правильный тип даты.
Если мы хотим выполнить запрос выше для любого пользователя и даты, мы должны параметризовать user_id и значение transaction_date. В JinjaSql соответствующий шаблон будет выглядеть так:
select
user_id
, count(*) as num_transactions
, sum(amount) as total_amount
from
transactions
where
user_id = {{ uid }}
and transaction_date = {{ tdate }}Здесь значения были заменены заполнителями с именами переменных Python, заключенными в двойные фигурные скобки {{ }}. Обратите внимание , что имена переменных uid и tdate были выбраны только чтобы показать, что они являются именами переменных и не имеют ничего общего с самими названиями столбцов. Более читаемая версия того же шаблона хранится в переменной Python
user_transaction_template = '''
select
user_id
, count(*) as num_transactions
, sum(amount) as total_amount
from
transactions
where
user_id = {{ user_id }}
and transaction_date = {{ transaction_date }}
'''Далее нам нужно установить параметры для запроса.
params = {
'user_id': 1234,
'transaction_date': '2019-03-02',
}Теперь сгенерировать SQL-запрос из этого шаблона следующим образом.
from jinjasql import JinjaSql j = JinjaSql(param_style='pyformat') query, bind_params = j.prepare_query(user_transaction_template, params)
Если мы напечатаем query и bind_params, мы обнаружим, что первая является параметризованной строкой, а вторая является одним обьектом OrderedDict:
>>> print(query)
select
user_id
, count(*) as num_transactions
, sum(amount) as total_amount
from
transactions
where
user_id = %(user_id)s
and transaction_date = %(transaction_date)s
>>> print(bind_params)
OrderedDict([('user_id', 1234), ('transaction_date', '2018-03-01')])Выполнение параметризованных запросов
Многие соединения с базой данных имеют возможность передавать в качестве аргумента bind_params метод, выполняющий SQL-запрос для установки соединения. Для исследователя данных может быть естественным получить результаты запроса во фрейме данных Pandas. Когда у нас есть соединение conn, это так же просто, как запустить read_sql:
import pandas as pd frm = pd.read_sql(query, conn, params=bind_params)
Посмотрите документы JinjaSql для других примеров.
От шаблона к окончательному запросу SQL
Часто желательно полностью развернуть запрос со всеми параметрами перед его выполнением. Например, регистрация полного запроса неоценима для отладки пакетных процессов, поскольку можно скопировать и вставить запрос из журналов непосредственно в интерактивный интерфейс SQL. Заманчиво подставить в bind_params строку query. Однако мы быстро обнаруживаем, что строковые параметры должны быть заключены в кавычки, чтобы получить правильный SQL. Например, в приведенном выше шаблоне значение даты должно быть заключено в одинарные кавычки.
>>> print(query % bind_params) select user_id , count(*) as num_transactions , sum(amount) as total_amount from transactions where user_id = 1234 and transaction_date = 2018-03-01
Чтобы справиться с этим, нам нужна вспомогательная функция для правильного цитирования параметров, которые являются строками. Мы определяем, является ли параметр строкой, вызывая
from six import string_types isinstance(value, string_types)
Это работает как для Python 3, так и для 2.7. Параметры строки преобразуются в тип str, одинарные кавычки в именах экранируются другой одинарной кавычкой, и, наконец, все значение заключается в одинарные кавычки.
from six import string_types
def quote_sql_string(value):
'''
Если `value` является строковым типом, экранирует одинарные кавычки в
строке и возвращает строку, заключенную в одинарные кавычки
'''
if isinstance(value, string_types):
new_value = str(value)
new_value = new_value.replace("'", "''")
return "'{}'".format(new_value)
return valueНаконец, чтобы преобразовать шаблон в правильный SQL, мы в цикле перебрали bind_params, заключили в кавычки строки, а затем выполнили подстановку строк.
from copy import deepcopy
def get_sql_from_template(query, bind_params):
if not bind_params:
return query
params = deepcopy(bind_params)
for key, val in params.items():
params[key] = quote_sql_string(val)
return query % paramsТеперь мы можем легко получить окончательный запрос, который мы можем отправить или запустить в интерактивном режиме:
>>> print(get_sql_from_template(query, bind_params)) select user_id , count(*) as num_transactions , sum(amount) as total_amount from transactions where user_id = 1234 and transaction_date = '2018-03-01'
Собрав все это вместе, другая вспомогательная функция оборачивает вызовы JinjaSql и просто берет template и объект parameters и возвращает полный SQL.
from jinjasql import JinjaSql def apply_sql_template(template, parameters): ''' Apply a JinjaSql template (string) substituting parameters (dict) and return the final SQL. ''' j = JinjaSql(param_style='pyformat') query, bind_params = j.prepare_query(template, parameters) return get_sql_from_template(query, bind_params)
Вычислить статистику по столбцу
Вычисление статистики по значениям, хранящимся в конкретном столбце базы данных, удобно как при первом исследовании данных, так и для проверки данных в продакшене. Поскольку для простоты мы хотим продемонстрировать только некоторые особенности шаблонов, давайте просто поработаем со столбцами integer, такими как столбец user_id в таблице transactions. Для целочисленных столбцов нас интересует количество уникальных значений, минимальных и максимальных значений и количество нулей. Некоторые столбцы могут иметь, скажем, значение по умолчанию, -1 недостатки которого выходят за рамки этой публикации, однако мы хотим зафиксировать это, сообщив количество значений по умолчанию.
Рассмотрим следующий шаблон и функцию. Функция принимает имя таблицы, имя столбца и значение по умолчанию в качестве аргументов и возвращает SQL для вычисления статистики.
COLUMN_STATS_TEMPLATE = '''
select
{{ column_name | sqlsafe }} as column_name
, count(*) as num_rows
, count(distinct {{ column_name | sqlsafe }}) as num_unique
, sum(case when {{ column_name | sqlsafe }} is null then 1 else 0 end) as num_nulls
{% if default_value %}
, sum(case when {{ column_name | sqlsafe }} = {{ default_value }} then 1 else 0 end) as num_default
{% else %}
, 0 as num_default
{% endif %}
, min({{ column_name | sqlsafe }}) as min_value
, max({{ column_name | sqlsafe }}) as max_value
from
{{ table_name | sqlsafe }}
'''
def get_column_stats_sql(table_name, column_name, default_value):
'''
Возвращает SQL для вычисления статистики столбца. Передача None для default_value
приводит к нулевому выводу числа значений по умолчанию
'''
# Note that a string default needs to be quoted first.
params = {
'table_name': table_name,
'column_name': column_name,
'default_value': quote_sql_string(default_value),
}
return apply_sql_template(COLUMN_STATS_TEMPLATE, params)Эта функция проста и очень мощна, потому что она применяется к любому столбцу в любой таблице. Обратите внимание на синтаксис {% if default_value %} в шаблоне. Если значение по умолчанию, которое передается в функцию None, SQL возвращает ноль в поле num_default.
Приведенная выше функция и шаблон также будут работать со строками, датами и другими типами данных, если для параметра default_value установлено значение None. Однако для более разумной обработки различных типов данных необходимо расширить функцию, чтобы также принимать тип данных в качестве аргумента и строить логику, характерную для различных типов данных. Например, может потребоваться узнать минимальное и максимальное значения длины строки вместо минимального и максимального значения самого значения.
Давайте посмотрим на вывод для столбца transactions.user_id.
>>> print(get_column_stats_sql('transactions', 'user_id', None))
select
user_id as column_name
, count(*) as num_rows
, count(distinct user_id) as num_unique
, sum(case when user_id is null then 1 else 0 end) as num_nulls
, 0 as num_default
, min(user_id) as min_value
, max(user_id) as max_value
from
transactionsОбратите внимание, что вместо {% %} появляются пустые строки, которые можно удалить.
Резюме
С помощью вспомогательных функций, описанных выше, создание и запуск шаблонных SQL-запросов в python очень просты. Поскольку детали подстановки параметров скрыты, можно сосредоточиться на создании шаблона и набора параметров, а затем вызвать одну функцию для получения окончательного SQL.
Одним из важных предостережений является риск внедрения кода. Для пакетных процессов это не должно быть проблемой, но использование конструкции sqlsafe в веб-приложениях может быть опасным. Ключевое слово sqlsafe указывает на то, что пользователь (вы) уверен, что никакой инъекции кода невозможно, и берет на себя ответственность за это.
С другой стороны, возможность помещать произвольную строку в запрос позволяет передавать целые блоки кода в шаблон. Например, вместо передачи table_name='transactions', можно передавать '(select * from transactions where transaction_date = 2018-03-01) t', и запрос все равно будет работать.
