如何高效实现Pandas DataFrame全量数据的SQL更新？

原来的逐行循环更新会产生大量独立SQL请求，每个请求都要经历网络传输、数据库解析执行的开销，数据量大时速度极慢。下面是两种高效的批量更新方案：

方案1：临时表关联更新（通用推荐） #

先把需要更新的数据写入数据库临时表，再通过一次UPDATE JOIN语句完成所有更新，仅需两次数据库交互：

with engine.begin() as conn:
    # 将DataFrame写入临时表（不同数据库临时表语法略有差异）
    # PostgreSQL用temp table，MySQL用`#temp_update_table`，SQL Server用`#temp_update_table`
    df.to_sql('temp_update_table', conn, index=False, if_exists='replace')
    
    # 执行批量更新（PostgreSQL语法）
    conn.execute('''
        UPDATE SQL_TABLE t
        SET Column1 = temp.Column1
        FROM temp_update_table temp
        WHERE t.primary_key = temp.primary_key
    ''')
    
    # 如果是MySQL，替换为以下UPDATE语句：
    # conn.execute('''
    #     UPDATE SQL_TABLE t
    #     JOIN temp_update_table temp ON t.primary_key = temp.primary_key
    #     SET t.Column1 = temp.Column1
    # ''')

方案2：批量参数化更新（适合小到中等数据量） #

利用SQL的VALUES子句构造批量更新语句，通过一次请求完成所有更新：

from sqlalchemy import text

with engine.begin() as conn:
    # 整理更新参数（Column1值, 主键值）
    update_params = [(row['Column1'], row['primary_key']) for _, row in df.iterrows()]
    
    # PostgreSQL批量更新语法
    conn.execute(text('''
        UPDATE SQL_TABLE t
        SET Column1 = data.Column1
        FROM (VALUES :updates) AS data(Column1, primary_key)
        WHERE t.primary_key = data.primary_key
    '''), {'updates': update_params})

核心优化点 #

• 减少数据库交互次数：从N次请求降到1-2次，大幅降低网络IO和数据库执行开销
• 利用数据库的批量处理能力：数据库对批量操作的优化远优于逐行执行

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