How to make a get_or_create using OR with SQLAlchemy?

Question

I have a get or create method that works well, but it ANDs the arguments, using filter_by. I want the same thing, but using or, similar to:

def get_or_create_or(cls, session, **kwargs):
    instance = session.query(cls).filter(or_(**kwargs)).first()
    if instance:
        return instance
    else:
        instance = cls(**kwargs)
        return instance

This doesn't work because or_ doesn't expect keyword arguments. How can I make a generalized version of this?

For example, the current get_or_create I have can take in a phone and an email, and will return a row that matches both. I want to be able to pass in a phone and an email and get a row that matches either.

http://stackoverflow.com/questions/2546207/does-sqlalchemy-have-an-equivalent-of-djangos-get-or-create — Eugene Soldatov, Mar 16 '15 at 18:08
Unless I'm mistaken, that's not what I want. It's ANDing parameters. I want to OR them — mergesort, Mar 16 '15 at 18:10

score 2 · Accepted Answer · edited Jan 29 '19 at 19:43

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.

Isn't this different than upsert? How else could I check to see if a user with these credentials exists, and if they don't then create one? FWIW I have unique constraints on the necessary fields. Thanks for an amazing answer btw — mergesort, Mar 16 '15 at 19:08
@kishinmanglani Not really conceptually. You have all the same race conditions, and the solution described in that article is how how solve them. No matter what you do, there is always going to be a sliver of time between when you query if the item exists and when you create it if it doesn't and commit it, which allows for two sessions to both create the same new entry. Your system needs to be able to detect this failure mode and correctly handle it. — aruisdante, Mar 16 '15 at 19:46
@kishinmanglani Also, if you have unique constraints, you can save yourself a query by simply assuming the entry doesn't exist and attempting to create it, then catching the failure if the unique constraint is violated (let the database do the work for you), and then doing your query and returning the result in the case of failure. Which makes more sense will depend on which is the more common occurrence; New entries being successfully created or existing entries being returned. But you're still going to have to handle the constraint failure either way for the above reasons. — aruisdante, Mar 16 '15 at 19:50

How to make a get_or_create using OR with SQLAlchemy?

1 Answers1