PyObject: A Python object maker for database result sets

We use procedural SQL calls to populate our Django views. To do so we make use of Python's psycopg module. However, the default data structure returned by psycopg isn't particularly maintainable when dealing with complex data sets. As a result, we found it necessary to write a simple database wrapper which converts psycopg lists to on-the-fly Python objects.

The following use-case highlights how our database wrapper can be utilised to write more readable, pythonic code when dealing with psycopg.

A simple database table.

t_first_name VARCHAR (100) NOT NULL,
t_last_name VARCHAR (100) NOT NULL,
t_email VARCHAR (100) NOT NULL,
t_password VARCHAR (50) NOT NULL

Using psycopg we would access information from the people table in the following manner:

»»» import psycopg
»»» connection = psycopg.connect("dbname=test user=clw password=***")
»»» cursor = connection.cursor()
»»» cursor.execute("SELECT * FROM people WHERE id=%s, (1,))
»»» person = cursor.fetchone()
»»» print person[0], person[1], person[2], person[3], person[4]
»»» 1 sebastian ritter password

As can be seen in this simple example, indexing a row can quickly become cumbersome. Furthermore, an explicit knowledge of the database layout is required to know which index corresponds to which column in the table. Due to this close coupling, changing the definition of the people table, say by adding a middle name between the first and last name fields, would offset all indexes by one.

We wrote our Python object wrapper to eliminate list indexing, and provide more meaningful attribute names to the values returned. The wrapper allows us to access our data using object notation in the following manner:

»»» connection = psycopg.connect("dbname=test user=clw password=***")
»»» cursor = connection.cursor()
»»» cursor.execute("SELECT * FROM people WHERE id=%s, (1,))
»»» person = PyObject (cursor, "NewClass")
»»» person
»»» <objectmaker.NewClass object at 0xb7d8372c>
»»» print person.t_first_name
»»» sebastian
»»» print person.__dict__
»»» {'id':1, 't_first_name':'sebastian', 't_last_name':'ritter', 't_password':'password', 't_email''}

We found that applying this approach has made our code much more extendible. Making changes in the database does not affect our Python code and its intentions are immediately clear.

Using the principles above, we have developed more complicated wrapper methods to handle multi-row return sets (using generators), as well as a grouping wrapper that returns multi-tiered objects. Although implementations details can be found in the source files provided at the end of this article, a demonstration of how to use the grouping wrapper is provided below.

Another database table.

CREATE TABLE spendings (
n_person_id INTEGER REFERENCES people(id) NOT NULL,
n_amount NUMERIC (13,2) NOT NULL

We use the spendings table above to demonstrate how we can construct a set of people objects, each containing a second-tier listing of spending objects.

»»» connection = psycopg.connect("dbname=test user=clw password=***")
»»» cursor = connection.cursor()
»»» cursor.execute("SELECT
p.t_first_name, p.t_last_name, s.n_amount
people p, spendings s
WHERE = s.n_person_id")
»»» people = PyGroupedList (cursor, [("People", (0,2)), ("Spending", (2,3))])
»»» people
»»» <objectmaker.Collection object at 0x97a3e6c>
»»» for person in people:
»»» print person.t_first_name, person.t_last_name
»»» for spending in person:
»»» print spending.n_amount

sebastian ritter
john smith

This rather basic implementation of PyGroupedList takes as a parameter, the desired tiering structure to be returned. At the moment PyGroupedList requires the return-set to be ordered by each tier and does not understand slice notation such as [:2]. We intended to continue our development on these wrapper methods as they have become a vital part of our code base.

A Brief History

Campbell-Lange Workshop's (CLW) software development team constantly tries to challenge their implementation methodologies in order to stay ahead of the curve. Having moved away from more traditional web development languages such as PHP in 2006, we now use Django, a Python based framework.

Providing a means for clean and pragmatic design, Django has been ideal for our software endeavours. The templating language, form validation and URL dispatcher fit seamlessly with our development mantras. We have, however, found that Django's Object Relational Mapper (ORM), the component responsible for mediating database queries, was simply not powerful enough to handle the complexity of our web services.

Being firm believers that terse procedural SQL is the foundation for a robust system, CLW developed this light-weight database wrapper around Python's psycopg module.

import psycopg
from new import classobj
from copy import copy

class Wrapper (object):
_cache = {}

def PyObject (self, cursor, class_name):
row = cursor.fetchone()
return None
if row:
thisdict = {}
for j in range(len(cursor.description)):
return Wrapper._inst_maker(class_name, (object,), thisdict)
return None
def _inst_maker (name, base_clses, attrs):
class_def = Wrapper._cache[name]
instance = class_def()
return instance
def _class_maker (name, base_clses, attr_names):
class_obj = classobj(name, base_clses,
dict(map(lambda x:(x,None), attr_names)))
Wrapper._cache[name] = class_obj
# Register the class in the module's namespace
globals()[name] = class_obj
return copy(class_obj)

The PyObject method iterates through the columns of a row returned from a psycopg cursor. It constructs a dictionary of key, value pairs that correspond to the column names and values of the row. Once created, the dictionary is passed to the static _inst_maker method which will return an object instance. To generate the instance, said method checks the wrapper's cache to determine if a class object with the given name already exists. If this is the case, the object is simply instantiated and its attributes are updated with the values of the dictionary. If no such class object exists, _class_maker makes use of the classobj method found in the built-in module to create the object. Before returning the class, _class_maker will ensure the object is cached (to avoid repetitive creation) and registers the class with the Wrapper module's name-space. Registration is done such that the class objects can be serialized.

As can be seen from the use-case above, the end result of applying the wrapper to a psycopg cursor is a fully instantiated object. It provides a means to access the data returned from a database call via object notation, a much more readable alternative to the "list of lists" returned by default.