A simple Object Relational Mapper (Python recipe) by Andy Chambers

ActiveState Code (http://code.activestate.com/recipes/473834/)

from Cheetah.Template import Template
import psycopg
conn = psycopg.connect(database="demo")
class Model:
    '''A class providing limited introspective access to (currently) 
    a Postresql Database.
    '''
    global conn 
    def __init__(self, connection=conn):
        self.conn = connection

    def __getattr__(self, name):
        """Returns a subclass of Relation for each table found in
        the model.

        N.B. This returns a class object rather than an instance.  From
        this object you can:-
           - query the database for a cursor,
           - instantiate an instance of the class (i.e. a single row 
             from the database)
           - obtain meta-data from the database table or view
        """
        if name.lower() in self.tables():
            DataSource = type(name, (Relation, dict), {})
            return DataSource
        else:
            raise AttributeError, 'Attribute %s not found' % name


    def getView(self, view_name):
        """The same as __getattr__ except from here, you're allowed to
        specify the view as a string (rather than an object)
        """
        if view_name.lower() in self.tables_and_views():
            DataSource = type(view_name, (Relation, type), {'conn': self.conn})
            return DataSource
        else:
            raise ValueError, 'View %s not found' % view_name


    def tables_and_views(self, schemas=None):
        """Returns a list of tables and views contained in the database on the
        current connection.

        If specified, schemas should be an iterable of schemas from which the
        tables/views should be gathered
        """
        sql = '''select tablename
                 from pg_tables
        	 union
        	 select viewname
        	 from pg_views
        '''
        if schemas:
            sql += "where schemaname in ("
            sql += ", ".join(["%s" for i in schemas]) + ")" 
        cursor = self.conn.cursor()
        cursor.execute(sql, schemas)
        result = cursor.fetchall()
        return [i[0] for i in result]

    def tables(self, schema=None):
        """Returns a list of tables contained in the database to which the
        class is currently attached.

        If specified, schemas should be an iterable of schemas from which the
        tables should be gathered.
        """
        sql = '''select tablename
                 from pg_tables
        '''
        if schema:
            sql += "where schemaname = %s"
        cursor = self.conn.cursor()
        cursor.execute(sql, (schema, ))
        result = cursor.fetchall()
        return [i[0] for i in result]

class Relation:

    conn = psycopg.connect(database="demo")

    def __init__(self, **kwargs):
        tablename = self.__class__.__name__
        sql = self.render_query(view=tablename, pkey=kwargs)
        cursor = self.execute(sql, kwargs)
        result = cursor.fetchone()
        self.data = {}
        for key, value in zip([i[0] for i in cursor.description], result):	
            self.data[key] = value

    def render_query(self, view=None, pkey=None):
        template = Template('''
        select * 
        from $view
        where 
        #for $key, $val in $pkey.iteritems()
        $key = %($key)s
        #end for
        ''', [locals()])
        return template.respond()

    def __getattr__(self, name):
        if name in self.data:
            return self.data[name]
    def __getitem__(self, name):
        if name in self.data:
            return self.data[name]

    @classmethod
    def description(cls):
        """Returns a description of this relations attributes

        This method simply returns the description attribute from the
        DB API cursor object.  This is a sequence of 7 item sequences
        of the form (name, type_code, display_size, internal_size, 
        precision, null_ok) of which only the first two items are
        guaranteed.
        """
        cursor = cls.execute('''select * from %s
                                limit 1''' % cls.__name__)
        return cursor.description


    @classmethod
    def selectAll(cls):
        """A convenience method for selecting an entire relation from
        the database (although we simply forward the call to the more
        general execute)
        """
        return cls.execute("select * from %s" % cls.__name__)
    
    @classmethod
    def execute(cls, template, params=None, commit=True):
        """Executes the specified statement and returns a copy of the cursor
        """
        cursor = cls.conn.cursor()
        cursor.execute(template, params) #execute stmt returns None
        commit and  cls.conn.commit()
        return cursor

    @classmethod
    def insert(cls, mapping):
        """Attempts to insert the specified key value mapping into this table
        """
        keys_fmt = map(lambda x: "%%(%s)s" % x, mapping.keys())
        template = """insert into %(table)s (%(keys)s)
            values (%(values)s);""" % { 'table': cls.__name__, 
                                        'keys': ", ".join(mapping.keys()),
                                        'values': ", ".join(keys_fmt) }
        cls.execute(template, mapping)


if __name__ == '__main__':
    #replace contact with table in your database
    conn = psycopg.connect(database="demo")
    model = Model(conn)
    Contacts = model.contacts
    print Contacts.description   #lists your table attributes
    c = Contacts(id=9)           #c now represents a row in your db
    print c.email                #only if there exists an email attribute
    print c['email']

    cursor = model.contact.selectAll()
    results = cursor.fetchall()