用 Python 标准库 sqlite3 处理 JSON 数据

从 SQLite version 3.38.0 (2022-02-22) 开始，它默认携带了JSON支持。使用Python标准库sqlite3处理JSON结构的数据是十分简单的。

根据文档sqlite3 - 注册可调用的适配器可以知道，如果我们需要把dict或list类型的Python对象自动转换成JSON字符串，只需要使用如下代码：

import sqlite3
import json

sqlite3.register_adapter(dict, json.dumps)
sqlite3.register_adapter(list, json.dumps)

再使用declared types模式，把数据库里JSON类型的字段自动转换为Python对象。

sqlite3.register_converter("JSON", json.loads)

con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.cursor()
cur.execute("CREATE TABLE test(d JSON)")

cur.execute("INSERT INTO test(d) VALUES (?)", ({"json": "value"},))
cur.execute("SELECT d FROM test")
print("with declared types:", cur.fetchone()[0])
cur.close()
con.close()

只需要把如下代码放在项目的入口处，就可以轻松的在sqlite3里使用JSON了。

import sqlite3
import json

# Register the adapter
sqlite3.register_adapter(dict, json.dumps)
sqlite3.register_adapter(list, json.dumps)

# Register the converter
sqlite3.register_converter("JSON", json.loads)

# Patch the connect method
_raw_connect = sqlite3.connect
sqlite3.connect = lambda *args, **kwargs: raw_connect(
    *args, **{"detect_types": sqlite3.PARSE_DECLTYPES, **kwargs}
)