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 if self.cn is not None: self.cn.close() 238 self.cn = None
239
240 - def AddTable(self,tableName,colString):
241 """ adds a table to the database 242 243 **Arguments** 244 245 - tableName: the name of the table to add 246 247 - colString: a string containing column defintions 248 249 **Notes** 250 251 - if a table named _tableName_ already exists, it will be dropped 252 253 - the sqlQuery for addition is: "create table %(tableName) (%(colString))" 254 255 256 """ 257 c = self.GetCursor() 258 try: 259 c.execute('drop table %s cascade'%tableName) 260 except Exception: 261 try: 262 c.execute('drop table %s'%tableName) 263 except Exception: 264 pass 265 self.Commit() 266 267 addStr = 'create table %s (%s)'%(tableName,colString) 268 try: 269 c.execute(addStr) 270 except Exception: 271 import traceback 272 print('command failed:',addStr) 273 traceback.print_exc() 274 else: 275 self.Commit()
276 - def InsertData(self,tableName,vals):
277 """ inserts data into a table 278 279 **Arguments** 280 281 - tableName: the name of the table to manipulate 282 283 - vals: a sequence with the values to be inserted 284 285 """ 286 c = self.GetCursor() 287 if type(vals) != tuple: 288 vals = tuple(vals) 289 insTxt = '('+','.join([DbModule.placeHolder]*len(vals))+')' 290 #insTxt = '(%s'%('%s,'*len(vals)) 291 #insTxt = insTxt[0:-1]+')' 292 cmd = "insert into %s values %s"%(tableName,insTxt) 293 try: 294 c.execute(cmd,vals) 295 except Exception: 296 import traceback 297 print('insert failed:') 298 print(cmd) 299 print('the error was:') 300 traceback.print_exc() 301 raise DbError("Insert Failed")
302
303 - def InsertColumnData(self,tableName,columnName,value,where):
304 """ inserts data into a particular column of the table 305 306 **Arguments** 307 308 - tableName: the name of the table to manipulate 309 310 - columnName: name of the column to update 311 312 - value: the value to insert 313 314 - where: a query yielding the row where the data should be inserted 315 316 """ 317 c = self.GetCursor() 318 cmd = "update %s set %s=%s where %s"%(tableName,columnName, 319 DbModule.placeHolder,where) 320 c.execute(cmd,(value,))
321
322 - def AddColumn(self,tableName,colName,colType):
323 """ adds a column to a table 324 325 **Arguments** 326 327 - tableName: the name of the table to manipulate 328 329 - colName: name of the column to insert 330 331 - colType: the type of the column to add 332 333 """ 334 c = self.GetCursor() 335 try: 336 c.execute("alter table %s add %s %s"%(tableName,colName,colType)) 337 except Exception: 338 print('AddColumn failed')
339
340 - def Commit(self):
341 """ commits the current transaction 342 343 344 """ 345 self.cn.commit()
346