Package rdkit :: Package Dbase :: Module DbConnection
[hide private]
[frames] | no frames]

Source Code for Module rdkit.Dbase.DbConnection

  1  # $Id$ 
  2  # 
  3  #  Copyright (C) 2000-2006  greg Landrum and Rational Discovery LLC 
  4  # 
  5  #   @@ All Rights Reserved @@ 
  6  #  This file is part of the RDKit. 
  7  #  The contents are covered by the terms of the BSD license 
  8  #  which is included in the file license.txt, found at the root 
  9  #  of the RDKit source tree. 
 10  # 
 11  """ defines class _DbConnect_, for abstracting connections to databases 
 12   
 13  """ 
 14  from __future__ import print_function 
 15  from rdkit import RDConfig 
 16  import sys,types 
 17   
18 -class DbError(RuntimeError):
19 pass
20 21 from rdkit.Dbase import DbUtils,DbInfo,DbModule 22 23
24 -class DbConnect(object):
25 """ This class is intended to abstract away many of the details of 26 interacting with databases. 27 28 It includes some GUI functionality 29 30 """
31 - def __init__(self,dbName='',tableName='',user='sysdba',password='masterkey'):
32 """ Constructor 33 34 **Arguments** (all optional) 35 36 - dbName: the name of the DB file to be used 37 38 - tableName: the name of the table to be used 39 40 - user: the username for DB access 41 42 - password: the password to be used for DB access 43 44 45 """ 46 47 self.dbName = dbName 48 self.tableName = tableName 49 self.user = user 50 self.password = password 51 self.cn = None 52 self.cursor = None
53
54 - def UpdateTableNames(self,dlg):
55 """ Modifies a connect dialog to reflect new table names 56 57 **Arguments** 58 59 - dlg: the dialog to be updated 60 61 62 """ 63 self.user = self.userEntry.GetValue() 64 self.password = self.passwdEntry.GetValue() 65 self.dbName = self.dbBrowseButton.GetValue() 66 for i in xrange(self.dbTableChoice.Number()): 67 self.dbTableChoice.Delete(0) 68 69 names = self.GetTableNames() 70 71 for name in names: 72 self.dbTableChoice.Append(name) 73 dlg.sizer.Fit(dlg) 74 dlg.sizer.SetSizeHints(dlg) 75 dlg.Refresh()
76
77 - def GetTableNames(self,includeViews=0):
78 """ gets a list of tables available in a database 79 80 **Arguments** 81 82 - includeViews: if this is non-null, the views in the db will 83 also be returned 84 85 **Returns** 86 87 a list of table names 88 89 **Notes** 90 91 - this uses _DbInfo.GetTableNames_ 92 93 94 """ 95 return DbInfo.GetTableNames(self.dbName,self.user,self.password, 96 includeViews=includeViews,cn=self.cn)
97
98 - def GetColumnNames(self,table='',join='',what='*',where='',**kwargs):
99 """ gets a list of columns available in the current table 100 101 **Returns** 102 103 a list of column names 104 105 **Notes** 106 107 - this uses _DbInfo.GetColumnNames_ 108 109 110 """ 111 if not table: table = self.tableName 112 return DbInfo.GetColumnNames(self.dbName,table, 113 self.user,self.password, 114 join=join,what=what,cn=self.cn)
115 - def GetColumnNamesAndTypes(self,table='',join='',what='*',where='',**kwargs):
116 """ gets a list of columns available in the current table along with their types 117 118 **Returns** 119 120 a list of 2-tuples containing: 121 122 1) column name 123 124 2) column type 125 126 **Notes** 127 128 - this uses _DbInfo.GetColumnNamesAndTypes_ 129 130 131 """ 132 if not table: table = self.tableName 133 return DbInfo.GetColumnNamesAndTypes(self.dbName,table, 134 self.user,self.password, 135 join=join,what=what,cn=self.cn)
136 - def GetColumns(self,fields,table='',join='',**kwargs):
137 """ gets a set of data from a table 138 139 **Arguments** 140 141 - fields: a string with the names of the fields to be extracted, 142 this should be a comma delimited list 143 144 **Returns** 145 146 a list of the data 147 148 **Notes** 149 150 - this uses _DbUtils.GetColumns_ 151 152 """ 153 if not table: table = self.tableName 154 return DbUtils.GetColumns(self.dbName,table,fields, 155 self.user,self.password, 156 join=join)
157
158 - def GetData(self,table=None,fields='*',where='',removeDups=-1,join='', 159 transform=None,randomAccess=1,**kwargs):
160 """ a more flexible method to get a set of data from a table 161 162 **Arguments** 163 164 - table: (optional) the table to use 165 166 - fields: a string with the names of the fields to be extracted, 167 this should be a comma delimited list 168 169 - where: the SQL where clause to be used with the DB query 170 171 - removeDups: indicates which column should be used to recognize 172 duplicates in the data. -1 for no duplicate removal. 173 174 **Returns** 175 176 a list of the data 177 178 **Notes** 179 180 - this uses _DbUtils.GetData_ 181 182 183 """ 184 if table is None: 185 table = self.tableName 186 kwargs['forceList'] = kwargs.get('forceList',0) 187 return DbUtils.GetData(self.dbName,table,fieldString=fields,whereString=where, 188 user=self.user,password=self.password,removeDups=removeDups, 189 join=join,cn=self.cn, 190 transform=transform,randomAccess=randomAccess,**kwargs)
191
192 - def GetDataCount(self,table=None,where='',join='',**kwargs):
193 """ returns a count of the number of results a query will return 194 195 **Arguments** 196 197 - table: (optional) the table to use 198 199 - where: the SQL where clause to be used with the DB query 200 201 - join: the SQL join clause to be used with the DB query 202 203 204 **Returns** 205 206 an int 207 208 **Notes** 209 210 - this uses _DbUtils.GetData_ 211 212 """ 213 if table is None: 214 table = self.tableName 215 return DbUtils.GetData(self.dbName,table,fieldString='count(*)', 216 whereString=where,cn=self.cn, 217 user=self.user,password=self.password,join=join,forceList=0)[0][0]
218 219
220 - def GetCursor(self):
221 """ returns a cursor for direct manipulation of the DB 222 only one cursor is available 223 224 """ 225 if self.cursor is not None: 226 return self.cursor 227 228 self.cn = DbModule.connect(self.dbName,self.user,self.password) 229 self.cursor = self.cn.cursor() 230 return self.cursor
231
232 - def KillCursor(self):
233 """ closes the cursor 234 235 """ 236 self.cursor = None 237 self.cn = None
238
239 - def AddTable(self,tableName,colString):
240 """ adds a table to the database 241 242 **Arguments** 243 244 - tableName: the name of the table to add 245 246 - colString: a string containing column defintions 247 248 **Notes** 249 250 - if a table named _tableName_ already exists, it will be dropped 251 252 - the sqlQuery for addition is: "create table %(tableName) (%(colString))" 253 254 255 """ 256 c = self.GetCursor() 257 try: 258 c.execute('drop table %s cascade'%tableName) 259 except: 260 try: 261 c.execute('drop table %s'%tableName) 262 except: 263 pass 264 self.Commit() 265 266 addStr = 'create table %s (%s)'%(tableName,colString) 267 try: 268 c.execute(addStr) 269 except: 270 import traceback 271 print('command failed:',addStr) 272 traceback.print_exc() 273 else: 274 self.Commit()
275 - def InsertData(self,tableName,vals):
276 """ inserts data into a table 277 278 **Arguments** 279 280 - tableName: the name of the table to manipulate 281 282 - vals: a sequence with the values to be inserted 283 284 """ 285 c = self.GetCursor() 286 if type(vals) != types.TupleType: 287 vals = tuple(vals) 288 insTxt = '('+','.join([DbModule.placeHolder]*len(vals))+')' 289 #insTxt = '(%s'%('%s,'*len(vals)) 290 #insTxt = insTxt[0:-1]+')' 291 cmd = "insert into %s values %s"%(tableName,insTxt) 292 try: 293 c.execute(cmd,vals) 294 except: 295 import traceback 296 print('insert failed:') 297 print(cmd) 298 print('the error was:') 299 traceback.print_exc() 300 raise DbError("Insert Failed")
301
302 - def InsertColumnData(self,tableName,columnName,value,where):
303 """ inserts data into a particular column of the table 304 305 **Arguments** 306 307 - tableName: the name of the table to manipulate 308 309 - columnName: name of the column to update 310 311 - value: the value to insert 312 313 - where: a query yielding the row where the data should be inserted 314 315 """ 316 c = self.GetCursor() 317 cmd = "update %s set %s=%s where %s"%(tableName,columnName, 318 DbModule.placeHolder,where) 319 c.execute(cmd,(value,))
320
321 - def AddColumn(self,tableName,colName,colType):
322 """ adds a column to a table 323 324 **Arguments** 325 326 - tableName: the name of the table to manipulate 327 328 - colName: name of the column to insert 329 330 - colType: the type of the column to add 331 332 """ 333 c = self.GetCursor() 334 try: 335 c.execute("alter table %s add %s %s"%(tableName,colName,colType)) 336 except: 337 print('AddColumn failed')
338
339 - def Commit(self):
340 """ commits the current transaction 341 342 343 """ 344 self.cn.commit()
345