SQLAlchemy equivalent to SQL "LIKE" statement

In case you want the case insensitive like clause implementation:

session.query(TableName).filter(TableName.colName.ilike(f'%{search_text}%')).all()

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

Try this code:

output = dbsession.query(<model_class>).filter(
    <model_class>.email.ilike("%" + <email> + "%")
)

Apart from the obvious

select(User).where(User.name.like(f'%{some_term}%'))

SQLAlchemy provides some other methods of generating a LIKE clause.

• startswith: search term followed by % wildcard

select(User.name).where(User.name.startswith('A'))

SELECT users.name 
FROM users 
WHERE (users.name LIKE :name_1 || '%')

• endswith: search term preceded by % wildcard

select(User.name).where(User.name.endswith('e'))

SELECT users.name 
FROM users 
WHERE (users.name LIKE '%' || :name_1)

• contains: search term inside % wildcards

select(User.name).where(User.name.contains('i'))

SELECT users.name 
FROM users 
WHERE (users.name LIKE '%' || :name_1 || '%')

Like the like and ilike methods, each of these has a case-insensitive counterpart: istartswith, iendswith and icontains.

All these methods are available on core table columns too, and also work with the legacy session.query syntax.

In SQLAlchemy 1.4/2.0:

q = session.query(User).filter(User.name.like('e%'))

If you use native sql, you can refer to my code, otherwise just ignore my answer.

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

from sqlalchemy import text

bar_tags = "banana"

# '%' attention to spaces
query_sql = """SELECT * FROM table WHERE tags LIKE '%' :bar_tags '%'"""

# db is sqlalchemy session object
tags_res_list = db.execute(text(query_sql), {"bar_tags": bar_tags}).fetchall()

While the accepted answer works fine, "The ORM Query object is a legacy construct as of SQLAlchemy 2.0" (ref: Legacy Query API - SQLAlchemy 2.0 Documentation).

The corresponding SQLAlchemy v2.0 equivalent to the SQL LIKE statement using the select construct is as shown in the python 3.10 code snippet below:

from typing import List

from sqlalchemy import select
from sqlalchemy.orm import Session

...

def get_multi_like_tag_substring_bidirectional(
    db: Session,
    *,
    tags_search_substring: str,
    skip: int = 0,
    limit: int = 10,
) -> List[Post]:
    return db.scalars(
        select(Post)
        .where(Post.tags.like(f"%{tags_search_substring}%"))
        .offset(skip)
        .limit(limit)
    ).all()

...

banana_tagged_posts = get_multi_like_tag_substring_bidirectional(
    db=db_session,
    tags_search_substring = "banana"
)
    

What I've been using all these years:

model = MyModel
col = "name_of_my_column"
value = "your-value"

query = model.query.filter(getattr(model, col).like("%{}%".format(value))).all()

Using PostgreSQL like (see accepted answer above) somehow didn't work for me although cases matched, but ilike (case insensisitive like) does.

As you can see below, I am adding leading and trailing space to the tags column so that i can match on the desired tag surrounded by space to ensure that only the full words are matched preventing 'pineapple' to match 'apple' or 'sqlachemy' to match 'sql':

tag = 'banana'
search = f"% {tag} %"  # !spaces around the tag
query = select(MyTable).filter((' ' + MyTable.tags + ' ').like(search))