Python is a great language to build commercial applications using databases. Often times an object relational mapper is used to isolate the Python code from the database (like in Django or SQLAlchemy).

Using the database API directly however has the benefit of SQL. SQL is taylormade for database access and is therefore much easier to understand and easier to build in the first place. Using SQL lets you use vendor specific features effortlessly.

I try to use SQL for database access wherever possible. To ease the transition between the two languages I use these patterns frequently:

class ObjectFromRow(object):
    """
    Base class for query results.
    """
    def __init__(self, row):
        """
        Stores all fields from a single DB row in this object as attributes
        """
        for k, v in row.items():
            setattr(self, k, v)

    def __getitem__(self, name):
        """
        Container method to allow x[y] like access which is needed for string
        formatting as in "%(bla)s" % obj
        """
        value = self
        for n in name.split(','):
            value = getattr(value, n)
        return value

class Address(ObjectFromRow):
    """
    create table person(
        firstname varchar(30),
        lastname varchar(30),
        birthdate date,
        zipcode varchar(5),
        city varchar(30));
    """
    pass

class AddressListPerZIP(object):
    @classmethod
    def listFactory(self, cursor, day):
        """
        print a birthday list for day
        """
        criteria = { 'birthday': day.strftime("%%-%m-%d") } # ignore year of birth
        query = "select firstname"\
                "      ,lastname"\
                "      ,birthdate"\
                "from person "\
                "where birthdate like %(birthday)s"
        cursor.execute(query, criteria)
        for row in cursor.fetchall():
            address = Address(row)
            print "%(lastname)s, %(firstname)s, %(birtdate)s" % address

ObjectFromRow defines a generic class to build python objects from query rows. The objects instances variables are automatically initialized from the db row. The __getitem__ method defines the method to access instance variable like a[‘b’].

The last tow lines of the code show this pattern in action: an address object is created from the row retrieved, formatted using “%(name)s” while accessing instance variables through __getitem__(name).

These development patterns allow for a streamlined and uncomplicated cooperation of procedural Python code and SQL.

This pattern is sure to work with psycopg2 (the Python PostgreSQL API) and does not work with MySQL unfortunately.