How to search for multiple values in column with Sqlalchemy postgres

Question

I have a query in PostgreSQL that searches and selects all the rows with values '0' or '3' inside an arrayed column called 'news'. This column has an array of multiple values. For example:

id   | country | news
--------------------- 
one  | xyz     | {'2','4','8'}
two  | esc     | {'0','4','2'}
three| eec     | {'9','3','5'}

So,

SELECT * FROM table WHERE news && '{"0", "3"}';

results in row two and three being selected. Perfect. But I need to do this in sqlalchemy.

Does anyone know how this can be written in SQLalchemy?

@balderman helped me with resources that I used to come up with this sqlalchemy code:

full_id_list = []
for n in ['0','3']:
    ids = db.session.query(table).filter(table.news.op('@>')([n]))
    full_id_list.append(booklist)

But is there a simpler way, without using a for Loop?

Adam Schroeder · Accepted Answer · 2019-07-06 04:35:54Z

3

Solved it, finally.

db.session.query(Table).filter(Table.news.op('&&')(['0','3']))

All I had to do is change the operation (.op) from @> to &&, because && means you can search for multiple values inside a column with an array of values.

answered Jul 6, 2019 at 4:35

Adam Schroeder

7582 gold badges9 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Devang Mistry Over a year ago

I had to use string (wrapped in { }) instead of array. db.session.query(Table).filter(Table.news.op('&&')("{item_1,item_2}")). This is in Postgres 10.11 and SQLAlchemy 1.3.11

balderman · Accepted Answer · 2019-07-02 17:10:09Z

2

See https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#sqlalchemy.dialects.postgresql.ARRAY.Comparator

query = session.query(table).filter(table.news.contains([some_int])).all()

answered Jul 2, 2019 at 17:10

balderman

24k8 gold badges39 silver badges60 bronze badges

4 Comments

Adam Schroeder Over a year ago

Thank you @balderman but I get an error: ``` sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation) malformed array literal: "%" LINE 3: WHERE (table.news LIKE '%' || ARRAY['0','3'] || '%') ^ DETAIL: Array value must start with "{" or dimension information. ``` Do you know what that means??

balderman Over a year ago

@AdamSchroeder "The postgresql.ARRAY type provides all operations defined on the core types.ARRAY type, including support for “dimensions”, indexed access, and simple matching such as types.ARRAY.Comparator.any() and types.ARRAY.Comparator.all(). postgresql.ARRAY class also provides PostgreSQL-specific methods for containment operations, including postgresql.ARRAY.Comparator.contains() postgresql.ARRAY.Comparator.contained_by(), and postgresql.ARRAY.Comparator.overlap(), e.g.: mytable.c.data.contains([1, 2])"

Adam Schroeder Over a year ago

thank you for your help, but I don't understand very well the information in the link. I am very new to PostgreSQL and therefore, I'm lost with the documentation's language. Do you know why I'm getting this error. My column is of type text[], and it has multiple values in each cell: {2,3,4} or {1,3}. So a simple .contains([0,3]) should give me the rows that have those strings. Your code should be working... Is there another way of doing this?

balderman Over a year ago

see stackoverflow.com/questions/10344468/… as well

Collectives™ on Stack Overflow

How to search for multiple values in column with Sqlalchemy postgres

2 Answers 2

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related