使用pandas.read_sql_query匹配多列对应值组合的语法问题

要实现col1和col2的对应组合匹配（即('a',1)、('b',2)、('c',3)这样的配对，而非两个ANY条件产生的笛卡尔积），你需要利用PostgreSQL的复合类型数组特性，配合psycopg2对元组列表的参数支持，以下是正确实现方式：

正确实现代码 #

基于你的可复现示例，修改查询部分即可：

import numpy as np
import pandas as pd
from sqlalchemy import create_engine

list1 = ["a", "b", "c"]
list2 = [1, 2, 3]
n = 100

engine = create_engine(
    "postgresql+psycopg2://postgres:password@localhost:5432/database"
)

np.random.seed(2022)
df = pd.DataFrame(
    {
        "col1": np.random.choice(list1, n, replace=True),
        "col2": np.random.choice(list2, n, replace=True),
    }
)

# 写入数据表
df.to_sql("toy_table", engine, if_exists="replace", index=False)

# 正确的查询语句
df_query = pd.read_sql_query(
    """
    select * from toy_table
    where (col1, col2) = any(%(arr)s::(text, integer)[])
    """,
    con=engine,
    params={"arr": list(zip(list1, list2))},
)

# 预期结果
rows = [(x, y) for x, y in zip(list1, list2)]
df_expected = df.loc[df.apply(lambda x: tuple(x.values) in rows, axis=1)]

# 断言通过
assert df_expected.equals(df_query)

错误原因解析 #

第一种尝试的错误 #

你最初的写法array[(%(list1)s, %(list2)s)]会将两个独立数组拼接成二维数组，而非行类型数组。此时(col1, col2)是单个复合行，无法与二维数组的元素（数组类型）进行比较，因此触发类型不匹配错误。

第二种尝试的错误 #

传入列表的列表[[x,y]]时，psycopg2会将每个子列表解析为文本数组元素，而非复合行类型。比如['a',1]会被识别为text类型的数组元素，在比较时会尝试将'a'转换为整数类型，因此触发数值转换错误。改为传递元组的列表（zip生成的元组），并指定复合类型(text, integer)[]，就能让PostgreSQL正确识别每个元素为行类型，与(col1, col2)匹配。

替代方案（使用VALUES子句） #

如果你更习惯用IN而非ANY，也可以通过unnest配合子查询实现：

df_query = pd.read_sql_query(
    """
    select * from toy_table
    where (col1, col2) in (
        select * from unnest(%(arr)s::(text, integer)[])
    )
    """,
    con=engine,
    params={"arr": list(zip(list1, list2))},
)

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