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