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

Source Code for Module rdkit.Dbase.DbConnection

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