OperationalError: no such function: REGEXP

Python Help
1

I just tried this in sqlite3 command line and it works !

sqlite> .open oewn-plus-2026-sqlite-3.0.1.sqlite
sqlite> SELECT COUNT(w.`wordid`) AS `Total` FROM `words` w WHERE w.`word` REGEXP '[0-9]' = 0 AND 1;
151867

So the issue isn’t with SQLite3 but with python ?

sql_stmt = """SELECT COUNT(w.`wordid`) AS `Total` FROM `words` w WHERE w.`word` REGEXP '[0-9]' = 0 AND 1;"""
self.cursor.execute(sql_stmt)
row = self.cursor.fetchone()
self.total = row['Total']
print("total: ", self.total)

returns

An error occurred: no such function: REGEXP
 ▕  Exception in ASGI application
Traceback (most recent call last):
  File "/Users/myUsername/projects/words/api/main.py", line 60, in read_pattern
    wordFinder.run()
    ~~~~~~~~~~~~~~^^
  File "/Users/myUsername/projects/words/api/Words.py", line 52, in run
    self.cursor.execute(sql_stmt)
    ~~~~~~~~~~~~~~~~~~~^^^^^^^^^^
sqlite3.OperationalError: no such function: REGEXP
2

From the sqlite docs:

The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If an application-defined SQL function named “regexp” is added at run-time, then the “X REGEXP Y” operator will be implemented as a call to “regexp(Y,X)”.

It appears that the sqlite3 CLI implements regexp() as an extension:

  1. Miscellaneous Extension Features

    The CLI is built with several SQLite extensions that are not included with the SQLite library. A few add features not described in the preceding sections, namely:

    • …
    • support for POSIX extended regular expressions bound to the REGEXP operator.
3

You can implement the regexp() function yourself like this:

import re

conn = sqlite3.connect(...)
conn.create_function('regexp', 2, lambda pattern, s: re.search(pattern, s) is not None)
4

One thing to add to Benjy’s version: if the column has any NULLs, SQLite passes None as s and re.search raises a TypeError, which shows up as a user-defined function error. Guarding it avoids that:

import re
import sqlite3

def regexp(pattern, s):
    if s is None:
        return False
    return re.search(pattern, s) is not None

conn = sqlite3.connect("words.sqlite")
conn.create_function("regexp", 2, regexp, deterministic=True)

It has to be registered on every connection you open, since it isn’t stored in the database file.