How to do an upsert with SqlAlchemy?

Question

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

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.

Answer 1

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.

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.

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

Answer 2

SQLAlchemy supports ON CONFLICT with two methods on_conflict_do_update() and on_conflict_do_nothing().

Copying from the documentation:

from sqlalchemy.dialects.postgresql import insert  stmt = insert(my_table).values(user_email='[email protected]', data='inserted data') stmt = stmt.on_conflict_do_update(     index_elements=[my_table.c.user_email],     index_where=my_table.c.user_email.like('%@gmail.com'),     set_=dict(data=stmt.excluded.data) ) conn.execute(stmt)