Any one knows how could I use the equivalent of REGEXP_LIKE in SQLAlchemy? For example I'd like to be able to do something like:
sa.Session.query(sa.Table).filter(sa.Table.field.like(regex-to match))
Thanks for your help!
Add a comment
|
3 Answers
It should (I have no access to Oracle) work like this:
sa.Session.query(sa.Table) \
.filter(sa.func.REGEXP_LIKE(sa.Table.c.column, '[[:digit:]]'))
5 Comments
Luigi Tiburzi
func has no attribute named REGEXP_LIKE. Also what is the meaning of .c.column? Is this meant to be the field? I tried .field and .field.column but neither is working
plaes
What database are you using?
Luigi Tiburzi
PostgreSql... but wasn't sqlAlchemy independent from the db?
plaes
Oh..
REGEXP_LIKE is Oracle-specific. Although SQLAlchemy tries really hard to be database agnostic, it doesn't unfortunately apply to special functionality.plaes
Yes, it seems that most of the regular expression matching supported by PostgreSQL is not supported yet by SQLAlchemy
In cases when you need to do database specific function which is not supported by SQLAlchemy you can use literal filter. So you can still use SQLAlchemy to build query for you - i.e. take care about joins etc.
Here is example how to put together literal filter with PostgreSQL Regex Matching operator ~
session.query(sa.Table).filter("%s ~':regex_pattern'" % sa.Table.c.column.name).params(regex_pattern='stack')
or you can manually specify table and column as a part of literal string to avoid ambigious column names case
session.query(sa.Table).filter("table.column ~':regex_pattern'" ).params(regex_pattern='[123]')
4 Comments
Luigi Tiburzi
Excuse me but your answer gives me an error at regex_pattern. Are the parenthesis put in correct way? Is table.column correct? Shouldn't it be sa.Table.column? In any case a receive an error even in this way but I'm unable to tell what the error is, it only says (ProgrammingError) syntax error at or near "1"
vvladymyrov
Sorry for typo. There should be no parenthesis after regex_pattern. Please see update version. Also depending
sa.Table type there are different ways to get column. If it is Table(..) then user sa.Table.c.column.name. If it is mapped object then just sa.Table.column.nameLuigi Tiburzi
I'm sorry, I don't understand, this is my query: sa.Session.query(sa.BaseApparati).filter("sa.BaseApparati.column.nome ~':regex_pattern'" ).params(regex_pattern='[123]').all(). The name of the table is "BaseApparati" and that of the column is "nome", what is the correct typing? I still get "Programming Error...". Thanks
plaes
It seems that you have a confusion between table objects and ORM objects. In your ORM model you access fields (
obj.nome), but with table objects you need to use columns: obj.c.nome.This is not fully portable, but here is a Postgres solution, which uses a ~ operator. We can use arbitrary operators thus:
sa.Session.query(sa.Table).filter(sa.Table.field.op('~', is_comparison=True)(regex-to match))
Or, assuming a default precedence of 0,
sa.Session.query(sa.Table).filter(sa.Table.field.op('~', 0, True)(regex-to match))
This also works with ORM constructs:
sa.Session.query(SomeClass).filter(SomeClass.field.op('~', 0, True)(regex-to match))
