Mercurial > sqlpython
annotate docs/source/capabilities.rst @ 329:3efffbf7481f
fixed bug in assigning 0, null to bind vars
| author | Catherine Devlin <catherine.devlin@gmail.com> |
|---|---|
| date | Mon, 06 Apr 2009 14:45:05 -0400 |
| parents | dd7b3e4b58dd |
| children | 80a1976decf2 2fd766784ff2 |
| rev | line source |
|---|---|
| 281 | 1 ============================== |
| 247 | 2 SQLPython's extra capabilities |
| 3 ============================== | |
| 4 | |
| 5 For the most part, SQLPython simply duplicates SQL\*Plus's capabilites. | |
| 6 | |
| 279 | 7 Neatened output |
| 8 =============== | |
| 9 | |
| 10 When printing query results, sqlpython economizes on screen space by allocating | |
| 11 only the width each column actually needs. | |
| 12 | |
| 13 Smart prompt | |
| 14 ============ | |
| 15 | |
| 16 sqlpython automatically uses `username`@`instance`> as its prompt, helping | |
| 17 avoid wrong-instance and wrong-user errors. | |
| 18 | |
| 19 Tab completion | |
| 20 ============== | |
| 21 | |
| 22 When typing SQL commands, hitting `<TAB>` after entering part of an object | |
| 23 or column name brings up a list of appropriate possibilities or, if there | |
| 24 is only one possibility, fills in the rest of the name. This feature is | |
| 25 not yet very reliable, but can save typing. | |
| 26 | |
| 27 Scripting | |
| 28 ========= | |
| 29 | |
| 30 Like SQL\*Plus, sqlpython can run scripts (text files with series of SQL and | |
| 31 sqlpython commands) with `@/path/to/script.sql` or (for online scripts) | |
| 32 `@http://scripthost/scriptlibrary/script.sql`. | |
| 33 | |
| 34 History | |
| 35 ======= | |
| 36 | |
| 37 The up- and down-arrow keys allow you to scroll through the lines entered so far | |
| 38 in your sqlpython session. | |
| 39 | |
| 40 Commands are also entered into a command history. | |
| 41 | |
| 286 | 42 history *or* hi |
| 43 List entire command history | |
| 279 | 44 |
| 286 | 45 list *or* li |
| 46 List only last command | |
| 279 | 47 |
| 286 | 48 hi `<N>` |
| 49 List command number <N> from history. | |
| 279 | 50 |
| 286 | 51 hi `<N>-`, hi `-<N>` |
| 52 List commands from <N> onward, or up to <N> | |
| 279 | 53 |
| 286 | 54 hi `<str>` |
| 55 Lists commands that include the string <str> | |
| 279 | 56 |
| 286 | 57 hi `/<regex>/` |
| 58 Lists commands that match the regular expression <regex> | |
| 279 | 59 |
| 286 | 60 run, r, *or* `\\g` |
| 61 Run the most recent command again | |
| 279 | 62 |
| 286 | 63 run `<N>` |
| 64 Run command <N> | |
| 279 | 65 |
| 286 | 66 run `<str>`, run `/<regex>/` |
| 67 Run command matching <str> or <regex> (as for `history`) - | |
| 68 if multiple items would match, run most recent | |
| 279 | 69 |
| 281 | 70 Special I/O destinations |
| 71 ======================== | |
| 72 | |
| 73 Much as in a UNIX shell, you can follow a command with a special output destination. | |
| 74 | |
| 75 `> {filename}` sends the output to a file. This is more convenient than SQL\*Plus's | |
| 76 SPOOL {filename}... SPOOL OFF (though you can use those as well). | |
| 77 | |
| 78 `>` alone (no filename) sends the output to the paste buffer. | |
| 79 | |
| 80 `|` pipes the output to an operating-system command. | |
| 81 | |
| 82 When `< {filename}` is included in your command, it is replaced with the contents of | |
| 83 {filename} before the command is run. | |
| 84 | |
| 85 Examples:: | |
| 86 | |
| 283 | 87 Need examples!!!! |
| 88 | |
| 281 | 89 Special output formats |
| 90 ====================== | |
| 91 | |
| 92 By replacing the `;` that terminates a SELECT statement with a backslash-character | |
| 93 sequence, you can get output in a number of useful formats. The `terminators` | |
| 283 | 94 command lists them, for your convenience. |
| 281 | 95 |
| 283 | 96 ========== ======================== ================================ |
| 97 terminator format Useful for | |
| 98 ========== ======================== ================================ | |
| 99 ; standard Oracle format | |
| 100 \\c CSV (with headings) sending to spreadsheets | |
| 101 \\C CSV (no headings) | |
| 102 \\g list wide output with linewraps | |
| 103 \\G aligned list | |
| 104 \\h HTML table web reports | |
| 105 \\i INSERT statements copying to other instances | |
| 106 \\j JSON | |
|
329
3efffbf7481f
fixed bug in assigning 0, null to bind vars
Catherine Devlin <catherine.devlin@gmail.com>
parents:
294
diff
changeset
|
107 \\r ReStructured Text inclusion in documentation |
| 283 | 108 \\s CSV (with headings) |
| 109 \\S CSV (no headings) | |
|
329
3efffbf7481f
fixed bug in assigning 0, null to bind vars
Catherine Devlin <catherine.devlin@gmail.com>
parents:
294
diff
changeset
|
110 \\t transposed "wide" tables like v$database |
| 283 | 111 \\x XML |
| 112 \\l line plot, with markers | |
| 113 \\L scatter plot (no lines) | |
| 114 \\b bar graph | |
| 115 \\p pie chart | |
| 116 ========== ======================== ================================ | |
| 281 | 117 |
| 118 Most of these output formats are even more useful when combined with special output | |
|
329
3efffbf7481f
fixed bug in assigning 0, null to bind vars
Catherine Devlin <catherine.devlin@gmail.com>
parents:
294
diff
changeset
|
119 destinations. For example, `SELECT * FROM party\\h > /var/www/party_report.html` |
| 281 | 120 could create an HTML report in the webserver's documents directory, ready to serve. |
| 121 | |
| 247 | 122 UNIX-like commands |
| 123 ================== | |
| 124 | |
| 279 | 125 Many sqlpython commands allow you to act as though the database objects |
| 126 were files in a UNIX filesystem. Many of the commands also accept flags | |
| 127 to modify their behavior. | |
| 128 | |
| 286 | 129 ls `{object type/object name, with wildcards}` |
| 281 | 130 Lists objects from the data dictionaries, as though they were in a |
| 131 *object_type*/*object_name* directory structure. Thus, `ls view/\*` | |
| 132 lists all the user's views. Calling with no argument is equivalent | |
| 133 to `ls *`. | |
| 134 | |
| 135 Options:: | |
| 136 | |
| 137 -l, --long long descriptions | |
| 138 -a, --all all schemas' objects (otherwise, you only get your own) | |
| 139 -t, --timesort Sort by last_ddl_time | |
| 140 -r, --reverse Reverse order while sorting | |
| 247 | 141 |
| 281 | 142 `ls -lt *;10` lists the ten items with the most recent last_ddl_time; |
| 143 this can be a good way to answer the question, "What was I working on?" | |
| 144 | |
| 286 | 145 cat `{remainder of query}` |
| 146 Shorthand for "SELECT * FROM". Can be combined with anything else | |
| 147 that fits into a SELECT statement (WHERE, ORDER BY, etc.) | |
| 281 | 148 |
| 286 | 149 grep `{target}` `{table}` `[{table2,...}]` |
| 150 Equivalent to SELECT * FROM {table} WHERE *any column* LIKE '%{target}%'. | |
| 151 Useful when you don't know, don't remember, or don't care which column | |
| 152 a value may be found in. | |
| 281 | 153 |
| 154 Options:: | |
| 155 | |
| 156 -i, --ignore-case Case-insensitive search | |
| 247 | 157 |
| 281 | 158 find -c {target}, find -t {column} |
| 159 Lists all tables or columns whose names contain {target}. More convenient than | |
| 160 querying user_tab_columns/all_tab_columns or user_tables/all_tables. | |
| 161 Options:: | |
| 283 | 162 |
| 281 | 163 -a Find all objects (not just my own) |
| 164 | |
| 286 | 165 Data dictionary exploration |
| 166 =========================== | |
| 167 | |
| 168 refs `{table_name}` | |
| 169 Lists all foreign key constraints on the table or referring to the table. | |
| 170 | |
| 171 deps `{object_name}` | |
| 172 Lists all objects dependent upon the named object. | |
| 173 | |
| 174 comments `{table_name}` | |
| 175 Prints comments on a table and its columns. | |
| 176 | |
| 177 PL/SQL source code | |
| 178 ================== | |
| 179 | |
| 180 pull {object_name} | |
| 181 Displays the PL/SQL source code for {object_name}. | |
| 182 | |
| 183 Options: | |
| 184 -d, --dump dump results to files (object_type/object_name.sql) | |
| 185 -f, --full get dependent objects as well | |
| 186 -a, --all all schemas' objects | |
| 187 | |
| 188 bzr, git, hg `{object_name}` | |
| 189 Dump source code to files, as `pull -f`, but also creates or commits to a | |
| 190 repository of the appropriate distributed version control system | |
| 191 (Bazaar, Git, or Mercurial, respectively). | |
| 192 | |
| 193 find `{target}` | |
| 194 Lists all PL/SQL objects whose source code contains the {target} string. | |
| 195 Always case-insensitive. | |
| 196 Options:: | |
| 197 | |
| 198 -a Search all PL/SQL objects (not just my own) | |
| 199 | |
| 247 | 200 PostgreSQL-like shortcuts |
| 201 ========================= | |
| 202 | |
| 283 | 203 psql, the command-line client for the open-source database `PostgreSQL <http://www.postgresql.org/>`_ uses a number |
| 204 of backslash-character sequences as convenient shortcuts. sqlpython steals many of | |
| 205 them. | |
| 206 | |
| 207 ===== =================== | |
| 247 | 208 \\c connect |
| 209 \\d desc | |
| 210 \\e edit | |
| 211 \\g run | |
| 212 \\h help | |
| 213 \\i load | |
| 214 \\o spool | |
| 215 \\p list | |
| 216 \\q quit | |
| 217 \\w save | |
| 218 \\db _dir_tablespaces | |
| 219 \\dd comments | |
| 220 \\dn _dir_schemas | |
| 221 \\dt _dir_tables | |
| 222 \\dv _dir_views | |
| 223 \\di _dir_indexes | |
| 224 \\? help psql | |
| 283 | 225 ===== =================== |
| 281 | 226 |
| 227 Bind variables | |
| 228 ============== | |
| 229 | |
| 230 Bind variables work in sqlpython as they do in SQL\*Plus, but they are set dynamically; there | |
| 231 is no need to declare them before use. The syntax for setting them is more permissive than | |
| 232 in SQL\*Plus; all these are recognized:: | |
| 233 | |
| 234 exec :mybind := 'value' | |
| 235 exec :mybind = 'value' | |
| 236 :mybind := 'value' | |
| 237 :mybind = 'value' | |
| 238 | |
| 239 The current values of all bind variables can be viewed with the `print` command. | |
| 240 | |
| 241 The `bind` command creates and populates bind variables for the final row of the most recent | |
| 242 SELECT statement executed; each column name is used as a bind variable, which is filled with | |
| 243 the value. `bind -r {rownumber}` does the same, but fills from row {rownumber} instead of | |
| 244 from the final row (row numbers begin at 0 for this command). | |
| 245 | |
| 246 When the `autobind` sqlpython parameter is True, a `bind` statement is issued automatically | |
| 247 after every query that returns exactly one row. | |
| 248 | |
| 249 Bind variables are available from within Python as a dictionary named `binds` (see Python). | |
| 250 | |
| 251 Substitution variables | |
| 252 ====================== | |
| 253 | |
| 254 Substitution variables ("&" variables) work much as they do in SQL\*Plus. As in SQL\*Plus, | |
| 255 the `scan` parameter determines whether queries are scanned to replace substitution | |
| 256 variables. Unlike SQL\*Plus, sqlpython knows how annoying it is to hit a substitution | |
| 257 variable you didn't expect, so entering "SET SCAN OFF" when prompted for a substitution | |
| 258 variable actually aborts the substitution process. | |
| 259 | |
| 247 | 260 Wild SQL |
| 261 ======== | |
| 262 | |
| 263 Wild SQL is a nonstandard SQL feature that must be enabled with `set wildsql on`. When it is | |
| 282 | 264 enabled, column names in a SELECT statement do not need to be explicitly typed; they can be |
| 265 specified with special Wild SQL symbols: wildcards (`*`, `%`, `_`); column numbers (`#{N}`); | |
| 266 and NOT-style exclusion (`!`). The symbols can even be combined. | |
| 247 | 267 |
| 282 | 268 :: |
| 247 | 269 |
| 282 | 270 jrrt@orcl> cat party |
| 271 | |
| 272 NAME STR INT WIS DEX CON CHA | |
| 273 ------- --- --- --- --- --- --- | |
| 274 Frodo 8 14 16 15 14 16 | |
| 275 Gimli 17 12 10 11 17 11 | |
| 276 Legolas 13 15 14 18 15 17 | |
| 277 Sam 11 9 14 11 16 13 | |
| 278 | |
| 279 4 rows selected. | |
| 280 | |
| 281 jrrt@orcl> set wild on | |
| 282 wildsql - was: False | |
| 283 now: True | |
| 284 jrrt@orcl> select *i* from party; | |
| 285 | |
| 286 INT WIS | |
| 287 --- --- | |
| 288 14 16 | |
| 289 12 10 | |
| 290 15 14 | |
| 291 9 14 | |
| 292 | |
| 293 4 rows selected. | |
| 294 | |
| 295 jrrt@orcl> select #1, #5 from party; | |
| 296 | |
| 297 NAME DEX | |
| 298 ------- --- | |
| 299 Frodo 15 | |
| 300 Gimli 11 | |
| 301 Legolas 18 | |
| 302 Sam 11 | |
| 303 | |
| 304 4 rows selected. | |
| 305 | |
| 306 jrrt@orcl> select !str from party; | |
| 307 | |
| 308 NAME INT WIS DEX CON CHA | |
| 309 ------- --- --- --- --- --- | |
| 310 Frodo 14 16 15 14 16 | |
| 311 Gimli 12 10 11 17 11 | |
| 312 Legolas 15 14 18 15 17 | |
| 313 Sam 9 14 11 16 13 | |
| 314 | |
| 315 4 rows selected. | |
| 316 | |
| 317 jrrt@orcl> select n*, !#3, !c* from party; | |
| 318 | |
| 319 NAME STR WIS DEX | |
| 320 ------- --- --- --- | |
| 321 Frodo 8 16 15 | |
| 322 Gimli 17 10 11 | |
| 323 Legolas 13 14 18 | |
| 324 Sam 11 14 11 | |
| 325 | |
| 326 4 rows selected. | |
| 247 | 327 |
| 282 | 328 Wild SQL symbols only work in the first SELECT statement in a query; they do not work in |
| 329 subqueries, subsequent UNIONed queries, etc. | |
| 330 | |
| 331 Python | |
| 332 ====== | |
| 333 | |
| 334 The `py` command allows the user to execute Python commands, either one-at-a-time (with | |
| 335 `py {command}`) or in an interactive environment (beginning with a bare `py` statement, | |
| 294 | 336 and continuing until Ctrl-D, `quit()`, or `exit()` is entered). |
| 282 | 337 |
| 338 A history of result sets from each query is exposed to the python session as the list `r`; | |
| 294 | 339 the most recent result set is `r[-1]`. Each row can be references as a tuple, or as an |
| 340 object with an attribute for each column. | |
| 341 | |
| 342 Bind variables are exposed as the dictionary `binds`. Each row from each result set has | |
| 343 a .bind() method that fills a bind varible for each column with that row's value. | |
| 282 | 344 |
| 345 Resultsets in `r` are read-only, but `binds` can be written as well as read, and will | |
| 346 be working bind variables in the SQL environment. | |
| 347 | |
| 294 | 348 SQL and sqlpython commands can be issued from the Python environment with `sql("{your SQL}")`. |
| 349 | |
| 350 All variables are retained each time the python environment is entered (whether interactively, | |
| 351 or with one-line `py` statements). | |
| 282 | 352 :: |
| 247 | 353 |
| 294 | 354 0:testschema@orcl> select title, author from play; |
| 282 | 355 |
| 356 TITLE AUTHOR | |
| 357 --------------- ----------- | |
| 358 Timon of Athens Shakespeare | |
| 359 Twelfth Night Shakespeare | |
| 360 The Tempest Shakespeare | |
| 361 Agamemnon Aeschylus | |
| 362 | |
| 363 4 rows selected. | |
| 364 | |
| 294 | 365 0:testschema@orcl> py import urllib |
| 366 0:testschema@orcl> py current_season = urllib.urlopen('http://cincyshakes.com/').read() | |
| 367 0:testschema@orcl> py | |
| 368 Python 2.5.2 (r252:60911, Jul 31 2008, 17:28:52) | |
| 369 [GCC 4.2.3 (Ubuntu 4.2.3-2ubuntu7)] on linux2 | |
| 370 Type "help", "copyright", "credits" or "license" for more information. | |
| 371 (mysqlpy) | |
| 372 | |
| 373 py <command>: Executes a Python command. | |
| 374 py: Enters interactive Python mode; end with `Ctrl-D`, `quit()`, or 'exit`. | |
| 375 Past SELECT results are exposed as list `r`; | |
| 376 most recent resultset is `r[-1]`. | |
| 377 SQL bind, substitution variables are exposed as `binds`, `substs`. | |
| 378 SQL and sqlpython commands can be issued with sql("your non-python command here"). | |
| 379 | |
| 282 | 380 >>> r[-1] |
| 381 [('Timon of Athens', 'Shakespeare'), ('Twelfth Night', 'Shakespeare'), ('The Tempest', 'Shakespeare'), ('Agamemnon', 'Aeschylus')] | |
| 294 | 382 >>> r[-1][0][0] |
| 383 'Timon of Athens' | |
| 282 | 384 >>> for row in r[-1]: |
| 294 | 385 ... print "%s, by %s" % (row.title, row.author) |
| 386 ... | |
| 387 Timon of Athens, by Shakespeare | |
| 388 Twelfth Night, by Shakespeare | |
| 389 The Tempest, by Shakespeare | |
| 390 Agamemnon, by Aeschylus | |
| 282 | 391 >>> [row.title for row in r[-1] if row.title in current_season] |
| 392 ['Timon of Athens', 'Twelfth Night'] | |
| 294 | 393 >>> binds['author'] = 'Shakespeare' |
| 394 >>> query = "SELECT title FROM play WHERE author = :author" | |
| 395 >>> sql(query) | |
| 396 | |
| 397 TITLE | |
| 398 --------------- | |
| 399 Timon of Athens | |
| 400 Twelfth Night | |
| 401 The Tempest | |
| 402 | |
| 403 3 rows selected. | |
| 404 | |
| 405 >>> r[-1] | |
| 406 [('Timon of Athens',), ('Twelfth Night',), ('The Tempest',)] | |
| 407 >>> r[-1][0] | |
| 408 ('Timon of Athens',) | |
| 409 >>> r[-1][0].bind() | |
| 410 >>> binds['title'] | |
| 411 'Timon of Athens' | |
| 412 >>> quit() | |
| 413 0:testschema@orcl> select title, author from play where title = :title; | |
| 282 | 414 |
| 415 TITLE AUTHOR | |
| 416 --------------- ----------- | |
| 417 Timon of Athens Shakespeare | |
| 418 | |
| 419 1 row selected. | |
| 294 | 420 |
| 281 | 421 Parameters |
| 422 ========== | |
| 423 | |
| 424 Several parameters control the behavior of sqlpython itself. | |
| 425 | |
| 283 | 426 ===================== =================================================== =============== |
| 427 parameter effect default | |
| 428 ===================== =================================================== =============== | |
| 429 autobind When True, single-row queries automatically `bind` False | |
| 430 commit_on_exit Automatically commits work at end of session True | |
| 431 continuation_prompt Prompt for second line and onward of long statement > | |
| 432 default_file_name The file opened by `edit`, if not specified afiedt.buf | |
| 433 echo Echo command entered before executing False | |
| 434 editor Text editor invoked by `edit`. varies | |
| 294 | 435 heading Print column names along with results True |
| 283 | 436 maxfetch Maximum number of rows to return from any query 1000 |
| 437 maxtselctrows Maximum # of rows from a tselect or \\n query 10 | |
| 438 prompt Probably unwise to change user@instance> | |
| 439 scan Interpret & as indicating substitution variables True | |
| 440 serveroutput Print DBMS_OUTPUT.PUT_LINE results True | |
| 441 sql_echo Print text of "behind-the-scenes" queries False | |
| 442 timeout In seconds 30 | |
| 443 timing Print time for each command to execute False | |
| 444 wildsql Accept *, %, #, and ! in column names False | |
| 445 ===================== =================================================== =============== | |
| 281 | 446 |
| 447 The user can change these with the `set {paramname} {new-value}` statement. | |
| 283 | 448 The True/False parameters accept new values permissively, recognizing "True", "False", |
| 449 "T", "F", "yes", "no", "on", "off", etc. | |
| 281 | 450 |
| 283 | 451 `set` and `show` both list the current values of the sqlpython parameters. They |
| 452 also recognize any abbreviated parameter name, so long as it is long enough to be | |
| 453 unique. That is, `show maxf` is recognized as `show maxfetch`, but `show max` is | |
| 454 too short to distinguish between `maxfetch` and `maxtselctrows`. | |
| 455 | |
| 281 | 456 `show parameter {param}` shows current Oracle parameters (from v$parameter), as it does |
| 457 in SQL\*Plus. | |
| 458 | |
| 459 Tuning | |
| 460 ====== | |
| 461 | |
| 462 In sqlpython, `explain {SQL ID}` shows the execution plan for the SQL statement with the | |
| 463 given ID. If SQL ID is omitted, it defaults to the most recent SQL executed. | |
| 464 (This is not necessarily the last statement `EXPLAIN PLAN` was issued against.) | |
| 465 | |
| 466 Other specialized sqlpython tuning commands include: | |
| 467 | |
| 468 load | |
| 469 Displays OS load on cluster nodes (10gRAC) | |
| 470 | |
| 471 longops | |
| 472 Displays long-running operations | |
| 473 | |
| 474 sessinfo | |
| 475 Reports session info for the given sid, extended to RAC with gv$ | |
| 476 | |
| 477 top, top9i | |
| 478 Displays active sessions | |
| 479 | |
| 480 | |
| 481 | |
| 482 | |
| 483 |