changeset 336:ee7875c7e928

merge in changes to poutput, perror, pfeedback
author Catherine Devlin <catherine.devlin@gmail.com>
date Wed, 08 Apr 2009 17:06:04 -0400
parents 00b183a103b3 (current diff) 1cde0ec62e61 (diff)
children a8835fe129f6
files sqlpython/sqlpyPlus.py sqlpython/sqlpython.py
diffstat 4 files changed, 58 insertions(+), 61 deletions(-) [+]
line wrap: on
line diff
--- 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
--- 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 
 
--- 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 
--- 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)))