On Feb 9, 2010, at 9:55 AM, Joel wrote:
4. use regular expressions to filter my orm queries, i.e., be able to
write x = page_q.filter(model.Page.title.op('regexp')(searchTerm)
Such a filter works on MySQL without alteration, but with SQLite it
seems I need to define a regexp function. I've found out how to write
such a function:
import re
def regexp(expr, item):
r = re.compile(expr)
return r.match(item) is not None
I've also found out that when using sqlalchemy's SQL Expression API, I
can give sqlite this function with the following:
connection = engine.connect()
connection.connection.create_function('regexp', 2, regexp)
So: is it possible to use sqlalchemy's Object Relational API and still
give sqlite the regexp function?
My attempt to do this was to change my app's model/__init__.py
init_model(engine) function to the following:
def init_model(engine):
connection = engine.connect()
connection.connection.create_function('regexp', 2, regexp)
meta.Session.configure(bind=connection)
meta.engine = engine
I can do this and I can setup my app (paster setup-app
development.ini) and I can serve my app, but when I try to do a
search I get the following error:
ProgrammingError: (ProgrammingError) SQLite objects created in a
thread can only be used in that same thread.The object was created in
thread id 139951621744368 and this is thread id 139951533046032 None
[{}]
So what am I doing wrong? Is it even possible to create an RDBMS-
specific function while using sqlalchemy's ORM?
It sounds like SQLite requires you define your regexp function in every
different thread/connection used. In that case you'd want SQLAlchemy's
connection pool to create your function every time it establishes a new
connection to SQLite.
You can do that via a PoolListener:
http://www.sqlalchemy.org/docs/reference/sqlalchemy/interfaces.html?highlight=poollistener#sqlalchemy.interfaces.PoolListener
It might look something like this:
class SQLiteSetup(PoolListener):
def connect(self, conn, conn_record):
conn.create_function('regexp', 2, regexp)
And in your load_environment:
engine = engine_from_config(config, 'sqlalchemy.', listeners=[SQLiteSetup])
--
Philip Jenvey
--
You received this message because you are subscribed to the Google Groups
pylons-discuss group.
To post to this group, send email to pylons-disc...@googlegroups.com.
To unsubscribe from this group, send email to
pylons-discuss+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/pylons-discuss?hl=en.
