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?

Harlan

I found this link really helpful thought to share across. tutorialspoint.com/sqlalchemy/… – Groan

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

Wreak

Perfect! Do you know if there's a better way to distinguish between apple and pineapple than adding a leading space? – Harlan

Best way would be to just normalize your database and add 2 separate tables for tags and tag-to-task relations, and then use JOINs instead of LIKE. Otherwise, yes, seems like you will have to have some kind of separator around each tag in the string. Leading space is not enough, since there is also pen and pencil, with %pen%. If you do something like "|apple|pineapple|pen|pencil|" and match "%|pen|%" it should not collide. – Wreak

With normalizing, I'm not quite sure how I'll have more than one tag associated with a given task, or vice versa using the tag map. The "Toxi" solution appears to group the collection of tags as a single item, rather than storing each individually? And the method used in this (elixir.ematia.de/trac/wiki/Recipes/TagCloud) recipe appears to only allow one tag per item. What resources would be best for elucidating this topic? I've read this (dev.mysql.com/tech-resources/articles/…) too, but can't picture how to manage multiple tags. – Harlan

As I said, you need two tables. Basically, its just typical Many-to-Many relation, so you can follow SQLAlchemy's guide on it: sqlalchemy.org/docs/… You will have tags table, where you store tag name and other tag info, and you will have task_tags table, which will have one record for each tag added to the task. So task with 2 tags will just have 2 records in task_tags table. – Wreak

Is this safe from SQL-injections? I want to specify this as a user input – Demetra

Does it work with a blob data type? My table column is encrypted but it won't work. However, if I just perform Post.query.filter(Post.title == 'My Title') it works. – Archival

@GaryOldfaber - I know this is some time later.. But 1) putting % at start and end is like doing a contains.. But 2) putting a % solely at the end is like doing a begins with, or 3) a % at the start is like doing an ends with .. So adapt the of use of % symbol to accommodate your requirements. I know this is some years later, but I hope this helps for any future readers. – Ixia

Answer 2

In case you want the case insensitive like clause implementation:

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

Antisana

Answer 3

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

Stagestruck

What is the difference between these two operators? – Klaus

@Klaus depends on your DB backend see SQL-Alchemy docs: docs.sqlalchemy.org/en/14/core/… In Postgres you get to_tsquery which lets you add text operators for things like OR and AND postgresql.org/docs/current/… – Acuff

FWIW: I found that match did not fully support partial string matches (eg abc% matching abcd) where like does. – Acuff

Answer 4

Try this code:

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

Coff

Answer 5

In SQLAlchemy 1.4/2.0:

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

Nightspot

Answer 6

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

Urtication

Thank you very much. Could you explain the LIKE '%' :bar_tags '%'syntax ? That's wired. – Babbler

This doesn't work, at least in SQLA 1.4 with sqlite or postgresql+psycopg2; the quoted % characters cause syntax errors. – Onwards

Same for sqla 1.2, doesn't work. – Bacchic

I think some punctuation characters may have been eaten by SO. Use two vertical bars as string concatenation between the '%' and :bar_tags. – Torrell

Answer 7

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

Cuccuckold

Answer 8

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.

Onwards

Answer 9

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

Converted

ILIKE is the case-insensitive version of LIKE, so your inputs differed only in case. – Nickelic

Answer 10

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

Syngamy

Answer 11

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

Lovmilla

Answer 12

Doing it like this worked for me (Oracle) as native sql

"SELECT * FROM table WHERE tags LIKE '%' || :bar_tags || '%' "

Codification