PostgreSQL ILIKE query with SQLAlchemy

python postgresql sqlalchemy flask flask-sqlalchemy
sundance picture sundance · Dec 4, 2013 · Viewed 22.1k times · Source

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))

Answer

user1454592 picture user1454592 · Dec 4, 2013

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

Related questions

unable to create autoincrementing primary key with flask-sqlalchemy

I want my model's primary key to be an autoincrementing integer. Here is how my model looks like class Region(db.Model): __tablename__ = 'regions' id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(100)) parent_…

Read More
Can Alembic Autogenerate column alterations?

I was able to use alembic --autogenerate for when adding / removing columns. However, when I wanted to modify for example a "url" column from 200 characters to 2000 characters, it doesn't detect the change. How can I make Alembic (using SQLAlchemy), detect …

Read More
How to write DataFrame to postgres table?

There is DataFrame.to_sql method, but it works only for mysql, sqlite and oracle databases. I cant pass to this method postgres connection or sqlalchemy engine.

Read More