SQLAlchemy - adding OR condition with different filter

Question

For the SQL statement, I want to do an OR filter only if the input variable is not None.

E.g

# input variable
var_1 = "apple"
var_2 = "pear"

query = session.query(Table)
if var_1:
    query = query.filter(or_(Table.field1 == var_1))
if var_2:
    query = query.filter(or_(Table.field2 == var_2))

But this is not possible. The 2nd query became an AND statement instead.

I don't want to do this because I don't want to compare an input variable if it's null or empty string.

query = query.filter(or_(Table.field1 == var_1, Table.field2 == var_2))

How do I solve this?

alecxe · Accepted Answer · 2016-01-17 12:38:44Z

26

You can dynamically construct the "OR" part:

query = session.query(Table)

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

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

Also note that the def is a reserved word in Python, consider renaming this variable.

answered Jan 17, 2016 at 12:38

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

kevlarr Over a year ago

Ah nice, pretty convenient that or_ can handle a single filter element properly

Alex Over a year ago

Use or_(False, *conditions) if you might have zero conditions. Since sqlalchemy 1.4 or_ must have at least one parameter as it says here

caimaoy Over a year ago

Nice, but can I write the code Table.field1 by using reflection? like Table['field']

Collectives™ on Stack Overflow

SQLAlchemy - adding OR condition with different filter

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related