如何将PANDAS DATAFRAME与列表列保存到SQL Lite？

发布于 2025-02-02 11:34:28 字数 651 浏览 1 评论 0原文

我有以下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')

我尝试将其保存在SQL Lite数据库中，

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

但会出现一个错误： “误差绑定参数3-可能不支持的类型。”

如何更好地将列“ arr”保存在sqllite中？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

想你的星星会说话 2025-02-09 11:34:28

如何将整个表达式转换为字符串，然后将字符串存储在SQLite中？然后检索并使用eval（）重新填充复杂的python数据类型？

>>> 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']]}

或者，使用Pickle（）而不是sqlite。

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.

回复收藏 0 原文

~没有更多了~