Filter a sqlalchemy query by field that contains a substring from a list

user_13708308 0
Viewed 1235

I am working on an advanced search filter for my web app, and want to know if there's a way to implement a filter like this. What I need is to be able to specify a list of substrings, and then filter the query for values that contain any substring from the specified list.

Account.name [Cheyenne, Dan, Fran, Sharon, Karen]

filter_list = [‘an’, ‘ar’]

Post Query [Dan, Fran, Sharon, Karen]

So in the example above, Cheyenne would be removed because that name doesn't contain 'an' or 'ar'.

What I've Tried What I've tried so far are the contains and the in_ operator although I'm not sure how I can combine the two though. _in will only work if the strings are an exact match, and contains will only work with 1 string at time. Any ideas?

query = session.query(Account).filter(Account.name).in_(filter_list)

query = session.query(Account).filter(Account.name).contains(filter_list[0])

sqlalchemy python
1 Answers

I ended up figuring it out on my own. Here's how I did it, if anybody is curious.

mylist = ['ar', 'an']

filter_list = [Account.first_name.contains(x) for x in mylist]

q = session.query(Account).filter(
    or_(
        *filter_list
    )
)
user_137083080
Related