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](http://stackoverflow.com/questions/16573095/case-insensitive-flask-sqlalchemy-query) — Mark Hildreth, Dec 04 '13 at 00:15

score 71 · Accepted Answer · answered Dec 04 '13 at 05:42

71

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 04 '13 at 05:42

user1454592

1,135
1
11
11

Does this affect performance compared to the more restricted `like()` function? – benjaminz Apr 08 '16 at 11:13
It depends on an engine you use: PostgreSQL, MySQL or else. SQLAlchemy is just a wrapper for SQL. – Eugene Kovalev Jan 06 '20 at 13:14

score 25 · Answer 2 · answered Feb 13 '19 at 14:03

25

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

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

answered Feb 13 '19 at 14:03

Anatoly E

1,071
11
9

score 1 · Answer 3 · answered Jun 22 '21 at 09:50

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

PostgreSQL ILIKE query with SQLAlchemy

3 Answers3

Linked

Related