Tidbits @ Kassemi

A collection of opinions, thoughts, tricks and misc. information.

Monday, January 16, 2006

 

CherryPy, SQLite, and SQLObject... A freaking mess!

Okay, well, maybe not so much. Turbogears uses all of these, so why wasn't my project properly accessing the database, instead giving me a whole load of threading errors?

I was working with threading turned off without realizing it, and the second I turned the threadpool setting in Cherrypy to a non-zero value, I began getting loads of errors from SQLObject, stating that the thread that my database object was in was not the same as the one CP was using... So, where do you start? First of all, you take a look at:

http://www.cherrypy.org/wiki/SQLObjectThreadPerConnection

The solution posed works perfectly, assuming, of course, that you want to program your code specifically to use an sqlite database. The ideal solution is that instead of using the SQLiteConnection() method, we would just use our typical connectionForURI, with a simple URI. So, why does SQLObject throw up these errors, even when we're calling for a new connection with connectionForURI every time? I spent some time looking through SQLObject code, and found a little section in connectionForURI that looks like this:


def connectionForURI(self, uri, **args):
if args:
if '?' not in uri:
uri += '?'
uri += urllib.urlencode(args)
if self.cachedURIs.has_key(uri):
return self.cachedURIs[uri]
if uri.find(':') != -1:
scheme, rest = uri.split(':', 1)
assert self.schemeBuilders.has_key(scheme), (
"No SQLObject driver exists for %s (only %s)"
% (scheme, ', '.join(self.schemeBuilders.keys())))
conn = self.schemeBuilders[scheme]().connectionFromURI(uri)
else:
# We just have a name, not a URI
assert self.instanceNames.has_key(uri), "No SQLObject driver exists under the name %s" % uri
conn = self.instanceNames[uri]
# @@: Do we care if we clobber another connection?
self.cachedURIs[uri] = conn
return conn


It's located in the dbconnection.py file of the sqlobject source. Take a quick look, and you'll see that the first conditional checks for a cache of the connection. If it finds it, it will return the cached connection, instead of creating a new one. This works wonderfully for thread-safe databases such as mysql and postgres, but leads to problems with sqlite...

The cachedURI's variable becomes accessable via the dbconnection.TheURIOpener object, which is simply an instantiation of the connectionForURI's parent class... So, there is a very simple solution:


if yourDBsURIString.startswith('sqlite'):
dbconnection.TheURIOpener.cachedURIs = {}
connection = connectionForURI(yourDBsURIString)
sqlhub.threadConnection = connection


Place that in the list that CP starts whenever it makes a thread, and you won't have any more problems with sqlite threading and sqlobject.

Until next time,
James

Comments:
Thanks a lot. "Freakin' mess" is exactly what it felt like until I ran across your hint.

Volodya Orlenko
 
Post a Comment



<< Home

Archives

August 2005   September 2005   October 2005   November 2005   December 2005   January 2006   February 2006   March 2006   April 2006   June 2006   July 2006   August 2006   September 2006   October 2006   November 2006  

This page is powered by Blogger. Isn't yours?