参数化 'SELECT IN (...)' 查询

Question

我想用MySQLdb来创建一个带参数的查询，比如：

serials = ['0123456', '0123457']
c.execute('''select * from table where key in %s''', (serials,))

但是最后发送到数据库管理系统的却是：

select * from table where key in ("'0123456'", "'0123457'")

这样创建一个带参数的查询是否可能？还是说我必须自己循环来构建结果集？

注意：executemany(...) 这个方法不适用 - 它只会返回最后一个结果：

>>> c.executemany('''select * from table where key in (%s)''',
        [ (x,) for x in serials ] )
2L
>>> c.fetchall()
((1, '0123457', 'faketestdata'),)

---

最终的解决方案是根据Gareth的聪明回答调整过来的：

# Assume check above for case where len(serials) == 0
query = '''select * from table where key in ({0})'''.format(
    ','.join(["%s"] * len(serials)))
c.execute(query, tuple(serials)) # tuple() for case where len == 1

Answer 1

我想你想要的东西大概是这样的：

query = 'select * from table where key in (%s)' % ','.join('?' * len(serials))
c.execute(query, serials)