如何用Python将新CSV数据追加到PostgreSQL现有表末尾？

直接修改代码实现追加 #

你的当前代码每次运行都会删除并重建表，要实现数据累计存储，只需要移除删表和建表的语句，直接执行COPY命令即可——PostgreSQL的COPY FROM默认就是将数据追加到现有表中（只要表结构和CSV字段完全匹配）。

修改后的代码如下：

import psycopg2

def db_connection():
    conn_string = "host=address dbname='your_db' user='username' password='password'"
    conn = psycopg2.connect(conn_string)
    cursor = conn.cursor()
    print('数据库连接成功')

    # 移除删表和建表语句，确保test_data表已存在且结构与CSV匹配
    with open('/path/to/file/test_data.csv', 'r') as my_file:
        print('文件已加载到内存')
        
        # 上传逻辑保持不变，COPY默认执行追加操作
        SQL_STATEMENT = """
        COPY test_data FROM STDIN WITH
            CSV
            HEADER
            DELIMITER AS ','
        """

        cursor.copy_expert(sql=SQL_STATEMENT, file=my_file)
        print('数据已追加到表中')

    cursor.execute("grant select on table test_data to public")
    conn.commit()
    cursor.close()
    conn.close()  # 记得关闭数据库连接
    print('数据追加完成')

db_connection()

兼容首次运行的优化（可选） #

如果脚本可能在表不存在的情况下首次运行，可以添加表存在性检查，不存在则创建表：

import psycopg2

def db_connection():
    conn_string = "host=address dbname='your_db' user='username' password='password'"
    conn = psycopg2.connect(conn_string)
    cursor = conn.cursor()
    print('数据库连接成功')

    # 检查test_data表是否存在，不存在则创建
    cursor.execute("""
        SELECT EXISTS (
            SELECT FROM information_schema.tables 
            WHERE table_name = 'test_data'
        );
    """)
    table_exists = cursor.fetchone()[0]
    if not table_exists:
        cursor.execute("""
            create table test_data (
                id varchar,
                name varchar,
                address varchar
            )
        """)
        print('表test_data已创建')

    with open('/path/to/file/test_data.csv', 'r') as my_file:
        print('文件已加载到内存')
        
        SQL_STATEMENT = """
        COPY test_data FROM STDIN WITH
            CSV
            HEADER
            DELIMITER AS ','
        """

        cursor.copy_expert(sql=SQL_STATEMENT, file=my_file)
        print('数据已追加到表中')

    cursor.execute("grant select on table test_data to public")
    conn.commit()
    cursor.close()
    conn.close()
    print('数据追加完成')

db_connection()

方案对比：直接追加 vs 合并CSV后导入 #

直接追加是更优的方案，原因如下：

• 无需额外存储合并后的大文件，节省磁盘空间
• 每日实时处理，不需要等待积累多个CSV再操作，时效性更好
• 避免合并大文件时的内存占用问题，尤其是每日数据量较大的场景
• 逻辑更简单，减少出错概率

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