Subject: Re: using regular expressions with (py)sqlite

Michael Dunn wrote:
This is more a sqlite question than a pysqlite question, but I read
this list regularly, and I think the solution might be of interest to
others too. The sqlite refs [] mention
using regular expressions with sqlite matches. They say:

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 a user-defined function named "regexp" is defined at
run-time, that function will be called in order to implement the
REGEXP operator.
Has anybody here sucessfully done this, and if so, could you please
explain how. I can't work out from the docs what "defining a function
at run-time" would involve, except that it's probably some C/C++

Interesting. I didn't remember the REGEXP operator.

You can create user-defined functions in pysqlite using the create_function method of connection objects. See

Attached is a quick shot that should get you started.

-- Gerhard

from pysqlite2 import dbapi2 as sqlite
import re

# Return 1 if `item` matches the regular expression `expr`, 0 otherwise
def regexp(expr, item):
r = re.compile(expr)
return r.match(item) is not None

con = sqlite.connect(":memory:")

# Create the function "regexp" for the REGEXP operator of SQLite
con.create_function("regexp", 2, regexp)

cur = con.cursor()

# Create schema and test data
cur.execute("create table test(foo)")
cur.execute("insert into test(foo) values ('aaxaa')")
cur.execute("insert into test(foo) values ('aayaa')")

# Two demonstrations
print "1 --------------------"
cur.execute("select foo from test where foo regexp '.*x.*'")
print cur.fetchall()
print "2 --------------------"
cur.execute("select foo from test where foo regexp '[aA]'")
print cur.fetchall()
pysqlite mailing list
[email protected]

Programming list archiving by: Enterprise Git Hosting