Source Code for Module Dbase.DbConnection

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