Передача параметра в DB .execute для списка WHERE IN... INT

Question

С помощью спецификации API БД Python вы можете передать аргумент параметров в метод execute(). Часть моего утверждения - это предложение WHERE IN, и я использовал кортеж для заполнения IN. Например:

params = ((3, 2, 1), )
stmt = "SELECT * FROM table WHERE id IN %s"
db.execute(stmt, params)

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

ProgrammingError: ОШИБКА: синтаксическая ошибка в или около ")"
ЛИНИЯ 13: ГДЕ ИД (3,)

Как я могу заставить кортеж правильно работать с предложением?

Answer 1

Изменить: Пожалуйста, как @rspeer упоминает в комментарии, принять меры предосторожности, чтобы защитить себя от атаки SQL инъекций.

Тестирование с pg8000 (интерфейс Pure-Python, совместимый с DB-API 2.0, с ядром базы данных PostgreSQL):

Это рекомендуемый способ передачи нескольких параметров в предложение "IN".

params = [3,2,1]
stmt = 'SELECT * FROM table WHERE id IN (%s)' % ','.join('%s' for i in params)
cursor.execute(stmt, params)

Еще одно редактирование (полностью протестированный и рабочий пример):

>>> from pg8000 import DBAPI
>>> conn = DBAPI.connect(user="a", database="d", host="localhost", password="p")
>>> c = conn.cursor()
>>> prms = [1,2,3]
>>> stmt = 'SELECT * FROM table WHERE id IN (%s)' % ','.join('%s' for i in prms)
>>> c.execute(stmt,prms)
>>> c.fetchall()
((1, u'myitem1'), (2, u'myitem2'), (3, u'myitem3'))

Answer 2

Ошибка идет через запятую после 3. Просто оставьте ее для единичных значений, и все готово.

params = ((3), ... )
stmt = "SELECT * FROM table WHERE id IN %s"
db.execute(stmt, params)

Answer 3

Как сказано в вопросе, следующее не удастся:

params = ((3, 2, 1), )
stmt = "SELECT * FROM table WHERE id IN %s"
db.execute(stmt, params)

После pg8000 документы вIN можно заменить на ANY() чтобы дать тот же результат:

params = ((3, 2, 1), )
stmt = "SELECT * FROM table WHERE id = ANY(%s)"
db.execute(stmt, params)

Это отправляет запрос и параметры отдельно на сервер, избегая атак с использованием SQL-инъекций.

Answer 4

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

params = [3,2,1]
# SELECT * from table where id in (%s,%s,%s)
stmt = 'SELECT * FROM table WHERE id IN ({})'.format(','.join(['%s']*len(params)))
cursor.execute(stmt, tuple(params))

Answer 5

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

DB-API в Python, похоже, не дает вам возможности передавать кортежи как безопасно замененные параметры. Принятый ответ от Берни использует Питон % оператор замещения, что небезопасно.

Однако вам может не потребоваться передавать кортежи в качестве параметров, особенно если нужный кортеж является результатом другого запроса SQL (как вы указали Дэниелу). Вместо этого вы можете использовать подзапросы SQL.

Если набор идентификаторов, которые вы хотите в предложении IN, является результатом SELECT id FROM other_table WHERE use=true, например:

stmt = "SELECT * FROM table WHERE id IN (SELECT id FROM other_table WHERE use=true)"
db.execute(stmt)

И это тоже можно параметризировать (безопасный способ). Если вы хотите выбрать идентификаторы с данными parent_id:

stmt = "SELECT * FROM table WHERE id IN (SELECT id FROM other_table WHERE parent_id=%s)"
params = (parent_id,)
db.execute(stmt, params)

Answer 6

Решение с ф-струной.

params = [...]
stmt = f"SELECT * FROM table WHERE id IN ({','.join(['%s']*len(params ),)})"
db.execute(stmt, params)

Если есть другой заполнитель параметров, он будет таким

age = 18
params = [...]
stmt = f"SELECT * FROM table WHERE age>%s AND id IN ({','.join(['%s']*len(params ),)})"
db.execute(stmt, tuple([age] + params))