combine python lists into sql table

Question

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)

just a thought. Have you tried using string formatting ? Do you have to use lists as parameters? — Bushmaster
– Bushmaster, Commented Nov 26, 2022 at 21:10
@Clegane yes, this would be used as part of a larger query where the lists are input as parameters. — user3294195
– user3294195, Commented Nov 26, 2022 at 21:19

Adrian Klaver · Accepted Answer · 2022-11-26 21:38:06Z

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)

Collectives™ on Stack Overflow

combine python lists into sql table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related