Dynamics OR filtering with SQLAlchemy

Question

I have a list of of SQLAlchemy Model attributes. For example:

my_list = ['firstName', 'lastName']

I also have a person SQLAlchemy Object with firstName and lastName attributes.

I want to search query my database for people with a query as follows: session.filter( Person.lastName.like(query+'%') | Person.firstName.like(query+'%')).all()

The tricky part is that I want to generate the above filter dynamically from the my_list list. For example if an emailAddress is added to the list I want the query to also search via the objects email property.

Antti Haapala · Accepted Answer · 2018-04-06 21:24:52Z

1

With SQLAlchemy this is pretty easy if you know about reduce; use getattr to get the dynamically named column from the Person class.

from functools import reduce  # python 3
from operator import or_

columns = [ 'firstName', 'lastName' ]

# make a list of `Person.c.like(query+'%')`
likes = [ getattr(Person, c).like(query+'%') for c in column ]

# join them with | using reduce; does the same as likes[0]|likes[1]|....
final_filter = reduce(or_, likes)

session.filter(final_filter).all()

Though or_ also accepts any number of clauses to or together, so you can use argument unpacking too:

 final_filter = or_(*likes)

edited Apr 6, 2018 at 21:24

answered Mar 8, 2015 at 8:39

Antti Haapala

135k23 gold badges296 silver badges348 bronze badges

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

1 Comment

Jerad Over a year ago

Just to note for anyone hunting, you can use this approach for creating a list of conditions and passing them to the filter function. Something like: filters = [Reservation.start_date > req.start, Reservation.end_date <= req.end]. Then, conditions = reduce(and_, filters). Finally, db.session.query(Reservation).filter(conditions).all(). The advantage to this is that you can conditionally add filters in a cleaner fashion.

Collectives™ on Stack Overflow

Dynamics OR filtering with SQLAlchemy

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related