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