Fastest Way to Create a New Object Only if it Doesn't Already Exist (SQLAlchemy) [duplicate]

Question

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 

Answer 1

Following the solution of @WoLpH, this is the code that worked for me (simple version):

def get_or_create(session, model, **kwargs):     instance = session.query(model).filter_by(**kwargs).first()     if instance:         return instance     else:         instance = model(**kwargs)         session.add(instance)         session.commit()         return instance 

With this, I'm able to get_or_create any object of my model.

Suppose my model object is :

class Country(Base):     __tablename__ = 'countries'     id = Column(Integer, primary_key=True)     name = Column(String, unique=True) 

To get or create my object I write :

myCountry = get_or_create(session, Country, name=countryName) 

Answer 2

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.