CODEWITHSUNDEEP
pythonif-statementmethodssqlalchemyinline
Jean Monet
Jean Monet

Reputation: 2655

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

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.

Upvotes: 3

Views: 2282

Answers (1)

Pierre V.
Pierre V.

Reputation: 1625

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)

Upvotes: 3

Related Questions