Mercurial > sqlpython
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) +