in sqlite 转换_用参数替换SQLite“IN”claus

我正在尝试对IN子句使用带SQLite within Python的参数替换。下面是一个完整的运行示例，演示了：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: Error binding parameter 0 - probably unsupported type.

虽然我希望这个简化问题的任何答案都能奏效，但我想指出的是，我要执行的实际查询是在一个双重嵌套的子查询中。也就是说：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

)

)