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)

Answer 2

Approaching an identical problem, I found the in_ operator (marked as answer) not to work: I got a "malformed ARRAY literal" error (note: the the example given filters on a column of type String, not of type ARRAY) @van originated the approach that worked for me. However, the db object in my case does not have an or_ attribute. To get it to work I simply imported or_ directly from SQLAlchemy:

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

Upvotes belong to @van (I'd have commented on his, but don't have the reputation - sorry van).

Answer 3

Just use the in_ as documented here. In their example

query.filter(User.name.in_(['ed', 'wendy', 'jack']))

the list ['ed, 'wendy', 'jack'] are your categories and instead of User.name you use your table and the tags column.

Hope it helps.