Подстановка параметров для предложения SQLite "IN"
Я пытаюсь использовать подстановку параметров с SQLite в Python для предложения IN. Вот полный пример, который демонстрирует:
import sqlite3
c = sqlite3.connect(":memory:")
c.execute('CREATE TABLE distro (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)')
for name in 'Ubuntu Fedora Puppy DSL SuSE'.split():
c.execute('INSERT INTO distro (name) VALUES (?)', [ name ] )
desired_ids = ["1", "2", "5", "47"]
result_set = c.execute('SELECT * FROM distro WHERE id IN (%s)' % (", ".join(desired_ids)), ())
for result in result_set:
print result
Распечатывает:
(1, u'Ubuntu ') (2, u'Fedora') (5, u'SuSE ')
Поскольку в документах говорится, что "[y] вы не должны собирать ваш запрос, используя строковые операции Python, потому что это небезопасно; это делает вашу программу уязвимой для атаки SQL-инъекцией", - я надеюсь использовать подстановку параметров.
Когда я пытаюсь:
result_set = c.execute('SELECT * FROM distro WHERE id IN (?)', [ (", ".join(desired_ids)) ])
Я получаю пустой набор результатов, и когда я пытаюсь:
result_set = c.execute('SELECT * FROM distro WHERE id IN (?)', [ desired_ids ] )
Я получил:
InterfaceError: Ошибка привязки параметра 0 - возможно, неподдерживаемый тип.
Хотя я надеюсь, что любой ответ на эту упрощенную задачу сработает, я хотел бы отметить, что фактический запрос, который я хочу выполнить, находится во вложенном подзапросе с двойным вложением. Для остроумия:
UPDATE dir_x_user SET user_revision = user_attempted_revision
WHERE user_id IN
(SELECT user_id FROM
(SELECT user_id, MAX(revision) FROM users WHERE obfuscated_name IN
("Argl883", "Manf496", "Mook657") GROUP BY user_id
)
)
8 ответов
Вам нужно правильное количество ?с, но это не представляет риск инъекции sql:
>>> result_set = c.execute('SELECT * FROM distro WHERE id IN (%s)' %
','.join('?'*len(desired_ids)), desired_ids)
>>> print result_set.fetchall()
[(1, u'Ubuntu'), (2, u'Fedora'), (5, u'SuSE')]
Согласно http://www.sqlite.org/limits.html (элемент 9), SQLite не может (по умолчанию) обрабатывать более 999 параметров для запроса, поэтому решения здесь (создание необходимого списка заполнителей) будут потерпите неудачу, если у вас есть тысячи предметов, которые вы ищете IN, Если это так, вам нужно разбить список на части, затем просмотреть его части и объединить результаты самостоятельно.
Если вам не нужны тысячи предметов в вашем IN В этом случае решение Алекса - это способ сделать это (и похоже, как это делает Django).
Обновление: это работает:
import sqlite3
c = sqlite3.connect(":memory:")
c.execute('CREATE TABLE distro (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)')
for name in 'Ubuntu Fedora Puppy DSL SuSE'.split():
c.execute('INSERT INTO distro (name) VALUES (?)', ( name,) )
desired_ids = ["1", "2", "5", "47"]
result_set = c.execute('SELECT * FROM distro WHERE id IN (%s)' % ("?," * len(desired_ids))[:-1], desired_ids)
for result in result_set:
print result
Проблема заключалась в том, что вам нужно иметь один? для каждого элемента в списке ввода.
Заявление ("?," * len(desired_ids))[:-1] делает повторяющуюся строку "?", затем обрезает последнюю запятую. так что для каждого элемента в re quired_ids есть один знак вопроса.
Я всегда заканчиваю тем, что делаю что-то вроде этого:
query = 'SELECT * FROM distro WHERE id IN (%s)' % ','.join('?' for i in desired_ids)
c.execute(query, desired_ids)
Нет риска инъекции, потому что вы не помещаете строки из требуемых_идей в запрос напрямую.
Мне нужно было использовать некоторые другие именованные параметры, поэтому я разработал две вспомогательные функции, которыми, возможно, стоит поделиться.
def prepare_list_query(name, values):
"""Prepare SQLite query with named parameters."""
list_query = ", ".join(":{}_{}".format(name, i) for i in range(len(values)))
return list_query
def prepare_list_dict(name, values):
"""Prepare SQLite dict with named parameters."""
list_dict = {"{}_{}".format(name, i): value for i, value in enumerate(values)}
return list_dict
# Usage:
desired_ids = ["1", "2", "5", "47"]
desired_types = ["active", "inactive"]
sql = "SELECT * FROM distro WHERE id IN ({}) AND type IN ({})".format(
prepare_list_query("desired_id", desired_ids),
prepare_list_query("desired_type", desired_types),
)
sql_dict = {"some": "other parameters you might need"}
sql_dict.update(prepare_list_dict("desired_id", desired_ids))
sql_dict.update(prepare_list_dict("desired_type", desired_types))
# # This results in:
# sql = "SELECT * FROM distro WHERE id IN (:desired_id_0, :desired_id_1, :desired_id_2, :desired_id_3) AND type IN (:desired_type_0, :desired_type_1)
# # and
# sql_dict = {
# "some": "other parameters you might need",
# "desired_id_0": "1",
# "desired_id_1": "2",
# "desired_id_2": "5",
# "desired_id_3": "47",
# "desired_type_0": "active",
# "desired_type_1": "inactive",
# }
Я написал очень тонкий слой под названием
notanormдля решения этой проблемы.
https://pypi.org/project/notanorm/
... и тогда ваш код выглядит так:
import notanorm
c = notanorm.SqliteDb(":memory:")
c.query('CREATE TABLE distro (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)')
for name in 'Ubuntu Fedora Puppy DSL SuSE'.split():
c.insert('distro', name=name)
desired_ids = ["1", "2", "5", "47"]
result_set = c.select('distro', id=desired_ids)
for result in result_set:
print(result)
Который легко читается, а также позволяет когда-нибудь переключаться между базами данных.
{'id': 1, 'name': 'Ubuntu'}
{'id': 2, 'name': 'Fedora'}
{'id': 5, 'name': 'SuSE'}
Также подумайте о более тяжелой структуре: https://www.sqlalchemy.org/
Можно использовать очень тонкий слой, например, нотанорм
https://pypi.org/project/notanorm/
... и тогда ваш код выглядит так:
import notanorm
c = notanorm.SqliteDb(":memory:")
c.query('CREATE TABLE distro (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)')
for name in 'Ubuntu Fedora Puppy DSL SuSE'.split():
c.insert('distro', name=name)
desired_ids = ["1", "2", "5", "47"]
result_set = c.select('distro', id=desired_ids)
for result in result_set:
print(result)
Это легко читать, а также позволяет когда-нибудь переключать базы данных.
{'id': 1, 'name': 'Ubuntu'}
{'id': 2, 'name': 'Fedora'}
{'id': 5, 'name': 'SuSE'}
В случае, если у sqlite возникли проблемы с длиной SQL-запроса, неопределенное количество вопросительных знаков может быть своего рода путаницей.