SQLAlchemy query where a column contains a substring

Question

I'm constructing a query using SQLAlchemy and SQLite3 in which I'd like to select rows in which a String column contains a particular substring. What is the best way to accomplish this?

score 124 · Answer 1 · edited Aug 07 '23 at 16:13

124

You can filter using contains operator:

Model.query.filter(Model.columnName.contains('sub_string'))

To negate in, use not_ operator with it:

Model.query.filter(not_(Model.columnName.contains('sub_string')))

For case-insensitive matching, SQLAlchemy 2.0+ includes the icontains() operator (changelog entry).

edited Aug 07 '23 at 16:13

gertvdijk

24,056
6
41
67

answered Oct 29 '13 at 09:48

kartheek

6,434
3
42
41

Thanks for this answer. You've solved my problem. But can you give details about this function. I want to know how it works. – Wally Jan 05 '15 at 14:31
@Wally [sqlalchemy.orm.query.Query.filter](http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.filter) – danodonovan Jun 05 '15 at 13:01
3

@Wally have just revisited this comment which wasn't very helpful - sorry. This is better [sqlalchemy.orm.attributes.QueryableAttribute.contains](http://docs.sqlalchemy.org/en/latest/orm/internals.html?highlight=contains#sqlalchemy.orm.attributes.QueryableAttribute.contains) – danodonovan Mar 08 '16 at 17:02
2

Further comments on Bluehorn's answer, but I've come across situations where like(%...%) works but contains() simply doesn't. Don't know why just yet. – shermy May 23 '16 at 08:03

score 54 · Accepted Answer · edited Oct 24 '16 at 19:16

Filter by db.table.column.like('%needle%'). There is also ilike for a case insensitive search.

For a fancier interface you can allow for the known "dir" wildcards.

if '*' in needle or '_' in needle: 
    looking_for = needle.replace('_', '__')\
                        .replace('*', '%')\
                        .replace('?', '_')
else:
    looking_for = '%{0}%'.format(needle)

result = db.table.filter(db.table.column.ilike(looking_for))

Notes:

The db.table.filter and db.table.column is for SQLSoup (SQLSoup is useful if the database was made by another application)
for SQLAlchemy Core it is select(column_list).where(table.c.column.ilike(expr)). This interface is the way to go when you want all the power from raw SQL without having to compose statements by hand using string interpolation (use it along SQLSoup for introspection, so you don't need to declare tables)
for SQLAlchemy Declarative (the one used in Flask) it is Model.query.filter(Model.field.ilike(expr))

@Wally Flask-SQLAlchemy is a convenience package to connect SQLAlchemy to Flask. Querying doesn't change compared to SQLAlchemy Declarative. — Laogeodritt, Sep 22 '16 at 16:10

score 19 · Answer 3 · edited Oct 29 '13 at 09:52

19

While table.c.column.like("%...%") should work, there is a more direct way to say what you want:

table.c.column.contains("needle")

This will usually generate the same SQL query but it is better to read for the uninitiated. Note that contains does not seem to escape "_" and "%".

edited Oct 29 '13 at 09:52

Amith

6,818
6
34
45

answered Aug 08 '11 at 09:56

Bluehorn

2,956
2
22
29

2

like(%...%) isn't as direct, no - but I have a group of tables (models) that turn field.contains(k) into field LIKE '%%' || k || '%%' every time - not rendering the correct results. – shermy May 23 '16 at 07:59
TIL: You can add `autoescape=True` to do [escaping with the contains function](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.ColumnElement.contains) – thorsten Mar 10 '23 at 09:46

Sneha Ravichandran · Answer 4 · 2018-05-03T11:34:06.970

@app.route('/<var>', methods=['GET'])
def getdb(var):
    look_for = '%{0}%'.format(var)
    log1 = table.query.filter(table.col.like(look_for))

I've used SQLAlchemy and Flask (app.route on top is a decorator). I used the get API to take in the variable that the user wishes to search for and I'm converting that variable to store it in another variable called look_for(since var cannot be used directly in the query) by using the format() and log1 stores the queried tuples.

score 0 · Answer 5 · answered Sep 11 '22 at 20:08

0

Here is a way to di in the newest versions of sqlalchemy

query = select(table_name).where(table_name.column.contains("value_youre_searching_for"))
results = await db.execute(query)
data = results.scalars().all()

answered Sep 11 '22 at 20:08

DINA TAKLIT

7,074
10
69
74

SQLAlchemy query where a column contains a substring

5 Answers5

Linked