“选择......在哪里......在"参数数量未知 [英] "SELECT ... WHERE ... IN" with unknown number of parameters

问题描述

我正在尝试以...的形式执行查询

SELECT col2 FROM tab WHERE col1 IN (val1, val2, val3...)

...其中值存储在任意长度的 Python 列表/元组中.我似乎找不到一种干净"的方法来做到这一点.

<预><代码>>>>db = 连接(:内存:")>>>db.execute("CREATE TABLE tab (col1 INTEGER, col2 TEXT)")>>>db.execute("INSERT INTO tab VALUES(1,'one')")>>>db.execute("INSERT INTO tab VALUES(2,'two')")>>>db.execute("INSERT INTO tab VALUES(3,'three')")>>>db.execute("INSERT INTO tab VALUES(4,'four')")>>>db.execute("INSERT INTO tab VALUES(5,'five')")>>>数据库提交()# 预期结果>>>db.execute("SELECT col2 FROM tab WHERE col1 IN (1,3,4)").fetchall()[(u'one',), (u'three',), (u'four',)]>>>vals = (1,3,4)>>>db.execute("SELECT col2 FROM tab WHERE col1 IN (?)", vals).fetchall()回溯(最近一次调用最后一次):文件<stdin>"，第 1 行，在 <module> 中sqlite3.ProgrammingError:提供的绑定数量不正确.当前语句使用 1，并提供了 3.>>>db.execute("SELECT col2 FROM tab WHERE col1 IN (?)", (vals,)).fetchall()回溯(最近一次调用最后一次):文件<stdin>"，第 1 行，在 <module> 中sqlite3.InterfaceError:错误绑定参数 0 - 可能不受支持的类型.>>>db.execute("SELECT col2 FROM tab WHERE col1 IN (?)", (','.join(str(val) for val in vals),)).fetchall()[]>>>

现在我可以做到以下几点，(我认为...如果我错了请纠正我)保留了内置参数替换的安全性，但它仍然有点难看:

<预><代码>>>>db.execute("SELECT col2 FROM tab WHERE col1 IN (" + ",".join("?"*len(vals)) + ")", vals).fetchall()[(u'one',), (u'three',), (u'four',)]>>>

这是我最好的选择，还是有更好的方法?

解决方案

这是您最好的选择，无需使用额外的库.我过去确实提倡这种技术，事实上不止一次.

您也可以改用 SQLAlchemy，它会为您生成 SQL，但这需要您爬上它的学习曲线并重写大部分应用程序.

I'm attempting to perform a query in the form of...

SELECT col2 FROM tab WHERE col1 IN (val1, val2, val3...)

...where the values are stored in a Python list/tuple of arbitrary length. I can't seem to find a "clean" way to do it.

>>> db = connect(":memory:")
>>> db.execute("CREATE TABLE tab (col1 INTEGER, col2 TEXT)")
>>> db.execute("INSERT INTO tab VALUES(1,'one')")
>>> db.execute("INSERT INTO tab VALUES(2,'two')")
>>> db.execute("INSERT INTO tab VALUES(3,'three')")
>>> db.execute("INSERT INTO tab VALUES(4,'four')")
>>> db.execute("INSERT INTO tab VALUES(5,'five')")
>>> db.commit()

# Expected result
>>> db.execute("SELECT col2 FROM tab WHERE col1 IN (1,3,4)").fetchall()
[(u'one',), (u'three',), (u'four',)]

>>> vals = (1,3,4)

>>> db.execute("SELECT col2 FROM tab WHERE col1 IN (?)", vals).fetchall()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 3 supplied.

>>> db.execute("SELECT col2 FROM tab WHERE col1 IN (?)", (vals,)).fetchall()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

>>> db.execute("SELECT col2 FROM tab WHERE col1 IN (?)", (','.join(str(val) for val in vals),)).fetchall()
[]

>>> 

Now I can do the following, which (I think... please correct me if I'm wrong) retains the security of the built-in parameter substitution, but it's still a bit ugly:

>>> db.execute("SELECT col2 FROM tab WHERE col1 IN (" + ",".join("?"*len(vals)) + ")", vals).fetchall()
[(u'one',), (u'three',), (u'four',)]
>>> 

Is that my best option, or is there a nicer way around this?

解决方案

That's your best option without using additional libraries. I certainly have advocated just that technique in the past, more than once in fact.

You could also switch to using SQLAlchemy, which generates SQL for you, but that requires you to climb its learning curve and rewrite most of your application.

这篇关于“选择......在哪里......在"参数数量未知的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！