Menu
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)) 
668
Install a Postgres server locally and create a database. Use Python with SQLAlchemy to connect to the database and create tables. Use Python with SQLAlchemy to insert data and query the database.
The keyword ILIKE can be used instead of LIKE to make the match case insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension. The operator ~~ is equivalent to LIKE , and ~~* corresponds to ILIKE .
LIKE and ILIKE allow pattern matching within character-based column data. Their syntax is identical, but LIKE is case-sensitive, while ILIKE is case-insensitive.
Import necessary functions from the SQLAlchemy package. Establish connection with the PostgreSQL database using create_engine() function as shown below, create a table called books with columns book_id and book_price. Insert record into the tables using insert() and values() function as shown.
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
169
For python 3.6 instead of '%' + some_phrase + '%' you can write
Post.query.filter(Post.title.ilike(f'%{some_phrase}%')) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
