SQLPython’s primary function is to allow entry of SQL queries, much like SQL*Plus, psql, and the mysql command-line client. It aims to reproduce as many SQL*Plus capabilities as possible. In addition, it offers several extra features inspired by other command-line clients.
When printing query results, sqlpython economizes on screen space by allocating only the width each column actually needs.
sqlpython automatically uses username`@`instance> as its prompt, helping avoid wrong-instance and wrong-user errors.
When typing SQL commands, hitting <TAB> after entering part of an object or column name brings up a list of appropriate possibilities or, if there is only one possibility, fills in the rest of the name. This feature is not yet very reliable, but can save typing.
Like SQL*Plus, sqlpython can run scripts (text files with series of SQL and sqlpython commands) with @/path/to/script.sql or (for online scripts) @http://scripthost/scriptlibrary/script.sql.
If used on a *nix machine with readline installed, then bash-like access to the command history is available.
The up- and down-arrow keys allow you to scroll through the lines entered so far in your sqlpython session.
Commands are also entered into a command history.
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:
Need examples!!!!
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.
| terminator | format | 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 | |
| \r | ReStructured Text | inclusion in documentation |
| \s | CSV (with headings) | |
| \S | CSV (no headings) | |
| \t | transposed | “wide” 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.
Many sqlpython commands allow you to act as though the database objects were files in a UNIX filesystem. Many of the commands also accept flags to modify their behavior.
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
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?”
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
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)
Displays the PL/SQL source code for {object_name}.
| -d, --dump | dump results to files (object_type/object_name.sql) |
| -f, --full | get dependent objects as well |
| -a, --all | all schemas’ objects |
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)
psql, the command-line client for the open-source database PostgreSQL uses a number of backslash-character sequences as convenient shortcuts. sqlpython steals many of them.
| \c | connect |
| \d | desc |
| \e | edit |
| \g | run |
| \h | help |
| \i | load |
| \o | spool |
| \p | list |
| \q | quit |
| \w | save |
| \db | _dir_tablespaces |
| \dd | comments |
| \dn | _dir_schemas |
| \dt | _dir_tables |
| \dv | _dir_views |
| \di | _dir_indexes |
| \? | help psql |
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.
Once bind variables are defined, they can be used in SQL statements. The syntax is dependnent on which RDBMS is being queried.
———- —————————————— RDBMS bind variable example ———- —————————————— Oracle SELECT * FROM party WHERE name = :name; postgreSQL SELECT * FROM party WHERE name = %(name)s; MySQL SELECT * FROM party WHERE name = ; ———- ——————————————
Bind variables are available from within Python as a dictionary named binds (see Python).
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 is a nonstandard SQL feature that must be enabled with set wildsql on. When it is enabled, column names in a SELECT statement do not need to be explicitly typed; they can be specified with special Wild SQL symbols: wildcards (*, %, _); column numbers (#{N}); and NOT-style exclusion (!). The symbols can even be combined.
TODO: column number Wild SQL is not working as of sqlpython 1.7.0
jrrt@orcl> cat party
NAME STR INT WIS DEX CON CHA
------- --- --- --- --- --- ---
Frodo 8 14 16 15 14 16
Gimli 17 12 10 11 17 11
Legolas 13 15 14 18 15 17
Sam 11 9 14 11 16 13
4 rows selected.
jrrt@orcl> set wild on
wildsql - was: False
now: True
jrrt@orcl> select *i* from party;
INT WIS
--- ---
14 16
12 10
15 14
9 14
4 rows selected.
jrrt@orcl> select #1, #5 from party;
NAME DEX
------- ---
Frodo 15
Gimli 11
Legolas 18
Sam 11
4 rows selected.
jrrt@orcl> select !str from party;
NAME INT WIS DEX CON CHA
------- --- --- --- --- ---
Frodo 14 16 15 14 16
Gimli 12 10 11 17 11
Legolas 15 14 18 15 17
Sam 9 14 11 16 13
4 rows selected.
jrrt@orcl> select n*, !#3, !c* from party;
NAME STR WIS DEX
------- --- --- ---
Frodo 8 16 15
Gimli 17 10 11
Legolas 13 14 18
Sam 11 14 11
4 rows selected.
Wild SQL symbols only work in the first SELECT statement in a query; they do not work in subqueries, subsequent UNIONed queries, etc.
The py command allows the user to execute Python commands, either one-at-a-time (with py {command}) or in an interactive environment (beginning with a bare py statement, and continuing until Ctrl-D, quit(), or exit() is entered).
A history of result sets from each query is exposed to the python session as the list r; the most recent result set is r[-1]. Each row can be references as a tuple, or as an object with an attribute for each column.
Bind variables are exposed as the dictionary binds. Each row from each result set has a .bind() method that fills a bind varible for each column with that row’s value.
Resultsets in r are read-only, but binds can be written as well as read, and will be working bind variables in the SQL environment.
SQL and sqlpython commands can be issued from the Python environment with sql(“{your SQL}”).
All variables are retained each time the python environment is entered (whether interactively, or with one-line py statements).
0:testschema@orcl> select title, author from play;
TITLE AUTHOR
--------------- -----------
Timon of Athens Shakespeare
Twelfth Night Shakespeare
The Tempest Shakespeare
Agamemnon Aeschylus
4 rows selected.
0:testschema@orcl> py import urllib
0:testschema@orcl> py current_season = urllib.urlopen('http://cincyshakes.com/').read()
0:testschema@orcl> py
Python 2.5.2 (r252:60911, Jul 31 2008, 17:28:52)
[GCC 4.2.3 (Ubuntu 4.2.3-2ubuntu7)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
(mysqlpy)
py <command>: Executes a Python command.
py: Enters interactive Python mode; end with `Ctrl-D`, `quit()`, or 'exit`.
Past SELECT results are exposed as list `r`;
most recent resultset is `r[-1]`.
SQL bind, substitution variables are exposed as `binds`, `substs`.
SQL and sqlpython commands can be issued with sql("your non-python command here").
>>> r[-1]
[('Timon of Athens', 'Shakespeare'), ('Twelfth Night', 'Shakespeare'), ('The Tempest', 'Shakespeare'), ('Agamemnon', 'Aeschylus')]
>>> r[-1][0][0]
'Timon of Athens'
>>> for row in r[-1]:
... print "%s, by %s" % (row.title, row.author)
...
Timon of Athens, by Shakespeare
Twelfth Night, by Shakespeare
The Tempest, by Shakespeare
Agamemnon, by Aeschylus
>>> [row.title for row in r[-1] if row.title in current_season]
['Timon of Athens', 'Twelfth Night']
>>> binds['author'] = 'Shakespeare'
>>> query = "SELECT title FROM play WHERE author = :author"
>>> sql(query)
TITLE
---------------
Timon of Athens
Twelfth Night
The Tempest
3 rows selected.
>>> r[-1]
[('Timon of Athens',), ('Twelfth Night',), ('The Tempest',)]
>>> r[-1][0]
('Timon of Athens',)
>>> r[-1][0].bind()
>>> binds['title']
'Timon of Athens'
>>> quit()
0:testschema@orcl> select title, author from play where title = :title;
TITLE AUTHOR
--------------- -----------
Timon of Athens Shakespeare
1 row selected.
Several parameters control the behavior of sqlpython itself.
| parameter | effect | 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 along with results | 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”, etc.
set and show both list the current values of the sqlpython parameters. They also recognize any abbreviated parameter name, so long as it is long enough to be unique. That is, show maxf is recognized as show maxfetch, but show max is too short to distinguish between maxfetch and maxtselctrows.
show parameter {param} shows current Oracle parameters (from v$parameter), as it does in SQL*Plus.
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:
(Oracle only, for now)
When a SELECT query returns BLOB columns, most SQL tools simply cannot display the results. Sqlpython, however, will create a local file for each BLOB returned (up to the parameter bloblimit), and return the filepaths of the new files in the query results. In a tool like the GNOME terminal, these filepaths work as right-clickable links that can open the files.
When the \h terminator is used to generate HTML table output, if the BLOBs are images, they will be embedded as images in the generated table.