Mercurial > sqlpython
annotate sqlpython/sqlpython.py @ 198:b2d8bf5f89db
merged with changes from work
author | catherine@Elli.myhome.westell.com |
---|---|
date | Tue, 02 Dec 2008 11:00:21 -0500 |
parents | 3af1615e6ea8 |
children | 3ca2271847e2 |
rev | line source |
---|---|
189 | 1 # |
2 # SqlPython V1.5.0 | |
3 # Author: Luca.Canali@cern.ch, Apr 2006 | |
4 # Rev 29-May-08 | |
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 cmd2,getpass,binascii,cx_Oracle,re,os | |
12 import sqlpyPlus | |
13 __version__ = '1.5.0' | |
14 # complication! separate sessions -> | |
15 # separate transactions !!!!! | |
16 # also: timeouts, other session failures | |
17 | |
18 class sqlpython(cmd2.Cmd): | |
19 '''A python module to reproduce Oracle's command line with focus on customization and extention''' | |
20 | |
21 def __init__(self): | |
22 cmd2.Cmd.__init__(self) | |
23 self.prompt = 'SQL.No_Connection> ' | |
24 self.maxfetch = 1000 | |
25 self.terminator = ';' | |
26 self.timeout = 30 | |
27 self.commit_on_exit = True | |
28 | |
29 connection_modes = {re.compile(' AS SYSDBA', re.IGNORECASE): cx_Oracle.SYSDBA, | |
30 re.compile(' AS SYSOPER', re.IGNORECASE): cx_Oracle.SYSOPER} | |
31 def do_connect(self, arg): | |
32 '''Opens the DB connection''' | |
33 modeval = 0 | |
34 for modere, modevalue in self.connection_modes.items(): | |
35 if modere.search(arg): | |
36 arg = modere.sub('', arg) | |
37 modeval = modevalue | |
38 try: | |
39 orauser, oraserv = arg.split('@') | |
40 except ValueError: | |
41 try: | |
42 oraserv = os.environ['ORACLE_SID'] | |
43 except KeyError: | |
44 print 'instance not specified and environment variable ORACLE_SID not set' | |
45 return | |
46 orauser = arg | |
47 sid = oraserv | |
48 try: | |
49 host, sid = oraserv.split('/') | |
50 try: | |
51 host, port = host.split(':') | |
52 port = int(port) | |
53 except ValueError: | |
54 port = 1521 | |
55 oraserv = cx_Oracle.makedsn(host, port, sid) | |
56 except ValueError: | |
57 pass | |
58 try: | |
59 orauser, orapass = orauser.split('/') | |
60 except ValueError: | |
61 orapass = getpass.getpass('Password: ') | |
62 if orauser.upper() == 'SYS' and not modeval: | |
63 print 'Privilege not specified for SYS, assuming SYSOPER' | |
64 modeval = cx_Oracle.SYSOPER | |
65 try: | |
66 self.orcl = cx_Oracle.connect(orauser,orapass,oraserv,modeval) | |
67 self.curs = self.orcl.cursor() | |
68 self.prompt = '%s@%s> ' % (orauser, sid) | |
69 except Exception, e: | |
70 print e | |
71 | |
72 | |
73 def emptyline(self): | |
74 pass | |
75 | |
76 def do_terminators(self, arg): | |
77 """; standard Oracle format | |
78 \\c CSV (with headings) | |
79 \\C CSV (no headings) | |
80 \\g list | |
81 \\G aligned list | |
82 \\h HTML table | |
83 \\i INSERT statements | |
84 \\s CSV (with headings) | |
85 \\S CSV (no headings) | |
86 \\t transposed | |
87 \\x XML | |
88 \\l line plot, with markers | |
89 \\L scatter plot (no lines) | |
90 \\b bar graph | |
91 \\p pie chart""" | |
92 print self.do_terminators.__doc__ | |
93 | |
94 terminatorSearchString = '|'.join('\\' + d.split()[0] for d in do_terminators.__doc__.splitlines()) | |
95 | |
96 def default(self, arg): | |
198
b2d8bf5f89db
merged with changes from work
catherine@Elli.myhome.westell.com
parents:
197
diff
changeset
|
97 self.varsUsed = sqlpyPlus.findBinds(arg, self.binds, givenBindVars={}) |
b2d8bf5f89db
merged with changes from work
catherine@Elli.myhome.westell.com
parents:
197
diff
changeset
|
98 self.curs.execute('%s %s' % (arg.parsed.command, arg.parsed.args), self.varsUsed) |
189 | 99 print '\nExecuted%s\n' % ((self.curs.rowcount > 0) and ' (%d rows)' % self.curs.rowcount or '') |
198
b2d8bf5f89db
merged with changes from work
catherine@Elli.myhome.westell.com
parents:
197
diff
changeset
|
100 |
b2d8bf5f89db
merged with changes from work
catherine@Elli.myhome.westell.com
parents:
197
diff
changeset
|
101 def do_commit(self, arg=''): |
b2d8bf5f89db
merged with changes from work
catherine@Elli.myhome.westell.com
parents:
197
diff
changeset
|
102 self.default(self.parsed('commit %s;' % (arg))) |
b2d8bf5f89db
merged with changes from work
catherine@Elli.myhome.westell.com
parents:
197
diff
changeset
|
103 def do_rollback(self, arg=''): |
b2d8bf5f89db
merged with changes from work
catherine@Elli.myhome.westell.com
parents:
197
diff
changeset
|
104 self.default(self.parsed('rollback %s;' % (arg))) |
189 | 105 def do_quit(self, arg): |
106 if self.commit_on_exit and hasattr(self, 'curs'): | |
197 | 107 self.default(self.parsed('commit')) |
189 | 108 return cmd2.Cmd.do_quit(self, None) |
109 do_exit = do_quit | |
110 do_q = do_quit | |
111 | |
112 def pmatrix(rows,desc,maxlen=30): | |
113 '''prints a matrix, used by sqlpython to print queries' result sets''' | |
114 names = [] | |
115 maxen = [] | |
116 toprint = [] | |
117 for d in desc: | |
118 n = d[0] | |
119 names.append(n) # list col names | |
120 maxen.append(len(n)) # col length | |
121 rcols = range(len(desc)) | |
122 rrows = range(len(rows)) | |
123 for i in rrows: # loops for all rows | |
124 rowsi = map(str, rows[i]) # current row to process | |
125 split = [] # service var is row split is needed | |
126 mustsplit = 0 # flag | |
127 for j in rcols: | |
128 if str(desc[j][1]) == "<type 'cx_Oracle.BINARY'>": # handles RAW columns | |
129 rowsi[j] = binascii.b2a_hex(rowsi[j]) | |
130 maxen[j] = max(maxen[j], len(rowsi[j])) # computes max field length | |
131 if maxen[j] <= maxlen: | |
132 split.append('') | |
133 else: # split the line is 2 because field is too long | |
134 mustsplit = 1 | |
135 maxen[j] = maxlen | |
136 split.append(rowsi[j][maxlen-1:2*maxlen-1]) | |
137 rowsi[j] = rowsi[j][0:maxlen-1] # this implem. truncates after maxlen*2 | |
138 toprint.append(rowsi) # 'toprint' is a printable copy of rows | |
139 if mustsplit != 0: | |
140 toprint.append(split) | |
141 sepcols = [] | |
142 for i in rcols: | |
143 maxcol = maxen[i] | |
144 name = names[i] | |
145 sepcols.append("-" * maxcol) # formats column names (header) | |
146 names[i] = name + (" " * (maxcol-len(name))) # formats separ line with -- | |
147 rrows2 = range(len(toprint)) | |
148 for j in rrows2: | |
149 val = toprint[j][i] | |
150 if str(desc[i][1]) == "<type 'cx_Oracle.NUMBER'>": # right align numbers | |
151 toprint[j][i] = (" " * (maxcol-len(val))) + val | |
152 else: | |
153 toprint[j][i] = val + (" " * (maxcol-len(val))) | |
154 for j in rrows2: | |
155 toprint[j] = ' '.join(toprint[j]) | |
156 names = ' '.join(names) | |
157 sepcols = ' '.join(sepcols) | |
158 toprint.insert(0, sepcols) | |
159 toprint.insert(0, names) | |
160 return '\n'.join(toprint) | |
161 |