Regular expressions in SQLalchemy queries?

Question

Is it possible to use a regex in a way similar to session.query(MyObject).filter_by(REGEX)?

If not, how can I use sqlAlchemy to retrieve records that have a varchar PK beginning with a certain value (e.g. all those whose city field begins with "SA")? Thanks.

Answer 1

I think I got it:

session.query(Object).filter(Object.column.op('regexp')(REGEX))

Answer 2

For the record, you can do essentially the same syntax as Paulo Scardine's answer in SQLAlchemy too;

session.query(Object).filter(Object.column.like('something%'))

Answer 3

as of SqlAlchemy 1.4, there's a built-in regular expression operator, regexp_match.

Answer 4

[DISCLAIMER: no regex]

I'm answering to the question "how can I use sqlAlchemy to retrieve records that have a varchar PK beginning with a certain value" because for this simple use case a LIKE probably is both less expensive and more portable (asking for regular expressions seems like a manifestation of the XY Problem).

In SQLAlquemy (borrowing from Alex):

session.query(Object).filter(Object.column.like('something%'))

In SqlSoup I use:

db.table.filter(db.table.column.like('something%'))