SQLAlchemy equivalent to SQL "LIKE" statement

Question

A tags column has values like "apple banana orange" and "strawberry banana lemon". I want to find the SQLAlchemy equivalent statement to

SELECT * FROM table WHERE tags LIKE "%banana%"; 

What should I pass to Class.query.filter() to do this?

Answer 1

Each column has like() method, which can be used in query.filter(). Given a search string, add a % character on either side to search as a substring in both directions.

tag = request.form["tag"] search = "%{}%".format(tag) posts = Post.query.filter(Post.tags.like(search)).all() 

Answer 2

Adding to the above answer, whoever looks for a solution, you can also try 'match' operator instead of 'like'. Do not want to be biased but it perfectly worked for me in Postgresql.

Note.query.filter(Note.message.match("%somestr%")).all() 

It inherits database functions such as CONTAINS and MATCH. However, it is not available in SQLite.

For more info go Common Filter Operators