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