Python 'startswith' equivalent for SqlAlchemy

Question

I have a string, for which I need to find all records with matching prefixs:

path = '/abc/123/456'
session.query(Site).filter(path.startswith(Site.path_prefix))

The following records would match when path_prefix equals:

/abc
/ab
/abc/123

But not:

/asd
/abc/123/456/789
/kjk

Is this possible with SqlAlchemy, without switching over to python?

Answer 1

If you wrap the path variable in a bindparam() object then you can treat it like any column, including using the .contains() and .startswith() operators:

from sqlalchemy.sql.expression import bindparam

session.query(Site).filter(bindparam('path', path).contains(Site.path_prefix))

SQLAlchemy translates .contains() to:

? LIKE CONCAT('%', Site.path_prefix, '%')

on MySQL or

? LIKE '%' || Site.path_prefix || '%'

on other databases.

If you wanted to test for a .startswith() operation instead, that works too:

from sqlalchemy.sql.expression import bindparam

session.query(Site).filter(bindparam('path', path).startswith(Site.path_prefix))