SQLAlchemy + PostgreSQL + PG regex

Question

SA has regex support, but it looks like Python regex ( Regular expressions in SQLalchemy queries? )

I need to use a regular expression to match some lines (a line contains 1 line of the log, so the regular expression is a natural match), but for performance reasons, I would prefer to do this using the PG backend, for example, in this question :

select * from table where name ~ 'foo'; 

How can I combine both PG-regex AND SQLAlchemy objects in one query?

Answer 1

The filter() method of the Query object allows you to use the source 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') 

Since we define the "reg" binding parameter in the filter using text() , we need to make sure that we determine the value that we can do using params() .

Answer 2

Please note that you can also use the infix operator support:

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