# HG changeset patch # User Catherine Devlin # Date 1239224764 14400 # Node ID ee7875c7e92858e4f5281ff03178154946b523a1 # Parent 00b183a103b352e64a06250b4bc8bd4395fcd4c7# Parent 1cde0ec62e616e538d015dfdf2859cd1c7586a0d merge in changes to poutput, perror, pfeedback diff -r 00b183a103b3 -r ee7875c7e928 sqlpython/exampleSession.txt --- a/sqlpython/exampleSession.txt Wed Apr 08 17:01:40 2009 -0400 +++ b/sqlpython/exampleSession.txt Wed Apr 08 17:06:04 2009 -0400 @@ -228,14 +228,16 @@ default_file_name: afiedt.buf echo: False editor: gedit +feedback_to_output: False heading: True maxfetch: 1000 maxtselctrows: 10 prompt: 0:testschema@orcl> +quiet: False +rows_remembered: 10000 scan: True serveroutput: True sql_echo: False -store_results: True timeout: 30 timing: False wildsql: False diff -r 00b183a103b3 -r ee7875c7e928 sqlpython/mysqlpy.py --- a/sqlpython/mysqlpy.py Wed Apr 08 17:01:40 2009 -0400 +++ b/sqlpython/mysqlpy.py Wed Apr 08 17:06:04 2009 -0400 @@ -105,16 +105,13 @@ Do not confuse with `GET myfile.sql` and `@myfile.sql`, which get and run SQL scripts from disk.''' self.onecmd(self.query_load10g) - - def do_hello(self, arg): - print 'Hello, World!' def do_db(self,args,filepath='pass.txt'): '''Exec do_connect to db_alias in args (credentials form the file pass.txt) ''' try: f = open(filepath,'r') except IOError: - print 'Need a file %s containing username/password' % filepath + self.perror('Need a file %s containing username/password' % filepath) raise connectstr = f.readline().strip() if args: @@ -135,20 +132,20 @@ try: self.curs.execute(self.query) row = self.curs.fetchone() - print "\nSQL statement from cache" - print "------------------------\n" + self.poutput("\nSQL statement from cache") + self.poutput("------------------------\n") while row: - print "\nINST_ID = "+str(row[0])+" - SQL TEXT:\n", row[1].read() + self.poutput("\nINST_ID = "+str(row[0])+" - SQL TEXT:\n" + row[1].read()) row = self.curs.next() except Exception, e: - print e + self.perror(e) def do_explain(self,args): '''prints the plan of a given statement from the sql cache. 1 parameter: sql_id, see also do_sql ''' words = args.split() if len(words) > 2 and words[0].lower() == 'plan' and words[1].lower() == 'for': self.curs.execute('explain %s' % args) - print 'Explained. (see plan table)' + self.pfeedback('Explained. (see plan table)') return self.query = "select * from table(dbms_xplan.display_cursor('"+args+"'))" try: @@ -157,9 +154,9 @@ desc = self.curs.description self.rc = self.curs.rowcount if self.rc > 0: - print '\n' + sqlpython.pmatrix(rows,desc,200) + self.poutput('\n' + sqlpython.pmatrix(rows,desc,200)) except Exception, e: - print e + self.perror(e) def do_sessinfo(self,args): '''Reports session info for the given sid, extended to RAC with gv$''' @@ -170,7 +167,7 @@ self.onecmd('SELECT * from gv$session where sid=%s\\t' % args) except cx_Oracle.DatabaseError, e: if 'table or view does not exist' in str(e): - print 'This account has not been granted SELECT privileges to v$mystat or gv$session.' + self.perror('This account has not been granted SELECT privileges to v$mystat or gv$session.') else: raise diff -r 00b183a103b3 -r ee7875c7e928 sqlpython/sqlpyPlus.py --- a/sqlpython/sqlpyPlus.py Wed Apr 08 17:01:40 2009 -0400 +++ b/sqlpython/sqlpyPlus.py Wed Apr 08 17:06:04 2009 -0400 @@ -345,7 +345,7 @@ if cmd in ('select', 'sleect', 'insert', 'update', 'delete', 'describe', 'desc', 'comments', 'pull', 'refs', 'desc', 'triggers', 'find') \ and not hasattr(self, 'curs'): - print 'Not connected.' + self.perror('Not connected.') return '', '', '' return cmd, arg, line @@ -432,7 +432,7 @@ """Lists available first-character shortcuts (i.e. '!dir' is equivalent to 'shell dir')""" for (scchar, scto) in self.shortcuts: - print '%s: %s' % (scchar, scto) + self.poutput('%s: %s' % (scchar, scto)) tableNameFinder = re.compile(r'from\s+([\w$#_"]+)', re.IGNORECASE | re.MULTILINE | re.DOTALL) def formattedForSql(self, datum): @@ -577,7 +577,7 @@ idx -= 1 colnames = [columns_available[idx]] if not colnames: - print 'No columns found matching criteria.' + self.pfeedback('No columns found matching criteria.') return 'null from dual' for colname in colnames: if col.exclude: @@ -602,12 +602,11 @@ result = self.deadStarterCommaRegex.sub('', result) result = self.deadEnderCommaRegex.sub('', result) if not result.strip(): - print 'No columns found matching criteria.' + self.pfeedback('No columns found matching criteria.') return 'null from dual' return result + ' ' + columnlist.remainder - def do_prompt(self, args): - print args + do_prompt = Cmd.poutput def do_accept(self, args): try: @@ -621,8 +620,8 @@ subst = regexpr.search(raw) while subst: fullexpr, var = subst.group(1), subst.group(2) - print 'Substitution variable %s found in:' % fullexpr - print raw[max(subst.start()-20, 0):subst.end()+20] + self.pfeedback('Substitution variable %s found in:' % fullexpr) + self.pfeedback(raw[max(subst.start()-20, 0):subst.end()+20]) if var in self.substvars: val = self.substvars[var] else: @@ -633,7 +632,7 @@ if isglobal: self.substvars[var] = val raw = raw.replace(fullexpr, val) - print 'Substituted %s for %s' % (val, fullexpr) + self.pfeedback('Substituted %s for %s' % (val, fullexpr)) subst = regexpr.search(raw) # do not FINDALL b/c we don't want to ask twice return raw @@ -664,7 +663,7 @@ try: self.pystate['r'][-1][opts.row].bind() except IndexError: - print self.do_bind.__doc__ + self.poutput(self.do_bind.__doc__) def age_out_resultsets(self): total_len = sum(len(rs) for rs in self.pystate['r']) @@ -689,7 +688,7 @@ rowlimit = int(arg.parsed.suffix or 0) except ValueError: rowlimit = 0 - print "Specify desired number of rows after terminator (not '%s')" % arg.parsed.suffix + self.perror("Specify desired number of rows after terminator (not '%s')" % arg.parsed.suffix) if arg.parsed.terminator == '\\t': rowlimit = rowlimit or self.maxtselctrows self.varsUsed = findBinds(arg, self.binds, bindVarsIn) @@ -717,15 +716,15 @@ self.age_out_resultsets() self.stdout.write('\n%s\n' % (self.output(arg.parsed.terminator, rowlimit))) if self.rc == 0: - print '\nNo rows Selected.\n' + self.pfeedback('\nNo rows Selected.\n') elif self.rc == 1: - print '\n1 row selected.\n' + self.pfeedback('\n1 row selected.\n') if self.autobind: self.do_bind('') elif (self.rc < self.maxfetch and self.rc > 0): - print '\n%d rows selected.\n' % self.rc + self.pfeedback('\n%d rows selected.\n' % self.rc) else: - print '\nSelected Max Num rows (%d)' % self.rc + self.pfeedback('\nSelected Max Num rows (%d)' % self.rc) def do_cat(self, arg): '''Shortcut for SELECT * FROM''' @@ -772,7 +771,7 @@ self.stdout.write('REMARK BEGIN %s\n%s\nREMARK END\n\n' % (object_name, code)) except cx_Oracle.DatabaseError, errmsg: if object_type == 'JOB': - print '%s: DBMS_METADATA.GET_DDL does not support JOBs (MetaLink DocID 567504.1)' % object_name + self.pfeedback('%s: DBMS_METADATA.GET_DDL does not support JOBs (MetaLink DocID 567504.1)' % object_name) elif 'ORA-31603' in str(errmsg): # not found, as in package w/o package body pass else: @@ -1025,7 +1024,7 @@ try: return self._resolve(identifier) except (TypeError, IndexError): - print 'Could not resolve object %s.' % identifier + self.pfeedback('Could not resolve object %s.' % identifier) return '', '', '' def resolve_with_column(self, identifier): @@ -1046,7 +1045,7 @@ def spoolstop(self): if self.spoolFile: self.stdout = self.stdoutBeforeSpool - print 'Finished spooling to ', self.spoolFile.name + self.pfeedback('Finished spooling to ', self.spoolFile.name) self.spoolFile.close() self.spoolFile = None @@ -1059,13 +1058,17 @@ if arg.lower() != 'off': if '.' not in arg: arg = '%s.lst' % arg - print 'Sending output to %s (until SPOOL OFF received)' % (arg) + self.pfeedback('Sending output to %s (until SPOOL OFF received)' % (arg)) self.spoolFile = open(arg, 'w') self.stdout = self.spoolFile + def sqlfeedback(self, arg): + if self.sql_echo: + self.pfeedback(arg) + def do_write(self, args): 'Obsolete command. Use (query) > outfilename instead.' - print self.do_write.__doc__ + self.poutput(self.do_write.__doc__) return def do_compare(self, args): @@ -1078,7 +1081,7 @@ fnames = [] args2 = args.split(' to ') if len(args2) < 2: - print self.do_compare.__doc__ + self.pfeedback(self.do_compare.__doc__) return for n in range(len(args2)): query = args2[n] @@ -1128,7 +1131,7 @@ try: return self.onecmd('%s %s%s%s' % (commands[abbrev], args, arg.parsed.terminator, arg.parsed.suffix)) except KeyError: - print 'psql command \%s not yet supported.' % abbrev + self.perror('psql command \%s not yet supported.' % abbrev) @options([all_users_option]) def do__dir_tables(self, arg, opts): @@ -1137,8 +1140,7 @@ ''' sql = """SELECT table_name, 'TABLE' as type%s FROM %s_tables WHERE table_name LIKE '%%%s%%';""" % \ (opts.scope['col'], opts.scope['view'], arg.upper()) - if self.sql_echo: - print sql + self.sqlfeedback(sql) self.do_select(self.parsed(sql, terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix)) @options([all_users_option]) @@ -1148,8 +1150,7 @@ ''' sql = """SELECT view_name, 'VIEW' as type%s FROM %s_views WHERE view_name LIKE '%%%s%%';""" % \ (opts.scope['col'], opts.scope['view'], arg.upper()) - if self.sql_echo: - print sql + self.sqlfeedback(sql) self.do_select(self.parsed(sql, terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix)) def do__dir_indexes(self, arg): @@ -1173,8 +1174,7 @@ Lists all tablespaces. ''' sql = """SELECT tablespace_name, file_name from dba_data_files;""" - if self.sql_echo: - print sql + self.sqlfeedback(sql) self.do_select(self.parsed(sql, terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix)) def do__dir_schemas(self, arg): @@ -1182,8 +1182,7 @@ Lists all object owners, together with the number of objects they own. ''' sql = """SELECT owner, count(*) AS objects FROM all_objects GROUP BY owner ORDER BY owner;""" - if self.sql_echo: - print sql + self.sqlfeedback(sql) self.do_select(self.parsed(sql, terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix)) def do_head(self, arg): @@ -1205,7 +1204,7 @@ self.stdout.write('No bind variable %s\n' % arg) else: for (var, val) in sorted(self.binds.items()): - print ':%s = %s' % (var, val) + self.poutput(':%s = %s' % (var, val)) def split_on_parser(self, parser, arg): try: @@ -1258,13 +1257,13 @@ '''Sets or shows values of substitution (`&`) variables.''' if not arg: for (substvar, val) in sorted(self.substvars.items()): - print 'DEFINE %s = "%s" (%s)' % (substvar, val, type(val)) + self.poutput('DEFINE %s = "%s" (%s)' % (substvar, val, type(val))) assigned, var, val = self.interpret_variable_assignment(arg) if assigned: self.substvars[var] = val else: if var in self.substvars: - print 'DEFINE %s = "%s" (%s)' % (var, self.substvars[var], type(self.substvars[var])) + self.poutput('DEFINE %s = "%s" (%s)' % (var, self.substvars[var], type(self.substvars[var]))) def do_exec(self, arg): if arg.startswith(':'): @@ -1274,7 +1273,7 @@ try: self.curs.execute('begin\n%s;end;' % arg, varsUsed) except Exception, e: - print e + self.perror(e) ''' Fails: @@ -1291,7 +1290,7 @@ try: self.curs.execute('\n'.join(lines)) except Exception, e: - print e + self.perror(e) return lines.append(line) @@ -1426,7 +1425,7 @@ sql = self.parsed('SELECT * FROM %s WHERE %s;' % (target, sql), terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix) self.do_select(sql) except Exception, e: - print e + self.perror(e) import traceback traceback.print_exc(file=sys.stdout) @@ -1436,8 +1435,7 @@ return converter % {'colname': colname, 'typ': typ} def _execute(self, sql, bindvars={}): - if self.sql_echo: - print sql + self.sqlfeedback(sql) self.curs.execute(sql, bindvars) #@options([make_option('-l', '--long', action='store_true', @@ -1446,7 +1444,7 @@ '''Lists referential integrity (foreign key constraints) on an object or referring to it.''' if not arg.strip(): - print 'Usage: refs (table name)' + self.perror('Usage: refs (table name)') result = [] (type, owner, table_name) = self.resolve(arg.upper()) sql = """SELECT constraint_name, r_owner, r_constraint_name diff -r 00b183a103b3 -r ee7875c7e928 sqlpython/sqlpython.py --- a/sqlpython/sqlpython.py Wed Apr 08 17:01:40 2009 -0400 +++ b/sqlpython/sqlpython.py Wed Apr 08 17:06:04 2009 -0400 @@ -91,7 +91,7 @@ try: oraserv = os.environ['ORACLE_SID'] except KeyError: - print 'instance not specified and environment variable ORACLE_SID not set' + self.perror('instance not specified and environment variable ORACLE_SID not set') return orauser = arg sid = oraserv @@ -110,7 +110,7 @@ except ValueError: orapass = getpass.getpass('Password: ') if orauser.upper() == 'SYS' and not modeval: - print 'Privilege not specified for SYS, assuming SYSOPER' + self.pfeedback('Privilege not specified for SYS, assuming SYSOPER') modeval = cx_Oracle.SYSOPER result = self.url_connect('oracle://%s:%s@%s/?mode=%d' % (orauser, orapass, oraserv, modeval)) result['dbname'] = oraserv @@ -215,11 +215,11 @@ for err in errors: if (mintime is not None) and (err[8] < mintime): break - print '%s at line %d of %s %s.%s:' % (err[5], err[4], err[2], err[0], err[1]) - print err[7] - print (' ' * (err[3]-1)) + '^' - print err[6] - print '\n' + self.poutput('%s at line %d of %s %s.%s:' % (err[5], err[4], err[2], err[0], err[1])) + self.poutput(err[7]) + self.poutput((' ' * (err[3]-1)) + '^') + self.poutput(err[6]) + self.poutput('\n') def current_database_time(self): self.curs.execute('select sysdate from dual') @@ -243,7 +243,7 @@ \\L scatter plot (no lines) \\b bar graph \\p pie chart""" - print self.do_terminators.__doc__ + self.poutput(self.do_terminators.__doc__) terminatorSearchString = '|'.join('\\' + d.split()[0] for d in do_terminators.__doc__.splitlines()) @@ -260,7 +260,7 @@ executionmessage = '\nExecuted%s\n' % ((self.curs.rowcount > 0) and ' (%d rows)' % self.curs.rowcount or '') if self.rdbms == 'oracle': self._show_errors(all_users=True, limit=1, mintime=current_time) - print executionmessage + self.pfeedback(executionmessage) def do_commit(self, arg=''): self.default(self.parsed('commit %s;' % (arg)))