Python 'startswith' equivalent for SqlAlchemy

Question

I have a line for which I need to find all records with the corresponding prefixes:

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

The following entries will match when the path_prefix value is:

 /abc /ab /abc/123 

But not:

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

Is this possible with SqlAlchemy without switching to python?

Answer 1

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

 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, '%') 

in MySQL or

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

in other databases.

If you want to check the work of .startswith() , this also works:

 from sqlalchemy.sql.expression import bindparam session.query(Site).filter(bindparam('path', path).startswith(Site.path_prefix))