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