April 04, 2003
Exploring Object/Relational Mapping in Python
Long winded title that one, I'll have to revise it for future posts. This is the first in what I hope will be a series of posts. Its a learn while you go exercise, just like the rest of this blog, so please bear with me whilst I veer all over the place merrily making mistakes as I go.
I'm interested in object/relational mappers for a number of reasons. Primarily because I'm the database guy working with a bunch of java programmers during the day, but also because we need a nice easy to implement but lightweight persistence mechanism for PythonCard. These two driving factors aren't exactly identical but have bought to the same place. So, on with the investigation.
Our first cabs off the rank are the easiest to understand and explain and serve as a gentle introduction to the world of impedence mismatch.
dtupleThis module was written by Greg Stein, and is available from his python page. It converts the results of SQL queries on relational database into special objects whose attributes can then be accessed by subscript or dot notation. This is an extension to the standard db-api functionality of just returning each row as a tuple, requiring the programmer to then refer to each column value by index rather than a meaningful name.
Whilst dtuple is useful, it isn't an object relational mapper. It supports the transition of relations to objects but not back again. This means that the only way to create an object which dtuple understands is from the database. I can't create a new object and then persist it to the database, I have to do that myself and then fetch it into a dtuple type object.
dtuple makes the results of database queries more "pythonic" but doesn't support any kind of data manipulation. Which is fine if all you are doing in your application is pulling information out of a database. But I suspect that sooner or later you will want to change this data and put it back, in which case dtuple doesn't really help.
db_rowProvided by the The OPAL Group db_row performs a similar function to dtuple. It is available directly from their web page.
db_row also attempts to put a more "pythonic" face on the results of database queries. Its advantage over dtuple is that it uses new style classes (mental note to self, figure out what on earth that means). In practise this means that it re-uses dictionaries rather than creating a whole new one for each row returned and thus reduces the memory overhead when dealing with large result sets. For that reason alone it might be worth using, although I tend to avoid this problem these days by using generators to serve up the results of my queries.
Aside from that implementation detail the function is very similar to dtuple. So its also good for having more meaningful objects returned from queries but you end up writing your own SQL for everything else.
My conclusion is that these modules aren't what I am looking for. What might be worth investigating is extending dtuple or db_row so they support the missing functionality. Realistically though, I will only really consider that if none of the other available object relational wrappers are up to scratch.
Posted by Andy Todd at April 04, 2003 04:36 PM