如何使用 SqlAlchemy 进行更新插入? [英] How to do an upsert with SqlAlchemy?

问题描述

我有一条记录，如果它不存在，我希望存在于数据库中，如果它已经存在(主键存在)，我希望将字段更新为当前状态.这通常称为 upsert.

I have a record that I want to exist in the database if it is not there, and if it is there already (primary key exists) I want the fields to be updated to the current state. This is often called an upsert.

以下不完整的代码片段演示了哪些可以工作，但它看起来过于笨重(尤其是在有更多列的情况下).什么是更好/最好的方法?

The following incomplete code snippet demonstrates what will work, but it seems excessively clunky (especially if there were a lot more columns). What is the better/best way?

Base = declarative_base()
class Template(Base):
    __tablename__ = 'templates'
    id = Column(Integer, primary_key = True)
    name = Column(String(80), unique = True, index = True)
    template = Column(String(80), unique = True)
    description = Column(String(200))
    def __init__(self, Name, Template, Desc):
        self.name = Name
        self.template = Template
        self.description = Desc

def UpsertDefaultTemplate():
    sess = Session()
    desired_default = Template("default", "AABBCC", "This is the default template")
    try:
        q = sess.query(Template).filter_by(name = desiredDefault.name)
        existing_default = q.one()
    except sqlalchemy.orm.exc.NoResultFound:
        #default does not exist yet, so add it...
        sess.add(desired_default)
    else:
        #default already exists.  Make sure the values are what we want...
        assert isinstance(existing_default, Template)
        existing_default.name = desired_default.name
        existing_default.template = desired_default.template
        existing_default.description = desired_default.description
    sess.flush()

是否有更好或更不冗长的方法来做到这一点?像这样的东西会很棒:

Is there a better or less verbose way of doing this? Something like this would be great:

sess.upsert_this(desired_default, unique_key = "name")

虽然 unique_key kwarg 显然是不必要的(ORM 应该能够很容易地解决这个问题)我添加它只是因为 SQLAlchemy 往往只使用主键.例如:我一直在看是否 Session.merge 将是适用的，但这仅适用于主键，在这种情况下是一个自动递增的 id，对此目的不是非常有用.

although the unique_key kwarg is obviously unnecessary (the ORM should be able to easily figure this out) I added it just because SQLAlchemy tends to only work with the primary key. eg: I've been looking at whether Session.merge would be applicable, but this works only on primary key, which in this case is an autoincrementing id which is not terribly useful for this purpose.

一个示例用例只是在启动可能已升级其默认预期数据的服务器应用程序时.即:此更新插入没有并发问题.

A sample use case for this is simply when starting up a server application that may have upgraded its default expected data. ie: no concurrency concerns for this upsert.

推荐答案

SQLAlchemy 确实有一个保存或更新"行为，在最近的版本中已经内置到 session.add 中，但是以前是单独的 session.saveorupdate 调用.这不是更新插入"，但它可能足以满足您的需求.

SQLAlchemy does have a "save-or-update" behavior, which in recent versions has been built into session.add, but previously was the separate session.saveorupdate call. This is not an "upsert" but it may be good enough for your needs.

您询问具有多个唯一键的类是件好事；我相信这正是没有单一正确方法可以做到这一点的原因.主键也是唯一键.如果没有唯一约束，只有主键，这将是一个足够简单的问题:如果给定ID不存在，或者ID为None，则创建一个新记录；否则使用该主键更新现有记录中的所有其他字段.

It is good that you are asking about a class with multiple unique keys; I believe this is precisely the reason there is no single correct way to do this. The primary key is also a unique key. If there were no unique constraints, only the primary key, it would be a simple enough problem: if nothing with the given ID exists, or if ID is None, create a new record; else update all other fields in the existing record with that primary key.

但是，当存在额外的唯一约束时，这种简单方法存在逻辑问题.如果您想更新"一个对象，并且您的对象的主键匹配现有记录，但另一个唯一列匹配不同 记录，那么您会怎么做?类似地，如果主键不匹配现有记录，但另一个唯一列确实匹配现有记录，那怎么办?对于您的特定情况，可能有正确的答案，但总的来说，我认为没有唯一的正确答案.

However, when there are additional unique constraints, there are logical issues with that simple approach. If you want to "upsert" an object, and the primary key of your object matches an existing record, but another unique column matches a different record, then what do you do? Similarly, if the primary key matches no existing record, but another unique column does match an existing record, then what? There may be a correct answer for your particular situation, but in general I would argue there is no single correct answer.

这就是没有内置upsert"操作的原因.应用程序必须定义这在每种特定情况下的含义.

That would be the reason there is no built in "upsert" operation. The application must define what this means in each particular case.

这篇关于如何使用 SqlAlchemy 进行更新插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！