title: Filter with Array column with Postgres and SQLAlchemy
body: <p>I have a simple table with an int[] column, and I'd like to be able to select rows where any one of their array elements matches a value I have, and I cannot figure out how to do this using SQLAlchemy without just using a raw query, which I do not want to do.</p>
  
  <p>Here is the schema for the table ("testuser"):</p>
  
  <pre><code> Column  |          Type          |
  ---------+------------------------+
   id      | integer                |
   name    | character varying(250) |
   numbers | integer[]              |
  </code></pre>
  
  <p>Here is what it looks like with sample data:</p>
  
  <pre><code> id |  name   |    numbers
  ----+---------+---------------
    1 | David   | {25,33,42,55}
    2 | Salazar | {11,33,7,19}
    3 | Belinda | {32,6,20,23}
    4 | Casey   | {19,20,27,8}
    5 | Kathie  | {25,31,10,40}
    6 | Dianne  | {25,20,40,39}
    7 | Cortez  | {1,20,18,38}
  </code></pre>
  
  <p>Here is an SQL statement that generates what I want; I want to do this all in Python without simply writing the raw query (25 is just used as an example).</p>
  
  <pre><code>scrape=# select * from testuser where 25 = ANY(numbers);
   id |  name  |    numbers
  ----+--------+---------------
    5 | Kathie | {25,31,10,40}
    6 | Dianne | {25,20,40,39}
    1 | David  | {25,33,42,55}
  (3 rows)
  </code></pre>
  
  <p>Another way I found to write it:</p>
  
  <pre><code>scrape=# select * from testuser where numbers @&gt; ARRAY[25];
   id |  name  |    numbers
  ----+--------+---------------
    5 | Kathie | {25,31,10,40}
    6 | Dianne | {25,20,40,39}
    1 | David  | {25,33,42,55}
  (3 rows)
  </code></pre>
  
  <p>Here is the Python code I used to generate the table:</p>
  
  <pre><code>from sqlalchemy import Column, Integer, String
  from sqlalchemy.dialects import postgresql
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy import create_engine
  from sqlalchemy.orm import sessionmaker
  
  Base = declarative_base()
  
  class TestUser(Base):
      __tablename__ = 'testuser'
      id = Column(Integer, primary_key=True)
      name = Column(String(250))
      numbers = Column(postgresql.ARRAY(Integer))
  
  engine = create_engine('postgresql://postgres:{pw}@localhost:5432/scrape'.format(pw=POSTGRES_PASSWORD))
  
  Base.metadata.create_all(engine)
  
  DBSession = sessionmaker(bind=engine)
  session = DBSession()
  
  testcases = [{"numbers": [25, 33, 42, 55], "name": "David"}, {"numbers": [11, 33, 7, 19 ], "name":     "Salazar"}, {"numbers": [32, 6, 20, 23 ], "name": "Belinda"}, {"numbers": [19, 20, 27, 8 ], "name": "Casey"},     {"numbers": [25, 31, 10, 40 ], "name": "Kathie"}, {"numbers": [25, 20, 40, 39 ], "name": "Dianne"},     {"numbers": [1, 20, 18, 38 ], "name": "Cortez"} ]
  
  for t in testcases:
      session.add(TestUser(name=t['name'], numbers=t['numbers']))
  session.commit()
  </code></pre>