Welcome, guest | Sign In | My Account | Store | Cart

Associate object attributes with database columns.

Python, 127 lines
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
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"

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.

Created by j knapka on Mon, 7 Jan 2002 (PSF)
Python recipes (4591)
j knapka's recipes (2)

Required Modules

Other Information and Tasks