Psycopg2 copy_to执行不完全：大数据库导出CSV为空求助

问题代码 #

def backup_action():
    # read connection parameters
    conn = psycopg2.connect(clicked.get())
    # connect to the PostgreSQL server
    print('Connecting to the PostgreSQL database...')
    cursor = conn.cursor()
    f = open(cur_path+"/"+"kopia"+".csv", 'w')
    cursor.copy_to(f, 'mtr', sep=",")
    cursor.close()

问题描述 #

小型测试数据库上执行上述代码正常，但在存储实际数据的大型数据库上，CSV文件仅被创建却内容为空。此前用pyodbc备份时遇类似问题，通过延迟关闭连接解决，不确定Psycopg2是否有类似解决方案。

解决方案 #

1. 确保文件缓冲区刷新 #

大型数据库数据量较大，文件缓冲区未被填满时不会自动写入磁盘。用with语句管理文件，确保操作完成后自动关闭并刷新缓冲区：

def backup_action():
    conn = psycopg2.connect(clicked.get())
    print('Connecting to the PostgreSQL database...')
    cursor = conn.cursor()
    # with语句自动处理文件关闭
    with open(cur_path + "/kopia.csv", 'w') as f:
        cursor.copy_to(f, 'mtr', sep=",")
    cursor.close()
    conn.close()

2. 提交事务 #

Psycopg2默认开启事务，未提交的情况下，copy_to操作可能无法生效。添加事务提交步骤：

def backup_action():
    conn = psycopg2.connect(clicked.get())
    print('Connecting to the PostgreSQL database...')
    cursor = conn.cursor()
    with open(cur_path + "/kopia.csv", 'w') as f:
        cursor.copy_to(f, 'mtr', sep=",")
    conn.commit()  # 提交事务
    cursor.close()
    conn.close()

3. 添加异常捕获排查问题 #

通过异常捕获可以定位大型数据库是否存在权限、超时等问题：

def backup_action():
    cursor = None
    conn = None
    try:
        conn = psycopg2.connect(clicked.get())
        print('Connecting to the PostgreSQL database...')
        cursor = conn.cursor()
        with open(cur_path + "/kopia.csv", 'w') as f:
            cursor.copy_to(f, 'mtr', sep=",")
        conn.commit()
        print("备份完成")
    except Exception as e:
        print(f"备份失败: {str(e)}")
        if conn:
            conn.rollback()
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

核心逻辑：先确保文件操作完成再关闭连接，同时提交事务，避免因事务未提交或缓冲区未刷新导致的空文件问题。

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