У вас включен AdBlock или иной блокировщик рекламы.

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

Спасибо за понимание.

В другой раз
DevGang блог о програмировании
Авторизоваться

Простой подход к шаблонным 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', и запрос все равно будет работать.

#Python #SQL
Комментарии
Чтобы оставить комментарий, необходимо авторизоваться

Присоеденяйся в тусовку

Поделитесь своим опытом, расскажите о новом инструменте, библиотеке или фреймворке. Для этого не обязательно становится постоянным автором.

Попробовать