combine python lists into sql table-CodePudding

I have two lists which I want to combine into an sql table using pandas.read_sql. I tried using unnest, but it gives me the wrong output. Attempt below:

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)

CodePudding user response：

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

Your original 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},
)
 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

So you are doing a join between the two unnest sets.

What you want:

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)