DBAttrib (Python recipe) by j knapka

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

import dbi,odbc

class DBCore:
    """Basic DB access."""

    def __init__(self,dbname):
        self._DB = odbc.odbc(dbname)
        self._cur = self._DB.cursor()

    def executeUpdate(self,sql,*parms):
        self._cur.execute(sql,*parms)

    def executeQuery(self,sql,*parms,**kws):
        strip = 0
        try:
            strip = kws['strip']
        except KeyError:
            pass

        self._cur.execute(sql,*parms)
        results = self._cur.fetchall()

        if strip:
            # Remove extraneous sequence nesting.
            if len(results) == 1 and len(results[0]) == 1:
                return results[0][0]

        return results

    def execute(self,sql,*args,**kws):
        if sql[0:6] == "select":
            return self.executeQuery(sql,*args,**kws)
        else:
            self.executeUpdate(sql,*args,**kws)

class DBAttrib(DBCore):
    """Provides magic for getting and setting attributes
    in the database via ODBC."""

    def __init__(self,dbname,table,cols,where,whereparms):
        """
        dbname: ODBC data source name.
        table: DB table name.
        cols:  map of attribute names to names of columns in table.
        where: where clause of query to fetch this object's table data.
        whereparms: sequence containing parameters to <where>.
        """
        self.__dict__['_db_cols'] = cols
        self._db_table = table
        self._db_where = where
        self._db_whereparms = whereparms
        DBCore.__init__(self,dbname)

    def __setattr__(self,attr,value):
        """Look for a _set_attr method. If found, use it. Otherwise, if the
        attribute is a DB column, use ODBC. Otherwise, set in the
        object dict."""
        try:
            setmethod = getattr(self,"_set_"+attr)
            return apply(setmethod,(value,))
        except AttributeError:
            pass
        if attr in self._db_cols.keys():
            self._db_set_attr(attr,value)
            return
        self.__dict__[attr] = value

    def __getattr__(self,attr):
        """Ignore _set_ and _db_ attribs. If there's a _get_attr method,
        use it. Otherwise, if it's a DB column, use ODBC."""
        if attr[0:5] == "_set_":
            raise AttributeError
        if attr[0:5] == "_get_":
            raise AttributeError
        if attr[0:4] == "_db_":
            raise AttributeError
        try:
            getmethod = getattr(self,"_get_"+attr)
            return apply(getmethod)
        except AttributeError:
            pass
        if attr in self._db_cols.keys():
            return self._db_get_attr(attr)
        raise AttributeError

    def _db_get_attr(self,attr):
        """ Get attr from the database. """
        attr = self._db_cols[attr]
        sql = "select \"%s\" from \"%s\" %s"%(attr,self._db_table,
                                              self._db_where)
        return self.execute(sql,self._db_whereparms,strip=1)

    def _db_set_attr(self,attr,value):
        """ Set attr in the database. """
        attr = self._db_cols[attr]
        sql = "update \"%s\" set \"%s\" = ? %s"%(self._db_table,
                                                 attr,self._db_where)
        self.execute(sql,(value,)+self._db_whereparms)


# Example: the "Student" table has columns "Last Name", "First Name",
# "Middle", and "Customer ID."
class Student(DBAttrib):

    def __init__(self,dbname,student_id):
        DBAttrib.__init__(self,
                          
                          dbname,
                          
                          'Student',
                          
                          {'lastname':'Last Name',
                           'firstname':'First Name',
                           'middle':'Middle',
                           'custid':'Customer ID'},
                          
                          'where "Customer ID" = ?',
                          
                          (student_id,))

stu = Student("MYDB","999-9999")

# Fetch DB data.
print stu.custid,stu.lastname,stu.firstname,stu.middle

# Update DB data.
stu.middle = "X"