Storing values that are lists into database from dataframe using python

Question

How to store values to mysql database that is a dataframe consisting of lists in every row. Below is the dataframe:

0                    [Nissan Motor, Carlos Ghosn]
1                    [Nissan Motor, Carlos Ghosn]
2                                              []
3                              [David Muir, Trio]
4                                              []
5                                              []
6                                              []

Do you want comma-separated values to go in different columns or single column? — Mayank Porwal
– Mayank Porwal, Commented Nov 26, 2018 at 9:03

Srce Cde · Accepted Answer · 2018-11-26 10:19:57Z

1

Something like this?

from sqlalchemy.dialects import postgresql
x = [["Nissan Motor", "Carlos Ghosn"], ["Nissan Motor", "Carlos Ghosn"], [], ["David Muir", "Trio"], [], [],[]]
df = pd.DataFrame({"data": x})

df.to_sql(con = "connection_string", name = "table_name", schema = "schema_name", if_exists="replace", dtype={'data': postgresql.JSONB}, index = False)

Reading it back:

q = "select * from schema.table_name"
df = pd.read_sql(q, con="connection_string")
print(df)

Output:

                           data
0  [Nissan Motor, Carlos Ghosn]
1  [Nissan Motor, Carlos Ghosn]
2                            []
3            [David Muir, Trio]
4                            []
5                            []
6                            []

Used JSONB

answered Nov 26, 2018 at 10:19

Srce Cde

1,82412 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Storing values that are lists into database from dataframe using python

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