session.add with insert-or-update

[Keith Edmunds]

I'm new to SQLAlchemy. Sorry if it shows.

I'm using a MySQL backend. I've set up a declarative_base, defined a table class, set up a session. I defined a record as a dictionary and added it successfully to the db with:

incident = Incidents(**record)
session.add(incident)
session.commit()

The behaviour I'd like from the add/commit steps is to update any existing records that has a matching Primary Key, or insert as a new record if there's no match.

I see posts on how to do that with core functionality, but how would I do that using the ORM as above?

[Michael Mulqueen]

Hi Keith,

Small world!

You have at least 3 options depending on your requirements:

1. Handle it in your own application logic (e.g. make a get_or_create method) - I tend to prefer this, business rules for create vs. update often creeps in.

2. Use session.merge https://docs.sqlalchemy.org/en/13/orm/session_state_management.html#merging

3. Upserts - https://docs.sqlalchemy.org/en/13/dialects/mysql.html#insert-on-duplicate-key-update-upsert (this also exists for PostgreSQL).

First option example:

# SQLAlchemy models
class Incident(Base):
    incident_id = Column(Integer, primary_key=True)

    @classmethod
    def get_or_create(cls, session, id_=None):
        if id_:
            incident = session.query(cls).filter(cls.incident_id == id_).one()
        else:
            incident = Incident()
            session.add(incident)
        return incident

    def populate(self, data):
        for key, value in data.items():
            assert hasattr(self, key)
            setattr(self, key, value)



# Request handler.
def report_incident():
    incident = Incident.get_or_create(session, record.get("incident_id"))
    incident.populate(record)
    session.commit()

session.add doesn't imply insert by the way (it's adding to the session, not adding to the database), so calling it on an object that's been retrieved in the same session won't cause problems.

Hope that helps.

Cheers,

Mike

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
http://www.sqlalchemy.org/
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/9dfd3fb5-5516-4bea-8cd1-9abf3e6280fc%40googlegroups.com.

[Keith Edmunds]

Thanks Mike! That looks good, and I'm favouring handling it in the application as you suggest.

[Jonathan Vanasco]

FWIW: If your application is high-traffic/high-concurrency, depending on how your transactions are scoped within the code you may want to do the getcreate or create step that calls `.flush` within an exception block or savepoint, to catch duplicate inserts.

I've only had to do this on 2 (out of dozens) of projects, but it was a substantial improvement in performance.

[Keith Edmunds]

Thanks Jonathan. This is a very low traffic application, so not a problem but I appreciate you mentioning it.