Mercurial > sqlpython
annotate sqlpython/sqlpython.py @ 358:cdd403e73132
v 1.6.6
author | catherine@cordelia |
---|---|
date | Thu, 28 May 2009 17:37:20 -0400 |
parents | 9960bece1e88 |
children | 477f0bf652b2 |
rev | line source |
---|---|
189 | 1 # |
358 | 2 # SqlPython V1.6.6 |
189 | 3 # Author: Luca.Canali@cern.ch, Apr 2006 |
357 | 4 # Rev 1-May-09 |
189 | 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 | |
339
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
12 import sqlpyPlus, sqlalchemy, pyparsing |
358 | 13 __version__ = '1.6.6' |
189 | 14 |
339
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
15 class Parser(object): |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
16 comment_def = "--" + ~ ('-' + pyparsing.CaselessKeyword('begin')) + pyparsing.ZeroOrMore(pyparsing.CharsNotIn("\n")) |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
17 def __init__(self, scanner, retainSeparator=True): |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
18 self.scanner = scanner |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
19 self.scanner.ignore(pyparsing.sglQuotedString) |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
20 self.scanner.ignore(pyparsing.dblQuotedString) |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
21 self.scanner.ignore(self.comment_def) |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
22 self.scanner.ignore(pyparsing.cStyleComment) |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
23 self.retainSeparator = retainSeparator |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
24 def separate(self, txt): |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
25 itms = [] |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
26 for (sqlcommand, start, end) in self.scanner.scanString(txt): |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
27 if sqlcommand: |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
28 if type(sqlcommand[0]) == pyparsing.ParseResults: |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
29 if self.retainSeparator: |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
30 itms.append("".join(sqlcommand[0])) |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
31 else: |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
32 itms.append(sqlcommand[0][0]) |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
33 else: |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
34 if sqlcommand[0]: |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
35 itms.append(sqlcommand[0]) |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
36 return itms |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
37 |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
38 |
189 | 39 class sqlpython(cmd2.Cmd): |
40 '''A python module to reproduce Oracle's command line with focus on customization and extention''' | |
41 | |
42 def __init__(self): | |
43 cmd2.Cmd.__init__(self) | |
260 | 44 self.no_connection() |
189 | 45 self.maxfetch = 1000 |
46 self.terminator = ';' | |
47 self.timeout = 30 | |
48 self.commit_on_exit = True | |
261 | 49 self.connections = {} |
260 | 50 |
51 def no_connection(self): | |
261 | 52 self.prompt = 'SQL.No_Connection> ' |
260 | 53 self.curs = None |
315 | 54 self.conn = None |
258 | 55 self.connection_number = None |
189 | 56 |
317
f200a222a936
beginning to set up metadata.py
Catherine Devlin <catherine.devlin@gmail.com>
parents:
315
diff
changeset
|
57 def make_connection_current(self, connection_number): |
f200a222a936
beginning to set up metadata.py
Catherine Devlin <catherine.devlin@gmail.com>
parents:
315
diff
changeset
|
58 self.conn = self.connections[connection_number]['conn'] |
f200a222a936
beginning to set up metadata.py
Catherine Devlin <catherine.devlin@gmail.com>
parents:
315
diff
changeset
|
59 self.prompt = self.connections[connection_number]['prompt'] |
f200a222a936
beginning to set up metadata.py
Catherine Devlin <catherine.devlin@gmail.com>
parents:
315
diff
changeset
|
60 self.rdbms = self.connections[connection_number]['rdbms'] |
f200a222a936
beginning to set up metadata.py
Catherine Devlin <catherine.devlin@gmail.com>
parents:
315
diff
changeset
|
61 self.connection_number = connection_number |
f200a222a936
beginning to set up metadata.py
Catherine Devlin <catherine.devlin@gmail.com>
parents:
315
diff
changeset
|
62 self.curs = self.conn.cursor() |
f200a222a936
beginning to set up metadata.py
Catherine Devlin <catherine.devlin@gmail.com>
parents:
315
diff
changeset
|
63 |
259
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
64 def successful_connection_to_number(self, arg): |
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
65 try: |
261 | 66 connection_number = int(arg) |
259
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
67 except ValueError: |
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
68 return False |
317
f200a222a936
beginning to set up metadata.py
Catherine Devlin <catherine.devlin@gmail.com>
parents:
315
diff
changeset
|
69 self.make_connection_current(connection_number) |
f200a222a936
beginning to set up metadata.py
Catherine Devlin <catherine.devlin@gmail.com>
parents:
315
diff
changeset
|
70 if (self.rdbms == 'oracle') and self.serveroutput: |
f200a222a936
beginning to set up metadata.py
Catherine Devlin <catherine.devlin@gmail.com>
parents:
315
diff
changeset
|
71 self.curs.callproc('dbms_output.enable', []) |
259
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
72 return True |
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
73 |
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
74 def list_connections(self): |
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
75 self.stdout.write('Existing connections:\n') |
315 | 76 self.stdout.write('\n'.join('%s (%s)' % (v['prompt'], v['rdbms']) |
77 for (k,v) in sorted(self.connections.items())) + '\n') | |
259
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
78 |
260 | 79 def disconnect(self, arg): |
80 try: | |
81 connection_number = int(arg) | |
82 connection = self.connections[connection_number] | |
261 | 83 except (ValueError, KeyError): |
260 | 84 self.list_connections() |
85 return | |
86 if self.commit_on_exit: | |
261 | 87 connection['conn'].commit() |
260 | 88 self.connections.pop(connection_number) |
89 if connection_number == self.connection_number: | |
90 self.no_connection() | |
261 | 91 |
92 def closeall(self): | |
93 for connection_number in self.connections.keys(): | |
94 self.disconnect(connection_number) | |
95 self.curs = None | |
96 self.no_connection() | |
97 | |
319
3c58df9bcf14
now relying entirely on sqlalchemy connections
Catherine Devlin <catherine.devlin@gmail.com>
parents:
317
diff
changeset
|
98 def url_connect(self, arg): |
3c58df9bcf14
now relying entirely on sqlalchemy connections
Catherine Devlin <catherine.devlin@gmail.com>
parents:
317
diff
changeset
|
99 eng = sqlalchemy.create_engine(arg) |
315 | 100 self.conn = eng.connect().connection |
101 conn = {'conn': self.conn, 'prompt': self.prompt, 'dbname': eng.url.database, | |
102 'rdbms': eng.url.drivername, 'user': eng.url.username or '', | |
103 'eng': eng} | |
104 return conn | |
105 def ora_connect(self, arg): | |
106 modeval = 0 | |
107 oraserv = None | |
108 for modere, modevalue in self.connection_modes.items(): | |
109 if modere.search(arg): | |
110 arg = modere.sub('', arg) | |
111 modeval = modevalue | |
112 try: | |
113 orauser, oraserv = arg.split('@') | |
114 except ValueError: | |
115 try: | |
116 oraserv = os.environ['ORACLE_SID'] | |
117 except KeyError: | |
333 | 118 self.perror('instance not specified and environment variable ORACLE_SID not set') |
315 | 119 return |
120 orauser = arg | |
121 sid = oraserv | |
122 try: | |
123 host, sid = oraserv.split('/') | |
124 try: | |
125 host, port = host.split(':') | |
126 port = int(port) | |
127 except ValueError: | |
128 port = 1521 | |
129 oraserv = cx_Oracle.makedsn(host, port, sid) | |
130 except ValueError: | |
131 pass | |
132 try: | |
133 orauser, orapass = orauser.split('/') | |
134 except ValueError: | |
135 orapass = getpass.getpass('Password: ') | |
136 if orauser.upper() == 'SYS' and not modeval: | |
333 | 137 self.pfeedback('Privilege not specified for SYS, assuming SYSOPER') |
315 | 138 modeval = cx_Oracle.SYSOPER |
321
0c83ddee3a5c
trail tnsname with slash to attach mode parameter to url login
Catherine Devlin <catherine.devlin@gmail.com>
parents:
319
diff
changeset
|
139 result = self.url_connect('oracle://%s:%s@%s/?mode=%d' % (orauser, orapass, oraserv, modeval)) |
319
3c58df9bcf14
now relying entirely on sqlalchemy connections
Catherine Devlin <catherine.devlin@gmail.com>
parents:
317
diff
changeset
|
140 result['dbname'] = oraserv |
3c58df9bcf14
now relying entirely on sqlalchemy connections
Catherine Devlin <catherine.devlin@gmail.com>
parents:
317
diff
changeset
|
141 return result |
315 | 142 |
189 | 143 connection_modes = {re.compile(' AS SYSDBA', re.IGNORECASE): cx_Oracle.SYSDBA, |
144 re.compile(' AS SYSOPER', re.IGNORECASE): cx_Oracle.SYSOPER} | |
258 | 145 @cmd2.options([cmd2.make_option('-a', '--add', action='store_true', |
260 | 146 help='add connection (keep current connection)'), |
147 cmd2.make_option('-c', '--close', action='store_true', | |
148 help='close connection {N} (or current)'), | |
261 | 149 cmd2.make_option('-C', '--closeall', action='store_true', |
260 | 150 help='close all connections'),]) |
258 | 151 def do_connect(self, arg, opts): |
189 | 152 '''Opens the DB connection''' |
260 | 153 if opts.closeall: |
261 | 154 self.closeall() |
260 | 155 return |
156 if opts.close: | |
261 | 157 if not arg: |
158 arg = self.connection_number | |
260 | 159 self.disconnect(arg) |
160 return | |
258 | 161 if not arg: |
259
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
162 self.list_connections() |
258 | 163 return |
189 | 164 try: |
259
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
165 if self.successful_connection_to_number(arg): |
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
166 return |
258 | 167 except IndexError: |
259
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
168 self.list_connections() |
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
169 return |
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
170 try: |
315 | 171 connect_info = self.url_connect(arg) |
172 except sqlalchemy.exc.ArgumentError, e: | |
173 connect_info = self.ora_connect(arg) | |
174 if opts.add or (self.connection_number is None): | |
189 | 175 try: |
315 | 176 self.connection_number = max(self.connections.keys()) + 1 |
258 | 177 except ValueError: |
315 | 178 self.connection_number = 0 |
317
f200a222a936
beginning to set up metadata.py
Catherine Devlin <catherine.devlin@gmail.com>
parents:
315
diff
changeset
|
179 connect_info['prompt'] = '%d:%s@%s> ' % (self.connection_number, connect_info['user'], connect_info['dbname']) |
315 | 180 self.connections[self.connection_number] = connect_info |
317
f200a222a936
beginning to set up metadata.py
Catherine Devlin <catherine.devlin@gmail.com>
parents:
315
diff
changeset
|
181 self.make_connection_current(self.connection_number) |
315 | 182 self.curs = self.conn.cursor() |
317
f200a222a936
beginning to set up metadata.py
Catherine Devlin <catherine.devlin@gmail.com>
parents:
315
diff
changeset
|
183 if (self.rdbms == 'oracle') and self.serveroutput: |
259
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
184 self.curs.callproc('dbms_output.enable', []) |
335
00b183a103b3
bare prefix switches connection
Catherine Devlin <catherine.devlin@gmail.com>
parents:
324
diff
changeset
|
185 if (self.rdbms == 'mysql'): |
00b183a103b3
bare prefix switches connection
Catherine Devlin <catherine.devlin@gmail.com>
parents:
324
diff
changeset
|
186 self.curs.execute('SET SQL_MODE=ANSI') |
259
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
187 def postparsing_precmd(self, statement): |
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
188 stop = 0 |
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
189 self.saved_connection_number = None |
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
190 if statement.parsed.connection_number: |
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
191 saved_connection_number = self.connection_number |
258 | 192 try: |
259
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
193 if self.successful_connection_to_number(statement.parsed.connection_number): |
335
00b183a103b3
bare prefix switches connection
Catherine Devlin <catherine.devlin@gmail.com>
parents:
324
diff
changeset
|
194 if statement.parsed.command: |
00b183a103b3
bare prefix switches connection
Catherine Devlin <catherine.devlin@gmail.com>
parents:
324
diff
changeset
|
195 self.saved_connection_number = saved_connection_number |
261 | 196 except KeyError: |
259
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
197 self.list_connections() |
261 | 198 raise KeyError, 'No connection #%s' % statement.parsed.connection_number |
259
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
199 return stop, statement |
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
200 def postparsing_postcmd(self, stop): |
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
201 if self.saved_connection_number is not None: |
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
202 self.successful_connection_to_number(self.saved_connection_number) |
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
203 return stop |
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
204 |
256 | 205 do_host = cmd2.Cmd.do_shell |
189 | 206 |
207 def emptyline(self): | |
208 pass | |
264
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
209 |
265
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
210 def _show_errors(self, all_users=False, limit=None, mintime=None, targets=[]): |
264
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
211 if all_users: |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
212 user = '' |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
213 else: |
265
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
214 user = "AND ao.owner = user\n" |
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
215 if targets: |
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
216 target = 'AND (%s)\n' % ' OR '.join("ae.type || '/' || ae.name LIKE '%s'" % |
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
217 t.upper().replace('*','%') for t in targets) |
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
218 else: |
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
219 target = '' |
264
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
220 self.curs.execute(''' |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
221 SELECT ae.owner, ae.name, ae.type, ae.position, ae.line, ae.attribute, |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
222 ae.text error_text, |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
223 src.text object_text, |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
224 ao.last_ddl_time |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
225 FROM all_errors ae |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
226 JOIN all_objects ao ON ( ae.owner = ao.owner |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
227 AND ae.name = ao.object_name |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
228 AND ae.type = ao.object_type) |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
229 JOIN all_source src ON ( ae.owner = src.owner |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
230 AND ae.name = src.name |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
231 AND ae.type = src.type |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
232 AND ae.line = src.line) |
265
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
233 WHERE 1=1 |
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
234 %s%sORDER BY ao.last_ddl_time DESC''' % (user, target)) |
264
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
235 if limit is None: |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
236 errors = self.curs.fetchall() |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
237 else: |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
238 errors = self.curs.fetchmany(numRows = limit) |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
239 for err in errors: |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
240 if (mintime is not None) and (err[8] < mintime): |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
241 break |
333 | 242 self.poutput('%s at line %d of %s %s.%s:' % (err[5], err[4], err[2], err[0], err[1])) |
243 self.poutput(err[7]) | |
244 self.poutput((' ' * (err[3]-1)) + '^') | |
245 self.poutput(err[6]) | |
246 self.poutput('\n') | |
264
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
247 |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
248 def current_database_time(self): |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
249 self.curs.execute('select sysdate from dual') |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
250 return self.curs.fetchone()[0] |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
251 |
189 | 252 def do_terminators(self, arg): |
253 """; standard Oracle format | |
254 \\c CSV (with headings) | |
255 \\C CSV (no headings) | |
256 \\g list | |
257 \\G aligned list | |
258 \\h HTML table | |
259 \\i INSERT statements | |
266 | 260 \\j JSON |
308 | 261 \\r ReStructured Text |
189 | 262 \\s CSV (with headings) |
263 \\S CSV (no headings) | |
264 \\t transposed | |
265 \\x XML | |
266 \\l line plot, with markers | |
267 \\L scatter plot (no lines) | |
268 \\b bar graph | |
269 \\p pie chart""" | |
333 | 270 self.poutput(self.do_terminators.__doc__) |
189 | 271 |
272 terminatorSearchString = '|'.join('\\' + d.split()[0] for d in do_terminators.__doc__.splitlines()) | |
273 | |
339
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
274 bindScanner = {'oracle': Parser(pyparsing.Literal(':') + pyparsing.Word( pyparsing.alphanums + "_$#" )), |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
275 'postgres': Parser(pyparsing.Literal('%(') + |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
276 pyparsing.Word(pyparsing.alphanums + "_$#") + ')s')} |
340
001d01eeac90
bind vars for postgres
Catherine Devlin <catherine.devlin@gmail.com>
parents:
339
diff
changeset
|
277 def findBinds(self, target, givenBindVars = {}): |
339
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
278 result = givenBindVars |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
279 if self.rdbms in self.bindScanner: |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
280 for finding, startat, endat in self.bindScanner[self.rdbms].scanner.scanString(target): |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
281 varname = finding[1] |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
282 try: |
340
001d01eeac90
bind vars for postgres
Catherine Devlin <catherine.devlin@gmail.com>
parents:
339
diff
changeset
|
283 result[varname] = self.binds[varname] |
339
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
284 except KeyError: |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
285 if not givenBindVars.has_key(varname): |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
286 print 'Bind variable %s not defined.' % (varname) |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
287 return result |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
288 |
189 | 289 def default(self, arg): |
340
001d01eeac90
bind vars for postgres
Catherine Devlin <catherine.devlin@gmail.com>
parents:
339
diff
changeset
|
290 self.varsUsed = self.findBinds(arg, givenBindVars={}) |
247 | 291 ending_args = arg.lower().split()[-2:] |
292 if 'end' in ending_args: | |
246 | 293 command = '%s %s;' |
294 else: | |
264
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
295 command = '%s %s' |
317
f200a222a936
beginning to set up metadata.py
Catherine Devlin <catherine.devlin@gmail.com>
parents:
315
diff
changeset
|
296 if self.rdbms == 'oracle': |
315 | 297 current_time = self.current_database_time() |
263 | 298 self.curs.execute(command % (arg.parsed.command, arg.parsed.args), self.varsUsed) |
276
0b7031c2229e
autobind unit test failing
catherine@Elli.myhome.westell.com
parents:
266
diff
changeset
|
299 executionmessage = '\nExecuted%s\n' % ((self.curs.rowcount > 0) and ' (%d rows)' % self.curs.rowcount or '') |
317
f200a222a936
beginning to set up metadata.py
Catherine Devlin <catherine.devlin@gmail.com>
parents:
315
diff
changeset
|
300 if self.rdbms == 'oracle': |
315 | 301 self._show_errors(all_users=True, limit=1, mintime=current_time) |
333 | 302 self.pfeedback(executionmessage) |
198
b2d8bf5f89db
merged with changes from work
catherine@Elli.myhome.westell.com
parents:
197
diff
changeset
|
303 |
b2d8bf5f89db
merged with changes from work
catherine@Elli.myhome.westell.com
parents:
197
diff
changeset
|
304 def do_commit(self, arg=''): |
b2d8bf5f89db
merged with changes from work
catherine@Elli.myhome.westell.com
parents:
197
diff
changeset
|
305 self.default(self.parsed('commit %s;' % (arg))) |
b2d8bf5f89db
merged with changes from work
catherine@Elli.myhome.westell.com
parents:
197
diff
changeset
|
306 def do_rollback(self, arg=''): |
b2d8bf5f89db
merged with changes from work
catherine@Elli.myhome.westell.com
parents:
197
diff
changeset
|
307 self.default(self.parsed('rollback %s;' % (arg))) |
189 | 308 def do_quit(self, arg): |
261 | 309 if self.commit_on_exit: |
310 self.closeall() | |
189 | 311 return cmd2.Cmd.do_quit(self, None) |
312 do_exit = do_quit | |
313 do_q = do_quit | |
337 | 314 colorcodes = {'bold':{True:'\x1b[1m',False:'\x1b[22m'}, |
315 'red':{True:'\x1b[36m',False:'\x1b[39m'}, | |
316 'cyan':{True:'\x1b[31m',False:'\x1b[39m'}, | |
317 'underline':{True:'\x1b[4m',False:'\x1b[24m'}} | |
318 colors = True | |
319 def colorize(self, val, color): | |
320 if self.colors and (self.stdout == self.initial_stdout): | |
321 if color not in self.colorcodes: | |
322 if (color % 2): | |
323 color = 'red' | |
324 else: | |
325 color = 'cyan' | |
326 return self.colorcodes[color][True] + val + self.colorcodes[color][False] | |
327 return val | |
328 def pmatrix(self,rows,desc,maxlen=30,heading=True,restructuredtext=False): | |
329 '''prints a matrix, used by sqlpython to print queries' result sets''' | |
330 names = [] | |
331 maxen = [] | |
332 toprint = [] | |
333 for d in desc: | |
334 n = d[0] | |
335 names.append(n) # list col names | |
336 maxen.append(len(n)) # col length | |
337 rcols = range(len(desc)) | |
338 rrows = range(len(rows)) | |
339 for i in rrows: # loops for all rows | |
340 rowsi = map(str, rows[i]) # current row to process | |
341 split = [] # service var is row split is needed | |
342 mustsplit = 0 # flag | |
343 for j in rcols: | |
344 if str(desc[j][1]) == "<type 'cx_Oracle.BINARY'>": # handles RAW columns | |
345 rowsi[j] = binascii.b2a_hex(rowsi[j]) | |
346 maxen[j] = max(maxen[j], len(rowsi[j])) # computes max field length | |
347 if maxen[j] <= maxlen: | |
348 split.append('') | |
349 else: # split the line is 2 because field is too long | |
350 mustsplit = 1 | |
351 maxen[j] = maxlen | |
352 split.append(rowsi[j][maxlen-1:2*maxlen-1]) | |
353 rowsi[j] = rowsi[j][0:maxlen-1] # this implem. truncates after maxlen*2 | |
354 toprint.append(rowsi) # 'toprint' is a printable copy of rows | |
355 if mustsplit != 0: | |
356 toprint.append(split) | |
357 sepcols = [] | |
358 for i in rcols: | |
359 maxcol = maxen[i] | |
360 name = names[i] | |
361 sepcols.append("-" * maxcol) # formats column names (header) | |
362 names[i] = name + (" " * (maxcol-len(name))) # formats separ line with -- | |
363 rrows2 = range(len(toprint)) | |
364 for j in rrows2: | |
365 val = toprint[j][i] | |
366 if str(desc[i][1]) == "<type 'cx_Oracle.NUMBER'>": # right align numbers | |
367 toprint[j][i] = (" " * (maxcol-len(val))) + val | |
368 else: | |
369 toprint[j][i] = val + (" " * (maxcol-len(val))) | |
370 toprint[j][i] = self.colorize(toprint[j][i], i) | |
371 for j in rrows2: | |
372 toprint[j] = ' '.join(toprint[j]) | |
373 names = [self.colorize(name, n) for (n, name) in enumerate(names)] | |
374 names = ' '.join(names) | |
375 names = self.colorize(names, 'bold') | |
376 sepcols = ' '.join(sepcols) | |
377 if heading or restructuredtext: | |
378 toprint.insert(0, sepcols) | |
379 toprint.insert(0, names) | |
380 if restructuredtext: | |
381 toprint.insert(0, sepcols) | |
382 toprint.append(sepcols) | |
383 return '\n'.join(toprint) | |
189 | 384 |
337 | 385 |
386 | |
387 | |
189 | 388 |