Tidbits @ Kassemi

After writing a functional, yet somewhat limited database connection module for all my python database needs, I came across SQLObject. It sucks to find something that does what you want better than what you've got after you've put so much time into your own solution. So, SQLObject being open source, fast, clever and incredibly easy to use, I decided I'd give it a try. I also decided I'd use sqlite for my project, making things a little more modular and simple to use. So my rewrite of my ratings system began...

It took only about an hour and half to finish the new module, and it worked wonderfully in my simple tests. sqlite was probably one of the better systems I've worked with. Postgres is just too large and painful to get working properly for small projects... On top of that, sqlobject provided the access necessary to immediately port my application to postgres if I needed it. So, I fire up vim and start writing a product module, which is a general-purpose module for working with an index of products. I decided on using a separate sqlite database for that one, so I could place all my separate databases in a single directory, and back them up as necessary, etc. It was the perfect idea, until it went totally wrong :)

SQLite provides the sqlhub, which, as it states, is a hub for processing connections... Skimming the documentation, I didn't see much about it... And for good reason. Most of the time you won't be using multiple sqlite databases in the same program... So I set up each of my modules to use sqlhub.(thread|process)Connection to define the connection that they would use, thinking that the namespace would prevent any problems. Not so... Every time I'd try using both modules together I'd end up with a glaring python traceback pointing to some pysqlite internals...

So I've spent the past few hours googling around, and I've finally found a way to get this to work... I'd actually passed by the solution earlier in my hunting through __doc__'s in sqlobject, but didn't read it right... Anyway, take a look at:

help(sqlobject.dbconnection.ConnectionHub)

That's where the answer lies. It's too freaking simple :)

Just set up your modules to each use their own specific connections:

Module 1:

dbConnectionA = dbconnection.ConnectionHub()

class tableForA(SQLObject):
""" A table for module 1 """

_connection = dbConnectionA
...
dbConnectionA.threadConnection = connectionForURI(dbfile)

Module 2:

dbConnectionB = dbconnection.ConnectionHub()

class tableForB(SQLObject):
""" A table for module 2 """

_connection = dbConnectionB
...

And now you can use the modules together without any conflicting connections. I've yet to apply these to cherrpy, which I hear can be problematic due to SQLite's lack of happy threading support... That's for tomorrow...

-James