Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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.

Here is the schema for the table ("testuser"):

 Column  |          Type          |
---------+------------------------+
 id      | integer                |
 name    | character varying(250) |
 numbers | integer[]              |

Here is what it looks like with sample data:

 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}

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).

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)

Another way I found to write it:

scrape=# select * from testuser where numbers @> 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)

Here is the Python code I used to generate the table:

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()
share|improve this question

1 Answer 1

up vote 5 down vote accepted

So you want to use the Postgres Array Comparator.

query = session.query(TestUser).filter(TestUser.numbers.contains([some_int])).all()

or

query = session.query(TestUser).filter(TestUser.numbers.any(25)).all()
share|improve this answer
1  
Gave an error, but you pointed me in the right direction. Needs to either be [some_int] (contains requires comparison against an array) or alternatively, any(some_int) -- session.query(TestUser).filter(TestUser.numbers.any(25)).all() -- Thanks! –  profesor_tortuga May 10 '14 at 2:42
    
Cool, I updated my answer accordingly. –  ACV May 10 '14 at 3:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.