如何在psycopg中正确使用WHERE IN语句实现批量查询？

正确写法一：使用ANY操作符 #

psycopg会自动将Python列表转换为PostgreSQL数组，配合ANY操作符可直接匹配多个值：

topic_ids = ['1', '4']

with psycopg.connect(os.environ['DB_CONNECTION']) as conn:
    with conn.cursor() as cur:
        sql = "SELECT id FROM topics WHERE topic_id = ANY(%(ids)s)"
        cur.execute(sql, {"ids": topic_ids})
        result = cur.fetchall()  # 得到 [(2,), (21,)]

正确写法二：直接传入元组给IN子句 #

用%s作为占位符，将topic_ids转换为元组传入，psycopg会自动展开为IN所需的多个占位符：

topic_ids = ['1', '4']

with psycopg.connect(os.environ['DB_CONNECTION']) as conn:
    with conn.cursor() as cur:
        sql = "SELECT id FROM topics WHERE topic_id IN %s"
        cur.execute(sql, (tuple(topic_ids),))
        result = cur.fetchall()  # 得到 [(2,), (21,)]

原写法无结果的原因 #

你之前的写法IN (%(ids)s)，当传入元组('1','4')时，psycopg会将其处理为单个字符串值'(1,4)'，相当于执行了SELECT id FROM topics WHERE topic_id = '(1,4)'，而表中没有匹配该值的记录，所以返回空结果。

内容的提问来源于stack exchange，提问作者sjaustirni