["python","django","sqlalchemy"]
Does SQLAlchemy have an equivalent of Django's get_or_create?

I want to get an object from the database if it already exists (based on provided parameters) or create it if it does not. Django's get_or_create (or source) does this. Is there an equivalent shortcut in SQLAlchemy? I'm currently writing it out explicitly like this: def get_or_create_instrument(session, serial_number): instrument = session.query(Instrument).filter_by(serial_number=serial_number).first() if instrument: return instrument else: instrument = Instrument(serial_number) session.add(instrument) return instrument
That's basically the way to do it, there is no shortcut readily available AFAIK. You could generalize it ofcourse: def get_or_create(session, model, defaults=None, **kwargs): instance = session.query(model).filter_by(**kwargs).one_or_none() if instance: return instance, False else: params = {k: v for k, v in kwargs.items() if not isinstance(v, ClauseElement)} params.update(defaults or {}) instance = model(**params) try: session.add(instance) session.commit() except Exception: # The actual exception depends on the specific database so we catch all exceptions. This is similar to the official documentation: https://docs.sqlalchemy.org/en/latest/orm/session_transaction.html session.rollback() instance = session.query(model).filter_by(**kwargs).one() return instance, False else: return instance, True 2020 update (Python 3.9+ ONLY) Here is a cleaner version with Python 3.9's the new dict union operator (|=) def get_or_create(session, model, defaults=None, **kwargs): instance = session.query(model).filter_by(**kwargs).one_or_none() if instance: return instance, False else: kwargs |= defaults or {} instance = model(**kwargs) try: session.add(instance) session.commit() except Exception: # The actual exception depends on the specific database so we catch all exceptions. This is similar to the official documentation: https://docs.sqlalchemy.org/en/latest/orm/session_transaction.html session.rollback() instance = session.query(model).filter_by(**kwargs).one() return instance, False else: return instance, True Note: Similar to the Django version this will catch duplicate key constraints and similar errors. If your get or create is not guaranteed to return a single result it can still result in race conditions. To alleviate some of that issue you would need to add another one_or_none() style fetch right after the session.commit(). This still is no 100% guarantee against race conditions unless you also use a with_for_update() or serializable transaction mode.