|
0
|
1 #
|
|
|
2 # SqlPython V1.3
|
|
|
3 # Author: Luca.Canali@cern.ch, Apr 2006
|
|
|
4 # Rev 18-Oct-07
|
|
|
5 #
|
|
|
6 # A python module to reproduce Oracle's command line 'sqlplus-like' within python
|
|
|
7 # Intended to allow easy customizations and extentions
|
|
|
8 # Best used with the companion modules sqlpyPlus and mysqlpy
|
|
|
9 # See also http://twiki.cern.ch/twiki/bin/view/PSSGroup/SqlPython
|
|
|
10
|
|
|
11 import cmd,getpass,binascii,cx_Oracle,re
|
|
|
12 import pexpecter
|
|
|
13
|
|
|
14 # complication! separate sessions ->
|
|
|
15 # separate transactions !!!!!
|
|
|
16 # also: timeouts, other session failures
|
|
|
17
|
|
|
18 class sqlpython(cmd.Cmd):
|
|
|
19 '''A python module to reproduce Oracle's command line with focus on customization and extention'''
|
|
|
20
|
|
|
21 def __init__(self):
|
|
|
22 cmd.Cmd.__init__(self)
|
|
|
23 self.prompt = 'SQL.No_Connection> '
|
|
|
24 self.maxfetch = 1000
|
|
|
25 self.failoverSessions = []
|
|
|
26 self.terminator = ';'
|
|
|
27 self.timeout = 30
|
|
|
28
|
|
|
29 def do_connect(self, arg):
|
|
|
30 '''Opens the DB connection'''
|
|
|
31 try:
|
|
|
32 if arg.find('/') == -1:
|
|
|
33 orapass = getpass.getpass('Password: ')
|
|
|
34 orauser = arg.split('@')[0]
|
|
|
35 oraserv = arg.split('@')[1]
|
|
|
36 self.orcl = cx_Oracle.connect(orauser,orapass,oraserv)
|
|
|
37 arg = '%s/%s@%s' % (orauser, orapass, oraserv)
|
|
|
38 else:
|
|
|
39 self.orcl = cx_Oracle.connect(arg)
|
|
|
40 self.curs = self.orcl.cursor()
|
|
|
41 self.prompt = 'SQL.'+self.orcl.tnsentry+'> '
|
|
|
42 self.failoverSessions = [f for f in [fbs(arg) for fbs in pexpecter.available] if f.available]
|
|
|
43
|
|
|
44 except Exception, e:
|
|
|
45 print e
|
|
|
46
|
|
|
47 def emptyline(self):
|
|
|
48 pass
|
|
|
49
|
|
|
50 def do_quit(self, arg):
|
|
|
51 return 1
|
|
|
52
|
|
|
53 def fail(self, arg, do_everywhere=False):
|
|
|
54 if self.failover:
|
|
|
55 success, result = False, ''
|
|
|
56 for fbs in self.failoverSessions:
|
|
|
57 success, result = fbs.attempt(arg)
|
|
|
58 if success:
|
|
|
59 print result
|
|
|
60 if not do_everywhere:
|
|
|
61 return True
|
|
|
62 print result
|
|
|
63 return False
|
|
|
64
|
|
|
65 def designated_session(self, arg, sesstype):
|
|
|
66 for fbs in self.failoverSessions:
|
|
|
67 if fbs.valid and fbs.__class__ == sesstype:
|
|
|
68 success, result = fbs.attempt(arg)
|
|
|
69 print result
|
|
|
70 return
|
|
|
71 print 'Valid %s not found' % (sesstype.__name__)
|
|
|
72
|
|
|
73 def do_terminators(self, arg):
|
|
|
74 """; standard Oracle format
|
|
|
75 \\c CSV (with headings)
|
|
|
76 \\C CSV (no headings)
|
|
|
77 \\g list
|
|
|
78 \\G aligned list
|
|
|
79 \\h HTML table
|
|
|
80 \\i INSERT statements
|
|
|
81 \\s CSV (with headings)
|
|
|
82 \\S CSV (no headings)
|
|
|
83 \\t transposed
|
|
|
84 \\x XML"""
|
|
|
85 print self.do_terminators.__doc__
|
|
|
86
|
|
|
87 terminatorSearchString = '|'.join('\\' + d.split()[0] for d in do_terminators.__doc__.splitlines())
|
|
|
88
|
|
|
89 def do_yasql(self, arg):
|
|
|
90 '''Sends a command to a YASQL session (http://sourceforge.net/projects/yasql/)'''
|
|
|
91 self.designated_session(arg, pexpecter.YASQLSession)
|
|
|
92 do_y = do_yasql
|
|
|
93 def do_sqlplus(self, arg):
|
|
|
94 '''Sends a command to a SQL*Plus session'''
|
|
|
95 self.designated_session(arg, pexpecter.SqlPlusSession)
|
|
|
96 do_sqlp = do_sqlplus
|
|
|
97 def do_senora(self, arg):
|
|
|
98 '''Sends a command to a Senora session (http://senora.sourceforge.net/)'''
|
|
|
99 self.designated_session(arg, pexpecter.SenoraSession)
|
|
|
100 do_sen = do_senora
|
|
|
101
|
|
|
102 def default(self, arg, do_everywhere = False):
|
|
|
103 self.query = Statement(arg).query
|
|
|
104 try:
|
|
|
105 self.curs.execute(self.query)
|
|
|
106 print '\nExecuted\n'
|
|
|
107 if do_everywhere:
|
|
|
108 self.fail(arg, do_everywhere = True )
|
|
|
109 except Exception, e:
|
|
|
110 result = self.fail(arg)
|
|
|
111 if not result:
|
|
|
112 print str(e)
|
|
|
113
|
|
|
114 def do_commit(self, arg):
|
|
|
115 self.default('commit %s' % (arg), do_everywhere=True)
|
|
|
116 def do_rollback(self, arg):
|
|
|
117 self.default('rollback %s' % (arg), do_everywhere=True)
|
|
|
118
|
|
|
119 # shortcuts
|
|
|
120 do_q = do_quit
|
|
|
121 do_exit = do_quit
|
|
|
122
|
|
|
123 class Statement(object):
|
|
|
124 prompt2 = ' > '
|
|
|
125 stmtEndSearchString = r'(.*)(%s)\s*(\d+)?\s*$' % sqlpython.terminatorSearchString
|
|
|
126 stmtEnd = re.compile(stmtEndSearchString, re.MULTILINE | re.DOTALL)
|
|
|
127 def __init__(self, firstline):
|
|
|
128 v_Lines = []
|
|
|
129 v_Line = firstline
|
|
|
130 while 1:
|
|
|
131 m = self.stmtEnd.search(v_Line)
|
|
|
132 if m:
|
|
|
133 v_Line, self.outformat, suffix = m.groups()
|
|
|
134 v_Lines.append(v_Line)
|
|
|
135 self.query = '\n'.join(v_Lines)
|
|
|
136 self.rowlimit = int(suffix or 0)
|
|
|
137 return
|
|
|
138 v_Lines.append(v_Line)
|
|
|
139 v_Line = raw_input(self.prompt2)
|
|
|
140 self.query = '\n'.join(v_Lines)
|
|
|
141
|
|
|
142 def pmatrix(rows,desc,maxlen=30):
|
|
|
143 '''prints a matrix, used by sqlpython to print queries' result sets'''
|
|
|
144 names = []
|
|
|
145 maxen = []
|
|
|
146 toprint = []
|
|
|
147 for d in desc:
|
|
|
148 n = d[0]
|
|
|
149 names.append(n) # list col names
|
|
|
150 maxen.append(len(n)) # col length
|
|
|
151 rcols = range(len(desc))
|
|
|
152 rrows = range(len(rows))
|
|
|
153 for i in rrows: # loops for all rows
|
|
|
154 rowsi = map(str, rows[i]) # current row to process
|
|
|
155 split = [] # service var is row split is needed
|
|
|
156 mustsplit = 0 # flag
|
|
|
157 for j in rcols:
|
|
|
158 if str(desc[j][1]) == "<type 'cx_Oracle.BINARY'>": # handles RAW columns
|
|
|
159 rowsi[j] = binascii.b2a_hex(rowsi[j])
|
|
|
160 maxen[j] = max(maxen[j], len(rowsi[j])) # computes max field length
|
|
|
161 if maxen[j] <= maxlen:
|
|
|
162 split.append('')
|
|
|
163 else: # split the line is 2 because field is too long
|
|
|
164 mustsplit = 1
|
|
|
165 maxen[j] = maxlen
|
|
|
166 split.append(rowsi[j][maxlen-1:2*maxlen-1])
|
|
|
167 rowsi[j] = rowsi[j][0:maxlen-1] # this implem. truncates after maxlen*2
|
|
|
168 toprint.append(rowsi) # 'toprint' is a printable copy of rows
|
|
|
169 if mustsplit != 0:
|
|
|
170 toprint.append(split)
|
|
|
171 sepcols = []
|
|
|
172 for i in rcols:
|
|
|
173 maxcol = maxen[i]
|
|
|
174 name = names[i]
|
|
|
175 sepcols.append("-" * maxcol) # formats column names (header)
|
|
|
176 names[i] = name + (" " * (maxcol-len(name))) # formats separ line with --
|
|
|
177 rrows2 = range(len(toprint))
|
|
|
178 for j in rrows2:
|
|
|
179 val = toprint[j][i]
|
|
|
180 if str(desc[i][1]) == "<type 'cx_Oracle.NUMBER'>": # right align numbers
|
|
|
181 toprint[j][i] = (" " * (maxcol-len(val))) + val
|
|
|
182 else:
|
|
|
183 toprint[j][i] = val + (" " * (maxcol-len(val)))
|
|
|
184 for j in rrows2:
|
|
|
185 toprint[j] = ' '.join(toprint[j])
|
|
|
186 names = ' '.join(names)
|
|
|
187 sepcols = ' '.join(sepcols)
|
|
|
188 toprint.insert(0, sepcols)
|
|
|
189 toprint.insert(0, names)
|
|
|
190 return '\n'.join(toprint)
|
|
|
191
|
