CODEWITHSUNDEEP
pythonarrayspostgresqlormsqlalchemy
Adam Matan
Adam Matan

Reputation: 136499

SQLAlchemy, PostgreSQL: Query for rows with a specific item in an array

I am trying to filter PostgreSQL records using SQLALchemy ORM objects based on the existence of an integer variable in an array, but I can't find the right way to do it.

DB Settings

I have a PostgreSQL table with an array of integers:

my_db=> \d test_arr;
                           Table "public.test_arr"
  Column  |   Type    |                       Modifiers
----------+-----------+-------------------------------------------------------
 id       | integer   | not null default nextval('test_arr_id_seq'::regclass)
 partners | integer[] |

The table contains some values:

my_db=> SELECT * FROM test_arr;
 id | partners
----+----------
 12 | {1,2,3}
 13 | {2,3,4}
 14 | {3,4,5}
 15 | {4,5,6}
(4 rows)

Querying the table for the rows which contains the number 2 in the partners array is done in PostgreSQL using the ANY keyword:

my_db=> SELECT * FROM test_arr WHERE 2 = ANY(partners);
 id | partners
----+----------
 12 | {1,2,3}
 13 | {2,3,4}
(2 rows)

ORM with SQLAlchemy

SQLAlchemy supports PostgreSQL arrays, and the table is represented by:

class TestArr(Base):

    __tablename__ = 'test_arr'

    # override schema elements like Columns
    id = Column('id', Integer, Sequence('test_arr_id_seq'), primary_key=True)
    partners = Column(ARRAY(Integer))

    def __init__(self, partners):
        self.partners = partners

    def __str__(self):
        return '<%d for %s>' % (self.id, str(self.partners))

The problem

How do I run the equivalent of SELECT * FROM test_arr WHERE 2 = ANY(partners); using the Pythonic object?

What have I tried

I have tried using func, like the following:

print session.query(TestArr).filter(func.any(TestArr.partners, 2)).all()

But failed with Syntax Errors:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at or near "ANY"
LINE 3: WHERE ANY(test_arr.partners, 332)

Upvotes: 5

Views: 6011

Answers (1)

r-m-n
r-m-n

Reputation: 15120

you can use sqlalchemy.dialects.postgresql.Any

from sqlalchemy.dialects.postgresql import Any
session.query(TestArr).filter(Any(2, TestArr.partners)).all()

or @> array operator

session.query(TestArr).filter(TestArr.partners.op('@>')([2])).all()

Upvotes: 7

Related Questions