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?

Do you mean `Site.path_prefix in path`? Also, in pure Python semantic this will match strings like `23/4` and the empty string too, is that what you want? — kennytm, Nov 01 '12 at 11:43

Martijn Pieters · Accepted Answer · 2012-11-01T21:31:03.590

15

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

edited Nov 01 '12 at 21:31

answered Nov 01 '12 at 11:57

Martijn Pieters

I was thinking `startswith(path, Site.path_prefix)` (as path_prefix, path) was exactly the trap we fell in to wasn't it? – Jon Clements Nov 01 '12 at 12:14
@JonClements: Sure, but that's not the same as `in`. I've added the option already; just remove the first `%` wildcard. SQLAlchemy does offer a `startswith()` but only for columns, since this is the inverse we need to do the `LIKE` construction ourselves. – Martijn Pieters Nov 01 '12 at 12:14
@MartijnPieters, Is there an alternative to concat that works with sqlite? It seems like concat isn't supported there: OperationalError: (OperationalError) no such function: concat u'SELECT... – TheOne Nov 01 '12 at 13:20
@Ramin: Ah, my mistake, the `functions.concat()` was overkill. It's sufficient to use `+` string concatenation, it'll be translated correctly for different databases. – Martijn Pieters Nov 01 '12 at 14:04
@Ramin: Actually, since the central premise is to use a `bindparam()`, the SQLAlchemy `.startswith()` and `.contains()` operators also work. – Martijn Pieters Nov 01 '12 at 21:31

1 Answers1