PostgreSQL ILIKE query with SQLAlchemy

Question

I'd like to run a query that selects all posts, case insensitive, that have titles that match '%' + [some_phrase] + '%'. That is, select all rows that have titles that contain some phrase, case insensitive. From the research I've done, it looks like I need to use Postgres's ILIKE query for it to match case insensitive. How can I execute a query like this with SQLAlchemy?

class Post(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    title = db.Column(db.String(250))
    content = db.Column(db.String(5000))

possible duplicate of Case Insensitive Flask-SQLAlchemy Query — Mark Hildreth
– Mark Hildreth, Commented Dec 4, 2013 at 0:15

user1454592 · Accepted Answer · 2013-12-04 05:42:05Z

77

I think it should work

Post.query.filter(Post.title.ilike('%some_phrase%'))

http://docs.sqlalchemy.org/en/latest/orm/internals.html?highlight=ilike#sqlalchemy.orm.attributes.QueryableAttribute.ilike

answered Dec 4, 2013 at 5:42

user1454592

1,2552 gold badges12 silver badges13 bronze badges

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

2 Comments

benjaminz Over a year ago

Does this affect performance compared to the more restricted like() function?

Eugene Kovalev Over a year ago

It depends on an engine you use: PostgreSQL, MySQL or else. SQLAlchemy is just a wrapper for SQL.

Anatoly E · Accepted Answer · 2019-02-13 14:03:48Z

26

For python 3.6 instead of '%' + some_phrase + '%' you can write

Post.query.filter(Post.title.ilike(f'%{some_phrase}%'))

answered Feb 13, 2019 at 14:03

Anatoly E

1,14111 silver badges9 bronze badges

Comments

akpp · Accepted Answer · 2021-06-22 09:50:13Z

4

There is another workable solution:

from sqlalchemy.sql.operators import ilike_op
Post.query.filter(ilike_op(Post.title, f'%{some_phrase}%'))

This solution helped me to overcame the warning Unresolved attribute reference 'ilike' for class 'property' when I used hybrid_property

answered Jun 22, 2021 at 9:50

akpp

84813 silver badges15 bronze badges

Collectives™ on Stack Overflow

PostgreSQL ILIKE query with SQLAlchemy

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related