SQLAlchemy中的条件添加语句

通常，除非使用保证原子性的语句，否则您始终必须考虑可能由于多个参与者尝试插入或更新（不要忘记删除）而引起的竞争条件。即使使用MERGE语句（即使是单个语句），如果使用不正确也可能具有竞争条件。

传统上，这种“更新”是使用存储过程或其他SQL或特定于实现的特定功能（例如MERGE语句）执行的。

如果出现完整性错误，SQLAlchemy解决方案必须尝试插入并执行更新，或者如果不影响任何行，则执行udpate并尝试插入。如果两个操作都失败（行可能会在其中删除或插入），则应做好重试的准备：

from sqlalchemy.exc import IntegrityError

while True:  # Infinite loop, use a retry counter  if necessary
    try:
        # begin a save point, prevents the whole transaction failing
        # in case of an integrity error
        with session.begin_nested():
            session.add(record)
            # Flush instead of commit, we need the transaction intact
            session.flush()
            # If the flush is successful, break out of the loop as the insert
            # was performed
            break

    except IntegrityError:
        # Attempt the update. If the session has to reflect the changes
        # performed by the update, change the `synchronize_session` argument.
        if session.query(Class).\
                filter_by(ID=record.ID).\
                update({...},
                       syncronize_session=False):
            # 1 or more rows were affected (hopefully 1)
            break

        # Nothing was updated, perhaps a DELETE in between

    # Both operations have failed, retry

session.commit()

关于

如果在表或要提交到表的记录中已经存在具有主键（“ ID”）的记录，我想用新记录替换现有记录。

如果您可以确定不会对该表进行任何并发更新，则可以Session.merge执行以下任务：

# Records have primary key set, on which merge can either load existing
# state and merge, or create a new record in session if none was found.
for record in records:
    merged_record = session.merge(record)
    # Note that merged_record is not record

session.commit()

SQLAlchemy合并将首先检查身份映射中是否存在具有给定主键的实例。如果没有load通过，则通过True它，然后将在数据库中检查主键。如果给定实例没有主键或找不到实例，则将创建一个新实例。

然后，合并会将给定实例的状态复制到已定位/创建的实例上。返回新实例。