如何高效向PostgreSQL插入含不同键的Python字典列表？

问题背景 #

有一个Python字典列表，每个字典的键不完全一致，示例数据如下：

data = [
  {'name': 'Bob', 'age': 32},
  {'name': 'Sara', 'city': 'Dallas'},
  {'name': 'John', 'age': 45, 'city': 'Atlanta'}
]

需要将百万级别的这类数据批量插入到包含所有可能键（name、age、city）的PostgreSQL表中。原代码使用psycopg2.extras.execute_values时，因要求所有字典键一致而无法正常运行，需要修改流程实现适配。

原代码问题点：

• 仅取第一个字典的键作为插入列，导致后续缺失对应键的字典无法匹配列数
• 提取values时直接按字典values顺序，无法保证与列顺序对应，且缺失键时会少值

解决方案 #

核心思路是：先确定所有需要插入的列（覆盖所有字典的键），然后为每个字典按列顺序生成值，缺失的键用None填充（psycopg2会自动转换为PostgreSQL的NULL），再用execute_values批量插入。

完整修改代码 #

import psycopg2
from psycopg2.extras import execute_values

# 连接数据库
conn = psycopg2.connect(
    host="localhost",
    database="db_name",
    user="psql_user",
    password="psql_password",
)
conn.autocommit = True
cur = conn.cursor()

# 步骤1：获取所有需要插入的列（从所有字典的键中去重，也可直接指定表的列名）
all_columns = list({key for row in data for key in row.keys()})
# 可选：如果已知表的列，直接指定更可靠，比如：
# all_columns = ['name', 'age', 'city']

# 步骤2：构造INSERT语句，使用所有列
query = """INSERT INTO schema.table 
            ({}) VALUES %s
            ON CONFLICT (name) DO NOTHING""".format(
        ",".join(all_columns)
)

# 步骤3：按列顺序生成每个字典的values，缺失的键用None填充
values = [
    [row.get(col, None) for col in all_columns]
    for row in data
]

# 步骤4：执行批量插入
execute_values(cur, query, values)

# 关闭连接
cur.close()
conn.close()

关键修改说明 #

1. 获取全量列：不再仅依赖第一个字典的键，而是从所有数据字典中提取所有键并去重，确保覆盖所有可能的字段；如果已知目标表的列名，直接指定列列表会更高效且避免数据中遗漏的键。
2. 统一values格式：使用row.get(col, None)按列顺序提取值，缺失的字段自动填充None，保证每个values子列表的长度与列数一致，符合execute_values的要求。
3. 保持批量效率：依然使用execute_values实现批量插入，单条SQL语句处理大量数据，性能远优于逐条插入，适配百万级数据集。

进阶优化（可选） #

如果数据量极大（千万级以上），可以将数据分块处理，避免一次性加载过多数据到内存：

def chunk_data(data, chunk_size=10000):
    for i in range(0, len(data), chunk_size):
        yield data[i:i+chunk_size]

# 分块插入
for chunk in chunk_data(data):
    chunk_values = [[row.get(col, None) for col in all_columns] for row in chunk]
    execute_values(cur, query, chunk_values)

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