Python将JSON文件转SQLite表时参数绑定类型错误如何解决

你的JSON数据中存在嵌套结构：_id、cpg字段的值都是Python字典类型，而SQLite仅支持存储整数、浮点数、字符串、布尔值、NULL等基础标量类型，不支持直接写入字典这种复合类型，因此pandas调用to_sql写入时会触发类型不支持的报错。

有两种常用方案，推荐选择第一种更方便后续数据分析：

方案1：将嵌套字段展开为扁平的标量字段 #

把嵌套层级里的取值提取成独立的单列，所有字段都转为SQLite支持的基础类型后再写入，修改后的代码如下：

import pandas as pd
import json
import sqlite3

# 读取json文件
with open("users.json", encoding="utf-8") as f:
    raw_dat = [json.loads(line.strip()) for line in f]

# 展开嵌套字段，处理为扁平结构
processed_dat = []
for item in raw_dat:
    flat_item = {}
    # 提取_id的oid值
    flat_item["id"] = item["_id"]["$oid"]
    # 提取一级普通字段
    flat_item["barcode"] = item.get("barcode")
    flat_item["brand_code"] = item.get("brandCode")
    flat_item["category"] = item.get("category")
    flat_item["category_code"] = item.get("categoryCode")
    flat_item["name"] = item.get("name")
    flat_item["top_brand"] = item.get("topBrand")
    # 提取cpg嵌套字段
    flat_item["cpg_id"] = item["cpg"]["$id"]["$oid"]
    flat_item["cpg_ref"] = item["cpg"]["$ref"]
    processed_dat.append(flat_item)

# 生成DataFrame
df = pd.DataFrame(processed_dat)

# 写入SQLite数据库
con = sqlite3.connect("fetch_rewards.db")
# if_exists参数可选：replace=覆盖旧表, append=追加, fail=表存在则报错
df.to_sql("users", con, if_exists="replace", index=False)

con.commit()
con.close()

方案2：将嵌套字段转为JSON字符串存储 #

如果不需要拆分嵌套字段的内容，也可以直接把字典序列化为JSON字符串写入，在生成原始df之后增加两行转换逻辑即可：

# 把嵌套字段转为json字符串
df["_id"] = df["_id"].apply(json.dumps)
df["cpg"] = df["cpg"].apply(json.dumps)

该方案缺点是后续查询嵌套字段内的取值需要额外做JSON解析，使用便捷度不如方案1。

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