SQLAlchemy ORM / Python: one-liner to include query filter only if searched value is not None (inline 'if')

Question

Can I implement in Python an inline if at the method level?

Let's take this (simplified) situation:

# DB-level 'or'
db.session.query(TableClass).filter(or_(TableClass.column_1 == value_1,
                                        TableClass.column_2 == value_2)).first()

# basic Python-level 'if'
if value_1:
    db.session.query...value_1
if value_2:
    db.session.query...value_2

Instead of querying DB or adding additional if statements as per above, is it possible to achieve the same inline with something like:

# desired inline Python 'if' at method level
db.session.query(TableClass).filter({
           if value_1 TableClass.column_1 == value_1 \
           else if value_2 TableClass.column_2 == value_2 else None
    }).first()

# ^-- or something similar at method level

Alternatively, have a part of the SQLAlchemy filter be active only when the underlying value exists.

The main motivation here is DRY and avoiding DB search of None values.

Tried inline combination of starred expression with if statement as suggested:

session.query(Foo).filter((*[Foo.attr1==attr1, Foo.attr2==attr2] if attr2 else *[Foo.attr1==attr1]))

but I get SyntaxError: invalid syntax at if.

UPDATE

For now I went with this style of defining conditions as separate variables that can be appended together:

query = session.query(Table)

conditions = []
if abc:
    conditions.append(Table.field1 == foo)
if def:
    conditions.append(Table.field2 == bar)

query = query.filter(or_(*conditions))

Though I appreciate Pierre V's answer below fixing syntax for the starred expression and for the or_(TableClass.column_1 == value_1, true() if value_1 is None else false()) suggestion.

score 3 · Accepted Answer · answered Dec 14 '19 at 20:05

You could get rid of the syntax error by moving the parentheses:

session.query(Foo).filter(*([Foo.attr1==attr1, Foo.attr2==attr2] if attr2 else [Foo.attr1==attr1]))

Otherwise, you might achieve the desired result with true() and false() Sqlalchemy expressions.

Something like:

db.session.query(TableClass).filter(
    or_(TableClass.column_1 == value_1, true() if value_1 is None else false()),
    or_(TableClass.column_2 == value_2, true() if value_2 is None else false())
).first()

But imho it feels a lot clunkier than

query = db.session.query(TableClass)
if value_1:
    query = query.filter(TableClass.column_1 == value_1)
if value_2:
    query = query.filter(TableClass.column_2 == value_2)

Thank you for the suggestions. Is `or_(TableClass.column_2 == value_2, true() if value_2 is None else false())` inherent to SQLAlchemy's `or_` function or is it a Python notation? — Jean Monet, Dec 15 '19 at 13:30
I'd say it's a SQLAlchemy thing, as `or_()`, `true()` and `false()` are SQLAlchemy functions — Pierre V., Dec 15 '19 at 16:42

SQLAlchemy ORM / Python: one-liner to include query filter only if searched value is not None (inline 'if')

1 Answers1