DBAttrib

Title: DBAttrib Submitter: j knapka (other recipes) Last Updated: 2002/01/07 Version no: 1.1 Category: Databases

--Rate--5 - Excellent4 - Very good3 - Good2 - Needs work1 - Poor   2 vote(s)

Description: Associate object attributes with database columns. Source: Text Source 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" Discussion: This recipe is a bit lengthy, but worth it IMO. We hide a bunch of DB-access ugliness behind simple attribute access. Since _get_attr() and _set_attr() are tried before DB access is attempted, it's possible to customize DB access for individual attributes. For example, if there's a child table called "Student-Classes" that associates a collection of classes with each student, Student._get_classes() could be defined to select and return a list of the student's classes, and then stu.classes would return the list. An obvious and very effective optimization is to cache the results of DB fetches and return the cached values if they're available.