gpt4 book ai didi

python - 如何在 Python SQLite 中安全地使用 IN 命令?

转载 作者:行者123 更新时间:2023-12-04 01:07:42 25 4
gpt4 key购买 nike

有没有办法在 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 中实际上是不可行的,但我想知道为什么它是并且应该如此?

最佳答案

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

要解决这个问题,您只需根据需要在序列中生成尽可能多的单个占位符。通过喜欢:'(' + ','.join('?'*len(v)) + ')'

关于python - 如何在 Python SQLite 中安全地使用 IN 命令?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/65876587/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com