如何用Python读取PostgreSQL函数返回的Refcursor并转为DataFrame

完整Python代码 #

import psycopg2 as db
import datetime
import pandas as pd

# 连接数据库
conn = db.connect(host='abc', dbname='abc', user='a', password='abc', gssencmode="disable")
conn.set_session(autocommit=True)

func_date = datetime.date.today()
cur = conn.cursor()

# 调用函数获取游标名称列表
cur.callproc('func_name', [func_date])
cursor_names = [row[0] for row in cur.fetchall()]

# 存储每个游标对应的DataFrame
result_dfs = {}
for cursor_name in cursor_names:
    # 通过游标名称查询结果集
    cur.execute(f"SELECT * FROM {cursor_name}")
    # 获取数据和列名
    rows = cur.fetchall()
    columns = [desc[0] for desc in cur.description]
    # 转为DataFrame
    result_dfs[cursor_name] = pd.DataFrame(rows, columns=columns)

# 示例：使用获取到的DataFrame
print("users表数据：")
print(result_dfs['data_1'].head())
print("\nroles表数据：")
print(result_dfs['data_2'].head())

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

关键步骤说明 #

1. 提取游标名称：调用callproc后，fetchall()返回包含游标名称的元组列表，先提取出所有游标名称（比如data_1、data_2）。
2. 查询游标结果集：PostgreSQL中refcursor可直接作为数据源，通过SELECT * FROM <游标名称>就能获取函数中打开的结果集。
3. 构造DataFrame：用cursor.description获取结果集列名，结合fetchall()的行数据，生成带列名的DataFrame。
4. 统一存储：将每个游标对应的DataFrame存入字典，方便后续按名称快速访问。

注意事项 #

• 必须开启autocommit=True：refcursor的生命周期依赖事务，关闭自动提交会导致函数返回的游标无法被访问。
• 保证游标名称唯一：函数中定义的游标名称（data_1、data_2）需在当前连接中唯一，避免冲突。
• 及时清理资源：使用完毕后关闭游标和数据库连接，防止资源泄漏。

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