How to use regexp function in sqlite with sqlalchemy?

Question

I'd like to use regexp query in "sqlalchemy" as well as is done in "python sqlite", code below..

Unfinished sandbox script is this:

import os
import re
import sqlite3

#
# python sqlite
#

DB_PATH = __name__ + '.db'

try:
    os.remove(DB_PATH)
except:
    pass


def re_fn(expr, item):
    reg = re.compile(expr, re.I)
    return reg.search(item) is not None

conn = sqlite3.connect(':memory:')
conn = sqlite3.connect(DB_PATH)
conn.create_function("REGEXP", 2, re_fn)
cursor = conn.cursor()

cursor.execute(
    'CREATE TABLE t1 (id INTEGER PRIMARY KEY, c1 TEXT)'
)
cursor.executemany(
    #'INSERT INTO t1 (c1) VALUES (?)', [('aaa"test"',),('blah',)]
    'INSERT INTO t1 (c1) VALUES (?)', [
        ('dupa / 1st Part',), ('cycki / 2nd Part',), ('fiut / 3rd Part',)
    ]
)
cursor.execute(
    #'SELECT c1 FROM t1 WHERE c1 REGEXP ?',['2|3\w+part']
    'SELECT c1 FROM t1 WHERE c1 REGEXP ?',['\d\w+ part']
)
conn.commit()
data=cursor.fetchall()
print(data)



#
# sqlalchemy
#

import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.ext.declarative import declarative_base

DSN = 'sqlite:///' + DB_PATH
engine = sa.create_engine(DSN, convert_unicode=True)
db = orm.scoped_session(orm.sessionmaker(autocommit=False,
                                         autoflush=False,
                                         bind=engine))

Base = declarative_base(bind=engine)
meta = Base.metadata

class T1(Base):
    __table__ = sa.Table('t1', meta, autoload=True)

print(db.query(T1).all())

I've found that regexp function should be registered on each thread:

http://permalink.gmane.org/gmane.comp.web.pylons.general/12742

but I'm not able to adopt link's solution to my script + it's deprecated.

Update

I'd like to query this:

cursor.execute(
    #'SELECT c1 FROM t1 WHERE c1 REGEXP ?',['2|3\w+part']
    'SELECT c1 FROM t1 WHERE c1 REGEXP ?',['\d\w+ part']
)

but in sqlalchemy.

i don't understand what you are trying to do. what errors do you have? — njzk2
– njzk2, Commented Sep 17, 2012 at 14:58

xliiv · Accepted Answer · 2012-09-18 10:18:15Z

14

I've got the answer.. Complete working script with missing one line is this:

import os
import re
import sqlite3

DB_PATH = __name__ + '.db'

try:
    os.remove(DB_PATH)
except:
    pass


def re_fn(expr, item):
    reg = re.compile(expr, re.I)
    return reg.search(item) is not None

conn = sqlite3.connect(':memory:')
conn = sqlite3.connect(DB_PATH)
conn.create_function("REGEXP", 2, re_fn)
cursor = conn.cursor()

cursor.execute(
    'CREATE TABLE t1 (id INTEGER PRIMARY KEY, c1 TEXT)'
)
cursor.executemany(
    #'INSERT INTO t1 (c1) VALUES (?)', [('aaa"test"',),('blah',)]
    'INSERT INTO t1 (c1) VALUES (?)', [
        ('dupa / 1st Part',), ('cycki / 2nd Part',), ('fiut / 3rd Part',)
    ]
)
SEARCH_TERM = '3rd part'
cursor.execute(
    #'SELECT c1 FROM t1 WHERE c1 REGEXP ?',['2|3\w+part']
    'SELECT c1 FROM t1 WHERE c1 REGEXP ?',[SEARCH_TERM]
)
conn.commit()
data=cursor.fetchall()
print(data)



#
# sqlalchemy
#

import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.ext.declarative import declarative_base

DSN = 'sqlite:///' + DB_PATH

engine = sa.create_engine(DSN, convert_unicode=True)

conn = engine.connect()
conn.connection.create_function('regexp', 2, re_fn)

db = orm.scoped_session(orm.sessionmaker(autocommit=False,
                                         autoflush=False,
                                         bind=engine))

Base = declarative_base(bind=engine)
meta = Base.metadata

class T1(Base):
    __table__ = sa.Table('t1', meta, autoload=True)

print(db.query(T1.c1).filter(T1.c1.op('regexp')(SEARCH_TERM)).all())

Above works in sqlalchemy=0.6.3

In sqlalchemy=0.7.8 i got error:

"sqlalchemy.exc.OperationalError: (OperationalError) no such function: regexp .."

maybe because of this change:

When a file-based database is specified, the dialect will use NullPool as the source of connections. This pool closes and discards connections which are returned to the pool immediately. SQLite file-based connections have extremely low overhead, so pooling is not necessary. The scheme also prevents a connection from being used again in a different thread and works best with SQLite’s coarse-grained file locking. Changed in version 0.7: Default selection of NullPool for SQLite file-based databases. Previous versions select SingletonThreadPool by default for all SQLite databases.

from: http://docs.sqlalchemy.org/en/rel_0_7/dialects/sqlite.html?highlight=isolation_level#threading-pooling-behavior

and solution for that was: to add regexp fn in 'begin' event like this:

...

conn = engine.connect()
@sa.event.listens_for(engine, "begin")
def do_begin(conn):
    conn.connection.create_function('regexp', 2, re_fn)

db = orm.scoped_session(orm.sessionmaker(autocommit=False,
                                         autoflush=False,
                                         bind=engine))

...

answered Sep 18, 2012 at 10:18

xliiv

5,6895 gold badges32 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TmSmth Over a year ago

I don't understand where "re_fn" gets the regex pattern ? you put only your search_term as an argument but the function asks two. Thanks !

xliiv Over a year ago

I'd guess that item argument is a row which is injected by SQL engine. So programmer specifies regexp (like "\d+") and SQL engine injects db row. Having those two you decide in re_fn whether to return particular row or not.

TmSmth Over a year ago

oh ok got it, '3rd part' is expr, not ìtem ! Thx

dlazesz · Accepted Answer · 2022-01-20 22:43:38Z

0

Almost 10 years later...

It works more easier in SQLAlchemy 1.4:

stmt = select(table.c.some_column).where(
    table.c.some_column.regexp_match('^(b|c)')
)

Source: https://docs.sqlalchemy.org/en/14/core/sqlelement.html#sqlalchemy.sql.expression.ColumnElement.regexp_match

answered Jan 20, 2022 at 22:43

dlazesz

3083 silver badges18 bronze badges

Collectives™ on Stack Overflow

How to use regexp function in sqlite with sqlalchemy?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related