On SQLObject

I have recently used SQLObject in a few projects. My first encounter of SQLObject was as a component of TurboGears, a framework for creating web sites. But I soon realized that SQLObject can be quite handy on its own.

What is SQLObject then, really. I don't want to do a full tutorial on SQLObject, so please head over to the SQLObject website to familiarize yourself with it. In short, it is a object oriented wrapper to SQL databases, for Python. You can freely choose among several different backends, like SQLite, Mysql, Postgres, and so forth, and most of the details of the particular backend you use will be hidden within SQLObject. If you want to create a table with SQLObject you simply say

class MyTable(SQLObject):
name = UnicodeCol()
date = DateCol()

(assuming you have done from SQLObject import *, which you of course have not). SQLObject takes care of all the SQL woodoo, and you can go ahead saying things like

mt = MyTable(name="someday",date="2007-03-28")

To get rid of the entry again, you just say

mt.destroySelf()

In addition to get rid of the object within your Python code, this also removes the corresponding row in the table on the sql server.

So far all good, and quite stright forward. Next, you want one-to-many relations, or maybe many-to-many relations. No problem, SQLObject can handle those as well, using MultipleJoin or RelatedJoin, together with ForeignKey. Assume that you wants a many-to-many relation between the MyTable above, and some other table:

class FooTable(SQLObject):
 name = UnicodeCol(alternateID=True, length=50)
 mytables = RelatedJoin('MyTable')

To get symmetric behaviour, you also wants

  foos = RelatedJoin('FooTable')

inside MyTable. With this setting, you can still use destroySelf(), and SQLObject figures out the right thing. So, that was many-to-many relations.

Now, I had a one-to-many relation in my code, like this:

class Foo(SQLObject):
 name = UnicodeCol(alternateID=True, length=30)
 mybar = ForeignKey('Bar')

class Bar(SQLObject):
 name = UnicodeCol(alternateID=True, length=30)
 foos = MultipleJoin('Foo')

So, this is a one-to-many relation, as Bar may have many Foo's, but Foo only link to one Bar. Then I did something like:

b = Bar.byName('mybar')
b.destroySelf()

Only to discover that I got errors next time I tried to do anything with the Foo's. Reason was that there was Foo's still linked to the Bar I destroyed. Thinking about it, this make perfectly sense. It also shows that SQLObject is not perfect in some sense. It does what you tell it to do, even when that is evil. This can be both good and bad, depending on how you look at it. Fortunately, the fix is quite easy:

if len(b.foos) > 0:
 print "There are still Foo's linked to this Bar"

Then of course more lines, if you want to deal with it in code - like relinking the Foos to other Bars, or just leave the user with that message - probably followed by some raise to signal an exception.

If you go out there search the blogs, you will find people telling you that SQLObject is not good. They are probably right. But to me, it felt just right from the very beginning. When I dig deeper into it, I will probably complain too. But so far I am happy. Nevertheless, I see that something called SQLAlchemy is mentioned together with turbogears now, and I guess SQLAlchemy is a replacement for SQLObject. I have to check that out, maybe it is even better. Until then, I stick with SQLObject, at least for non-turbogears apps, because I think it does its jobb very well.

Take care folks, and keep that Python code flowing from your hands.