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 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
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