query filter on postgresql array column using array.contains()

Question

I have an array column (required of me, I know this is not typical convention) that I need to filter on in a query. I am trying to use contains(),

  db.session.query(table).filter((table.name.ilike
    ('%'+name+'%')),
    table.languages.contains(language)).limit(200)

But I keep getting this error:

    NotImplementedError: ARRAY.contains() not implemented for the base 
    ARRAY 
    type; please use the dialect-specific ARRAY type

I am new to Flask-Sqlalchemy so I am not sure how to use this correctly. Any help is much appreciated!

What is the type of language?

mad_
– mad_

2018-07-30 17:41:21 +00:00
Commented Jul 30, 2018 at 17:41 — mad_
– mad_, Commented Jul 30, 2018 at 17:41
@mad_ python accessing postgresql db using flask-sqlalchemy

Julia
– Julia

2018-07-30 20:13:36 +00:00
Commented Jul 30, 2018 at 20:13 — Julia
– Julia, Commented Jul 30, 2018 at 20:13
I was asking about the argument language

mad_
– mad_

2018-07-30 20:22:48 +00:00
Commented Jul 30, 2018 at 20:22 — mad_
– mad_, Commented Jul 30, 2018 at 20:22

Yaqiong · Accepted Answer · 2019-01-28 07:01:20Z

1

This Expection means you should use dialect-specific ARRAY type to instead the base ARRAY type. So in your models definition, Use sqlalchemy.dialects.postgresql.ARRAY to instead ARRAY

from sqlalchemy.dialects.postgresql import ARRAY
class table(db.Model):
    ...
    languages = db.Column(ARRAY) # notice this ARRAY import from sqlalchemy.dialects.postgresql

Now, you can use db.session.query(table).filter(table.languages.contains([language])).limit(200).

edited Jan 28, 2019 at 7:01

answered Jan 28, 2019 at 6:32

Yaqiong

213 bronze badges

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

4 Comments

Stefan Becker Over a year ago

And this answers the original question how?

Yaqiong Over a year ago

Yes, I have the same problem and use it fix out.

gehbiszumeis Over a year ago

Please put your answer always in context instead of just pasting code. See here for more details

Yaqiong Over a year ago

@gehbiszumeis Thank you. This is my first answer. I will re-edit it soon.

Collectives™ on Stack Overflow

query filter on postgresql array column using array.contains()

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related