使用 WHERE

本文介绍了使用 WHERE ___ IN ___ 语句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试弄清楚如何正确使用 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.