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