Does SQLalchemy Have an Equivalent of Django's Get_Or_Create

Does SQLAlchemy have an equivalent of Django's get_or_create?

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.

SQLAlchemy equivalent to Django's x.objects.extra()?

I think you're looking for Query.from_statement: http://www.sqlalchemy.org/docs/orm/query.html#sqlalchemy.orm.query.Query.from_statement

How to make a get_or_create using OR with SQLAlchemy?

Your problem isn't that or_ doesn't expect keyword arguments, It's that keyword arguments aren't a sensical thing to pass it in the first place. filter(), and_() and or_() all expect one or more BinaryExpressions that define the composite expression (in fact filter() simply wraps its own *args in a and_(*args) to allow for the implicit default and'ing). Your method signature should be:

def get_or_create_or(cls, session, *args):
    instance = session.query(cls).filter(or_(*args)).first()
    # rest of stuff

And it should be called like:

>>> get_or_create_or(MyClass, session, MyClass.attribute == "foo", MyClass.attribute_other > 2)

Your confusion may come from having used filter_by, which implicitly only allows for equality comparisons and thus using keyword arguments makes sense. Sadly, there is no OR'ing version of filter_by, for whatever reason. You could fake it via the following:

def or_filter_by_conditions(cls, **kwargs):
    return or_(getattr(cls, name) == value for name, value in kwargs.iteritems()) # python2
    return or_(getattr(cls, name) == value for name, value in kwargs.items()) # python3

Which turns your original method into:

def get_or_create_or(cls, session, **kwargs):
    instance = session.query(cls).filter(or_filter_by_conditions(**kwargs)).first()
    # rest

Though obviously you loose much expressive power as now you can only do equality comparisons for your filtering conditions. Of course if that's what you were doing anyway (which for an upsert might be reasonable), then that's not a big deal.

Having said that, in general, I recommend you have a read through why upsert is so complicated, because there are many potential huge gotcha's in the way you're trying to do this beyond the simple need to get your query conditions correct. This will only do what you expect if there is only ever a single user, working in a single session, ever connected to your database at a time.

SQLAlquemy: INSERT only if specific item(s) dont exist

You can make the fact column unique across all rows by using a unique constraint, ie. unique=True. Then just pass the datetime columns separately on create only:

class Facts(Base):
    __tablename__ = "facts"

    id = sqlColumn(Integer, primary_key=True)
    # Makes sure there are no duplicate facts on the database level
    fact = sqlColumn(String(500), unique=True)
    created_at = sqlColumn(DateTime)
    updated_at = sqlColumn(DateTime)

def get_or_create(session, model, created_at, **unique_kwargs):
    instance = session.query(model).filter_by(**unique_kwargs).one_or_none()

    if instance:
        return instance
    else:
        instance = model(created_at=created_at, updated_at=created_at, **unique_kwargs)
    session.add(instance)
    return instance

You could use this like:

result = get_or_create(session, Facts, datetime.now(), fact='A new fact')

The drawback of this approach is that one thread using a different session could create a fact at the same time another thread was creating the same thread and the code would fail on commit. The database integrity would be preserved with the constraint. You could remedy this by catching the intregity exception and trying get_or_create again. If you are using postgresql you should do an upsert, explained here: Upsert Documentation

Here is the SQLAlchemy documentation for creating unique constraints:
Unique Constraints

get_or_create object created or found?

You can take a look get_or_create(). An example from official documentation;

obj, created = Person.objects.get_or_create(
    first_name='John',
    last_name='Lennon',
    defaults={'birthday': date(1940, 10, 9)},
)

If an object is found, get_or_create() returns a tuple of that object and False.

Then check created variable;

In [15]: created
Out[15]: False 
# so, `get_or_create()` didn't create an object.

Is there something equivalent to django's managers in SQLAlchemy?

For common queries I add a class method to a mapped (ORM) class.
For example:

class User(object):

    @classmethod
    def get_by_username(cls, dbsession, username):
        return dbsession.query(cls).filter(cls.username==username).one()

The mapped class is essentially the manager.

---

Related Topics