在psycopg2中向SQL传递参数列表

Question

我有一份需要从数据库中获取的行的ID列表。我正在使用Python和psycopg2库，但我遇到的问题是如何有效地将这些ID传递给SQL查询？我的意思是，如果我知道这个列表的长度，那就很简单了，因为我可以手动或自动在查询字符串中添加所需数量的"%s"占位符。但是在这里，我不知道我需要多少个占位符。重要的是，我需要使用SQL的"id IN (id1, id2, ...)"语句来选择这些行。我知道可以检查列表的长度，然后把合适数量的"%s"拼接到查询字符串中，但我担心这样做会很慢而且看起来很糟糕。有没有人知道怎么解决这个问题？另外，请不要问我为什么需要用"IN"语句来做这个——这是我课堂作业的一部分，谢谢大家！

Answer 1

现在，psycopg2的sql模块（https://www.psycopg.org/docs/sql.html）可以用来防止错误和注入攻击，比如下面这样：

import psycopg2
from psycopg2 import sql

params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()

ids = ['a','b','c']
sql_query = sql.SQL('SELECT * FROM {} WHERE id IN ({});').format(
                    sql.Identifier('table_name'),
                    sql.SQL(',').join(map(sql.Literal, ids))
                )
print (sql_query.as_string(cur)) # for debug
cur.execute(sql_query)

from configparser import ConfigParser
def config(filename='database.ini', section='postgresql'):
    # create a parser
    parser = ConfigParser()
    # read config file
    parser.read(filename)

    # get section, default to postgresql
    db = {}
    if parser.has_section(section):
        params = parser.items(section)
        for param in params:
            db[param[0]] = param[1]
    else:
        raise Exception('Section {0} not found in the {1} file'.format(section, filename))

    return db

注意：sql.Identifier会在需要的时候自动加上引号，所以如果你在PostgreSQL中使用带引号的标识符也没问题（这样做是为了支持区分大小写的命名）。

下面是一个示例和database.ini的结构：

[postgresql]
host=localhost
port=5432
database=postgres
user=user
password=mypass

Answer 2

这个问题比较老了，可能现在已经有更新的答案了，不过我同事们现在采用的答案是这个：

sql = "SELECT * FROM table WHERE column = ANY(%(parameter_array)s)"
cur.execute(sql,{"parameter_array": [1, 2, 3]})

Answer 3

在psycopg2中，Python的元组会被转换成SQL列表：

cur.mogrify("SELECT * FROM table WHERE column IN %s;", ((1,2,3),))

这将输出

'SELECT * FROM table WHERE column IN (1,2,3);'

对于刚接触Python的新手来说：在这里使用元组而不是列表是非常重要的。下面是第二个例子：

cur.mogrify("SELECT * FROM table WHERE column IN %s;", 
    tuple([row[0] for row in rows]))