CODEWITHSUNDEEP
python-3.xpandasdataframe
Harini
Harini

Reputation: 33

Storing values that are lists into database from dataframe using python

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                                              []

Upvotes: 0

Views: 153

Answers (1)

Srce Cde
Srce Cde

Reputation: 1824

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

Upvotes: 1

Related Questions