Mercurial > sqlpython
changeset 281:701f0aae837a
got maxtselctrows working again
| author | catherine@dellzilla |
|---|---|
| date | Thu, 19 Mar 2009 16:45:45 -0400 |
| parents | 8ea39093ddf2 |
| children | 4eef08cfaf25 |
| files | docs/source/capabilities.rst sqlpython/mysqlpy.py sqlpython/sqlpyPlus.py |
| diffstat | 3 files changed, 236 insertions(+), 19 deletions(-) [+] |
line wrap: on
line diff
--- a/docs/source/capabilities.rst Thu Mar 19 13:14:39 2009 -0400 +++ b/docs/source/capabilities.rst Thu Mar 19 16:45:45 2009 -0400 @@ -1,3 +1,4 @@ +============================== SQLPython's extra capabilities ============================== @@ -66,6 +67,55 @@ Run command matching <str> or <regex> (as for `history`) - if multiple items would match, run most recent +Special I/O destinations +======================== + +Much as in a UNIX shell, you can follow a command with a special output destination. + +`> {filename}` sends the output to a file. This is more convenient than SQL\*Plus's +SPOOL {filename}... SPOOL OFF (though you can use those as well). + +`>` alone (no filename) sends the output to the paste buffer. + +`|` pipes the output to an operating-system command. + +When `< {filename}` is included in your command, it is replaced with the contents of +{filename} before the command is run. + +Examples:: + +Special output formats +====================== + +By replacing the `;` that terminates a SELECT statement with a backslash-character +sequence, you can get output in a number of useful formats. The `terminators` +command lists them, for your convenience:: + +=== ======================== ================================ + Especially useful for +=== ======================== ================================ +; standard Oracle format +\c CSV (with headings) sending to spreadsheets +\C CSV (no headings) +\g list wide output with linewraps +\G aligned list +\h HTML table web reports +\i INSERT statements copying to other instances +\j JSON +\s CSV (with headings) +\S CSV (no headings) +\t transposed "narrow" tables like v$database +\x XML +\l line plot, with markers +\L scatter plot (no lines) +\b bar graph +\p pie chart +=== ======================== ================================ + +Most of these output formats are even more useful when combined with special output +destinations. For example, `SELECT * FROM party\h > /var/www/party_report.html` +could create an HTML report in the webserver's documents directory, ready to serve. + UNIX-like commands ================== @@ -73,14 +123,41 @@ were files in a UNIX filesystem. Many of the commands also accept flags to modify their behavior. -ls - Lists objects from the data dictionaries, as though they were in a - *object_type*/*object_name* directory structure. Thus, `ls view/\*` - lists all the user's views. +ls {object type/object name, with wildcards} + Lists objects from the data dictionaries, as though they were in a + *object_type*/*object_name* directory structure. Thus, `ls view/\*` + lists all the user's views. Calling with no argument is equivalent + to `ls *`. + + Options:: + + -l, --long long descriptions + -a, --all all schemas' objects (otherwise, you only get your own) + -t, --timesort Sort by last_ddl_time + -r, --reverse Reverse order while sorting -cat - Shorthand for "SELECT * FROM" + `ls -lt *;10` lists the ten items with the most recent last_ddl_time; + this can be a good way to answer the question, "What was I working on?" + +cat {remainder of query} + Shorthand for "SELECT * FROM". Can be combined with anything else + that fits into a SELECT statement (WHERE, ORDER BY, etc.) + +grep {grep {target} {table} [{table2,...}] + Equivalent to SELECT * FROM {table} WHERE *any column* LIKE '%{target}%' + Useful when you don't know, don't remember, or don't care which column + a value may be found in. + + Options:: + + -i, --ignore-case Case-insensitive search +find -c {target}, find -t {column} + Lists all tables or columns whose names contain {target}. More convenient than + querying user_tab_columns/all_tab_columns or user_tables/all_tables. + Options:: + -a Find all objects (not just my own) + PostgreSQL-like shortcuts ========================= @@ -106,6 +183,62 @@ \\? help psql ----- ------------------ +PL/SQL source code +================== + +pull {object_name} + Displays the PL/SQL source code for {object_name}. + + Options: + -d, --dump dump results to files (object_type/object_name.sql) + -f, --full get dependent objects as well + -a, --all all schemas' objects + +bzr, git, hg {object_name} + Dump source code to files, as `pull -f`, but also creates or commits to a + repository of the appropriate distributed version control system + (Bazaar, Git, or Mercurial, respectively). + +find {target} + Lists all PL/SQL objects whose source code contains the {target} string. + Always case-insensitive. + Options:: + -a Search all PL/SQL objects (not just my own) + + +Bind variables +============== + +Bind variables work in sqlpython as they do in SQL\*Plus, but they are set dynamically; there +is no need to declare them before use. The syntax for setting them is more permissive than +in SQL\*Plus; all these are recognized:: + + exec :mybind := 'value' + exec :mybind = 'value' + :mybind := 'value' + :mybind = 'value' + +The current values of all bind variables can be viewed with the `print` command. + +The `bind` command creates and populates bind variables for the final row of the most recent +SELECT statement executed; each column name is used as a bind variable, which is filled with +the value. `bind -r {rownumber}` does the same, but fills from row {rownumber} instead of +from the final row (row numbers begin at 0 for this command). + +When the `autobind` sqlpython parameter is True, a `bind` statement is issued automatically +after every query that returns exactly one row. + +Bind variables are available from within Python as a dictionary named `binds` (see Python). + +Substitution variables +====================== + +Substitution variables ("&" variables) work much as they do in SQL\*Plus. As in SQL\*Plus, +the `scan` parameter determines whether queries are scanned to replace substitution +variables. Unlike SQL\*Plus, sqlpython knows how annoying it is to hit a substitution +variable you didn't expect, so entering "SET SCAN OFF" when prompted for a substitution +variable actually aborts the substitution process. + Wild SQL ======== @@ -120,3 +253,63 @@ Wild SQL can only be used in the primary column list of straightforward SELECT statements, not in subqueries, `UNION`ed queries, etc. + +Parameters +========== + +Several parameters control the behavior of sqlpython itself. + +===================== ================================================== =============== + default +===================== ================================================== =============== +autobind When True, single-row queries automatically `bind` False +commit_on_exit Automatically commits work at end of session True +continuation_prompt Prompt for second line and onward of long statement > +default_file_name The file opened by `edit`, if not specified afiedt.buf +echo Echo command entered before executing False +editor Text editor invoked by `edit`. varies +heading Print column names True +maxfetch Maximum number of rows to return from any query 1000 +maxtselctrows Maximum # of rows from a tselect or \\n query 10 +prompt Probably unwise to change user@instance> +scan Interpret & as indicating substitution variables True +serveroutput Print DBMS_OUTPUT.PUT_LINE results True +sql_echo Print text of "behind-the-scenes" queries False +timeout In seconds 30 +timing Print time for each command to execute False +wildsql Accept *, %, #, and ! in column names False +===================== ================================================== =============== + +The user can change these with the `set {paramname} {new-value}` statement. +The True/False parameters accept new +values permissively, recognizing "True", "False", "T", "F", "yes", "no", "on", "off"... + +`set` and `show` both list the current values of the sqlpython parameters. +`show parameter {param}` shows current Oracle parameters (from v$parameter), as it does +in SQL\*Plus. + +Tuning +====== + +In sqlpython, `explain {SQL ID}` shows the execution plan for the SQL statement with the +given ID. If SQL ID is omitted, it defaults to the most recent SQL executed. +(This is not necessarily the last statement `EXPLAIN PLAN` was issued against.) + +Other specialized sqlpython tuning commands include: + +load + Displays OS load on cluster nodes (10gRAC) + +longops + Displays long-running operations + +sessinfo + Reports session info for the given sid, extended to RAC with gv$ + +top, top9i + Displays active sessions + + + + +
--- a/sqlpython/mysqlpy.py Thu Mar 19 13:14:39 2009 -0400 +++ b/sqlpython/mysqlpy.py Thu Mar 19 16:45:45 2009 -0400 @@ -123,7 +123,10 @@ f.close() def do_tselect(self, arg): - '''executes a query and prints the result in trasposed form. Useful when querying tables with many columns''' + ''' + Executes a query and prints the result in trasposed form; + equivalent to terminating query with `\\t` instead of `;`. + Useful when querying tables with many columns and few rows.''' self.do_select(self.parsed(arg, terminator='\\t')) def do_sql(self,args):
--- a/sqlpython/sqlpyPlus.py Thu Mar 19 13:14:39 2009 -0400 +++ b/sqlpython/sqlpyPlus.py Thu Mar 19 16:45:45 2009 -0400 @@ -387,7 +387,7 @@ def __init__(self): sqlpython.sqlpython.__init__(self) self.binds = CaselessDict() - self.settable += 'autobind commit_on_exit maxfetch maxtselctrows result_history_max_mbytes scan serveroutput sql_echo timeout heading wildsql'.split() + self.settable += 'autobind commit_on_exit maxfetch maxtselctrows scan serveroutput sql_echo store_results timeout heading wildsql'.split() self.settable.remove('case_insensitive') self.settable.sort() self.stdoutBeforeSpool = sys.stdout @@ -400,7 +400,7 @@ self.scan = True self.substvars = {} self.result_history = [] - self.result_history_max_mbytes = 10 + self.store_results = True self.pystate = {'r': [], 'binds': self.binds} # overrides cmd's parseline @@ -655,7 +655,7 @@ return 'null from dual' return result + ' ' + columnlist.remainder - def do_ampersand_substitution(self, raw, regexpr, isglobal): + def ampersand_substitution(self, raw, regexpr, isglobal): subst = regexpr.search(raw) while subst: fullexpr, var = subst.group(1), subst.group(2) @@ -679,16 +679,27 @@ singleampre = re.compile( '(&([a-zA-Z\d_$#]+))') def preparse(self, raw, **kwargs): if self.scan: - raw = self.do_ampersand_substitution(raw, regexpr=self.doubleampre, isglobal=True) + raw = self.ampersand_substitution(raw, regexpr=self.doubleampre, isglobal=True) if self.scan: - raw = self.do_ampersand_substitution(raw, regexpr=self.singleampre, isglobal=False) + raw = self.ampersand_substitution(raw, regexpr=self.singleampre, isglobal=False) return raw rowlimitPattern = pyparsing.Word(pyparsing.nums)('rowlimit') terminators = '; \\C \\t \\i \\p \\l \\L \\b '.split() + output_templates.keys() - def do_bind(self, arg): - self.pystate['r'][-1][-1].bind() + @options([make_option('-r', '--row', type="int", default=-1, + help='Bind row #ROW instead of final row (zero-based)')]) + def do_bind(self, arg=None, opts={}): + ''' + Inserts the results from the final row in the last completed SELECT statement + into bind variables with names corresponding to the column names. When the optional + `autobind` setting is on, this will be issued automatically after every query that + returns exactly one row. + ''' + try: + self.pystate['r'][-1][opts.row].bind() + except IndexError: + print self.do_bind.__doc__ def do_select(self, arg, bindVarsIn=None, terminator=None): """Fetch rows from a table. @@ -706,6 +717,8 @@ except ValueError: rowlimit = 0 print "Specify desired number of rows after terminator (not '%s')" % arg.parsed.suffix + if arg.parsed.terminator == '\\t': + rowlimit = rowlimit or self.maxtselctrows self.varsUsed = findBinds(arg, self.binds, bindVarsIn) if self.wildsql: selecttext = self.expandWildSql(arg) @@ -717,20 +730,20 @@ if self.rc > 0: resultset = ResultSet() resultset.colnames = [d[0].lower() for d in self.curs.description] + resultset.pystate = self.pystate resultset.statement = 'select ' + selecttext resultset.varsUsed = self.varsUsed - resultset.pystate = self.pystate resultset.extend([Result(r) for r in self.rows]) for row in resultset: row.resultset = resultset + self.pystate['r'].append(resultset) self.stdout.write('\n%s\n' % (self.output(arg.parsed.terminator, rowlimit))) - self.pystate['r'].append(resultset) if self.rc == 0: print '\nNo rows Selected.\n' elif self.rc == 1: print '\n1 row selected.\n' if self.autobind: - self.do_bind(None) + self.do_bind() elif self.rc < self.maxfetch: print '\n%d rows selected.\n' % self.rc else: @@ -1029,7 +1042,8 @@ self.stdout = self.spoolFile def do_write(self, args): - print 'Use (query) > outfilename instead.' + 'Obsolete command. Use (query) > outfilename instead.' + print self.do_write.__doc__ return def do_compare(self, args): @@ -1226,6 +1240,9 @@ lines.append(line) def do_begin(self, arg): + ''' + PL/SQL blocks can be used normally in sqlpython, though enclosing statements in + REMARK BEGIN... REMARK END statements can help with parsing speed.''' self.anon_plsql('begin ' + arg) def do_declare(self, arg): @@ -1275,6 +1292,10 @@ make_option('-t', '--timesort', action='store_true', help="Sort by last_ddl_time"), make_option('-r', '--reverse', action='store_true', help="Reverse order while sorting")]) def do_ls(self, arg, opts): + ''' + Lists objects as through they were in an {object_type}/{object_name} UNIX + directory structure. `*` and `%` may be used as wildcards. + ''' statement = '''SELECT object_type || '/' || %(objname)s AS name %(moreColumns)s FROM %(whose)s_objects %(where)s ORDER BY %(orderby)s;''' % self._ls_statement(arg, opts) @@ -1282,7 +1303,7 @@ @options([make_option('-i', '--ignore-case', dest='ignorecase', action='store_true', help='Case-insensitive search')]) def do_grep(self, arg, opts): - """grep PATTERN TABLE - search for term in any of TABLE's fields""" + """grep {target} {table} [{table2,...}] - search for {target} in any of {table}'s fields""" targetnames = arg.split() pattern = targetnames.pop(0)