如何在pythonsqlite中安全地使用IN命令？

Question

有没有一种方法可以在Python中动态使用SQLite IN命令，而不提供准确的占位符数

例如，假设我正在尝试：

SELECT
*
FROM mytable
WHERE somecol IN (1, 3, 4, 7, 9)

在Python中，占位符是长度至少为1的元组，但我不确定如何（或者甚至，如果有可能）将它们与IN命令一起使用。我尝试过纯元组、字符串元组和纯字符串，包括自由元组和封闭元组，但均无效：

In [1]: import sqlite3
   ...: conn = sqlite3.connect(':memory:')
   ...: cur = conn.cursor()
   ...: l = [(i, chr(i+64)) for i in range(1, 11)]
   ...: cur.execute('CREATE TABLE mytable (somecol INTEGER, char TEXT)')
   ...: cur.executemany('INSERT INTO mytable VALUES(?, ?)', l)
   ...: conn.commit()

In [2]: tup = ((1, 3, 4, 7, 9),)
   ...: cur.execute('SELECT * FROM mytable WHERE somecol IN ?', tup)
OperationalError                          Traceback (most recent call last)
<ipython-input-2-195e99af7b4f> in <module>
----> 1 cur.execute('SELECT * FROM mytable WHERE somecol IN ?', tup).fetchall()

OperationalError: near "?": syntax error

In [3]: cur.execute('SELECT * FROM mytable WHERE somecol IN (?)', tup).fetchall()
---------------------------------------------------------------------------
InterfaceError                            Traceback (most recent call last)
<ipython-input-2-a6c2d28cce18> in <module>
----> 1 cur.execute('SELECT * FROM mytable WHERE somecol IN (?)', tup).fetchall()

InterfaceError: Error binding parameter 0 - probably unsupported type.

In [4]: tups = tuple(str(i) for i in tup)
   ...: cur.execute('SELECT * FROM mytable WHERE somecol IN ?', tups)
OperationalError                          Traceback (most recent call last)
<ipython-input-3-195e99af7b4f> in <module>
----> 1 cur.execute('SELECT * FROM mytable WHERE somecol IN ?', tups).fetchall()

OperationalError: near "?": syntax error

In [5]: # Empty list due to trying to fetch a somecol string value of "(1, 3, 4, 7, 9)"
   ...: cur.execute('SELECT * FROM mytable WHERE somecol IN (?)', tups).fetchall()
Out[5]: []

In [6]: stup = (', '.join(str(i) for i in tup[0]),)
   ...: cur.execute('SELECT * FROM mytable WHERE somecol IN ?', stup)
OperationalError                          Traceback (most recent call last)
<ipython-input-5-195e99af7b4f> in <module>
----> 1 cur.execute('SELECT * FROM mytable WHERE somecol IN ?', stup).fetchall()

OperationalError: near "?": syntax error

In [7]: # Empty list due to trying to fetch a somecol string value of "1, 3, 4, 7, 9"
   ...: cur.execute('SELECT * FROM mytable WHERE somecol IN (?)', stup).fetchall()
Out[7]: []

我知道如果我提供cur.execute('SELECT * FROM mytable WHERE somecol IN (?, ?, ?, ?, ?)', tup[0]).fetchall()，我会得到想要的结果，但那是因为我事先知道tup[0]的长度，并相应地调整了光标。然而，这将在我无法预料的应用程序上出现故障

我几乎可以肯定，在Python中，这实际上不是feasbile，但我想知道为什么是这样，为什么应该是这样

Answer 1

由于参数列表不能具有相同的长度，因此必须使用字符串格式来生成正确数量的参数标记检查此答案here

cur.execute("SELECT * FROM mytable WHERE somecol IN ({seq})".format( seq=','.join(['?']*len(tup[0]))),tup[0]).fetchall()

Answer 2

sqlite不支持其他数据库支持的序列占位符

为了解决这个问题，您只需在一个序列中根据需要生成尽可能多的单个占位符。类似Via:'(' + ','.join('?'*len(v)) + ')'

Answer 3

可以以逗号分隔的字符串连接所有值：

tup = (1, 3, 4, 7, 9)
s = ",".join(str(i) for i in tup)

然后使用运算符LIKE而不是IN：

sql = "SELECT * FROM mytable WHERE ',' || ? || ',' LIKE '%,' || somecol || ',%';"
cur.execute(sql, (s,)).fetchall()

这样，您只需要一个占位符