使用Pandas将JSON列表写入sqlite3 db以分隔行

k10s72fa  于 2022-11-30  发布在  SQLite
关注(0)|答案(1)|浏览(165)

想象一下如下所示的JSON:
[{"name": "foo", 'attr': ['bar', 'baz']}, {...}]
我可以很容易地将它读入DataFrame,没有问题。(Python 3.9.2,Pandas 1.5.1)
如果我只是试一下,我得到sqlite3.InterfaceError: Error binding parameter 2 - probably unsupported type.,所以它死在列表类型上。
我想做的是使用to_sql()或类似的方法写入一个规范化的表,例如:

name attr
foo  bar
foo  baz

有没有简单的方法可以做到这一点?而且,我可以完全控制JSON的生成,所以如果有更好的Pandas JSON格式,我可以重塑输入文件。

kmbjn2e3

kmbjn2e31#

如果我没理解错的话,你有多种方式来实现你的目标。

方法1:使用当前JSON结构

第一步,你有这样的命令:

my_json = [{"name": "foo1", 'attr': ['bar1', 'baz1']}, {"name": "foo2", 'attr': ['bar2', 'baz2']}]

然后创建一个pandas.DataFrame对象,如下所示:

import pandas as pd
my_df = pd.DataFrame(my_json)

它提供以下信息:

name          attr
0  foo1  [bar1, baz1]
1  foo2  [bar2, baz2]

现在,由于attr列包含列表,而列表是一个对象,因此无法将其导出到sqlite db。因此,我们将不得不对explode此列进行修改,以获得所需形式的my_df。为此,我们应用以下函数:

my_df = my_df.explode('attr').reset_index(drop=True)

现在,我们的数据框架如下所示:

name  attr
0  foo1  bar1
1  foo1  baz1
2  foo2  bar2
3  foo2  baz2

现在需要做的就是将它导出到sqlite。

my_engine = sqlalchemy.create_engine('sqlite:///test.db')
my_df.to_sql("test_table_1", my_engine, if_exists="replace", index=False)

现在我们有了一张table。

方法二:不同的JSON结构

既然您说您可以控制数据的结构,我个人希望在服务端做尽可能少的计算。所以我认为下面的JSON结构是最好的:

my_json2 = [
    {"name": "foo1", 'attr': 'bar1'},
    {"name": "foo1", 'attr': 'baz1'},
    {"name": "foo2", 'attr': 'bar2'},
    {"name": "foo2", 'attr': 'baz2'},
]

这样做的缺点是,JSON的大小(即行数)增加了,但是加载到df中是直接的。

my_df2 = pd.DataFrame(my_json2)

它给出:

name  attr
0  foo1  bar1
1  foo1  baz1
2  foo2  bar2
3  foo2  baz2

现在我们可以用与方法1类似的方式导出,

my_df2.to_sql('test_table_2', my_engine, if_exists='replace', index=False)

相关问题