Package rdkit :: Package Dbase :: Module DbInfo

[hide private]
[frames] | no frames]

# Source Code for Module rdkit.Dbase.DbInfo

      1  # $Id$
      2  #
      3  #  Copyright (C) 2003-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  from __future__ import print_function
     12  import sys
     13
     14  from rdkit import RDConfig
     15  from rdkit.Dbase import DbModule
     16
     17  sqlTextTypes = DbModule.sqlTextTypes
     18  sqlIntTypes = DbModule.sqlIntTypes
     19  sqlFloatTypes = DbModule.sqlFloatTypes
     20  sqlBinTypes = DbModule.sqlBinTypes
     21
     22
     23 -def GetDbNames(user='sysdba',password='masterkey',dirName='.',dBase='::template1',cn=None):
     24    """ returns a list of databases that are available
     25
     26      **Arguments**
     27
     28        - user: the username for DB access
     29
     30        - password: the password to be used for DB access
     31
     32      **Returns**
     33
     34        - a list of db names (strings)
     35
     36    """
     37    if DbModule.getDbSql:
     38      if not cn:
     39        try:
     40          cn = DbModule.connect(dBase,user,password)
     41        except Exception:
     42          print('Problems opening database: %s'%(dBase))
     43          return []
     44      c = cn.cursor()
     45      c.execute(DbModule.getDbSql)
     46      if RDConfig.usePgSQL:
     47        names = ['::'+str(x[0]) for x in c.fetchall()]
     48      else:
     49        names = ['::'+str(x[0]) for x in c.fetchall()]
     50      names.remove(dBase)
     51    elif DbModule.fileWildcard:
     52      import os.path,glob
     53      names = glob.glob(os.path.join(dirName,DbModule.fileWildcard))
     54    else:
     55      names = []
     56    return names
     57
     58
     59 -def GetTableNames(dBase,user='sysdba',password='masterkey',
     60                    includeViews=0,cn=None):
     61    """ returns a list of tables available in a database
     62
     63      **Arguments**
     64
     65        - dBase: the name of the DB file to be used
     66
     67        - user: the username for DB access
     68
     69        - password: the password to be used for DB access
     70
     71        - includeViews: if this is non-null, the views in the db will
     72          also be returned
     73
     74      **Returns**
     75
     76        - a list of table names (strings)
     77
     78    """
     79    if not cn:
     80      try:
     81        cn = DbModule.connect(dBase,user,password)
     82      except Exception:
     83        print('Problems opening database: %s'%(dBase))
     84        return []
     85    c = cn.cursor()
     86    if not includeViews:
     87      comm = DbModule.getTablesSql
     88    else:
     89      comm = DbModule.getTablesAndViewsSql
     90    c.execute(comm)
     91    names = [str(x[0]).upper() for x in c.fetchall()]
     92    if RDConfig.usePgSQL and 'PG_LOGDIR_LS' in names:
     93      names.remove('PG_LOGDIR_LS')
     94    return names
     95
     96
     97
     98 -def GetColumnInfoFromCursor(cursor):
     99    if cursor is None or cursor.description is None: return []
    100    results = []
    101    if not RDConfig.useSqlLite:
    102      for item in cursor.description:
    103        cName = item[0]
    104        cType = item[1]
    105        if cType in sqlTextTypes:
    106          typeStr='string'
    107        elif cType in sqlIntTypes:
    108          typeStr='integer'
    109        elif cType in sqlFloatTypes:
    110          typeStr='float'
    111        elif cType in sqlBinTypes:
    112          typeStr='binary'
    113        else:
    114          sys.stderr.write('odd type in col %s: %s\n'%(cName,str(cType)))
    115        results.append((cName,typeStr))
    116    else:
    117      from rdkit.six import PY2, PY3
    118      r = cursor.fetchone()
    119      if not r: return results
    120      for i,v in enumerate(r):
    121        cName = cursor.description[i][0]
    122        typ = type(v)
    123        if typ == str or (PY2 and typ == unicode):
    124          typeStr='string'
    125        elif typ == int:
    126          typeStr='integer'
    127        elif typ == float:
    128          typeStr='float'
    129        elif (PY2 and typ == buffer) or (PY3 and typ in (memoryview, bytes)):
    130          typeStr='binary'
    131        else:
    132          sys.stderr.write('odd type in col %s: %s\n'%(cName,typ))
    133        results.append((cName,typeStr))
    134    return results
    135
    136 -def GetColumnNamesAndTypes(dBase,table,
    137                             user='sysdba',password='masterkey',
    138                             join='',what='*',cn=None):
    139    """ gets a list of columns available in a DB table along with their types
    140
    141      **Arguments**
    142
    143        - dBase: the name of the DB file to be used
    144
    145        - table: the name of the table to query
    146
    147        - user: the username for DB access
    148
    149        - password: the password to be used for DB access
    150
    151        - join: an optional join clause (omit the verb 'join')
    152
    153        - what: an optional clause indicating what to select
    154
    155      **Returns**
    156
    157        - a list of 2-tuples containing:
    158
    159            1) column name
    160
    161            2) column type
    162
    163    """
    164    if not cn:
    165      cn = DbModule.connect(dBase,user,password)
    166    c = cn.cursor()
    167    cmd = 'select %s from %s'%(what,table)
    168    if join:
    169      cmd += ' join %s'%(join)
    170    c.execute(cmd)
    171    return GetColumnInfoFromCursor(c)
    172
    173 -def GetColumnNames(dBase,table,user='sysdba',password='masterkey',
    174                     join='',what='*',cn=None):
    175    """ gets a list of columns available in a DB table
    176
    177      **Arguments**
    178
    179        - dBase: the name of the DB file to be used
    180
    181        - table: the name of the table to query
    182
    183        - user: the username for DB access
    184
    185        - password: the password to be used for DB access
    186
    187        - join: an optional join clause  (omit the verb 'join')
    188
    189        - what: an optional clause indicating what to select
    190
    191      **Returns**
    192
    193        -  a list of column names
    194
    195    """
    196    if not cn:
    197      cn = DbModule.connect(dBase,user,password)
    198    c = cn.cursor()
    199    cmd = 'select %s from %s'%(what,table)
    200    if join:
    201      if join.strip().find('join') != 0:
    202        join = 'join %s'%(join)
    203      cmd +=' ' + join
    204    c.execute(cmd)
    205    c.fetchone()
    206    desc = c.description
    207    res = [str(x[0]) for x in desc]
    208    return res
    209