Mercurial > sqlpython

comparison sqlpython.py @ 0:9c87fa772ec1

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
before big refactor
author catherine@serenity.wpafb.af.mil
date Fri, 30 Nov 2007 13:04:51 -0500
parents
children 8fa146b9a2d7
comparison
equal deleted inserted replaced
-1:000000000000 0:9c87fa772ec1
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