5

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?

chrizonline
  • 4,779
  • 17
  • 62
  • 102

1 Answers1

24

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.

alecxe
  • 462,703
  • 120
  • 1,088
  • 1,195
  • Ah nice, pretty convenient that `or_` can handle a single filter element properly – kevlarr Aug 09 '19 at 18:35
  • 1
    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](https://docs.sqlalchemy.org/en/14/core/sqlelement.html#sqlalchemy.sql.expression.or_) – Alex Jul 27 '21 at 18:12
  • Nice, but can I write the code `Table.field1 ` by using reflection? like `Table['field']` – caimaoy Oct 18 '22 at 02:19