CODEWITHSUNDEEP
pythonregexsqlalchemy
dpq
dpq

Reputation: 9278

Regular expressions in SQLalchemy queries?

Is it possible to use a regex in a way similar to session.query(MyObject).filter_by(REGEX)?

If not, how can I use sqlAlchemy to retrieve records that have a varchar PK beginning with a certain value (e.g. all those whose city field begins with "SA")? Thanks.

Upvotes: 35

Views: 34393

Answers (5)

Alex
Alex

Reputation: 405

For those of you who are also trying to use regex in MSSQL Databases:

You can bring regex support by writing OLE Functions

but maybe you are not comfortable with the idea of writing functional extensions to your MSSQL Database or you simply are not allowed to do this.

Unfortunately, the use of regexp_match as mentioned by Todd Jacobus Answer in sqlalchemy 1.4 (latest release) is not backend-agnostic which includes MSSQL Databases.

However you might be able to "translate" your regex into a wildcard-pattern that you can use with the "LIKE" operator.

Unlike MySQL, MSSQL (or T-SQL) is capable of using wildcards
EG: '[A-Z0-9]%' matching a string of any length that must start with any letter from A to Z or numeral from 0 to 9.
Here you can see some useful examples

By fully translating a regex into a wildcard expression, the resulting expression however might not be very convenient to the reader.
EG: '[A-Z0-9]{5}' will translate into '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]' since quantors are only poorly available (_ and %).

When you are looking for a backend-agnostic, "good enough" solution, you may help yourself by using the "LIKE" operator with an expression that comes close to your regex to narrow down the number of results, and then filter the results again in your program logic.

Upvotes: 1

Todd Jacobus
Todd Jacobus

Reputation: 131

as of SqlAlchemy 1.4, there's a built-in regular expression operator, regexp_match.

Upvotes: 13

Paulo Scardine
Paulo Scardine

Reputation: 77399

[DISCLAIMER: no regex]

I'm answering to the question "how can I use sqlAlchemy to retrieve records that have a varchar PK beginning with a certain value" because for this simple use case a LIKE probably is both less expensive and more portable (asking for regular expressions seems like a manifestation of the XY Problem).

In SQLAlquemy (borrowing from Alex):

session.query(Object).filter(Object.column.like('something%'))

In SqlSoup I use:

db.table.filter(db.table.column.like('something%'))

Upvotes: 2

Alex
Alex

Reputation: 2040

For the record, you can do essentially the same syntax as Paulo Scardine's answer in SQLAlchemy too;

session.query(Object).filter(Object.column.like('something%'))

Upvotes: 11

dpq
dpq

Reputation: 9278

I think I got it:

session.query(Object).filter(Object.column.op('regexp')(REGEX))

Upvotes: 56

Related Questions