SQLAlchemy + PostgreSQL + PG regex

Question

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?

Answer 1

Note that you can also use the infix operator support:

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

Answer 2

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