How to use ilike sqlalchemy on postgresql array field?

Question

I'm trying to match some names with the names I already have in my Postgresql database, using the following code:

last_like = '{last_name}%'.format(last_name)
matches = Session.query(MyTable).filter(or_(
    MyTable.name.ilike(last_like),
    MyTable.other_names.any(last_like, operator=ilike_op),
)).all()

Essentially it tries to match the name column or any of the other names stored as an array in the other_names column.

But I get:

KeyError: <function ilike_op at 0x7fb5269e2500>

What am I doing wrong?

Danil · Accepted Answer · 2016-03-29 07:49:34Z

1

For use postgresql array field you need to use unnest() function. But you can't use result of unnest() in where clause.

Instead, you can use array_to_string function. Searching on string of other_names will give the same effect

from sqlalchemy import func as F
last_like = "%qq%"
matches = session.query(MyTable).filter(or_(
    MyTable.name.ilike(last_like),
    F.array_to_string(MyTable.other_names, ',').ilike(last_like),
)).all()

edited Mar 29, 2016 at 7:49

answered Mar 28, 2016 at 12:22

Danil

5,2611 gold badge42 silver badges55 bronze badges

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

Collectives™ on Stack Overflow

How to use ilike sqlalchemy on postgresql array field?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related