sqlpython: sqlpython/sqlpython.py annotate

annotate sqlpython/sqlpython.py @ 298:231964a69c0a

oops, updating version numbers in code

author	catherine@dellzilla
date	Tue, 24 Mar 2009 15:44:17 -0400
parents	0b7031c2229e
children	4d24fea42364

rev	line source
189 c5398d87498e cat bug catherine@dellzilla parents: diff changeset	1 #
298 231964a69c0a oops, updating version numbers in code catherine@dellzilla parents: 276 diff changeset	2 # SqlPython V1.6.2
189 c5398d87498e cat bug catherine@dellzilla parents: diff changeset	3 # Author: Luca.Canali@cern.ch, Apr 2006
229 411f78dc1e07 accept SET HEADING catherine@dellzilla parents: 223 diff changeset	4 # Rev 25-Feb-09
189 c5398d87498e cat bug catherine@dellzilla parents: diff changeset	5 #
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	6 # A python module to reproduce Oracle's command line 'sqlplus-like' within python
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	7 # Intended to allow easy customizations and extentions
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	8 # Best used with the companion modules sqlpyPlus and mysqlpy
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	9 # See also http://twiki.cern.ch/twiki/bin/view/PSSGroup/SqlPython
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	10
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	11 import cmd2,getpass,binascii,cx_Oracle,re,os
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	12 import sqlpyPlus
298 231964a69c0a oops, updating version numbers in code catherine@dellzilla parents: 276 diff changeset	13 __version__ = '1.6.2'
189 c5398d87498e cat bug catherine@dellzilla parents: diff changeset	14
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	15 class sqlpython(cmd2.Cmd):
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	16 '''A python module to reproduce Oracle's command line with focus on customization and extention'''
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	17
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	18 def __init__(self):
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	19 cmd2.Cmd.__init__(self)
260 fc106df4606b need to work on connection closing catherine@dellzilla parents: 259 diff changeset	20 self.no_connection()
189 c5398d87498e cat bug catherine@dellzilla parents: diff changeset	21 self.maxfetch = 1000
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	22 self.terminator = ';'
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	23 self.timeout = 30
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	24 self.commit_on_exit = True
261 0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	25 self.connections = {}
260 fc106df4606b need to work on connection closing catherine@dellzilla parents: 259 diff changeset	26
fc106df4606b need to work on connection closing catherine@dellzilla parents: 259 diff changeset	27 def no_connection(self):
261 0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	28 self.prompt = 'SQL.No_Connection> '
260 fc106df4606b need to work on connection closing catherine@dellzilla parents: 259 diff changeset	29 self.curs = None
fc106df4606b need to work on connection closing catherine@dellzilla parents: 259 diff changeset	30 self.orcl = None
258 a94fec8155da multiple connections catherine@Elli.myhome.westell.com parents: 257 diff changeset	31 self.connection_number = None
189 c5398d87498e cat bug catherine@dellzilla parents: diff changeset	32
259 c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	33 def successful_connection_to_number(self, arg):
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	34 try:
261 0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	35 connection_number = int(arg)
0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	36 self.orcl = self.connections[connection_number]['conn']
0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	37 self.prompt = self.connections[connection_number]['prompt']
0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	38 self.connection_number = connection_number
259 c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	39 self.curs = self.orcl.cursor()
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	40 if self.serveroutput:
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	41 self.curs.callproc('dbms_output.enable', [])
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	42 except ValueError:
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	43 return False
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	44 return True
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	45
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	46 def list_connections(self):
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	47 self.stdout.write('Existing connections:\n')
261 0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	48 self.stdout.write('\n'.join(v['prompt'] for (k,v) in sorted(self.connections.items())) + '\n')
259 c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	49
260 fc106df4606b need to work on connection closing catherine@dellzilla parents: 259 diff changeset	50 def disconnect(self, arg):
fc106df4606b need to work on connection closing catherine@dellzilla parents: 259 diff changeset	51 try:
fc106df4606b need to work on connection closing catherine@dellzilla parents: 259 diff changeset	52 connection_number = int(arg)
fc106df4606b need to work on connection closing catherine@dellzilla parents: 259 diff changeset	53 connection = self.connections[connection_number]
261 0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	54 except (ValueError, KeyError):
260 fc106df4606b need to work on connection closing catherine@dellzilla parents: 259 diff changeset	55 self.list_connections()
fc106df4606b need to work on connection closing catherine@dellzilla parents: 259 diff changeset	56 return
fc106df4606b need to work on connection closing catherine@dellzilla parents: 259 diff changeset	57 if self.commit_on_exit:
261 0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	58 connection['conn'].commit()
260 fc106df4606b need to work on connection closing catherine@dellzilla parents: 259 diff changeset	59 self.connections.pop(connection_number)
fc106df4606b need to work on connection closing catherine@dellzilla parents: 259 diff changeset	60 if connection_number == self.connection_number:
fc106df4606b need to work on connection closing catherine@dellzilla parents: 259 diff changeset	61 self.no_connection()
261 0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	62
0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	63 def closeall(self):
0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	64 for connection_number in self.connections.keys():
0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	65 self.disconnect(connection_number)
0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	66 self.curs = None
0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	67 self.no_connection()
0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	68
189 c5398d87498e cat bug catherine@dellzilla parents: diff changeset	69 connection_modes = {re.compile(' AS SYSDBA', re.IGNORECASE): cx_Oracle.SYSDBA,
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	70 re.compile(' AS SYSOPER', re.IGNORECASE): cx_Oracle.SYSOPER}
258 a94fec8155da multiple connections catherine@Elli.myhome.westell.com parents: 257 diff changeset	71 @cmd2.options([cmd2.make_option('-a', '--add', action='store_true',
260 fc106df4606b need to work on connection closing catherine@dellzilla parents: 259 diff changeset	72 help='add connection (keep current connection)'),
fc106df4606b need to work on connection closing catherine@dellzilla parents: 259 diff changeset	73 cmd2.make_option('-c', '--close', action='store_true',
fc106df4606b need to work on connection closing catherine@dellzilla parents: 259 diff changeset	74 help='close connection {N} (or current)'),
261 0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	75 cmd2.make_option('-C', '--closeall', action='store_true',
260 fc106df4606b need to work on connection closing catherine@dellzilla parents: 259 diff changeset	76 help='close all connections'),])
258 a94fec8155da multiple connections catherine@Elli.myhome.westell.com parents: 257 diff changeset	77 def do_connect(self, arg, opts):
189 c5398d87498e cat bug catherine@dellzilla parents: diff changeset	78 '''Opens the DB connection'''
260 fc106df4606b need to work on connection closing catherine@dellzilla parents: 259 diff changeset	79 if opts.closeall:
261 0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	80 self.closeall()
260 fc106df4606b need to work on connection closing catherine@dellzilla parents: 259 diff changeset	81 return
fc106df4606b need to work on connection closing catherine@dellzilla parents: 259 diff changeset	82 if opts.close:
261 0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	83 if not arg:
0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	84 arg = self.connection_number
260 fc106df4606b need to work on connection closing catherine@dellzilla parents: 259 diff changeset	85 self.disconnect(arg)
fc106df4606b need to work on connection closing catherine@dellzilla parents: 259 diff changeset	86 return
258 a94fec8155da multiple connections catherine@Elli.myhome.westell.com parents: 257 diff changeset	87 if not arg:
259 c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	88 self.list_connections()
258 a94fec8155da multiple connections catherine@Elli.myhome.westell.com parents: 257 diff changeset	89 return
189 c5398d87498e cat bug catherine@dellzilla parents: diff changeset	90 try:
259 c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	91 if self.successful_connection_to_number(arg):
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	92 return
258 a94fec8155da multiple connections catherine@Elli.myhome.westell.com parents: 257 diff changeset	93 except IndexError:
259 c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	94 self.list_connections()
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	95 return
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	96 modeval = 0
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	97 oraserv = None
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	98 for modere, modevalue in self.connection_modes.items():
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	99 if modere.search(arg):
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	100 arg = modere.sub('', arg)
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	101 modeval = modevalue
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	102 try:
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	103 orauser, oraserv = arg.split('@')
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	104 except ValueError:
189 c5398d87498e cat bug catherine@dellzilla parents: diff changeset	105 try:
259 c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	106 oraserv = os.environ['ORACLE_SID']
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	107 except KeyError:
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	108 print 'instance not specified and environment variable ORACLE_SID not set'
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	109 return
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	110 orauser = arg
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	111 self.sid = oraserv
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	112 try:
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	113 host, self.sid = oraserv.split('/')
189 c5398d87498e cat bug catherine@dellzilla parents: diff changeset	114 try:
259 c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	115 host, port = host.split(':')
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	116 port = int(port)
258 a94fec8155da multiple connections catherine@Elli.myhome.westell.com parents: 257 diff changeset	117 except ValueError:
259 c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	118 port = 1521
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	119 oraserv = cx_Oracle.makedsn(host, port, self.sid)
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	120 except ValueError:
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	121 pass
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	122 try:
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	123 orauser, orapass = orauser.split('/')
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	124 except ValueError:
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	125 orapass = getpass.getpass('Password: ')
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	126 if orauser.upper() == 'SYS' and not modeval:
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	127 print 'Privilege not specified for SYS, assuming SYSOPER'
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	128 modeval = cx_Oracle.SYSOPER
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	129 try:
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	130 self.orcl = cx_Oracle.connect(orauser,orapass,oraserv,modeval)
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	131 if opts.add or (self.connection_number is None):
261 0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	132 try:
0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	133 self.connection_number = max(self.connections.keys()) + 1
0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	134 except ValueError:
0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	135 self.connection_number = 0
0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	136 self.connections[self.connection_number] = {'conn':self.orcl}
259 c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	137 else:
261 0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	138 self.connections[self.connection_number] = {'conn':self.orcl}
259 c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	139 self.curs = self.orcl.cursor()
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	140 self.prompt = '%d:%s@%s> ' % (self.connection_number, orauser, self.sid)
261 0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	141 self.connections[self.connection_number]['prompt'] = self.prompt
259 c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	142 except Exception, e:
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	143 print e
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	144 return
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	145 if self.serveroutput:
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	146 self.curs.callproc('dbms_output.enable', [])
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	147 def postparsing_precmd(self, statement):
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	148 stop = 0
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	149 self.saved_connection_number = None
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	150 if statement.parsed.connection_number:
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	151 saved_connection_number = self.connection_number
258 a94fec8155da multiple connections catherine@Elli.myhome.westell.com parents: 257 diff changeset	152 try:
259 c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	153 if self.successful_connection_to_number(statement.parsed.connection_number):
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	154 self.saved_connection_number = saved_connection_number
261 0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	155 except KeyError:
259 c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	156 self.list_connections()
261 0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	157 raise KeyError, 'No connection #%s' % statement.parsed.connection_number
259 c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	158 return stop, statement
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	159 def postparsing_postcmd(self, stop):
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	160 if self.saved_connection_number is not None:
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	161 self.successful_connection_to_number(self.saved_connection_number)
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	162 return stop
c0847a4c7f49 one-shot connection changes catherine@Elli.myhome.westell.com parents: 258 diff changeset	163
256 d09f16b71f66 do_host catherine@Elli.myhome.westell.com parents: 255 diff changeset	164 do_host = cmd2.Cmd.do_shell
189 c5398d87498e cat bug catherine@dellzilla parents: diff changeset	165
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	166 def emptyline(self):
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	167 pass
264 a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	168
265 041c656dc8e5 show err working nicely now catherine@Elli.myhome.westell.com parents: 264 diff changeset	169 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	170 if all_users:
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	171 user = ''
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	172 else:
265 041c656dc8e5 show err working nicely now catherine@Elli.myhome.westell.com parents: 264 diff changeset	173 user = "AND ao.owner = user\n"
041c656dc8e5 show err working nicely now catherine@Elli.myhome.westell.com parents: 264 diff changeset	174 if targets:
041c656dc8e5 show err working nicely now catherine@Elli.myhome.westell.com parents: 264 diff changeset	175 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	176 t.upper().replace('*','%') for t in targets)
041c656dc8e5 show err working nicely now catherine@Elli.myhome.westell.com parents: 264 diff changeset	177 else:
041c656dc8e5 show err working nicely now catherine@Elli.myhome.westell.com parents: 264 diff changeset	178 target = ''
264 a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	179 self.curs.execute('''
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	180 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	181 ae.text error_text,
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	182 src.text object_text,
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	183 ao.last_ddl_time
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	184 FROM all_errors ae
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	185 JOIN all_objects ao ON ( ae.owner = ao.owner
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	186 AND ae.name = ao.object_name
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	187 AND ae.type = ao.object_type)
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	188 JOIN all_source src ON ( ae.owner = src.owner
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	189 AND ae.name = src.name
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	190 AND ae.type = src.type
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	191 AND ae.line = src.line)
265 041c656dc8e5 show err working nicely now catherine@Elli.myhome.westell.com parents: 264 diff changeset	192 WHERE 1=1
041c656dc8e5 show err working nicely now catherine@Elli.myhome.westell.com parents: 264 diff changeset	193 %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	194 if limit is None:
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	195 errors = self.curs.fetchall()
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	196 else:
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	197 errors = self.curs.fetchmany(numRows = limit)
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	198 for err in errors:
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	199 if (mintime is not None) and (err[8] < mintime):
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	200 break
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	201 print '%s at line %d of %s %s.%s:' % (err[5], err[4], err[2], err[0], err[1])
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	202 print err[7]
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	203 print (' ' * (err[3]-1)) + '^'
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	204 print err[6]
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	205 print '\n'
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	206
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	207 def current_database_time(self):
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	208 self.curs.execute('select sysdate from dual')
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	209 return self.curs.fetchone()[0]
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	210
189 c5398d87498e cat bug catherine@dellzilla parents: diff changeset	211 def do_terminators(self, arg):
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	212 """; standard Oracle format
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	213 \\c CSV (with headings)
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	214 \\C CSV (no headings)
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	215 \\g list
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	216 \\G aligned list
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	217 \\h HTML table
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	218 \\i INSERT statements
266 342e96de6de6 json not quite there catherine@Elli.myhome.westell.com parents: 265 diff changeset	219 \\j JSON
189 c5398d87498e cat bug catherine@dellzilla parents: diff changeset	220 \\s CSV (with headings)
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	221 \\S CSV (no headings)
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	222 \\t transposed
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	223 \\x XML
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	224 \\l line plot, with markers
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	225 \\L scatter plot (no lines)
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	226 \\b bar graph
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	227 \\p pie chart"""
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	228 print self.do_terminators.__doc__
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	229
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	230 terminatorSearchString = '\|'.join('\\' + d.split()[0] for d in do_terminators.__doc__.splitlines())
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	231
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	232 def default(self, arg):
198 b2d8bf5f89db merged with changes from work catherine@Elli.myhome.westell.com parents: 197 diff changeset	233 self.varsUsed = sqlpyPlus.findBinds(arg, self.binds, givenBindVars={})
247 f0f293d83337 begin docs catherine@dellzilla parents: 246 diff changeset	234 ending_args = arg.lower().split()[-2:]
f0f293d83337 begin docs catherine@dellzilla parents: 246 diff changeset	235 if 'end' in ending_args:
246 b5d4a122354a can round-trip a package now catherine@dellzilla parents: 242 diff changeset	236 command = '%s %s;'
b5d4a122354a can round-trip a package now catherine@dellzilla parents: 242 diff changeset	237 else:
264 a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	238 command = '%s %s'
a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	239 current_time = self.current_database_time()
263 362db47c17b4 error reported catherine@Elli.myhome.westell.com parents: 261 diff changeset	240 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	241 executionmessage = '\nExecuted%s\n' % ((self.curs.rowcount > 0) and ' (%d rows)' % self.curs.rowcount or '')
264 a8deaa38f11e show errors works. limiting ls catherine@Elli.myhome.westell.com parents: 263 diff changeset	242 self._show_errors(all_users=True, limit=1, mintime=current_time)
276 0b7031c2229e autobind unit test failing catherine@Elli.myhome.westell.com parents: 266 diff changeset	243 print executionmessage
198 b2d8bf5f89db merged with changes from work catherine@Elli.myhome.westell.com parents: 197 diff changeset	244
b2d8bf5f89db merged with changes from work catherine@Elli.myhome.westell.com parents: 197 diff changeset	245 def do_commit(self, arg=''):
b2d8bf5f89db merged with changes from work catherine@Elli.myhome.westell.com parents: 197 diff changeset	246 self.default(self.parsed('commit %s;' % (arg)))
b2d8bf5f89db merged with changes from work catherine@Elli.myhome.westell.com parents: 197 diff changeset	247 def do_rollback(self, arg=''):
b2d8bf5f89db merged with changes from work catherine@Elli.myhome.westell.com parents: 197 diff changeset	248 self.default(self.parsed('rollback %s;' % (arg)))
189 c5398d87498e cat bug catherine@dellzilla parents: diff changeset	249 def do_quit(self, arg):
261 0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	250 if self.commit_on_exit:
0044cfa5867a disconnections worked out catherine@dellzilla parents: 260 diff changeset	251 self.closeall()
189 c5398d87498e cat bug catherine@dellzilla parents: diff changeset	252 return cmd2.Cmd.do_quit(self, None)
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	253 do_exit = do_quit
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	254 do_q = do_quit
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	255
229 411f78dc1e07 accept SET HEADING catherine@dellzilla parents: 223 diff changeset	256 def pmatrix(rows,desc,maxlen=30,heading=True):
189 c5398d87498e cat bug catherine@dellzilla parents: diff changeset	257 '''prints a matrix, used by sqlpython to print queries' result sets'''
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	258 names = []
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	259 maxen = []
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	260 toprint = []
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	261 for d in desc:
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	262 n = d[0]
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	263 names.append(n) # list col names
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	264 maxen.append(len(n)) # col length
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	265 rcols = range(len(desc))
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	266 rrows = range(len(rows))
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	267 for i in rrows: # loops for all rows
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	268 rowsi = map(str, rows[i]) # current row to process
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	269 split = [] # service var is row split is needed
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	270 mustsplit = 0 # flag
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	271 for j in rcols:
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	272 if str(desc[j][1]) == "<type 'cx_Oracle.BINARY'>": # handles RAW columns
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	273 rowsi[j] = binascii.b2a_hex(rowsi[j])
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	274 maxen[j] = max(maxen[j], len(rowsi[j])) # computes max field length
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	275 if maxen[j] <= maxlen:
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	276 split.append('')
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	277 else: # split the line is 2 because field is too long
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	278 mustsplit = 1
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	279 maxen[j] = maxlen
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	280 split.append(rowsi[j][maxlen-1:2*maxlen-1])
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	281 rowsi[j] = rowsi[j][0:maxlen-1] # this implem. truncates after maxlen*2
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	282 toprint.append(rowsi) # 'toprint' is a printable copy of rows
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	283 if mustsplit != 0:
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	284 toprint.append(split)
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	285 sepcols = []
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	286 for i in rcols:
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	287 maxcol = maxen[i]
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	288 name = names[i]
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	289 sepcols.append("-" * maxcol) # formats column names (header)
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	290 names[i] = name + (" " * (maxcol-len(name))) # formats separ line with --
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	291 rrows2 = range(len(toprint))
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	292 for j in rrows2:
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	293 val = toprint[j][i]
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	294 if str(desc[i][1]) == "<type 'cx_Oracle.NUMBER'>": # right align numbers
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	295 toprint[j][i] = (" " * (maxcol-len(val))) + val
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	296 else:
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	297 toprint[j][i] = val + (" " * (maxcol-len(val)))
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	298 for j in rrows2:
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	299 toprint[j] = ' '.join(toprint[j])
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	300 names = ' '.join(names)
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	301 sepcols = ' '.join(sepcols)
229 411f78dc1e07 accept SET HEADING catherine@dellzilla parents: 223 diff changeset	302 if heading:
411f78dc1e07 accept SET HEADING catherine@dellzilla parents: 223 diff changeset	303 toprint.insert(0, sepcols)
411f78dc1e07 accept SET HEADING catherine@dellzilla parents: 223 diff changeset	304 toprint.insert(0, names)
189 c5398d87498e cat bug catherine@dellzilla parents: diff changeset	305 return '\n'.join(toprint)
c5398d87498e cat bug catherine@dellzilla parents: diff changeset	306

Mercurial > sqlpython

annotate sqlpython/sqlpython.py @ 298:231964a69c0a