Dave Dave - 1 month ago 14x
Python Question

SQLAlchemy query where a column contains a substring

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?


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('?', '_')
    looking_for = '%{0}%'.format(needle)

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


  • The db.table.filter and db.table.collumn 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))