如何用Pandas将含列表的JSON数据拆分行写入SQLite3数据库？

现有JSON数据格式如下：

[{"name": "foo", "attr": ["bar", "baz"]}, {...}]

该数据已能正常读取为Pandas DataFrame（Python 3.9.2，Pandas 1.5.1），但直接调用to_sql()写入SQLite3时，报错：

sqlite3.InterfaceError: Error binding parameter 2 - probably unsupported type.

原因是SQLite不支持列表类型的字段，需要把数据转换成如下规范化表格形式再写入：

name attr
foo  bar
foo  baz

求简便实现方法，同时可以调整JSON的生成格式。

方法一：直接处理已读取的DataFrame #

用Pandas自带的explode()方法就能一键展开列表字段，这是最省事的方式：

import pandas as pd
import sqlite3

# 假设df是你已经读取好的DataFrame
df = pd.read_json("your_data.json")
# 把attr列的列表拆成单独行，同时保留name列对应值
df_exploded = df.explode('attr', ignore_index=True)
# 写入SQLite
conn = sqlite3.connect('your_db.db')
df_exploded.to_sql('your_table', conn, if_exists='replace', index=False)
conn.close()

explode()会自动把每个列表元素拆成独立行，完全匹配你要的格式。如果有多个列表字段需要展开，直接传字段列表就行：df.explode(['attr', 'other_list_col'])。

方法二：调整JSON生成格式（提前扁平化） #

如果能控制JSON的生成过程，直接生成扁平化的结构，省掉后续处理步骤：
把原JSON结构从

[{"name": "foo", "attr": ["bar", "baz"]}]

改成

[{"name": "foo", "attr": "bar"}, {"name": "foo", "attr": "baz"}]

这样读取成DataFrame后可以直接调用to_sql()写入，不需要额外处理。要是从其他数据源生成JSON，在生成阶段就做拆分，能减少后续的工作量。

内容的提问来源于stack exchange，提问作者gglazer