使用psycopg2.extras.execute_values批量插入PostGIS数据库遇问题求助

我使用DuckDB读取包含Well Known Blob（WKB）几何信息的Parquet文件，通过psycopg2将数据写入PostgreSQL/PostGIS数据库。单条插入可正常执行但速度极慢，参考资料实现批量插入逻辑后，先触发'dict_keyiterator'对象不可下标的错误；调整后数据能传入数据库，但出现列a被整行数据填充，其余列（含几何列）正常的异常，求排查问题根源。

原单条插入代码 #

SQL = """INSERT INTO my_table(a,b,c,geometry,x,y)  VALUES (%s,%s,%s,ST_GeomFromWKB(%s, 4326),%s,%s);"""
params = [a,b,c,geometry,x,y]
cursor = pg_conn.cursor()
cursor.execute(SQL, params)

问题分析与解决方案 #

1. 'dict_keyiterator'对象不可下标错误原因及修复 #

该错误是因为直接对DuckDB返回行的keys()迭代器使用下标访问（如row.keys()[0]）。DuckDB查询结果的row.keys()返回的是迭代器而非列表，无法直接通过下标取值。修复方法是将迭代器转换为列表：

keys = list(row.keys())

2. 列a被整行填充的异常排查 #

此问题核心是批量插入的参数结构与SQL占位符不匹配，最常见的情况是误将整行数据作为列a的参数传入，而非提取列a的单独值。

正确的批量插入参数构造

确保每一行的参数是与SQL占位符顺序完全对应的元组/列表：

# 假设rows是DuckDB查询返回的结果集（每个元素为字典）
SQL = """INSERT INTO my_table(a,b,c,geometry,x,y) 
         VALUES (%s,%s,%s,ST_GeomFromWKB(%s, 4326),%s,%s);"""

# 构造参数列表：每个元素为对应列值的元组
params_list = [
    (row['a'], row['b'], row['c'], row['geometry'], row['x'], row['y'])
    for row in rows
]

# 使用executemany执行批量插入
cursor = pg_conn.cursor()
cursor.executemany(SQL, params_list)
pg_conn.commit()

更高效的批量插入优化

对于大数据量，推荐使用psycopg2.extras.execute_values，性能优于executemany：

from psycopg2.extras import execute_values

SQL = """INSERT INTO my_table(a,b,c,geometry,x,y) 
         VALUES %s;"""

# 传入构造好的params_list，指定模板匹配占位符
execute_values(
    cursor, 
    SQL, 
    params_list, 
    template="(%s,%s,%s,ST_GeomFromWKB(%s, 4326),%s,%s)"
)
pg_conn.commit()

验证步骤 #

• 打印params_list的前2-3个元素，确认每个元组的第一个值是列a的正确数据，而非整行对象。
• 核对DuckDB返回行的结构（字典/元组），确保参数提取顺序与SQL中的列顺序完全一致。

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