Tidbits @ Kassemi

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