SQLAlchemy: Is it possible to combine "like" and "in" within the same query

Question

I have the following SQLAlchemy query working

incomplete_list = ['a','b','c']

complete_units = []
for elem in incomplete_list:
    query = Table.query.filter(Table.name.like(elem))
    for row in query:
        complete_units.append(row.unit_number)

I would like to avoid using for loop to query the database multiple times. Would it be possible to combine "in" and "like" operators so that I only query the database once? Or is there a better way of doing this query? Thanks

Answer 1

Adapted from this answer:

incomplete_list = ['a','b','c']
complete_units = []

clauses = [Table.name.like(elem) for elem in incomplete_list]
query = Table.query.filter(or_(*clauses))
complete_units = query.all()

Strictly speaking the result of this execution path might be different from you sample code because if the Table contains rows which would satisfy multiple criteria from the incomplete_list, your sample code will return the same row multiple times, whereas this code will do it only once (which I believe is desired).