CODEWITHSUNDEEP
pythonsqlalchemy
azndude956
azndude956

Reputation: 29

OR multiple filters for SQL Alchemy query

so I am trying to build multiple filters using python for sql alchemy calls. I 

query = MyModel.query

if 'column1' in data:
    try:
        value = int(data['column1'])
    except (TypeError, ValueError):
        pass
    else:
        query = query.filter(MyModel.column1 == value)

if 'column2' in data:
    try:
        value = datetime.utcfromtimestamp(int(data['column2']))
    except (TypeError, ValueError):
        pass
    else:
        query = query.filter(or_(MyModel.column2 >= value))

# etc.

return query

I am only doing this because the properties in the data object will vary and I do not know what I will get in terms of filter in the data object. Now when I execute the query...the statement ANDs both filters regardless of the or_ I put in the second filter query. What do I need to change in order creating the query to work properly?

Upvotes: 0

Views: 1442

Answers (1)

Simeon Visser
Simeon Visser

Reputation: 122326

Gather the constraints first and then apply or_:

constraints = []
if 'column1' in data:
    try:
        value = int(data['column1'])
    except (TypeError, ValueError):
        pass
    else:
        constraints.append(MyModel.column1 == value)

if 'column2' in data:
    try:
        value = datetime.utcfromtimestamp(int(data['column2']))
    except (TypeError, ValueError):
        pass
    else:
        constraints.append(MyModel.column2 >= value)

if constraints:
    query = query.filter(or_(*constraints))

Upvotes: 5

Related Questions