使用psycopg2.extras.execute_values向PostgreSQL插入JSON数组报错求助

错误原因分析 #

• psycopg2.ProgrammingError: can't adapt type 'dict'：psycopg2默认无法自动识别Python字典与PostgreSQL JSON类型的映射关系，需要显式声明转换规则。
• DETAIL: "[" must introduce explicitly-specified array dimensions：手动用json.dumps()把JSON数组转成字符串后，PostgreSQL会将其视为普通文本而非JSON数组类型，解析时因格式不匹配抛出错误。

解决方案 #

利用psycopg2.extras.Json适配器处理JSON类型，针对JSON数组列（json[]/jsonb[]）和普通JSON列（json/jsonb）分别做适配：

1. 导入必要模块

from psycopg2.extras import execute_values, Json

2. 处理数据行，适配PostgreSQL类型

假设你的DataFrame中：

• json_array_col 对应数据库的json[]类型列（存储多个JSON对象组成的数组）
• json_col 对应数据库的json类型列（存储单个JSON对象）

修改数据转换逻辑：

cols = list(df.columns)

def process_row(row):
    processed_vals = []
    for col_name, val in zip(cols, row):
        if col_name == "json_array_col":
            # 为json[]列：将列表内每个字典用Json包装，生成适配数组
            processed_vals.append([Json(item) for item in val])
        elif col_name == "json_col" and isinstance(val, dict):
            # 为json列：直接用Json包装单个字典
            processed_vals.append(Json(val))
        else:
            # 普通类型直接保留
            processed_vals.append(val)
    return tuple(processed_vals)

# 生成适配后的插入数据元组
tuples = [process_row(x) for x in df.to_numpy()]

3. 执行插入操作

用双引号包裹表名和列名，避免关键字冲突：

# 构建插入语句
query = 'INSERT INTO "mytable"({}) VALUES %s'.format(','.join(f'"{col}"' for col in cols))

with self.db.connect() as conn:
    with conn.cursor() as db_cursor:
        execute_values(db_cursor, query, tuples)

通用适配逻辑（多JSON列场景） #

如果有多个JSON数组列或不确定列类型，可以简化判断逻辑：

def process_row(row):
    processed_vals = []
    for val in row:
        if isinstance(val, list) and all(isinstance(item, dict) for item in val):
            # 列表元素全为字典，判定为json[]列
            processed_vals.append([Json(item) for item in val])
        elif isinstance(val, dict):
            # 单个字典，判定为json列
            processed_vals.append(Json(val))
        else:
            processed_vals.append(val)
    return tuple(processed_vals)

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