使用pandas.read_sql合并Python列表为SQL表时unnest结果不符求解

你当前的代码会生成笛卡尔积（9行数据），而非预期的一一对应（3行数据），因为逗号分隔的两个unnest语句默认是交叉连接，会把第一个列表的每个元素和第二个列表的每个元素配对。

正确实现方式 #

方法1：使用多参数unnest（PostgreSQL 9.4+） #

PostgreSQL支持在unnest中传入多个数组，原生保证相同位置的元素在同一行：

import pandas as pd
from sqlalchemy import create_engine

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

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

df_expected = pd.DataFrame({"list1": list1, "list2": list2})

# 修改后的查询
df_query = pd.read_sql_query(
    """
    select * 
    from unnest(%(list1)s, %(list2)s) as t(list1, list2)
    """,
    con=engine,
    params={"list1": list1, "list2": list2},
)

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

方法2：用ordinality按序号关联（兼容低版本PostgreSQL） #

如果你的PostgreSQL版本低于9.4，可以给每个unnest的元素添加序号，再通过序号连接两个结果集：

df_query = pd.read_sql_query(
    """
    select a.val as list1, b.val as list2
    from unnest(%(list1)s) with ordinality as a(val, idx)
    inner join unnest(%(list2)s) with ordinality as b(val, idx)
    on a.idx = b.idx
    """,
    con=engine,
    params={"list1": list1, "list2": list2},
)

两种方法都能让两个列表的元素按位置一一对应，生成和df_expected完全一致的DataFrame，断言不会报错。

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