Dbase.DbConnection

22 """ This class is intended to abstract away many of the details of 23 interacting with databases. 24 25 It includes some GUI functionality 26 27 """

28 - def __init__(self,dbName='',tableName='',user='sysdba',password='masterkey'):

29 """ Constructor 30 31 **Arguments** (all optional) 32 33 - dbName: the name of the DB file to be used 34 35 - tableName: the name of the table to be used 36 37 - user: the username for DB access 38 39 - password: the password to be used for DB access 40 41 42 """ 43 44 self.dbName = dbName 45 self.tableName = tableName 46 self.user = user 47 self.password = password 48 self.cn = None 49 self.cursor = None

50

51 - def UpdateTableNames(self,dlg):

52 """ Modifies a connect dialog to reflect new table names 53 54 **Arguments** 55 56 - dlg: the dialog to be updated 57 58 59 """ 60 self.user = self.userEntry.GetValue() 61 self.password = self.passwdEntry.GetValue() 62 self.dbName = self.dbBrowseButton.GetValue() 63 for i in xrange(self.dbTableChoice.Number()): 64 self.dbTableChoice.Delete(0) 65 66 names = self.GetTableNames() 67 68 for name in names: 69 self.dbTableChoice.Append(name) 70 dlg.sizer.Fit(dlg) 71 dlg.sizer.SetSizeHints(dlg) 72 dlg.Refresh()

73

74 - def GetTableNames(self,includeViews=0):

75 """ gets a list of tables available in a database 76 77 **Arguments** 78 79 - includeViews: if this is non-null, the views in the db will 80 also be returned 81 82 **Returns** 83 84 a list of table names 85 86 **Notes** 87 88 - this uses _DbInfo.GetTableNames_ 89 90 91 """ 92 return DbInfo.GetTableNames(self.dbName,self.user,self.password, 93 includeViews=includeViews,cn=self.cn)

94

95 - def GetColumnNames(self,table='',join='',what='*',where='',**kwargs):

96 """ gets a list of columns available in the current table 97 98 **Returns** 99 100 a list of column names 101 102 **Notes** 103 104 - this uses _DbInfo.GetColumnNames_ 105 106 107 """ 108 if not table: table = self.tableName 109 return DbInfo.GetColumnNames(self.dbName,table, 110 self.user,self.password, 111 join=join,what=what,cn=self.cn)

112 - def GetColumnNamesAndTypes(self,table='',join='',what='*',where='',**kwargs):

113 """ gets a list of columns available in the current table along with their types 114 115 **Returns** 116 117 a list of 2-tuples containing: 118 119 1) column name 120 121 2) column type 122 123 **Notes** 124 125 - this uses _DbInfo.GetColumnNamesAndTypes_ 126 127 128 """ 129 if not table: table = self.tableName 130 return DbInfo.GetColumnNamesAndTypes(self.dbName,table, 131 self.user,self.password, 132 join=join,what=what,cn=self.cn)

133 - def GetColumns(self,fields,table='',join='',**kwargs):

134 """ gets a set of data from a table 135 136 **Arguments** 137 138 - fields: a string with the names of the fields to be extracted, 139 this should be a comma delimited list 140 141 **Returns** 142 143 a list of the data 144 145 **Notes** 146 147 - this uses _DbUtils.GetColumns_ 148 149 """ 150 if not table: table = self.tableName 151 return DbUtils.GetColumns(self.dbName,table,fields, 152 self.user,self.password, 153 join=join)

154

155 - def GetData(self,table=None,fields='*',where='',removeDups=-1,join='', 156 transform=None,randomAccess=1,**kwargs):

157 """ a more flexible method to get a set of data from a table 158 159 **Arguments** 160 161 - table: (optional) the table to use 162 163 - fields: a string with the names of the fields to be extracted, 164 this should be a comma delimited list 165 166 - where: the SQL where clause to be used with the DB query 167 168 - removeDups: indicates which column should be used to recognize 169 duplicates in the data. -1 for no duplicate removal. 170 171 **Returns** 172 173 a list of the data 174 175 **Notes** 176 177 - this uses _DbUtils.GetData_ 178 179 180 """ 181 if table is None: 182 table = self.tableName 183 kwargs['forceList'] = kwargs.get('forceList',0) 184 return DbUtils.GetData(self.dbName,table,fieldString=fields,whereString=where, 185 user=self.user,password=self.password,removeDups=removeDups, 186 join=join,cn=self.cn, 187 transform=transform,randomAccess=randomAccess,**kwargs)

