Mercurial > sqlpython
changeset 4:23c3a58d7804
about to strip out tselect
| author | devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil |
|---|---|
| date | Tue, 04 Dec 2007 16:28:55 -0500 |
| parents | cd23cd62de3c |
| children | 65ae6cec71c6 |
| files | mysqlpy.py sqlpyPlus.py |
| diffstat | 2 files changed, 115 insertions(+), 67 deletions(-) [+] |
line wrap: on
line diff
--- a/mysqlpy.py Mon Dec 03 16:43:43 2007 -0500 +++ b/mysqlpy.py Tue Dec 04 16:28:55 2007 -0500 @@ -109,9 +109,9 @@ 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 + self.query = 'select ' + arg # sqlpython.finishStatement('select '+arg) + (self.query, terminator, rowlimit) = sqlpython.findTerminator(self.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
--- a/sqlpyPlus.py Mon Dec 03 16:43:43 2007 -0500 +++ b/sqlpyPlus.py Tue Dec 04 16:28:55 2007 -0500 @@ -26,6 +26,74 @@ """ # note in cmd.cmd about supporting emacs commands? +descQueries = { +'TABLE': """ + 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;""", +'PROCEDURE':""" + 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;""", +'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""", +'TRIGGER':""" + trigger_name, + trigger_type, + triggering_event, + table_owner, + base_object_type, + table_name, + column_name, + when_clause, + status, + description, + action_type, + crossedition +FROM all_triggers +WHERE owner = :owner +AND trigger_name = :object_name +\\t +""" + } +descQueries['VIEW'] = descQueries['TABLE'] +descQueries['FUNCTION'] = descQueries['PROCEDURE'] + queries = { 'resolve': """ SELECT object_type, object_name, owner FROM ( @@ -46,51 +114,6 @@ 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 @@ -311,8 +334,9 @@ self.spoolFile = None self.autobind = False self.failover = False - self.singleline = '''select insert update delete + self.multiline = '''select insert update delete tselect create drop alter'''.split() + self.excludeFromHistory = '''run r list l history hi ed'''.split() def default(self, arg, do_everywhere=False): sqlpython.sqlpython.default(self, arg, do_everywhere) @@ -347,27 +371,32 @@ 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)) + pipefilename = 'sqlpython.pipe.tmp' + for (idx, pipedCommand) in enumerate(pipedCommands[:-1]): + savestdout = sys.stdout + f = open(pipefilename,'w') + sys.stdout = f + self.precmd(pipedCommand) + self.onecmd(pipedCommand) + self.postcmd(False, pipedCommands[0]) + f.close() + sys.stdout = savestdout + f = os.popen('%s < %s' % (pipedCommands[idx+1], pipefilename)) + f.read() + ''' try: args = line.split(None,1) args[0] = args[0].lower() statement = ' '.join(args) - if args[0] in self.singleline: + if args[0] in self.multiline: statement = sqlpython.finishStatement(statement) - self.history.append(statement) + if args[0] not in self.excludeFromHistory: + self.history.append(statement) return statement except Exception: return line @@ -454,6 +483,8 @@ result = '\n'.join(result) elif outformat == '\\h': result = self.output_as_html_table() + elif outformat == '\\t': + else: result = sqlpython.pmatrix(self.rows, self.curs.description, self.maxfetch) return result @@ -555,15 +586,13 @@ "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}) + if object_type in ('TABLE','VIEW','PROCEDURE','FUNCTION','TRIGGER'): + self.do_select(descQueries[object_type],{'object_name':object_name, 'owner':owner}) elif object_type == 'PACKAGE': - self.curs.execute(queries['PackageObjects'], {'package_name':object_name, 'owner':owner}) + self.curs.execute(descQueries['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}) + self.do_select(descQueries['PackageObjArgs'],{'package_name':object_name, 'owner':owner, 'object_name':packageObj_name}) do_desc = do_describe def do_comments(self, arg): @@ -593,6 +622,9 @@ object_type, owner, object_name = '', '', '' return object_type, owner, object_name + def do_resolve(self, arg): + print self.resolve(arg) + def do_shell(self, arg): 'execute a command as if at the OS prompt.' os.system(arg) @@ -799,7 +831,23 @@ self.do_setbind(arg[1:]) else: self.default('exec %s' % arg) - + + def do_cat(self, arg): + targets = arg.split() + for target in targets: + self.do_select('* from %s' % target) + + def do_grep(self, arg): + """grep PATTERN TABLE - search for term in any of TABLE's fields""" + targets = arg.split() + pattern = targets.pop(0) + for target in targets: + sql = [] + self.curs.execute('select * from %s where 1=0' % target) + sql = ' or '.join("%s LIKE '%%%s%%'" % (d[0], pattern) for d in self.curs.description) + sql = '* FROM %s WHERE %s' % (target, sql) + self.do_select(sql) + def _test(): import doctest doctest.testmod()