title: SQLAlchemy, PostgreSQL: Query for rows with a specific item in an array
body: <p>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.</p>
  
  <h1>DB Settings</h1>
  
  <p>I have a PostgreSQL table with an <a href="http://www.postgresql.org/docs/9.3/static/arrays.html" rel="nofollow">array of integers</a>:</p>
  
  <pre><code>my_db=&gt; \d test_arr;
                             Table "public.test_arr"
    Column  |   Type    |                       Modifiers
  ----------+-----------+-------------------------------------------------------
   id       | integer   | not null default nextval('test_arr_id_seq'::regclass)
   partners | integer[] |
  </code></pre>
  
  <p>The table contains some values:</p>
  
  <pre><code>my_db=&gt; SELECT * FROM test_arr;
   id | partners
  ----+----------
   12 | {1,2,3}
   13 | {2,3,4}
   14 | {3,4,5}
   15 | {4,5,6}
  (4 rows)
  </code></pre>
  
  <p>Querying the table for the rows which contains the number <code>2</code> in the <code>partners</code> array is done in PostgreSQL using the <code>ANY</code> keyword:</p>
  
  <pre><code>my_db=&gt; SELECT * FROM test_arr WHERE 2 = ANY(partners);
   id | partners
  ----+----------
   12 | {1,2,3}
   13 | {2,3,4}
  (2 rows)
  </code></pre>
  
  <h1>ORM with SQLAlchemy</h1>
  
  <p>SQLAlchemy <a href="http://docs.sqlalchemy.org/en/rel_0_9/dialects/postgresql.html#postgresql-data-types" rel="nofollow">supports PostgreSQL arrays</a>, and the table is represented by:</p>
  
  <pre><code>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 '&lt;%d for %s&gt;' % (self.id, str(self.partners))
  </code></pre>
  
  <h1>The problem</h1>
  
  <p>How do I run the equivalent of <code>SELECT * FROM test_arr WHERE 2 = ANY(partners);</code> using the Pythonic object?</p>
  
  <h1>What have I tried</h1>
  
  <p>I have tried using <code>func</code>, like the following:</p>
  
  <pre><code>print session.query(TestArr).filter(func.any(TestArr.partners, 2)).all()
  </code></pre>
  
  <p>But failed with Syntax Errors:</p>
  
  <pre><code>sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at or near "ANY"
  LINE 3: WHERE ANY(test_arr.partners, 332)
  </code></pre>