Python 'startswith' Equivalent For SqlAlchemy

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 reco

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