批量循环更新时psycopg2连接的最优处理方案咨询

连接方式选择 #

• 优先选择全程使用同一连接，任务结束后再关闭
  每次迭代新建连接的开销极大——数据库建立TCP连接、完成认证、分配资源都需要时间，数千次迭代会大幅拖慢任务速度，还会给数据库服务器带来不必要的负载，甚至可能触发连接数上限导致报错。
  复用单连接时，还可以通过批量提交事务优化性能：比如每处理100条数据执行一次conn.commit()，避免单条提交的频繁IO开销，进一步提升效率。

单次更新失败后的处理 #

• 优先执行回滚，无需立即关闭连接
  psycopg2连接在抛出异常后会进入“事务失败”状态，此时必须调用conn.rollback()重置事务状态，之后连接仍可正常用于后续更新操作。只有当出现不可恢复的连接错误（比如网络中断、数据库端强制断开连接）时，才需要关闭连接并重新建立。

示例代码 #

import psycopg2
from psycopg2 import IntegrityError, OperationalError

# 初始化单连接
conn = psycopg2.connect("dbname=your_db user=your_user password=your_pwd")
cur = conn.cursor()

batch_size = 100
count = 0

for item in your_data_list:
    try:
        # 检查X条件是否不满足
        cur.execute("SELECT id FROM your_table WHERE X条件 AND id = %s", (item["id"],))
        if not cur.fetchone():
            # 执行更新
            cur.execute("UPDATE your_table SET ... WHERE id = %s", (item["id"],))
            count += 1
            # 批量提交
            if count % batch_size == 0:
                conn.commit()
                print(f"已提交{count}条更新")
    except IntegrityError as e:
        print(f"数据更新冲突: {str(e)}")
        conn.rollback()
    except OperationalError as e:
        print(f"连接异常，重建连接: {str(e)}")
        conn.close()
        # 重建连接继续任务
        conn = psycopg2.connect("dbname=your_db user=your_user password=your_pwd")
        cur = conn.cursor()

# 提交剩余未完成的更新
if count % batch_size != 0:
    conn.commit()

# 任务结束关闭连接
cur.close()
conn.close()

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