How can I do REGEXP_REPLACE in SQLAlchemy with SQLite?

Question

I have a Postgres-backed website, and use REGEXP_REPLACE to replace the first instance of a value in some columns. This is fine. The problem comes where I need to run CI, and for one reason and another (limitations of Github Actions and Windows VMs), I can't use Postgres in my tests.

Choosing SQLite produced a few test failures, and I realised that SQLite doesn't have REGEXP_REPLACE built in.

    def do_execute(self, cursor, statement, parameters, context=None):
>       cursor.execute(statement, parameters)
E       sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such function: regexp_replace
E       [SQL: SELECT job_site.created_at AS job_site_created_at, ..., job_site.neighborhood_id AS job_site_neighborhood_id 
E       FROM job_site 
E       WHERE regexp_replace(job_site.name, ?, ?) = ?]
E       [parameters: (' - ', ':', 'BGS:NEEA - 1894 - 05700 Brown Islands Suite 681')]
E       (Background on this error at: http://sqlalche.me/e/e3q8)

How can I add this function to SQLite in SQLAlchemy?

score 0 · Accepted Answer · answered Apr 06 '20 at 09:22

0

The solution turned out to be to register regexp_replace as a GenericFunction (docs), and add a dialect-specific compiler (docs) to be used when running with the SQLite engine.

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.functions import GenericFunction


class regexp_replace(GenericFunction):
    type = String
    identifier = "regexp_replace"


@compiles(regexp_replace, "sqlite")
def visit_regexp_replace(element, compiler, **kw):
    column, pattern, replacement = list(element.clauses)
    return (
        f"SUBSTR({column}, 0, INSTR ({column}, '{pattern.value}')) "
        f"|| '{replacement.value}' "
        f"|| SUBSTR({column}, INSTR ({column}, '{pattern.value}') + LENGTH('{pattern.value}'))"
    )

It is also possible to use the SQLite C extension as suggested here, but I didn't look into making that work.

answered Apr 06 '20 at 09:22

Jamie Bull

12,889
15
77
116

1

One more available technique is to register a Python function as `regexp_replace` in SQLite: https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function. That'd allow you to perform an actual regexp replace. – Ilja Everilä Apr 06 '20 at 15:26
@IljaEverilä I did see that in the docs, but wasn't clear where/when I would register that using SQLAlchemy – Jamie Bull Apr 06 '20 at 17:57
1

One option is to do it when a DB-API connection is created. There's an example of that in the part that covers enabling foreign key checks in SQLite: https://docs.sqlalchemy.org/en/13/dialects/sqlite.html#foreign-key-support. – Ilja Everilä Apr 06 '20 at 18:00

How can I do REGEXP_REPLACE in SQLAlchemy with SQLite?

1 Answers1