1
2
3
4
5
6
7
8
9
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
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