How do I compare Python list with Postgres Array in SQLAlchemy?

Question

I have a column in my PSQL table type array of type integers, let's call the column tags.I have a Python list that has type integers, lets' call it categories.

How do I run a SQLAlchemy query so that I return any row in my table if the tags column has one or more matching integers to the categories array?

Answer 1

Assuming that the list of categories is not very large, this is one way how to go about it using an OR of any(...) for each category:

q = db.session.query(MyTable)
clauses = [MyTable.tags.any(cat_id) for cat_id in categories]
q = q.filter(db.or_(*clauses))
for r in q.all():
    print(r)