回答此问题可获得 20 贡献值,回答如果被采纳可获得 50 分。
<p>有没有一种方法可以在Python中动态使用SQLite <code>IN</code>命令,而不提供准确的占位符数</p>
<p>例如,假设我正在尝试:</p>
<pre><code>SELECT
*
FROM mytable
WHERE somecol IN (1, 3, 4, 7, 9)
</code></pre>
<p>在Python中,占位符是长度至少为1的元组,但我不确定<em>如何</em>(或者甚至<em><strong>,如果<strong></em>有可能)将它们与<code>IN</code>命令一起使用。我尝试过纯元组、字符串元组和纯字符串,包括自由元组和封闭元组,但均无效:</p>
<pre class="lang-py prettyprint-override"><code>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]: []
</code></pre>
<p>我知道如果我提供<code>cur.execute('SELECT * FROM mytable WHERE somecol IN (?, ?, ?, ?, ?)', tup[0]).fetchall()</code>,我会得到想要的结果,但那是因为我事先知道<code>tup[0]</code>的长度,并相应地调整了光标。然而,这将在我无法预料的应用程序上出现故障</p>
<p>我几乎可以肯定,在Python中,这实际上不是feasbile,但我想知道为什么是这样,为什么应该是这样</p>