0

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?

Oleg
  • 1
  • 1
  • The list type is not supported by sqlite. . You need to split the 'arr' column into three columns 'arr0', 'arr1', 'arr2' and write it to the database like this. – Сергей Кох May 28 '22 at 18:31
  • Does this answer your question? [Python insert numpy array into sqlite3 database using pandas](https://stackoverflow.com/questions/50569070/python-insert-numpy-array-into-sqlite3-database-using-pandas) – Vandan Revanur May 28 '22 at 18:35

1 Answers1

1

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.

Artur Pollan
  • 31
  • 4