如何通过psycopg2向PostgreSQL的json[]字段插入JSON数组

psycopg2的Json适配器默认将Python字典序列化为JSON格式的text类型值，传入由Json对象组成的列表时，psycopg2会自动将整个列表适配为text[]（文本数组）类型，和表字段定义的json[]类型不匹配，触发类型错误。

以下两种方案二选一即可，不要混用避免出现重复序列化问题：

• 方案1：SQL侧显式类型转换（改动量最小）
  无需修改现有Python侧的参数构造逻辑，仅需在插入语句中给数组参数增加::json[]显式类型转换，修改后的执行代码如下：

  cur.execute("insert into testtable values(%(id)s, %(data)s::json[])", insertdata)
  

  执行时PostgreSQL会自动将传入的文本数组逐元素转换为json类型后存入对应字段。

• 方案2：提前注册Json类型适配器（无需修改SQL语句）
  建立数据库连接后先注册json类型自动适配，后续直接传入原生Python字典组成的列表即可，不需要手动给每个元素套Json包装，示例代码如下：

  import psycopg2
  from psycopg2.extras import register_json
  
  # 替换为自己的数据库连接配置
  conn = psycopg2.connect(dbname="你的库名", user="用户名", password="密码", host="地址")
  register_json(conn)
  cur = conn.cursor()
  
  insertdata = {"id": "001", "data": [{"foo": 1}, {"foo": 2}]}
  cur.execute("insert into testtable values(%(id)s, %(data)s)", insertdata)
  conn.commit()
  

  注册适配后psycopg2会自动识别列表内的字典结构，将整个列表适配为标准的json[]类型完成插入，不需要额外做类型转换。

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