Skip to content

Instantly share code, notes, and snippets.



Last active May 13, 2021
What would you like to do?
Regular expression filters in SQLAlchemy
Module implementing an enhanced string column type for SQLAlchemy
with a support for regular expression operators in Postgres and SQLite.
import re
from sqlalchemy import String as _String, event, exc
from sqlalchemy.engine import Engine
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import BinaryExpression, func, literal
from sqlalchemy.sql.operators import custom_op
import sqlite3
__all__ = ['String']
class String(_String):
"""Enchanced version of standard SQLAlchemy's :class:`String`.
Supports additional operators that can be used while constructing
filter expressions.
class comparator_factory(_String.comparator_factory):
"""Contains implementation of :class:`String` operators
related to regular expressions.
def regexp(self, other):
return RegexMatchExpression(self.expr, literal(other), custom_op('~'))
def iregexp(self, other):
return RegexMatchExpression(self.expr, literal(other), custom_op('~*'))
def not_regexp(self, other):
return RegexMatchExpression(self.expr, literal(other), custom_op('!~'))
def not_iregexp(self, other):
return RegexMatchExpression(self.expr, literal(other), custom_op('!~*'))
class RegexMatchExpression(BinaryExpression):
"""Represents matching of a column againsts a regular expression."""
@compiles(RegexMatchExpression, 'sqlite')
def sqlite_regex_match(element, compiler, **kw):
"""Compile the SQL expression representing a regular expression match
for the SQLite engine.
# determine the name of a custom SQLite function to use for the operator
operator = element.operator.opstring
func_name, _ = SQLITE_REGEX_FUNCTIONS[operator]
except (KeyError, ValueError), e:
would_be_sql_string = ' '.join((compiler.process(element.left),
raise exc.StatementError(
"unknown regular expression match operator: %s" % operator,
would_be_sql_string, None, e)
# compile the expression as an invocation of the custom function
regex_func = getattr(func, func_name)
regex_func_call = regex_func(element.left, element.right)
return compiler.process(regex_func_call)
@event.listens_for(Engine, 'connect')
def sqlite_engine_connect(dbapi_connection, connection_record):
"""Listener for the event of establishing connection to a SQLite database.
Creates the functions handling regular expression operators
within SQLite engine, pointing them to their Python implementations above.
if not isinstance(dbapi_connection, sqlite3.Connection):
for name, function in SQLITE_REGEX_FUNCTIONS.values():
dbapi_connection.create_function(name, 2, function)
# Mapping from the regular expression matching operators
# to named Python functions that implement them for SQLite.
'~': ('REGEXP',
lambda value, regex: bool(re.match(regex, value))),
'~*': ('IREGEXP',
lambda value, regex: bool(re.match(regex, value, re.IGNORECASE))),
'!~': ('NOT_REGEXP',
lambda value, regex: not re.match(regex, value)),
'!~*': ('NOT_IREGEXP',
lambda value, regex: not re.match(regex, value, re.IGNORECASE)),

This comment has been minimized.

Copy link

@datavistics datavistics commented Jan 17, 2018

I used this to create a column, then I populated the database (sqlite3) with a few rows, but I get this error from the Pycharm database console:

[2018-01-17 12:14:13] Connected
sql> select count(*) from relationships_submission where relationships_submission.title REGEXP '\[\d+\]'
[2018-01-17 12:14:13] [1] [SQLITE_ERROR] SQL error or missing database (no such function: REGEXP)
sql> select count(*) from relationships_submission where relationships_submission.title ~ '\[\d+\]'
[2018-01-17 12:14:23] [1] [SQLITE_ERROR] SQL error or missing database (near "~": syntax error)

Any advice?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment