46

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

2 Answers 2

85

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()
Sign up to request clarification or add additional context in comments.

3 Comments

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!
There is a problem when you use ARRAY(db.String) instead db.Integer, you need make a casting TestUser.numbers.contains(cast([some_int], ARRAY(String)))
Does this solution works if column is nullable and there can be a null instead of array?
2

Damn SQLAlchemy, took me some time to figure out that the correct way will be:

TelegramUser.query.filter(TelegramUser.selected_exchanges.contains(f"{{{platform_name}}}")).all()

Where the platform_name is a str and the selected_exchanges column is defined as follows:

from sqlalchemy.dialects import postgresql as pg

selected_exchanges = db.Column(pg.ARRAY(db.String, dimensions=1), nullable=True)

1 Comment

The correct way is in the accepted answer. Forming SQL manually is error prone and unnecessary. contains([platform_name]) will work and is safer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.