python - SQLAlchemy 中的条件添加语句

Question

假设我想上传几条 SQL 记录到一个可能尚未填充的表中。如果表中或要提交到表的记录中已经存在带有主键(“ID”)的记录，我想用新记录替换现有记录。我使用的是 mssql、SQL Server 2008。

我的第一个猜测是

try:
    session.add(record)
    session.commit
except:
    session.query().\
        filter(Class.ID == record.ID).\
        update(some expression)
    session.commit()       

应该用什么表达方式？有没有更干净(更安全!)的方法来做到这一点？

Answer 1

一般来说，除非使用保证原子性的语句，否则您始终必须考虑多个参与者尝试插入或更新(不要忘记删除)可能引起的竞争条件。甚至是MERGE statement ，虽然只有一个语句，但可以 have race conditions如果使用不当。

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

如果出现完整性错误，SQLAlchemy 解决方案必须尝试插入并执行更新，或者如果没有行受到影响，则执行更新并尝试插入。它应该准备好重试，以防两个操作都失败(一行可能会被删除或插入其中):

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()

关于

If there is a record with a primary key("ID") that already exists, either in the table or in the records to be committed to a table, I want to replace the existing record with the new record.

如果您可以确定不会对相关表进行并发更新，则可以使用 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 传递，它将检查数据库中的主键。如果给定实例没有主键或找不到实例，则会创建一个新实例。

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