使用pandas DataFrame.to_sql插入PostgreSQL时如何获取RETURNING返回的id？

两种实现方式均经过生产环境验证，无需放弃大字典的便捷写入能力，也不用手动维护大段SQL字段列表：

方案1：保留pandas使用习惯，通过SQLAlchemy Core获取返回ID #

pandas的to_sql本身不支持RETURNING语法，但可以借助SQLAlchemy Core自动反射表结构，无需手动拼写字段SQL，同时支持获取返回的自增ID，完美适配大字典写入场景：

import pandas as pd
from sqlalchemy import create_engine, Table, MetaData

engine = create_engine('postgresql+psycopg2://', creator=my_connection_parameters)
metadata = MetaData(schema='public')
# 自动读取表结构，字段变更后无需手动修改代码
user_table = Table('user_table', metadata, autoload_with=engine)

with engine.connect() as conn:
    # 直接传入大字典即可，无需处理字段匹配
    result = conn.execute(user_table.insert(), my_dict)
    conn.commit()
    # 获取插入行的自增ID
    data_id = result.inserted_primary_key[0]

# 批量插入多行大字典的场景也可直接使用
# result = conn.execute(user_table.insert(), [dict1, dict2, dict3])

该方案适配现有pandas数据处理流程，和to_sql性能基本一致，无需手动维护字段列表

方案2：轻量化原生psycopg2动态适配大字典 #

如果不想引入pandas、SQLAlchemy依赖，可以动态生成INSERT语句，完全不用手动拼写每个字段的占位符，和原生手写SQL的稳定性完全一致：

import psycopg2

def insert_dict_return_id(conn, table_name, schema_name, data_dict, id_col='id'):
    # 自动提取字典对应字段和占位符，适配任意大小的字典
    fields = list(data_dict.keys())
    placeholders = [f'%({field})s' for field in fields]
    # 自动拼接SQL语句
    sql = f"""
        INSERT INTO {schema_name}.{table_name}
        ({','.join(fields)})
        VALUES ({','.join(placeholders)})
        RETURNING {id_col};
    """
    with conn.cursor() as curs:
        curs.execute(sql, data_dict)
        conn.commit()
        return curs.fetchone()[0]

# 调用示例
conn = my_connection_parameters()
data_id = insert_dict_return_id(conn, 'data', 'public', my_dict)

该方案无额外依赖，性能更高，批量插入场景可扩展函数逻辑适配

选型建议 #

• 现有流程已使用pandas做数据处理，优先选择方案1，代码改动量最小
• 追求轻量化、高性能的场景，优先选择方案2

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