# HG changeset patch # User catherine@dellzilla # Date 1236028909 18000 # Node ID 8866fe0706c31179c233f518e2c31de1c4268186 # Parent d1f1022d238748fe7b2a32131e74cd7a487335c0 message when all columns excluded diff -r d1f1022d2387 -r 8866fe0706c3 setup.py --- a/setup.py Mon Mar 02 06:38:30 2009 -0500 +++ b/setup.py Mon Mar 02 16:21:49 2009 -0500 @@ -17,7 +17,7 @@ url="https://twiki.cern.ch/twiki/bin/view/PSSGroup/SqlPython", packages=find_packages(), include_package_data=True, - install_requires=['pyparsing','cmd2>=0.4.6','cx_Oracle','genshi>=0.5'], + install_requires=['pyparsing','cmd2>=0.4.7','cx_Oracle','genshi>=0.5'], keywords = 'client oracle database', license = 'MIT', platforms = ['any'], diff -r d1f1022d2387 -r 8866fe0706c3 sqlpython/mysqlpy.py --- a/sqlpython/mysqlpy.py Mon Mar 02 06:38:30 2009 -0500 +++ b/sqlpython/mysqlpy.py Mon Mar 02 16:21:49 2009 -0500 @@ -13,9 +13,9 @@ class mysqlpy(sqlpyPlus): ''' -MySqlPy V1.5.3 - 'sqlplus in python' +MySqlPy V1.6.0 - 'sqlplus in python' Author: Luca.Canali@cern.ch -Rev: 1.5.3, 25-Feb-09 +Rev: 1.6.0, 02-Mar-09 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 diff -r d1f1022d2387 -r 8866fe0706c3 sqlpython/sqlpyPlus.py --- a/sqlpython/sqlpyPlus.py Mon Mar 02 06:38:30 2009 -0500 +++ b/sqlpython/sqlpyPlus.py Mon Mar 02 16:21:49 2009 -0500 @@ -391,9 +391,10 @@ do__load = Cmd.do_load def do_remark_begin(self, line): - '''Wrapping a *single* SQL or PL/SQL statement in `REMARK_BEGIN` and `REMARK_END` + ''' + Wrapping a *single* SQL or PL/SQL statement in `REMARK_BEGIN` and `REMARK_END` tells sqlpython to submit the enclosed code directly to Oracle as a single - unit of code. + unit of code. Without these markers, sqlpython fails to properly distinguish the beginning and end of all but the simplest PL/SQL blocks, causing errors. sqlpython also @@ -401,7 +402,8 @@ the statement has ended yet; `REMARK_BEGIN` and `REMARK_END` allow it to skip this parsing. - REMARK_BEGIN and REMARK_END will be read as comments by standard SQL*Plus. + Standard SQL*Plus interprets REMARK_BEGIN and REMARK_END as comments, so it is + safe to include them in SQL*Plus scripts. ''' statement = [] next = self.pseudo_raw_input(self.continuationPrompt) @@ -409,8 +411,7 @@ #and not next.lower().strip().startswith('--- end'): statement.append(next) next = self.pseudo_raw_input(self.continuationPrompt) - statement = self.parsed('\n'.join(statement)) - return self.onecmd(statement) + return self.onecmd('\n'.join(statement)) def remark(self, line): pass @@ -524,9 +525,11 @@ pyparsing.SkipTo(pyparsing.stringEnd)('remainder') negator = pyparsing.Literal('!')('exclude') - colNumber = pyparsing.Optional(negator) + pyparsing.Literal('.') + pyparsing.Word('-' + pyparsing.nums, pyparsing.nums)('column_number') + colNumber = pyparsing.Optional(negator) + pyparsing.Literal('#') + pyparsing.Word('-' + pyparsing.nums, pyparsing.nums)('column_number') colName = negator + pyparsing.Word('$_#' + pyparsing.alphas, '$_#' + pyparsing.alphanums)('column_name') wildColName = pyparsing.Optional(negator) + pyparsing.Word('?*%$_#' + pyparsing.alphas, '?*%$_#' + pyparsing.alphanums, min=2)('column_name') + colNumber.ignore(pyparsing.cStyleComment).ignore(Parser.comment_def). \ + ignore(pyparsing.sglQuotedString).ignore(pyparsing.dblQuotedString) wildSqlParser = colNumber ^ colName ^ wildColName wildSqlParser.ignore(pyparsing.cStyleComment).ignore(Parser.comment_def). \ ignore(pyparsing.sglQuotedString).ignore(pyparsing.dblQuotedString) @@ -577,22 +580,23 @@ replacers[arg[startpos:endpos]].extend(i for i in include_here if i not in replacers[arg[startpos:endpos]]) excluded.add(colname) else: - #included.add(colname) excluded.discard(colname) replacers[arg[startpos:endpos]].append(colname) replacers = sorted(replacers.items(), key=len, reverse=True) result = columnlist.columns for (target, replacement) in replacers: - cols = [r for r in replacement if r not in excluded and r not in included] - + cols = [r for r in replacement if r not in excluded and r not in included] replacement = ', '.join(cols) included.update(cols) result = result.replace(target, replacement) - # some column names could get wiped out completely - fix their dangling commas + # some column names could get wiped out completely, so we fix their dangling commas result = self.emptyCommaRegex.sub(',', result) result = self.deadStarterCommaRegex.sub('', result) result = self.deadEnderCommaRegex.sub('', result) + if not result.strip(): + print 'No columns found matching criteria.' + return 'null from dual' return result + ' ' + columnlist.remainder rowlimitPattern = pyparsing.Word(pyparsing.nums)('rowlimit') @@ -669,7 +673,7 @@ ddlargs = [object_type, object_name] else: ddlargs = [object_type, object_name, owner] - self.stdout.write(str(self.curs.callfunc('DBMS_METADATA.GET_DDL', cx_Oracle.CLOB, ddlargs))) + self.stdout.write('REMARK_BEGIN\n%s\nREMARK_END\n\n' % str(self.curs.callfunc('DBMS_METADATA.GET_DDL', cx_Oracle.CLOB, ddlargs))) except cx_Oracle.DatabaseError: if object_type == 'JOB': print '%s: DBMS_METADATA.GET_DDL does not support JOBs (MetaLink DocID 567504.1)' % object_name @@ -678,7 +682,7 @@ if opts.full: for dependent_type in ('OBJECT_GRANT', 'CONSTRAINT', 'TRIGGER'): try: - self.stdout.write(str(self.curs.callfunc('DBMS_METADATA.GET_DEPENDENT_DDL', cx_Oracle.CLOB, + self.stdout.write('REMARK_BEGIN\n%s\nREMARK_END\n\n' % str(self.curs.callfunc('DBMS_METADATA.GET_DEPENDENT_DDL', cx_Oracle.CLOB, [dependent_type, object_name, owner]))) except cx_Oracle.DatabaseError: pass diff -r d1f1022d2387 -r 8866fe0706c3 sqlpython/sqlpython.py --- a/sqlpython/sqlpython.py Mon Mar 02 06:38:30 2009 -0500 +++ b/sqlpython/sqlpython.py Mon Mar 02 16:21:49 2009 -0500 @@ -10,7 +10,7 @@ import cmd2,getpass,binascii,cx_Oracle,re,os import sqlpyPlus -__version__ = '1.5.3' +__version__ = '1.6.0' class sqlpython(cmd2.Cmd): '''A python module to reproduce Oracle's command line with focus on customization and extention'''