Передать аргументы из переменных в SQL-запрос
Таким образом не выходит. И еще подскажите, пожалуйста, в моей таблице есть поле used, в котором по умолчанию во всех записях 0 , мне нужно, чтобы запрос обновлял первую попавшуюся запись, где used равно 0 , а остальные не трогал. Благодарю!
1 ответ 1
Для конкатенции строки с переменными вы можете использовать один из следующих вариантов:
- Обычная конкатенция оператором +
- Форматирования с помощью оператора %
- Использовать метод format() для строк
- Использовать f-строки (только для python 3)
Пример с использованием метода .format()
def insertUser(ID, AUTHOR): con = lite.connect("something.db") with con: cur = con.cursor() sql = """UPDATE someth SET used = 1, dis_name = <>, dis_id = <> WHERE used = 0 ORDER BY id ASC LIMIT 1""".format(AUTHOR, ID) cur.execute(sql) Для того, чтобы обновить только первую запись, необходимо использовать команду LIMIT с значением 1 вместе ORDER BY (для сортировки по ключевому полю). В примере использована колонка id (как правило, так обозначают в таблицах primary key поля). Если у вас нет такого поля, можете заменить на существующее
Если вы используете sqlite, скомпилированную без объявления опции
#define SQLITE_ENABLE_UPDATE_DELETE_LIMIT То предыдущий код работать не будет, но вы можете реализовать аналог с помощью подзапроса:
def insertUser(ID, AUTHOR): con = lite.connect("something.db") with con: cur = con.cursor() sql = """UPDATE someth SET used = 1, dis_name = <>, dis_id = <> WHERE used = ( SELECT id FROM someth WHERE ORDER BY id ASC LIMIT 1) """.format(AUTHOR, ID) cur.execute(sql) Либо перекомпировать sqlite согласно инструкции с включенной опцией SQLITE_ENABLE_UPDATE_DELETE_LIMIT
Простой подход к шаблонным 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 = > and transaction_date = >
Здесь значения были заменены заполнителями с именами переменных Python, заключенными в двойные фигурные скобки > . Обратите внимание , что имена переменных uid и tdate были выбраны только чтобы показать, что они являются именами переменных и не имеют ничего общего с самими названиями столбцов. Более читаемая версия того же шаблона хранится в переменной Python
user_transaction_template = ''' select user_id , count(*) as num_transactions , sum(amount) as total_amount from transactions where user_id = > and transaction_date = > '''
Далее нам нужно установить параметры для запроса.
Теперь сгенерировать 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(): paramsSql python с параметрами = 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 > as column_name , count(*) as num_rows , count(distinct >) as num_unique , sum(case when > is null then 1 else 0 end) as num_nulls , sum(case when > = > then 1 else 0 end) as num_default , 0 as num_default , min(>) as min_value , max(>) as max_value from > ''' 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)
Эта функция проста и очень мощна, потому что она применяется к любому столбцу в любой таблице. Обратите внимание на синтаксис в шаблоне. Если значение по умолчанию, которое передается в функцию 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’ , и запрос все равно будет работать.