Python SQLAlchemy执行DELETE后INSERT触发唯一约束错误如何解决？

问题原因 #

通过create_engine.connect()获取的PostgreSQL连接默认关闭自动提交，所有DML操作（删除、插入）都运行在同一个隐式开启的事务中，未手动提交的事务不会将变更持久化，但这不是触发psycopg2.errors.UniqueViolation的直接原因——同一个事务内的操作是互相可见的，删除的结果对后续插入操作是生效的。

触发唯一约束冲突最常见的3个场景：

• 待插入的values列表内部存在重复的唯一键/主键值，注意检查是否有大小写敏感、首尾空格这类隐形重复，或者你判断重复的字段和表实际的唯一约束（可能是多字段组合唯一约束）不匹配
• 删除操作执行到插入操作的间隙，有其他并发的数据库会话向my_table中写入了和你待插入数据重复的唯一键值，未提交的删除操作只会对已存在的行加锁，无法阻止其他会话插入你将要写入的新数据
• 之前的代码运行异常导致连接未正常关闭，残留了未回滚的事务，表里存在未被当前会话可见的重复数据

调整方案 #

你不需要引入Session，以下两种调整方式都可以稳定运行：

方案1：用事务上下文管理器（更推荐，保证操作原子性） #

把删除、插入操作放到事务上下文中，操作完成后自动提交，出错自动回滚，避免事务残留问题：

from sqlalchemy import create_engine, MetaData, Table

db = create_engine("postgresql://...", echo=False).connect()
metadata = MetaData()
my_table = Table('my_table', metadata, autoload_with=db)
values = [...] # 你的待插入列表数据

# 开启事务上下文
with db.begin():
    db.execute(my_table.delete())
    db.execute(my_table.insert(), values)

# 上下文退出时自动提交，无需手动调用commit
db.close()

方案2：手动控制事务提交回滚 #

如果你不想用上下文管理器，可以手动管理事务生命周期，增加异常回滚逻辑：

from sqlalchemy import create_engine, MetaData, Table

db = create_engine("postgresql://...", echo=False).connect()
metadata = MetaData()
my_table = Table('my_table', metadata, autoload_with=db)
values = [...]

try:
    db.execute(my_table.delete())
    db.execute(my_table.insert(), values)
    db.commit()
except Exception as e:
    # 出错回滚所有变更
    db.rollback()
    raise e
finally:
    db.close()

额外优化：全表清理场景用TRUNCATE替代DELETE #

如果你的需求是清空全表再插入，使用TRUNCATE性能远高于DELETE，同时会直接重置表存储，减少并发冲突概率：

from sqlalchemy import text

# 替换delete语句为TRUNCATE，RESTART IDENTITY会同步重置自增序列，有外键依赖需加CASCADE
with db.begin():
    db.execute(text("TRUNCATE TABLE my_table RESTART IDENTITY"))
    db.execute(my_table.insert(), values)

注意TRUNCATE属于DDL操作，需要数据库账号有对应表的TRUNCATE权限。

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