changeset 0:9c87fa772ec1

before big refactor
author catherine@serenity.wpafb.af.mil
date Fri, 30 Nov 2007 13:04:51 -0500
parents
children 8fa146b9a2d7
files __init__.py mysqlpy.py pexpecter.py sqlpyPlus.py sqlpython.py
diffstat 5 files changed, 1219 insertions(+), 0 deletions(-) [+]
line wrap: on
line diff
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/__init__.py	Fri Nov 30 13:04:51 2007 -0500
@@ -0,0 +1,1 @@
+__all__ = ["sqlpython", "sqlpyPlus", "pexpecter", "mysqlpy"]
\ No newline at end of file
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/mysqlpy.py	Fri Nov 30 13:04:51 2007 -0500
@@ -0,0 +1,185 @@
+#!/usr/bin/python
+# MySqlPy V1.3
+# Author: Luca.Canali@cern.ch
+# 
+#
+# Companion of SqlPython, a python module that reproduces Oracle's command line within python
+# 'sqlplus inside python'
+# See also: http://twiki.cern.ch/twiki/bin/view/PSSGroup/SqlPython
+#           http://catherine.devlin.googlepages.com/
+
+from sqlpyPlus import *
+import binascii
+
+class mysqlpy(sqlpyPlus):
+    '''
+MySqlPy V1.3 - 'sqlplus in python'
+Author: Luca.Canali@cern.ch
+Rev: 1.3.0, 17-Oct-07
+
+Companion of SqlPython, a python module that reproduces Oracle's command line within python
+and sqlpyPlus. Major contributions by Catherine Devlin, http://catherinedevlin.blogspot.com
+Quick start command list:
+
+- top     -> executes a query to list all active sessions in (Oracle 10g and RAC)
+             (use: instance activity monitoring, a DBA tool)
+- tselect -> prints the result set in trasposed form, useful to print result sets with
+             many columns such as dba_ or v$ views (ex: dba_tables or v$instance)
+- py      -> execute a python command (C.D.) 
+- db      -> quick connect using credentials in pass.txt file
+             (Ex: write username and pass in pass.txt and then "db db_alias" to connect)
+- sql     -> prints the sql text from the cache. parameter: sql_id of the statement
+             (Ex: sql fzqa1qj65nagki)
+- explain -> prints the execution plan from the cache. parameter: sql_id of the statement 
+- sessinfo-> prints session information. 1 parameter sid (Ex: sql 101 print info for sid 101)
+- longops -> prints from gv$session_longops (running full scans, etc)
+- load    -> prints the OS load on all cluster nodes (10g RAC)
+- sleect,slect  -> alias for select (I mistyped select this way too many times...)
+- top9i   -> 9i (and single instance) version of top
+- describe, @, !, spool, show, set, list, get, write -> sql*plus-like, from sqlpyPlus (C.D.)
+- shortcuts: \c (connect), \d (describe), etc, from sqlpyPlus (C.D.)
+- :myvarname = xx, set autobind 1, print -> bind variables management extension, to sqlplus (C.D.)
+
+Example:
+ SQL> connect username@dbalias or username/pass@dbalias
+ SQL> select sysdate from dual;
+ SQL> exit
+    '''
+
+    def __init__(self):
+        sqlpyPlus.__init__(self)
+        self.maxtselctrows = 10
+        self.query_load10g = '''
+	  ins.instance_name,ins.host_name,round(os.value,2) load
+	  from gv$osstat os, gv$instance ins
+	  where os.inst_id=ins.inst_id and os.stat_name='LOAD'
+	  order by 3 desc;
+        '''
+        self.query_top9i = '''
+          sid,username,osuser||'@'||terminal "Server User@terminal",program,taddr, status,
+	  module, sql_hash_value hash, fixed_table_sequence seq, last_call_et elaps 
+          from v$session 
+          where username is not null and program not like 'emagent%' and status='ACTIVE'
+                and audsid !=sys_context('USERENV','SESSIONID'); 
+        '''
+        self.query_ractop = '''
+ 	inst_id||'_'||sid inst_sid,username,osuser||'@'||terminal "User@Term",program, decode(taddr,null,null,'NN') tr,  
+	sql_id, '.'||mod(fixed_table_sequence,1000) seq, state||': '||event event,
+	case state when 'WAITING' then seconds_in_wait else wait_time end w_tim, last_call_et elaps
+        from gv$session 
+        where status='ACTIVE' and username is not null 
+	      and not (event like '% waiting for messages in the queue' and state='WAITING')
+              and audsid !=sys_context('USERENV','SESSIONID');
+        '''
+        self.query_longops = '''
+        inst_id,sid,username,time_remaining remaining, elapsed_seconds elapsed, sql_hash_value hash, opname,message
+        from gv$session_longops
+        where time_remaining>0;
+        '''
+
+    def do_top9i(self,args):
+        '''Runs query_top9i defined above, to display active sessions in Oracle 9i'''
+        self.do_select(self.query_top9i)
+    
+    def do_top(self,args): 
+        '''Runs query_ractop defined above, to display active sessions in Oracle 10g (and RAC)'''
+        self.do_select(self.query_ractop)
+
+    def do_longops(self,args):
+        '''Runs query_longops defined above, to display long running operations (full scans, etc)'''
+        self.do_select(self.query_longops)
+
+    def do_load(self,args):
+        '''Runs query_load10g defined above, to display OS load on cluster nodes (10gRAC)'''
+        self.do_select(self.query_load10g)
+
+    def do_db(self,args,filepath='pass.txt'): 
+        '''Exec do_connect to db_alias in args (credentials form the file pass.txt) '''
+        f = open(filepath,'r')
+        connectstr = f.readline().strip() +'@'+args
+        self.do_connect(connectstr)
+        f.close()
+
+    def do_py(self, arg):  
+        '''Executes a python command'''
+        try:
+            exec(arg)
+        except Exception, e:
+            print e
+
+    def do_tselect(self, arg, rowlimit=None):  
+        '''executes a query and prints the result in trasposed form. Useful when querying tables with many columns''' 
+        self.query = sqlpython.Statement('select '+arg).query
+        try:
+            print self.query
+            self.curs.execute(self.query)
+            rows = self.curs.fetchmany(min(self.maxtselctrows, rowlimit or self.maxtselctrows))
+            desc = self.curs.description
+            self.rc = self.curs.rowcount
+            rows.insert(0,[desc[x][0] for x in range(len(desc))]) # adds column name to the row set
+            transpr = [[rows[y][x] for y in range(len(rows))]for x in range(len(rows[0]))] # matrix transpose
+            newdesc = [['ROW N.'+str(y),10] for y in range(len(rows))]
+            for x in range(len(desc)):
+                if str(desc[x][1]) == "<type 'cx_Oracle.BINARY'>":  # handles RAW columns
+                    rname = transpr[x][0]
+                    transpr[x] = map(binascii.b2a_hex, transpr[x])
+                    transpr[x][0] = rname
+            self.debg=transpr
+            newdesc[0][0] = 'COLUMN NAME'
+            if self.rc > 0:
+                print '\n' + sqlpython.pmatrix(transpr,newdesc)
+            if self.rc == 0:
+                print '\nNo rows Selected.\n'
+            elif self.rc == 1: 
+                print '\n1 row selected.\n'
+            elif self.rc < self.maxtselctrows:
+                print '\n%d rows selected.\n' % self.rc
+            else:
+                print '\nSelected Max Num rows (%d)' % self.rc                 
+        except Exception, e:
+            print e
+
+    def do_sql(self,args):
+        '''prints sql statement give the sql_id (Oracle 10gR2)'''
+        self.query = "select inst_id, sql_fulltext from gv$sqlstats where sql_id='"+args+"'"
+        try:
+            self.curs.execute(self.query)
+            row = self.curs.fetchone()
+            print "\nSQL statement from cache"
+            print "------------------------\n"
+            while row:
+                print "\nINST_ID = "+str(row[0])+" - SQL TEXT:\n", row[1].read()
+                row = self.curs.next()
+        except Exception, e:
+            print 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 '''
+        self.query = "select * from table(dbms_xplan.display_cursor('"+args+"'))"
+        try:
+            self.curs.execute(self.query)
+            rows = self.curs.fetchall()
+            desc = self.curs.description
+            self.rc = self.curs.rowcount
+            if self.rc > 0:
+                print '\n' + sqlpython.pmatrix(rows,desc,200)
+        except Exception, e:
+            print e
+
+    def do_sessinfo(self,args):
+        '''Reports session info for the give sid, extended to RAC with gv$'''
+        self.do_tselect('* from gv$session where sid='+args+';')
+
+    def do_sleect(self,args):    
+        '''implements sleect = select, a common typo'''
+        self.do_select(args)
+
+    do_slect = do_sleect
+
+def run():
+    my=mysqlpy()
+    print my.__doc__
+    my.cmdloop()
+
+if __name__ == '__main__':
+    run()
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/pexpecter.py	Fri Nov 30 13:04:51 2007 -0500
@@ -0,0 +1,93 @@
+"""
+pexpecter
+
+Uses pexpect to handle interactive sessions
+Create subclass of Session for each type of program to be used
+"""
+import re, os
+
+try:
+    import pexpect
+    
+    class Session(object):
+        available = True
+        call = 'theprogram %s'
+        errPattern = re.compile('.')
+        validPattern = re.compile('Connected to:')
+        promptstub = '>'
+        def __init__(self, argstring):
+            self.argstring = argstring
+            self.sess = pexpect.spawn("%s %s" % (self.call, self.argstring))
+            try:
+                self.sess.expect(self.promptstub)
+                self.valid = self.validPattern.search(self.sess.before)
+                self.prompt = '[\r\n]%s%s' % (self.sess.before.splitlines()[-1], self.promptstub)
+	    except:
+                self.valid = False        
+        def success(self, result):
+            return not self.errPattern.search(result)
+        def attempt(self, command, timeout=30):
+            self.sess.sendline(self._pre_attempt(command))
+            try:
+                self.sess.expect(self.prompt, timeout=timeout)
+            except pexpect.TIMEOUT:
+                return (False, """Errror: Waited %d seconds with no response from %s.
+                To wait longer, set timeout.""" % (timeout, str(self.__class__)))
+            result = self.sess.before
+            success = self.success(result)
+            if success:
+                print 'Executed through %s' % (str(self.__class__))
+            return (success, result)
+        def _pre_attempt(self, command):
+            return command
+        
+    class YASQLSession(Session):
+        errPattern = re.compile('\n[A-Z2]{3,4}-\d{4}:\s')
+        terminatorPattern = re.compile('(;|\\g|\\i|\/|\\G|\\s|\\S)\s*\d*\s*$')
+        call = os.popen('locate -r /yasql$').readline().strip()
+        if not call:
+            print 'yasql not found; commands cannot failover to YASQL'
+            available = False
+        def _pre_attempt(self, command):
+            if not self.terminatorPattern.search(command):
+                return '%s;' % (command)
+            return command
+        
+    class SQLSession(Session):
+        def _pre_attempt(self, command):
+            if command.strip()[-1] != ';':
+                return '%s;' % (command)
+            return command
+
+    class SqlPlusSession(SQLSession):
+        call = r'sqlplus'
+        errPattern = re.compile('\n[A-Z2]{3,4}-\d{4}:\s')
+        """        def _pre_attempt(self, command):
+            if command.strip()[-1] != ';':
+                return '%s;' % (command)
+            return command"""
+        # can still trip on: apparent error messages listed as data
+            
+    class SenoraSession(SQLSession):
+        errPattern = re.compile('(\n[A-Z2]{3,4}-\d{4}:\s)|(\nwhat ? )')
+        call = os.popen('locate -r Senora\.pm$').readline().strip()
+        if call:
+            call = 'perl %s' % (call)
+        else:
+            print 'Senora.pm not found; commands cannot failover to Senora'
+            available = False        
+    
+except ImportError:
+    print '''Python's pexpect module is not installed; cannot pass
+    commands through to sqlplus, etc.'''    
+    class Session(object):
+        valid = False    
+        available = False
+    class YASQLSession(Session):
+        pass
+    class SqlPlusSession(Session):
+        pass
+    class SenoraSession(Session):
+        pass
+        
+available = [s for s in [SenoraSession, YASQLSession, SqlPlusSession] if s.available]
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/sqlpyPlus.py	Fri Nov 30 13:04:51 2007 -0500
@@ -0,0 +1,749 @@
+"""sqlpyPlus - extra features (inspired by Oracle SQL*Plus) for Luca Canali's sqlpython.py
+
+Features include:
+ - SQL*Plus-style bind variables
+ - Query result stored in special bind variable ":_" if one row, one item
+ - SQL buffer with list, run, ed, get, etc.; unlike SQL*Plus, buffer stores session's full history
+ - @script.sql loads and runs (like SQL*Plus)
+ - ! runs operating-system command
+ - show and set to control sqlpython parameters
+ - SQL*Plus-style describe, spool
+ - write sends query result directly to file
+ - comments shows table and column comments
+ - compare ... to ... graphically compares results of two queries
+ - commands are case-insensitive
+ 
+Use 'help' within sqlpython for details.
+
+Compatible with sqlpython v1.3
+
+Set bind variables the hard (SQL*Plus) way
+exec :b = 3
+or with a python-like shorthand
+:b = 3
+
+- catherinedevlin.blogspot.com  May 31, 2006
+"""
+# note in cmd.cmd about supporting emacs commands?
+
+queries = {
+'resolve': """
+SELECT object_type, object_name, owner FROM (
+	SELECT object_type, object_name, user owner, 1 priority
+	FROM   user_objects
+	WHERE object_name = :objName
+    UNION ALL
+	SELECT ao.object_type, ao.object_name, ao.owner, 2 priority
+	FROM    all_objects ao
+	JOIN      user_synonyms us ON (us.table_owner = ao.owner AND us.table_name = ao.object_name)
+	WHERE us.synonym_name = :objName
+    AND   ao.object_type != 'SYNONYM'
+    UNION ALL
+	SELECT ao.object_type, ao.object_name, ao.owner, 3 priority
+	FROM    all_objects ao
+	JOIN      all_synonyms asyn ON (asyn.table_owner = ao.owner AND asyn.table_name = ao.object_name)
+	WHERE asyn.synonym_name = :objName
+    AND   ao.object_type != 'SYNONYM'
+	AND      asyn.owner = 'PUBLIC'
+) ORDER BY priority ASC""",
+'descTable': """
+ atc.column_name,
+            CASE atc.nullable WHEN 'Y' THEN 'NULL' ELSE 'NOT NULL' END "Null?",
+            atc.data_type ||
+              CASE atc.data_type WHEN 'DATE' THEN ''
+                             ELSE '(' ||
+                               CASE atc.data_type WHEN 'NUMBER' THEN TO_CHAR(atc.data_precision) ||
+                                 CASE atc.data_scale WHEN 0 THEN ''
+                                                 ELSE ',' || TO_CHAR(atc.data_scale) END
+                                              ELSE TO_CHAR(atc.data_length) END 
+                             END ||
+              CASE atc.data_type WHEN 'DATE' THEN '' ELSE ')' END
+              data_type
+FROM all_tab_columns atc
+WHERE atc.table_name = :object_name
+AND      atc.owner = :owner
+ORDER BY atc.column_id;""",
+'PackageObjects':"""
+SELECT DISTINCT object_name
+FROM all_arguments
+WHERE package_name = :package_name
+AND      owner = :owner""",
+'PackageObjArgs':"""
+             object_name,
+	     argument_name,	     
+	     data_type,
+	     in_out,
+	     default_value
+FROM all_arguments
+WHERE package_name = :package_name
+AND      object_name = :object_name
+AND      owner = :owner
+AND      argument_name IS NOT NULL
+ORDER BY sequence""",
+'descProcedure':"""
+	     argument_name,	     
+	     data_type,
+	     in_out,
+	     default_value
+FROM all_arguments
+WHERE object_name = :object_name
+AND      owner = :owner
+AND      package_name IS NULL
+AND      argument_name IS NOT NULL
+ORDER BY sequence;""",
+'tabComments': """
+SELECT comments
+FROM    all_tab_comments
+WHERE owner = :owner
+AND      table_name = :table_name""",
+'colComments': """
+atc.column_name,
+             acc.comments	     
+FROM all_tab_columns atc
+JOIN all_col_comments acc ON (atc.owner = acc.owner and atc.table_name = acc.table_name and atc.column_name = acc.column_name)
+WHERE atc.table_name = :object_name
+AND      atc.owner = :owner
+ORDER BY atc.column_id;""",
+}
+
+import sys, os, re, sqlpython, cx_Oracle, pyparsing
+
+if float(sys.version[:3]) < 2.3:
+    def enumerate(lst):
+        return zip(range(len(lst)), lst)
+    
+class SoftwareSearcher(object):
+    def __init__(self, softwareList, purpose):
+        self.softwareList = softwareList
+        self.purpose = purpose
+        self.software = None
+    def invoke(self, *args):
+        if not self.software:
+            (self.software, self.invokeString) = self.find()            
+        argTuple = tuple([self.software] + list(args))
+        os.system(self.invokeString % argTuple)
+    def find(self):
+        if self.purpose == 'text editor':
+            software = os.environ.get('EDITOR')
+            if software:
+                return (software, '%s %s')
+        for (n, (software, invokeString)) in enumerate(self.softwareList):
+            if os.path.exists(software):
+                if n > (len(self.softwareList) * 0.7):
+                    print """
+    
+    Using %s.  Note that there are better options available for %s,
+    but %s couldn't find a better one in your PATH.
+    Feel free to open up %s
+    and customize it to find your favorite %s program.
+    
+    """ % (software, self.purpose, __file__, __file__, self.purpose)
+                return (software, invokeString)
+            stem = os.path.split(software)[1]
+            for p in os.environ['PATH'].split(os.pathsep):
+                if os.path.exists(os.sep.join([p, stem])):
+                    return (stem, invokeString)
+        raise (OSError, """Could not find any %s programs.  You will need to install one,
+    or customize %s to make it aware of yours.
+    Looked for these programs:
+    %s""" % (self.purpose, __file__, "\n".join([s[0] for s in self.softwareList])))
+    #v2.4: %s""" % (self.purpose, __file__, "\n".join(s[0] for s in self.softwareList)))
+
+softwareLists = {
+    'diff/merge': [  
+                    ('/usr/bin/meld',"%s %s %s"),
+                    ('/usr/bin/kdiff3',"%s %s %s"),
+                    (r'C:\Program Files\Araxis\Araxis Merge v6.5\Merge.exe','"%s" %s %s'),                
+                    (r'C:\Program Files\TortoiseSVN\bin\TortoiseMerge.exe', '"%s" /base:"%s" /mine:"%s"'),
+                    ('FileMerge','%s %s %s'),        
+                    ('kompare','%s %s %s'),   
+                    ('WinMerge','%s %s %s'),         
+                    ('xxdiff','%s %s %s'),        
+                    ('fldiff','%s %s %s'),
+                    ('gtkdiff','%s %s %s'),        
+                    ('tkdiff','%s %s %s'),         
+                    ('gvimdiff','%s %s %s'),        
+                    ('diff',"%s %s %s"),
+                    (r'c:\windows\system32\comp.exe',"%s %s %s")],
+    'text editor': [
+        ('gedit', '%s %s'),
+        ('textpad', '%s %s'),
+        ('notepad.exe', '%s %s'),
+        ('pico', '%s %s'),
+        ('emacs', '%s %s'),
+        ('vim', '%s %s'),
+        ('vi', '%s %s'),
+        ('ed', '%s %s'),
+        ('edlin', '%s %s')
+                   ]
+}
+
+diffMergeSearcher = SoftwareSearcher(softwareLists['diff/merge'],'diff/merge')
+editSearcher = SoftwareSearcher(softwareLists['text editor'], 'text editor')
+editor = os.environ.get('EDITOR')
+if editor:
+    editSearcher.find = lambda: (editor, "%s %s")
+
+class CaselessDict(dict):
+    """dict with case-insensitive keys.
+
+    Posted to ASPN Python Cookbook by Jeff Donner - http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/66315"""
+    def __init__(self, other=None):
+        if other:
+            # Doesn't do keyword args
+            if isinstance(other, dict):
+                for k,v in other.items():
+                    dict.__setitem__(self, k.lower(), v)
+            else:
+                for k,v in other:
+                    dict.__setitem__(self, k.lower(), v)
+    def __getitem__(self, key):
+        return dict.__getitem__(self, key.lower())
+    def __setitem__(self, key, value):
+        dict.__setitem__(self, key.lower(), value)
+    def __contains__(self, key):
+        return dict.__contains__(self, key.lower())
+    def has_key(self, key):
+        return dict.has_key(self, key.lower())
+    def get(self, key, def_val=None):
+        return dict.get(self, key.lower(), def_val)
+    def setdefault(self, key, def_val=None):
+        return dict.setdefault(self, key.lower(), def_val)
+    def update(self, other):
+        for k,v in other.items():
+            dict.__setitem__(self, k.lower(), v)
+    def fromkeys(self, iterable, value=None):
+        d = CaselessDict()
+        for k in iterable:
+            dict.__setitem__(d, k.lower(), value)
+        return d
+    def pop(self, key, def_val=None):
+        return dict.pop(self, key.lower(), def_val)
+    
+class NotSettableError(Exception):
+    None
+
+class Parser(object):
+    comment_def = "--" + pyparsing.ZeroOrMore(pyparsing.CharsNotIn("\n"))    
+    def __init__(self, scanner, retainSeparator=True):
+        self.scanner = scanner
+        self.scanner.ignore(pyparsing.sglQuotedString)
+        self.scanner.ignore(pyparsing.dblQuotedString)
+        self.scanner.ignore(self.comment_def)
+        self.scanner.ignore(pyparsing.cStyleComment)
+        self.retainSeparator = retainSeparator
+    def separate(self, txt):
+        itms = []
+        for (sqlcommand, start, end) in self.scanner.scanString(txt):
+            if sqlcommand:
+                if type(sqlcommand[0]) == pyparsing.ParseResults:
+                    if self.retainSeparator:
+                        itms.append("".join(sqlcommand[0]))
+                    else:
+                        itms.append(sqlcommand[0][0])
+                else:
+                    if sqlcommand[0]:
+                        itms.append(sqlcommand[0])
+        return itms
+
+pipeSeparator = Parser(pyparsing.SkipTo((pyparsing.Literal('|') ^ pyparsing.StringEnd()), include=True), retainSeparator=False) 
+bindScanner = Parser(pyparsing.Literal(':') + pyparsing.Word( pyparsing.alphanums + "_$#" ))
+commandSeparator = Parser(pyparsing.SkipTo((pyparsing.Literal(';') ^ pyparsing.StringEnd()), include=True))
+
+def findBinds(target, existingBinds, givenBindVars = {}):
+    result = givenBindVars
+    for finding, startat, endat in bindScanner.scanner.scanString(target):
+        varname = finding[1]
+        try:
+            result[varname] = existingBinds[varname]
+        except KeyError:
+            if not givenBindVars.has_key(varname):
+                print 'Bind variable %s not defined.' % (varname)                
+    return result
+
+class sqlpyPlus(sqlpython.sqlpython):
+    def __init__(self):
+        sqlpython.sqlpython.__init__(self)
+        self.binds = CaselessDict()
+        self.sqlBuffer = []
+        self.settable = ['maxtselctrows', 'maxfetch', 'autobind', 'failover', 'timeout'] # settables must be lowercase
+        self.stdoutBeforeSpool = sys.stdout
+        self.spoolFile = None
+        self.autobind = False
+        self.failover = False
+
+    def default(self, arg, do_everywhere=False):
+        sqlpython.sqlpython.default(self, arg, do_everywhere)
+        self.sqlBuffer.append(self.query)            
+
+    # overrides cmd's parseline
+    shortcuts = {'?': 'help', '@': 'getrun', '!': 'shell', ':': 'setbind', '\\': 'psql'}
+    def parseline(self, line):
+        """Parse the line into a command name and a string containing
+        the arguments.  Returns a tuple containing (command, args, line).
+        'command' and 'args' may be None if the line couldn't be parsed.	
+        Overrides cmd.cmd.parseline to accept variety of shortcuts.."""
+        line = line.strip()
+        if not line:
+            return None, None, line
+        shortcut = self.shortcuts.get(line[0])
+        if shortcut:
+            cmd, arg = shortcut, line[1:].strip()
+        else:
+            i, n = 0, len(line)
+            while i < n and line[i] in self.identchars: i = i+1
+            cmd, arg = line[:i], line[i:].strip()
+        if cmd.lower() in ('select', 'sleect', 'insert', 'update', 'delete', 'describe',
+                          'desc', 'comments') \
+            and not hasattr(self, 'curs'):
+            print 'Not connected.'
+            return '', '', ''
+        return cmd, arg, line
+
+    def precmd(self, line):
+        """Hook method executed just before the command line is
+        interpreted, but after the input prompt is generated and issued.
+        Makes commands case-insensitive (but unfortunately does not alter command completion).
+        """
+        savestdout = sys.stdout
+        pipefilename = 'sqlpython.pipeline.tmp'
+        pipedCommands = pipeSeparator.separate(line)
+        if len(pipedCommands) > 1:
+            f = open(pipefilename,'w')
+            sys.stdout = f
+            self.precmd(pipedCommands[0])
+            self.onecmd(pipedCommands[0])
+            self.postcmd(False, pipedCommands[0])
+            f.close()
+            sys.stdout = savestdout
+            os.system('%s < %s' % (pipedCommands[1], pipefilename))
+        try:
+            args = line.split(None,1)
+            args[0] = args[0].lower()
+            return ' '.join(args)
+        except Exception:
+            return line
+
+    def do_shortcuts(self,arg):
+        """Lists available first-character shortcuts
+        (i.e. '!dir' is equivalent to 'shell dir')"""
+        for (scchar, scto) in self.shortcuts.items():
+            print '%s: %s' % (scchar, scto)
+
+
+    def colnames(self):
+        return [d[0] for d in curs.description]
+
+    def sql_format_itm(self, itm, needsquotes):
+        if itm is None:
+            return 'NULL'
+        if needsquotes:
+            return "'%s'" % str(itm)
+        return str(itm)
+    def output_as_insert_statements(self):
+        usequotes = [d[1] != cx_Oracle.NUMBER for d in self.curs.description]
+        def formatRow(row):
+            return ','.join(self.sql_format_itm(itm, useq)
+                            for (itm, useq) in zip(row, usequotes))
+        result = ['INSERT INTO %s (%s) VALUES (%s);' %
+                  (self.tblname, ','.join(self.colnames), formatRow(row))
+                  for row in self.rows]
+        return '\n'.join(result)
+        
+    def output_row_as_xml(self, row):
+        result = ['  <%s>\n    %s\n  </%s>' %
+                  (colname.lower(), str('' if (itm is None) else itm), colname.lower()) 
+                  for (itm, colname) in zip(row, self.colnames)]
+        return '\n'.join(result)        
+    def output_as_xml(self):
+        result = ['<%s>\n%s\n</%s>' %
+                  (self.tblname, self.output_row_as_xml(row), self.tblname)
+                  for row in self.rows]
+        return '\n'.join(result)
+
+    def output_as_html_table(self):
+        result = ''.join('<th>%s</th>' % c for c in self.colnames)
+        result = ['  <tr>\n    %s\n  </tr>' % result]
+        for row in self.rows:
+            result.append('  <tr>\n    %s\n  </tr>' %
+                          (''.join('<td>%s</td>' %
+                                   str('' if (itm is None) else itm)
+                           for itm in row)))                
+            result = '''<table id="%s">
+%s
+</table>''' % (self.tblname, '\n'.join(result))
+        return '\n'.join(result)
+
+    def output_as_list(self, align):
+        result = []
+        colnamelen = max(len(colname) for colname in self.colnames) + 1        
+        for (idx, row) in enumerate(self.rows):
+            result.append('\n**** Row: %d' % (idx+1))
+            for (itm, colname) in zip(row, self.colnames):
+                if align:
+                    colname = colname.ljust(colnamelen)
+                result.append('%s: %s' % (colname, itm))
+        return '\n'.join(result)
+
+    tableNameFinder = re.compile(r'from\s+([\w$#_"]+)', re.IGNORECASE | re.MULTILINE | re.DOTALL)          
+    def output(self, outformat, rowlimit):
+        self.tblname = self.tableNameFinder.search(self.curs.statement).group(1)
+        self.colnames = [d[0] for d in self.curs.description]
+        if outformat == '\\i':
+            result = self.output_as_insert_statements()
+        elif outformat ==  '\\x':
+            result = self.output_as_xml()
+        elif outformat == '\\g':
+            result = self.output_as_list(align=False)
+        elif outformat == '\\G':
+            result = self.output_as_list(align=True)            
+        elif outformat in ('\\s', '\\S', '\\c', '\\C'): #csv
+            result = []
+            if outformat in ('\\s', '\\c'):
+                result.append(','.join('"%s"' % colname for colname in self.colnames))
+            for row in self.rows:
+                result.append(','.join('"%s"' % ('' if itm is None else itm) for itm in row))
+            result = '\n'.join(result)
+        elif outformat == '\\h':
+            result = self.output_as_html_table()
+        else:
+            result = sqlpython.pmatrix(self.rows, self.curs.description, self.maxfetch)
+        return result
+                        
+    def do_select(self, arg, bindVarsIn=None):
+        """Fetch rows from a table.
+        
+        Limit the number of rows retrieved by appending
+        an integer after the terminator
+        (example: SELECT * FROM mytable;10 )
+        
+        Output may be formatted by choosing an alternative terminator
+        ("help terminators" for details)
+        """
+        bindVarsIn = bindVarsIn or {}
+        stmt = sqlpython.Statement('select '+arg)
+        self.query = stmt.query
+        if stmt.outformat == '\\t':
+            self.do_tselect(' '.join(self.query.split()[1:]) + ';', stmt.rowlimit)
+        else:
+            try:
+                self.varsUsed = findBinds(self.query, self.binds, bindVarsIn)
+                self.curs.execute(self.query, self.varsUsed)
+                self.rows = self.curs.fetchmany(min(self.maxfetch, (stmt.rowlimit or self.maxfetch)))
+                self.desc = self.curs.description
+                self.rc = self.curs.rowcount
+                if self.rc > 0:
+                    print '\n' + self.output(stmt.outformat, stmt.rowlimit)
+                if self.rc == 0:
+                    print '\nNo rows Selected.\n'
+                elif self.rc == 1: 
+                    print '\n1 row selected.\n'
+                    if self.autobind:
+                        self.binds.update(dict(zip([d[0] for d in self.desc], self.rows[0])))
+                elif self.rc < self.maxfetch:
+                    print '\n%d rows selected.\n' % self.rc
+                else:
+                    print '\nSelected Max Num rows (%d)' % self.rc                 
+            except Exception, e:
+                print e
+                import traceback
+                traceback.print_exc(file=sys.stdout)
+        self.sqlBuffer.append(self.query)
+
+    def showParam(self, param):
+        param = param.strip().lower()
+        if param in self.settable:
+            val = getattr(self, param)
+            print '%s: %s' % (param, str(getattr(self, param)))
+
+    def do_show(self, arg):
+        'Shows value of a (sqlpython, not ORACLE) parameter'
+        arg = arg.strip().lower()
+        if arg:
+            self.showParam(arg)
+        else:
+            for param in self.settable:
+                self.showParam(param)
+
+    def cast(self, current, new):
+        typ = type(current)
+        if typ == bool:
+            new = new.lower()            
+            try:
+                if (new=='on') or (new[0] in ('y','t')):
+                    return True
+                return False
+            except TypeError:
+                None
+        try:
+            return typ(new)
+        except:
+            print "Problem setting parameter (now %s) to %s; incorrect type?" % (current, new)
+            return current
+    
+    def do_set(self, arg):
+        'Sets a (sqlpython, not ORACLE) parameter'        
+        try:
+            paramName, val = arg.split(None, 1)
+        except Exception:
+            self.do_show(arg)
+            return
+        paramName = paramName.lower()
+        try:
+            current = getattr(self, paramName)
+            if callable(current):
+                raise NotSettableError
+        except (AttributeError, NotSettableError):
+            self.fail('set %s' % arg)
+            return
+        val = self.cast(current, val.strip(';'))
+        print paramName, ' - was: ', current
+        setattr(self, paramName.lower(), val)
+        print 'now: ', val
+    
+    def do_describe(self, arg):
+        "emulates SQL*Plus's DESCRIBE"
+        object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper())
+        print "%s %s.%s" % (object_type, owner, object_name)
+        if object_type in ('TABLE','VIEW'):
+            self.do_select(queries['descTable'],{'object_name':object_name, 'owner':owner})
+        elif object_type == 'PACKAGE':
+            self.curs.execute(queries['PackageObjects'], {'package_name':object_name, 'owner':owner})
+            for (packageObj_name,) in self.curs:
+                print packageObj_name
+                self.do_select(queries['PackageObjArgs'],{'package_name':object_name, 'owner':owner, 'object_name':packageObj_name})
+        else:
+            self.do_select(queries['descProcedure'],{'owner':owner, 'object_name':object_name})
+    do_desc = do_describe
+    
+    def do_comments(self, arg):
+        'Prints comments on a table and its columns.'
+        object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper())
+        if object_type:
+            self.curs.execute(queries['tabComments'],{'table_name':object_name, 'owner':owner})
+            print "%s %s.%s: %s" % (object_type, owner, object_name, self.curs.fetchone()[0])
+            self.do_select(queries['colComments'],{'owner':owner, 'object_name': object_name})
+
+    def resolve(self, identifier):
+        """Checks (my objects).name, (my synonyms).name, (public synonyms).name
+        to resolve a database object's name. """
+        parts = identifier.split('.')
+        try:
+            if len(parts) == 2:
+                owner, object_name = parts
+                self.curs.execute('SELECT object_type FROM all_objects WHERE owner = :owner AND object_name = :object_name',
+                                  {'owner': owner, 'object_name': object_name})
+                object_type = self.curs.fetchone()[0]
+            elif len(parts) == 1:
+                object_name = parts[0]
+                self.curs.execute(queries['resolve'], {'objName':object_name})
+                object_type, object_name, owner = self.curs.fetchone()
+        except TypeError:
+            print 'Could not resolve object %s.' % identifier
+            object_type, owner, object_name = '', '', ''
+        return object_type, owner, object_name
+
+    def do_shell(self, arg):
+        'execute a command as if at the OS prompt.'
+        os.system(arg)
+        
+    def spoolstop(self):
+        if self.spoolFile:
+            sys.stdout = self.stdoutBeforeSpool
+            print 'Finished spooling to ', self.spoolFile.name
+            self.spoolFile.close()
+            self.spoolFile = None
+    
+    def do_spool(self, arg):
+        """spool [filename] - begins redirecting output to FILENAME."""
+        self.spoolstop()
+        arg = arg.strip()
+        if not arg:
+            arg = 'output.lst'
+        if arg.lower() != 'off':
+            if '.' not in arg:
+                arg = '%s.lst' % arg
+            print 'Sending output to %s (until SPOOL OFF received)' % (arg)
+            self.spoolFile = open(arg, 'w')
+            sys.stdout = self.spoolFile
+
+    def write(self, arg, fname): 
+        originalOut = sys.stdout
+        f = open(fname, 'w')
+        sys.stdout = f
+        self.onecmd(arg)
+        f.close()
+        sys.stdout = originalOut
+        
+    def do_write(self, args):
+        'write [filename.extension] query - writes result to a file'
+        words = args.split(None, 1)
+        if len(words) > 1 and '.' in words[0]:
+            fname, command = words
+        else:
+            fname, command = 'output.txt', args
+        self.write(command, fname)
+        print 'Results written to %s' % os.path.join(os.getcwd(), fname)
+        
+    def do_compare(self, args):
+        """COMPARE query1 TO query2 - uses external tool to display differences.
+    
+        Sorting is recommended to avoid false hits."""
+        fnames = []
+        args2 = args.split(' to ')
+        for n in range(len(args2)):
+            query = args2[n]
+            fnames.append('compare%s.txt' % n)
+            if query.rstrip()[-1] != self.terminator: 
+                query = '%s%s' % (query, self.terminator)
+            self.write(query, fnames[n])           
+        diffMergeSearcher.invoke(fnames[0], fnames[1])
+
+    bufferPosPattern = re.compile('\d+')
+    rangeIndicators = ('-',':')
+    def bufferPositions(self, arg):
+        if not self.sqlBuffer:
+            return []
+        arg = arg.strip(self.terminator)
+        arg = arg.strip()
+        if not arg:
+            return [0]
+        arg = arg.strip().lower()
+        if arg in ('*', 'all', '-', ':'):
+            return range(len(self.sqlBuffer))
+
+        edges = [e for e in self.bufferPosPattern.findall(arg)]
+        edges = [int(e) for e in edges]
+        if len(edges) > 1:
+            edges = edges[:2]
+        else:
+            if arg[0] in self.rangeIndicators or arg[-1] in self.rangeIndicators:
+                edges.append(0)
+        edges.sort()
+        start = max(edges[0], 0)
+        end = min(edges[-1], len(self.sqlBuffer)-1)
+        return range(start, end+1)
+    def do_run(self, arg):
+        'run [N]: runs the SQL that was run N commands ago'	
+        for pos in self.bufferPositions(arg):
+            self.onecmd(self.sqlBuffer[-1-pos])
+    def do_list(self, arg):
+        'list [N]: lists the SQL that was run N commands ago'
+        for pos in self.bufferPositions(arg):
+            print '*** %i statements ago ***' % pos
+            print self.sqlBuffer[-1-pos]
+    def load(self, fname):
+        """Pulls command(s) into sql buffer.  Returns number of commands loaded."""
+        initialLength = len(self.sqlBuffer)
+        try:
+            f = open(fname, 'r')
+        except IOError, e:
+            try:
+                f = open('%s.sql' % fname, 'r')
+            except:
+                print 'Problem opening file %s: \n%s' % (fname, e)
+                return 0
+        txt = f.read()
+        f.close()
+        self.sqlBuffer.extend(commandSeparator.separate(txt))                           
+        return len(self.sqlBuffer) - initialLength
+    def do_ed(self, arg):
+        'ed [N]: brings up SQL from N commands ago in text editor, and puts result in SQL buffer.'
+        fname = 'mysqlpy_temp.sql'
+        try:
+            buffer = self.sqlBuffer[-1 - (int(arg or 0))]
+        except IndexError:
+            buffer = ''
+        f = open(fname, 'w')
+        f.write(buffer)
+        f.close()
+        editSearcher.invoke(fname)
+        self.load(fname)
+    do_edit = do_ed
+    def do_get(self, fname):
+        'Brings SQL commands from a file to the in-memory SQL buffer.'
+        commandsLoaded = self.load(fname)
+        if commandsLoaded:
+            self.do_list('1-%d' % (commandsLoaded-1))
+    def do_getrun(self, fname):
+        'Brings SQL commands from a file to the in-memory SQL buffer, and executes them.'
+        commandNums = range(self.load(fname))
+        commandNums.reverse()
+        for commandNum in commandNums:
+            self.do_run(str(commandNum))
+            self.sqlBuffer.pop()
+    def do_psql(self, arg):
+        '''Shortcut commands emulating psql's backslash commands.
+        
+        \c connect
+        \d desc
+        \e edit
+        \g run
+        \h help
+        \i getrun
+        \o spool
+        \p list
+        \w save
+        \? help psql'''
+        commands = {}
+        for c in self.do_psql.__doc__.splitlines()[2:]:
+            (abbrev, command) = c.split(None, 1)
+            commands[abbrev[1:]] = command
+        words = arg.split(None,1)
+        abbrev = words[0]
+        try:
+            args = words[1]
+        except IndexError:
+            args = ''
+        try:
+            self.onecmd('%s %s' % (commands[abbrev], args))
+            self.onecmd('q')
+        except KeyError:
+            print 'psql command \%s not yet supported.' % abbrev        
+    def do_save(self, fname):
+        'save FILENAME: Saves most recent SQL command to disk.'
+        f = open(fname, 'w')
+        f.write(self.sqlBuffer[-1])
+        f.close()
+        
+    def do_print(self, arg):
+        'print VARNAME: Show current value of bind variable VARNAME.'
+        if arg:
+            if arg[0] == ':':
+                arg = arg[1:]
+            try:
+                print self.binds[arg]
+            except KeyError:
+                print 'No bind variable ', arg
+        else:
+            self.do_setbind('')
+    def do_setbind(self, arg):
+        args = arg.split(None, 2)
+        if len(args) == 0:
+            for (var, val) in self.binds.items():
+                print ':%s = %s' % (var, val)
+        elif len(args) == 1:
+            try:
+                print ':%s = %s' % (args[0], self.binds[args[0]])
+            except KeyError, e:
+                print noSuchBindMsg % args[0]
+        elif len(args) > 2 and args[1] in ('=',':='):
+            var, val = args[0], args[2]
+            if val[0] == val[-1] == "'" and len(val) > 1:
+                val = val[1:-1]
+            self.binds[var] = val
+        else:
+            print 'Could not parse ', args            
+    def do_exec(self, arg):
+        if arg[0] == ':':
+            self.do_setbind(arg[1:])
+        else:
+            self.default('exec %s' % arg)
+            
+def _test():
+    import doctest
+    doctest.testmod()
+    
+if __name__ == "__main__":
+    "Silent return implies that all unit tests succeeded.  Use -v to see details."
+    _test()
\ No newline at end of file
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/sqlpython.py	Fri Nov 30 13:04:51 2007 -0500
@@ -0,0 +1,191 @@
+#
+# SqlPython V1.3
+# Author: Luca.Canali@cern.ch, Apr 2006
+# Rev 18-Oct-07
+#
+# A python module to reproduce Oracle's command line 'sqlplus-like' within python
+# Intended to allow easy customizations and extentions 
+# Best used with the companion modules sqlpyPlus and mysqlpy 
+# See also http://twiki.cern.ch/twiki/bin/view/PSSGroup/SqlPython
+
+import cmd,getpass,binascii,cx_Oracle,re
+import pexpecter
+    
+    # complication! separate sessions ->
+    # separate transactions !!!!!
+    # also: timeouts, other session failures
+	    
+class sqlpython(cmd.Cmd):
+    '''A python module to reproduce Oracle's command line with focus on customization and extention'''
+
+    def __init__(self):
+        cmd.Cmd.__init__(self)
+        self.prompt = 'SQL.No_Connection> '
+        self.maxfetch = 1000
+        self.failoverSessions = []
+        self.terminator = ';'
+        self.timeout = 30
+                
+    def do_connect(self, arg):
+        '''Opens the DB connection'''
+        try:
+            if arg.find('/') == -1:
+                orapass = getpass.getpass('Password: ')
+                orauser = arg.split('@')[0]
+                oraserv = arg.split('@')[1]
+                self.orcl = cx_Oracle.connect(orauser,orapass,oraserv)
+                arg = '%s/%s@%s' % (orauser, orapass, oraserv)
+            else:
+                self.orcl = cx_Oracle.connect(arg)
+            self.curs = self.orcl.cursor()
+            self.prompt = 'SQL.'+self.orcl.tnsentry+'> '
+            self.failoverSessions = [f for f in [fbs(arg) for fbs in pexpecter.available] if f.available]
+
+        except Exception, e:
+            print e
+    
+    def emptyline(self):
+        pass
+    
+    def do_quit(self, arg):
+        return 1
+    
+    def fail(self, arg, do_everywhere=False):
+        if self.failover:
+            success, result = False, ''
+            for fbs in self.failoverSessions:
+                success, result = fbs.attempt(arg)
+                if success:
+                    print result
+                    if not do_everywhere:
+                        return True
+            print result 
+        return False
+                
+    def designated_session(self, arg, sesstype):
+        for fbs in self.failoverSessions:
+            if fbs.valid and fbs.__class__ == sesstype:
+                success, result = fbs.attempt(arg)
+                print result
+                return
+        print 'Valid %s not found' % (sesstype.__name__)
+        
+    def do_terminators(self, arg):
+        """;    standard Oracle format
+\\c   CSV (with headings)
+\\C   CSV (no headings)
+\\g   list
+\\G   aligned list
+\\h   HTML table
+\\i   INSERT statements
+\\s   CSV (with headings)
+\\S   CSV (no headings)
+\\t   transposed
+\\x   XML"""
+        print self.do_terminators.__doc__
+    
+    terminatorSearchString = '|'.join('\\' + d.split()[0] for d in do_terminators.__doc__.splitlines())
+        
+    def do_yasql(self, arg):
+        '''Sends a command to a YASQL session (http://sourceforge.net/projects/yasql/)'''
+        self.designated_session(arg, pexpecter.YASQLSession)
+    do_y = do_yasql
+    def do_sqlplus(self, arg):
+        '''Sends a command to a SQL*Plus session'''
+        self.designated_session(arg, pexpecter.SqlPlusSession)
+    do_sqlp = do_sqlplus
+    def do_senora(self, arg):
+        '''Sends a command to a Senora session (http://senora.sourceforge.net/)'''
+        self.designated_session(arg, pexpecter.SenoraSession)
+    do_sen = do_senora       
+        
+    def default(self, arg, do_everywhere = False):
+        self.query = Statement(arg).query
+        try:
+            self.curs.execute(self.query)
+            print '\nExecuted\n'
+            if do_everywhere:
+                self.fail(arg, do_everywhere = True )
+        except Exception, e:
+            result = self.fail(arg)
+            if not result:
+                print str(e)
+            
+    def do_commit(self, arg):
+        self.default('commit %s' % (arg), do_everywhere=True)
+    def do_rollback(self, arg):
+        self.default('rollback %s' % (arg), do_everywhere=True)        
+        
+    # shortcuts
+    do_q = do_quit
+    do_exit = do_quit
+
+class Statement(object):
+    prompt2 = ' > '
+    stmtEndSearchString = r'(.*)(%s)\s*(\d+)?\s*$' % sqlpython.terminatorSearchString
+    stmtEnd = re.compile(stmtEndSearchString, re.MULTILINE | re.DOTALL)
+    def __init__(self, firstline):
+        v_Lines = []
+        v_Line = firstline
+        while 1:
+            m = self.stmtEnd.search(v_Line)
+            if m:
+                v_Line, self.outformat, suffix = m.groups()
+                v_Lines.append(v_Line) 
+                self.query = '\n'.join(v_Lines)
+                self.rowlimit = int(suffix or 0)
+                return
+            v_Lines.append(v_Line)
+            v_Line = raw_input(self.prompt2)
+        self.query = '\n'.join(v_Lines)
+    
+def pmatrix(rows,desc,maxlen=30):
+    '''prints a matrix, used by sqlpython to print queries' result sets'''
+    names = []
+    maxen = []
+    toprint = []
+    for d in desc:
+        n = d[0]
+        names.append(n)      # list col names
+        maxen.append(len(n)) # col length
+    rcols = range(len(desc))
+    rrows = range(len(rows))
+    for i in rrows:          # loops for all rows
+        rowsi = map(str, rows[i]) # current row to process
+        split = []                # service var is row split is needed
+        mustsplit = 0             # flag 
+        for j in rcols:
+            if str(desc[j][1]) == "<type 'cx_Oracle.BINARY'>":  # handles RAW columns
+                 rowsi[j] = binascii.b2a_hex(rowsi[j])
+            maxen[j] = max(maxen[j], len(rowsi[j]))    # computes max field length
+            if maxen[j] <= maxlen:
+                split.append('')
+            else:                    # split the line is 2 because field is too long
+                mustsplit = 1   
+                maxen[j] = maxlen
+                split.append(rowsi[j][maxlen-1:2*maxlen-1])
+                rowsi[j] = rowsi[j][0:maxlen-1] # this implem. truncates after maxlen*2
+        toprint.append(rowsi)        # 'toprint' is a printable copy of rows
+        if mustsplit != 0:
+            toprint.append(split)
+    sepcols = []
+    for i in rcols:
+        maxcol = maxen[i]
+        name = names[i]
+        sepcols.append("-" * maxcol)  # formats column names (header)
+        names[i] = name + (" " * (maxcol-len(name))) # formats separ line with --
+        rrows2 = range(len(toprint))
+        for j in rrows2:
+            val = toprint[j][i]
+            if str(desc[i][1]) == "<type 'cx_Oracle.NUMBER'>":  # right align numbers
+                toprint[j][i] = (" " * (maxcol-len(val))) + val
+            else:
+                toprint[j][i] = val + (" " * (maxcol-len(val)))
+    for j in rrows2:
+        toprint[j] = ' '.join(toprint[j])
+    names = ' '.join(names)
+    sepcols = ' '.join(sepcols)
+    toprint.insert(0, sepcols)
+    toprint.insert(0, names)
+    return '\n'.join(toprint)
+