[Python] Parameter substitution for a SQLite "IN" clause

I am trying to use parameter substitution with SQLite within Python for an IN clause. Here is a complete running example that demonstrates:

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


It prints out:

(1, u'Ubuntu') (2, u'Fedora') (5, u'SuSE')
​

As the docs state that "[y]ou shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack," I am hoping to use parameter substitution.

When I try:

result_set = c.execute('SELECT * FROM distro WHERE id IN (?)', [ (", ".join(desired_ids)) ])


I get an empty result set, and when I try:

result_set = c.execute('SELECT * FROM distro WHERE id IN (?)', [ desired_ids ] )


I get:

InterfaceError: Error binding parameter 0 - probably unsupported type.
​

While I hope that any answer to this simplified problem will work, I would like to point out that the actual query I want to perform is in a doubly-nested subquery. To wit:

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

Continue reading...