SQLAlchemy + PostgreSQL + PG regex


SA has support for regexes but those seem to be Python regexps (Regular expressions in SQLalchemy queries?)

I need to use regex on matching some rows (a row contains 1 log line, so regex is a natural match) but for performance reasons I would prefer to do it using PG backend, like in this question:

select * from table where name ~ 'foo';

How can I combine both PG-implemented regex AND SQLAlchemy object selection in one query?

The filter() method of the Query object allows for you to use raw SQL for the filter. So, you could do...

Table.query.filter("name ~ 'foo'")

Note that if you want to provide this as an argument, you can use text() and params()...

from sqlalchemy.sql import text

Table.query.filter(text('name ~ :reg')).params(reg='foo')

Because we define the "reg" bind parameter in the filter using text(), we need to make sure we define the value, which we can do using params().