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