使用 WHERE ___ IN ___ 语句 [英] Using a WHERE ___ IN ___ statement
问题描述
我正在尝试弄清楚如何正确使用 WHERE _ IN _ 语句
I'm trying to figure out how to properly use a WHERE _ IN _ statement
定义:
c.execute('''CREATE TABLE IF NOT EXISTS tab (
_id integer PRIMARY KEY AUTOINCREMENT,
obj text NOT NULL
) ;''')
我正在尝试做这样的事情:
I'm trying to do something like this:
list_of_vars=['foo','bar']
statement="SELECT * FROM tab WHERE obj IN (?)"
c.execute(statement,"'"+"','".join(list_of_vars)+"'")
或者,我也试过这个,它直接评估为上述
Alternatively, I've also tried this, which directly evaluates to the above
statement="SELECT * FROM tab WHERE obj IN (?)"
c.execute(statement,"'foo','bar'")
我得到的错误是:
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 9 supplied
这给了我一个错误.当我这样做时,它可以工作,但不建议这样做,因为它容易受到 SQL 注入攻击.
This is giving me an error. When I do it this way, it works, but this is not recommended as it is vulnerable to a SQL injection attack.
statement="SELECT * FROM tab WHERE obj IN ("+"'"+"','".join(statement)+"'"+")
推荐答案
您需要创建足够的参数来匹配您的变量列表:
You need to create enough parameters to match your list of vars:
statement = "SELECT * FROM tab WHERE obj IN ({0})".format(', '.join(['?'] * len(list_of_vars)))
c.execute(statement, list_of_vars)
请注意,您传入 list_of_vars
作为参数值列表.使用 ', '.join()
我们生成一个由逗号分隔的 ?
字符的字符串,然后使用 .format()
插入该字符串进入声明.
Note that you pass in list_of_vars
as the parameter values list. Using the ', '.join()
we generate a string of ?
characters separated by commas, then use .format()
to insert that into the statement.
对于一长串变量,使用临时表来保存这些值可能更有效,然后对临时表使用 JOIN
而不是 IN
> 带有绑定参数的子句.
For a long list of variables, it may be more efficient to use a temporary table to hold those values, then use a JOIN
against the temporary table rather than an IN
clause with bind parameters.
这篇关于使用 WHERE ___ IN ___ 语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!