如何在plpython3u存储过程中将Pandas DataFrame写入PostgreSQL表？

在plpython3u存储过程中，你可以通过以下两种常用方法将处理后的Pandas DataFrame写回PostgreSQL表：

方法1：构造参数化批量INSERT语句（轻量场景） #

通过plpy.execute执行参数化的INSERT语句，避免SQL注入，适合中小数据集：

CREATE OR REPLACE PROCEDURE public.plpy_proc_clas_full()
LANGUAGE 'plpython3u'
AS $BODY$
import pandas as pd

# 读取原表数据到DataFrame
data_lt = plpy.execute('SELECT "key", "value" FROM public."<your-table>" ORDER BY "key"')
df = pd.DataFrame.from_records(data_lt)

# 模拟数据处理步骤（根据你的业务需求修改）
df['value'] = df['value'] * 2

# 准备插入目标表（假设目标表为public.target_table，结构与原表一致）
target_columns = ('"key"', '"value"')
# 生成参数列表，每个元素是DataFrame一行的元组
params = [tuple(row) for _, row in df.iterrows()]
# 构造占位符字符串
placeholders = ', '.join(['(%s, %s)'] * len(params))

# 执行批量插入
insert_sql = f'INSERT INTO public.target_table ({", ".join(target_columns)}) VALUES {placeholders}'
plpy.execute(insert_sql, params)

$BODY$;

方法2：结合psycopg2与pandas.to_sql（高效场景） #

利用psycopg2获取当前会话连接，搭配pandas的to_sql方法实现高效批量写入，适合大数据集：

CREATE OR REPLACE PROCEDURE public.plpy_proc_clas_full()
LANGUAGE 'plpython3u'
AS $BODY$
import pandas as pd
import psycopg2
from sqlalchemy import create_engine

# 读取原表数据到DataFrame
data_lt = plpy.execute('SELECT "key", "value" FROM public."<your-table>" ORDER BY "key"')
df = pd.DataFrame.from_records(data_lt)

# 模拟数据处理步骤
df['value'] = df['value'] + 10

# 获取当前PostgreSQL连接（无需重新配置连接参数）
conn = psycopg2.connect(dbname=plpy.execute('SELECT current_database()')[0]['current_database()'])
# 创建SQLAlchemy引擎
engine = create_engine('postgresql+psycopg2://', creator=lambda: conn)

# 将DataFrame写入目标表
df.to_sql(
    name='target_table',  # 目标表名
    schema='public',      # 目标表所在schema
    con=engine,
    if_exists='append',   # 写入策略：append追加/replace覆盖/fail报错
    index=False           # 不写入DataFrame的索引列
)

$BODY$;

注意事项 #

• 确保执行存储过程的PostgreSQL用户拥有目标表的INSERT权限
• 参数化查询（方法1）能有效避免SQL注入风险，优先使用
• 大数据集推荐方法2，to_sql默认会分批次写入，性能更优
• 若目标表不存在，可将if_exists设为replace自动创建，但会清空原有数据

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