188

189 - def GetDataCount(self,table=None,where='',join='',**kwargs):

190 """ returns a count of the number of results a query will return 191 192 **Arguments** 193 194 - table: (optional) the table to use 195 196 - where: the SQL where clause to be used with the DB query 197 198 - join: the SQL join clause to be used with the DB query 199 200 201 **Returns** 202 203 an int 204 205 **Notes** 206 207 - this uses _DbUtils.GetData_ 208 209 """ 210 if table is None: 211 table = self.tableName 212 return DbUtils.GetData(self.dbName,table,fieldString='count(*)', 213 whereString=where,cn=self.cn, 214 user=self.user,password=self.password,join=join,forceList=0)[0][0]

215 216

217 - def GetCursor(self):

218 """ returns a cursor for direct manipulation of the DB 219 only one cursor is available 220 221 """ 222 if self.cursor is not None: 223 return self.cursor 224 225 self.cn = DbModule.connect(self.dbName,self.user,self.password) 226 self.cursor = self.cn.cursor() 227 return self.cursor

228

229 - def KillCursor(self):

230 """ closes the cursor 231 232 """ 233 self.cursor = None 234 self.cn = None

235

236 - def AddTable(self,tableName,colString):

237 """ adds a table to the database 238 239 **Arguments** 240 241 - tableName: the name of the table to add 242 243 - colString: a string containing column defintions 244 245 **Notes** 246 247 - if a table named _tableName_ already exists, it will be dropped 248 249 - the sqlQuery for addition is: "create table %(tableName) (%(colString))" 250 251 252 """ 253 c = self.GetCursor() 254 try: 255 c.execute('drop table %s cascade'%tableName) 256 except: 257 try: 258 c.execute('drop table %s'%tableName) 259 except: 260 pass 261 self.Commit() 262 263 addStr = 'create table %s (%s)'%(tableName,colString) 264 try: 265 c.execute(addStr) 266 except: 267 import traceback 268 print 'command failed:',addStr 269 traceback.print_exc() 270 else: 271 self.Commit()

272 - def InsertData(self,tableName,vals):

273 """ inserts data into a table 274 275 **Arguments** 276 277 - tableName: the name of the table to manipulate 278 279 - vals: a sequence with the values to be inserted 280 281 """ 282 c = self.GetCursor() 283 if type(vals) != types.TupleType: 284 vals = tuple(vals) 285 insTxt = '('+','.join([DbModule.placeHolder]*len(vals))+')' 286 #insTxt = '(%s'%('%s,'*len(vals)) 287 #insTxt = insTxt[0:-1]+')' 288 cmd = "insert into %s values %s"%(tableName,insTxt) 289 try: 290 c.execute(cmd,vals) 291 except: 292 import traceback 293 print 'insert failed:' 294 print cmd 295 print 'the error was:' 296 traceback.print_exc() 297 raise DbError,"Insert Failed"

298

299 - def InsertColumnData(self,tableName,columnName,value,where):

300 """ inserts data into a particular column of the table 301 302 **Arguments** 303 304 - tableName: the name of the table to manipulate 305 306 - columnName: name of the column to update 307 308 - value: the value to insert 309 310 - where: a query yielding the row where the data should be inserted 311 312 """ 313 c = self.GetCursor() 314 cmd = "update %s set %s=%s where %s"%(tableName,columnName, 315 DbModule.placeHolder,where) 316 c.execute(cmd,(value,))

317

318 - def AddColumn(self,tableName,colName,colType):

319 """ adds a column to a table 320 321 **Arguments** 322 323 - tableName: the name of the table to manipulate 324 325 - colName: name of the column to insert 326 327 - colType: the type of the column to add 328 329 """ 330 c = self.GetCursor() 331 try: 332 c.execute("alter table %s add %s %s"%(tableName,colName,colType)) 333 except: 334 print 'AddColumn failed'

335

336 - def Commit(self):

337 """ commits the current transaction 338 339 340 """ 341 self.cn.commit()

Source Code for Module Dbase.DbConnection