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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/pylons-discuss?hl=en.
