CODEWITHSUNDEEP
pythonmysqlsqlalchemy
bgw
bgw

Reputation: 78

Column value as a `REGEXP` pattern in SQLAlchemy?

Is is possible to use column value as a REGEXP pattern in SQLAlchemy?

I have a table:

CREATE TABLE my_table (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
column int(200) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

With two sample rows:

INSERT INTO my_table (id, column)
VALUES
(1, '^S[[:digit:]]{10}$'),
(2, '^M[[:digit:]]{8}$');

In mysql one can do:

SELECT id FROM my_table WHERE 'S1803020001' REGEXP my_table.column;
+----+
| id |
+----+
|  1 |
+----+

I am looking for a way to do it in SQLAlchemy.

That's how I use literal regexp with my model:

MyTable.query.filter(MyTable.column.op('regexp')(r'pattern'))

but this time I am looking for a way to swap 'pattern' and 'MyTable.column'.

I am aware it might not be supported due to various underlying database backends, as not all backends support non-literal regexp patterns. Nonetheless I am curious if and how can it be achieved.

Upvotes: 0

Views: 499

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52957

Using literal() simply reverse the operation:

literal('foo').op('regexp')(MyTable.column)

Upvotes: 1

Related Questions