将参数传递给DB .execute以获取WHERE IN ... INT列表 [英] Passing param to DB .execute for WHERE IN... INT list

问题描述

使用Python的DB API规范，您可以将参数参数传递给execute（）方法。我的声明的一部分是WHERE IN子句，我一直在使用元组填充IN。例如：

With Python's DB API spec you can pass an argument of parameters to the execute() method. Part of my statement is a WHERE IN clause and I've been using a tuple to populate the IN. For example:

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

但是当我遇到参数元组只是1个元组的情况，执行将失败。

But when I run into a situation where the parameter tuple is only a tuple of 1 item, the execute fails.

ProgrammingError：ERROR：语法错误位于或在）附近

第13行：哪里有ID（3，）

ProgrammingError: ERROR: syntax error at or near ")"
LINE 13: WHERE id IN (3,)

如何获取元组可以正确使用子句？

How can I get the tuple to work with clause properly?

推荐答案

编辑：请正如@rspeer在评论中所述，请采取预防措施以保护自己免受SQL注入攻击。

Please, as @rspeer mentions in a comment, do take precautions to protect yourself from SQL injection attack.

使用 pg8000 （与PostgreSQL数据库引擎的DB-API 2.0兼容的Pure-Python接口）：

Testing with pg8000 (a DB-API 2.0 compatible Pure-Python interface to the PostgreSQL database engine):

这是将多个参数传递给 IN子句的推荐方法。

This is the recommended way to pass multiple parameters to an "IN" clause.

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

另一个编辑（经过充分测试和有效的示例）：

Another edit (fully tested and working example):

>>> 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'))

这篇关于将参数传递给DB .execute以获取WHERE IN ... INT列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！