pandas 将python列表合并到sql表中

8yoxcaq7 于 2022-11-27 发布在 Python

关注(0)|答案(1)|浏览(126)

我有两个列表，我想使用pandas.read_sql将它们合并到一个sql表中。我尝试使用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]

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

# query
df_query = pd.read_sql_query(
    """
    select * 
    from unnest(%(list1)s) as list1, 
        unnest(%(list2)s) as list2
    """,
    con=engine,
    params={"list1": list1, "list2": list2},
)

# throws assertion error
assert df_query.equals(df_expected)

pandas

来源：https://stackoverflow.com/questions/74585449/combine-python-lists-into-sql-table

1条答案

按热度按时间

gev0vcfq1#

df_expected                                                                                                                                                               
  list1  list2
0     a      1
1     b      2
2     c      3

您的原始查询：

df_query = pd.read_sql_query(
    """
    select * 
    from unnest(%(list1)s) as list1, 
        unnest(%(list2)s) as list2
    """,
    con=engine,
    params={"list1": list1, "list2": list2},
)
 df_query                                                                                                                                                                    
  list1  list2
0     a      1
1     a      2
2     a      3
3     b      1
4     b      2
5     b      3
6     c      1
7     c      2
8     c      3

因此，您要在两个unnest集合之间执行联接。
您需要的内容：

df_query = pd.read_sql_query(
    """
    select  unnest(%(list1)s) as list1, 
        unnest(%(list2)s) as list2
    """,
    con=engine,
    params={"list1": list1, "list2": list2},
)
 df_query                                                                                                                                                                  
  list1  list2
0     a      1
1     b      2
2     c      3

#The below succeeds
assert df_query.equals(df_expected)

赞(0）回复(0）举报 2022-11-27

我来回答

pandas 将python列表合并到sql表中

1条答案

相关问题

热门标签

最新问答