How to save pandas dataframe with list column to sql lite?

Question

I have the following pandas dataframe

tmp=pd.DataFrame({'test':['aaa','bb','cccc'],
                  'date':['2019-05-16 05:59:36','2020-05-16 05:59:36','2021-05-16 05:59:36'],
                  'arr':[['a','b','c'],['q','ww','dd'],['dsaa','daaaa','13-dasdas']]})
tmp.date=tmp.date.astype('datetime64')

I try to save it in sql lite database

import sqlite3
database = "./tmp.sqlite"
conn = sqlite3.connect(database)
tmp.to_sql('tmp', con=conn,if_exists='replace')
conn.close()

but get an error: "Error binding parameter 3 - probably unsupported type."

how to better save column 'arr' in sqllite?

The list type is not supported by sqlite. <sqlite.org/datatype3.html>. You need to split the 'arr' column into three columns 'arr0', 'arr1', 'arr2' and write it to the database like this. — Сергей Кох
– Сергей Кох, Commented May 28, 2022 at 18:31
Does this answer your question? Python insert numpy array into sqlite3 database using pandas — Vandan Revanur
– Vandan Revanur, Commented May 28, 2022 at 18:35

Artur Pollan · Accepted Answer · 2022-05-28 18:45:47Z

How about converting the entire expression to a string and simply storing the string in SQLite? Then retrieve it and use eval() to repopulate the complex Python data type?

>>> a={'test':['aaa','bb','cccc'],
       'date':['2019-05-16 05:59:36','2020-05-16 05:59:36','2021-05-16 05:59:36'],
       'arr':[['a','b','c'],['q','ww','dd'],['dsaa','daaaa','13-dasdas']]}
>>> s=str(a)
>>> s
"{'test': ['aaa', 'bb', 'cccc'], 'date': ['2019-05-16 05:59:36', '2020-05-16 05:59:36', '2021-05-16 05:59:36'], 'arr': [['a', 'b', 'c'], ['q', 'ww', 'dd'], ['dsaa', 'daaaa', '13-dasdas']]}"
>>> eval(s)
{'test': ['aaa', 'bb', 'cccc'], 'date': ['2019-05-16 05:59:36', '2020-05-16 05:59:36', '2021-05-16 05:59:36'], 'arr': [['a', 'b', 'c'], ['q', 'ww', 'dd'], ['dsaa', 'daaaa', '13-dasdas']]}

Or, use pickle() instead of SQLite.

Collectives™ on Stack Overflow

How to save pandas dataframe with list column to sql lite?

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related