sqlite3“输入"条款 [英] sqlite3 "IN" clause

问题描述

我有一个包含不同列(pageNum、value1、value2、value3)的表格.

I have a table with different columns (pageNum, value1, value2, value3).

我想在 python3.3 和 sqlite3 中做类似的事情:

I want to do something like that in python3.3 and sqlite3:

selection = (100, 200, 300)

cursor.execute(""" SELECT value1, value3
                  FROM myTable
                  WHERE value2>? and pageNum IN ?
                  """, (10, selection))

但似乎是在抱怨:

sqlite3.OperationalError: near "?": syntax error

请注意，问题是关于如何以这种方式使用 IN 子句，而不是关于如何进行查询，当然在这种情况下可以以不同的方式完成.>

Note that the question is about how to use the IN clause that way, not about how to do the query, which of course in that case it could be done in a different way.

推荐答案

因此，Alex Martelli 的答案实质上是在解决您的问题，因为 Python 对 IN 的支持非常笨拙，并且本质上您有提供与集合中的值一样多的 ?.

So the answer from Alex Martelli is essentially solving your problem since python support for the IN is quite unwieldy, and in essence you have to provide as many ? as you have values in your collection.

selection = (100, 200, 300)
questionmarks = '?' * len(selection)
formatted_query = 'SELECT value1, value3 FROM myTable
              WHERE value2>? AND pageNum IN ({})'.format(','.join(questionmarks))
query_args = [10]
query_args.extend(selection)
result_set = c.execute(formatted_query, query_args)

上面的代码应该可以做，这里有一些关于它的作用的解释:

the above code should do, and heres bit of explanation on what it does:

它本质上准备了包含您需要的 ? 数量的 sql 语句，然后使用列表中提供的所有参数执行查询，以便 sqlite 负责转义这些值.

It essentially prepares the sql statement with as many ? as you need and then executes the query with all your arguments supplied in a list so sqlite will take care of escaping the values.

如果您将查询转换为字符串，则必须确保您自己清理了不推荐的值.

If you convert your query to a string you will have to make sure that you sanitize the values yourself which is not recommended.

这篇关于sqlite3“输入"条款的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！