Introduction
NB: I wrote this recipe some months ago. I would now prefer the approach indicated in DataAwareControlsMixin.
The recipe DataAwareControlsMixin shows a way of using a mixin with one of the wxPython controls so that the composite object is capable of interacting with a data source, ie, so that the control is data-aware.
The present recipe illustrates how to decorate a database cursor so that it can interact with such data-aware controls.
What Objects are Involved
No wxPython objects are directly involved in this recipe.
Process Overview
The code below creates a database cursor, decorates the cursor to make it capable of interacting with a data-aware control, and then exercises the decorated version of the cursor. As the decorated cursor is being manipulated it can notify data-aware control "listeners" about aspects of its state; the decorate cursor can also interrogate its listeners for advice about their states.
A typical listener would be a text editing control. The text editor would update its contents when informed by the cursor that the cursor has been repositioned. The cursor can also inquire of the text editor whether its content has been changed by the user, to decide whether the new contents should be posted to the database.
The heart of the decorator is its call method. Notice that it is only here that the decorator adds attributes to the underlying cursor that are used in co-operating with its associated data-aware controls. Some of the attributes are curried functions, so that all state information decorates the underlying cursor, rather than an instance of the decorator class.
Special Concerns
The code shows how to decorate a MySQL cursor object. However, other cursors can probably be handled in similar ways.
At this point the code is capable of interacting with a cursor that represents selected columns from a table and the table name must be declared explicitly.
Unfortunately the decorator does not hide all means of manipulating the cursor, which implies that the decorated cursor can be manipulated without its data-aware control listeners being informed. On the other hand, decoration of this kind makes all of the abilities of the underlying cursor available to the user, and these abilities could be incorporated into the decorator as necessary. In fact, one feature of this design is that, in some cases, the decorator can be subclassed without making the result very ugly.
The decorated cursor maintains a copy of the "current" record as part of the decoration--which is unfortunate. Overall, the database handling is somewhat naïve.
When this recipe is used with MySQL, in particular, the table that is accessed must have a primary index for the replaceRow method to work. (Otherwise, this method will create an additional row without deleting the original.)
Code Sample
1 from types import FloatType,IntType,LongType,StringType
2
3 class decorateCursor:
4 def replaceRow(self,cursor):
5 cursor . seek ( -1, 1 )
6 listenerValues={}
7 for listener in cursor.listeners:
8 fieldName,fieldValue=listener.reportValue()
9 if fieldName :
10 listenerValues[fieldName]=fieldValue
11 if cursor . row :
12 for fieldName in cursor.fieldNames:
13 if fieldName in listenerValues:
14 cursor.row[cursor.fieldNames.index(fieldName)]=listenerValues[fieldName]
15 sqlStatement="replace %s(%s) values(%s)" %(cursor.tableName,','.join(cursor.fieldNames),'%s')
16 fieldValues=[]
17 for fieldName in cursor.fieldNames:
18 fieldValue=cursor.field(fieldName)
19 if type(fieldValue) in [FloatType,IntType,LongType]:
20 fieldValues.append(str(fieldValue))
21 elif type(fieldValue)==StringType:
22 fieldValues.append("'%s'"%fieldValue)
23 elif fieldValue==None:
24 fieldValues.append('NULL')
25 else:
26 print fieldValue,type(fieldValue)
27 sqlStatement=sqlStatement%','.join(fieldValues)
28 cursor.execute(sqlStatement)
29
30 def getFieldNames(self, cursor):
31 return [fieldVector[0] for fieldVector in cursor.description]
32
33 def addListener(self,cursor,listener):
34 if not listener in cursor.listeners:
35 cursor.listeners.append(listener)
36 listener.notify()
37
38 def informListeners(self,cursor):
39 if cursor.informing:
40 for listener in cursor.listeners:
41 listener.notify()
42
43 def fetchRow(self,cursor):
44 cursor.row=list(cursor.fetchone())
45 cursor.informListeners ( )
46
47 def gotoBOF ( self, cursor ) :
48 cursor . seek ( 0 )
49 cursor . fetchRow ( )
50
51 def gotoEOF ( self, cursor ) :
52 cursor . seek ( cursor . rowcount - 1 )
53 cursor . fetchRow ( )
54
55 def gotoNext ( self, cursor ) :
56 if cursor . rownumber < cursor . rowcount :
57 cursor . fetchRow ( )
58
59 def gotoPrevious ( self, cursor ) :
60 if cursor . rownumber > 1 :
61 cursor . seek ( -2, 1 )
62 cursor . fetchRow ( )
63
64 def field(self,cursor,whichField):
65 if cursor . row == None :
66 return None
67 if type(whichField)==IntType:
68 return cursor.row[whichField]
69 elif type(whichField)==StringType:
70 return cursor.row[cursor.fieldNames.index(whichField)]
71 else:
72 raise "unrecognised field type (must be either string or integer)"
73
74 def anyListenerChanged(self,cursor):
75 return any(listener.changed() for listener in cursor.listeners)
76
77 class simpleCurry:
78 def __init__(self,func,cursor):
79 self.func=func
80 self.cursor=cursor
81
82 def __call__(self,*arg):
83 return self.func(self.cursor, *arg)
84
85 def __call__(self,cursor,tableName,informing=1,readonly=0):
86 cursor.row = None
87 cursor.tableName=tableName
88 cursor.readonly=readonly
89 cursor.listeners=[]
90 cursor.informing=informing
91 cursor.addListener=self.simpleCurry(self.addListener,cursor)
92 cursor.informListeners=self.simpleCurry(self.informListeners,cursor)
93 cursor.fetchRow=self.simpleCurry(self.fetchRow,cursor)
94 cursor . gotoBOF = self.simpleCurry ( self . gotoBOF, cursor )
95 cursor . gotoEOF = self.simpleCurry ( self . gotoEOF, cursor )
96 cursor . gotoNext = self . simpleCurry ( self . gotoNext, cursor )
97 cursor . gotoPrevious = self . simpleCurry ( self . gotoPrevious, cursor )
98 cursor.field=self.simpleCurry(self.field,cursor)
99 cursor.anyListenerChanged=self.simpleCurry(self.anyListenerChanged,cursor)
100 cursor.getFieldNames=self.simpleCurry(self.getFieldNames, cursor )
101 cursor.fieldNames = cursor.getFieldNames ( )
102 cursor.replaceRow=self.simpleCurry(self.replaceRow,cursor)
103 return cursor
104
105 if __name__ == "__main__" :
106
107 class dummyDataAwareControl:
108 def notify(self):
109 pass
110
111 def changed(self):
112 return 1
113
114 def reportValue(self):
115 return 'date','2010-01-01'
116
117 listener1=dummyDataAwareControl()
118
119 from MySQLdb import Connect
120
121 connection=Connect(db='test')
122 cursor=connection.cursor()
123 cursor.execute('select * from logger')
124
125 c=decorateCursor()(cursor,'logger')
126
127 c.addListener(listener1)
128
129 while c.rownumber < c.rowcount:
130 cursor.fetchRow()
131 for fieldName in c.fieldNames:
132 fieldValue=c.field(fieldName)
133 if type(fieldValue)==StringType:
134 print "%s: '%s'"% ( fieldName, fieldValue, ),
135 else:
136 print fieldName, fieldValue,
137 print
138
139 if c.anyListenerChanged():
140 c.replaceRow()
Comments
I plan to add yet another recipe that illustrates how to make this decorator work with data-aware controls created using a mixin (in another recipe). Any comment or concern, please write Bill Bell.