How to search for multiple values in column with Sqlalchemy postgres

user_8005777 0
Viewed 2582

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?

flask-sqlalchemy sqlalchemy postgresql sql python
2 Answers
user_4150160

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.

user_80057770
Related