How to do an upsert with SqlAlchemy?

python, sqlalchemy, upsert

Solution

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='a@b.com', 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)

Problem

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.

Original source

Related problems