Mercurial > sqlpython
annotate docs/source/capabilities.rst @ 352:8e341308ea45
find working
| author | catherine@cordelia |
|---|---|
| date | Sat, 25 Apr 2009 06:54:44 -0400 |
| parents | 80a1976decf2 |
| children |
| 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 ===== =================== |
|
342
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
226 |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
227 Multiple sessions |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
228 ================= |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
229 |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
230 When the `-a` flag is used with the `connect` or `\c` command, the new connection |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
231 is in addition to the old connection. `connect -a {username@instance}` is used to create a new connection, senora does not close the |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
232 old connection. It keeps both connections alive, and switching between them is far more convenient |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
233 than creating new connections:: |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
234 |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
235 0:jrrt@orcl> connect -a scott/tiger@orcl |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
236 1:scott@orcl> select * from dept; |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
237 |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
238 DEPTNO DNAME LOC |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
239 ------ ---------- -------- |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
240 10 ACCOUNTING NEW YORK |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
241 20 RESEARCH DALLAS |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
242 30 SALES CHICAGO |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
243 40 OPERATIONS BOSTON |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
244 |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
245 4 rows selected. |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
246 |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
247 1:scott@orcl> 0:select * from party; |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
248 |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
249 NAME STR INT WIS DEX CON CHA |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
250 ------- --- --- --- --- --- --- |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
251 Frodo 8 14 16 15 14 16 |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
252 Gimli 17 12 10 11 17 11 |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
253 Legolas 13 15 14 18 15 17 |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
254 Sam 11 9 14 11 16 13 |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
255 |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
256 4 rows selected. |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
257 |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
258 1:scott@orcl> 0: |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
329
diff
changeset
|
259 0:jrrt@orcl> |
| 281 | 260 |
| 261 Bind variables | |
| 262 ============== | |
| 263 | |
| 264 Bind variables work in sqlpython as they do in SQL\*Plus, but they are set dynamically; there | |
| 265 is no need to declare them before use. The syntax for setting them is more permissive than | |
| 266 in SQL\*Plus; all these are recognized:: | |
| 267 | |
| 268 exec :mybind := 'value' | |
| 269 exec :mybind = 'value' | |
| 270 :mybind := 'value' | |
| 271 :mybind = 'value' | |
| 272 | |
| 273 The current values of all bind variables can be viewed with the `print` command. | |
| 274 | |
| 275 The `bind` command creates and populates bind variables for the final row of the most recent | |
| 276 SELECT statement executed; each column name is used as a bind variable, which is filled with | |
| 277 the value. `bind -r {rownumber}` does the same, but fills from row {rownumber} instead of | |
| 278 from the final row (row numbers begin at 0 for this command). | |
| 279 | |
| 280 When the `autobind` sqlpython parameter is True, a `bind` statement is issued automatically | |
| 281 after every query that returns exactly one row. | |
| 282 | |
| 283 Bind variables are available from within Python as a dictionary named `binds` (see Python). | |
| 284 | |
| 285 Substitution variables | |
| 286 ====================== | |
| 287 | |
| 288 Substitution variables ("&" variables) work much as they do in SQL\*Plus. As in SQL\*Plus, | |
| 289 the `scan` parameter determines whether queries are scanned to replace substitution | |
| 290 variables. Unlike SQL\*Plus, sqlpython knows how annoying it is to hit a substitution | |
| 291 variable you didn't expect, so entering "SET SCAN OFF" when prompted for a substitution | |
| 292 variable actually aborts the substitution process. | |
| 293 | |
| 247 | 294 Wild SQL |
| 295 ======== | |
| 296 | |
| 297 Wild SQL is a nonstandard SQL feature that must be enabled with `set wildsql on`. When it is | |
| 282 | 298 enabled, column names in a SELECT statement do not need to be explicitly typed; they can be |
| 299 specified with special Wild SQL symbols: wildcards (`*`, `%`, `_`); column numbers (`#{N}`); | |
| 300 and NOT-style exclusion (`!`). The symbols can even be combined. | |
| 247 | 301 |
| 282 | 302 :: |
| 247 | 303 |
| 282 | 304 jrrt@orcl> cat party |
| 305 | |
| 306 NAME STR INT WIS DEX CON CHA | |
| 307 ------- --- --- --- --- --- --- | |
| 308 Frodo 8 14 16 15 14 16 | |
| 309 Gimli 17 12 10 11 17 11 | |
| 310 Legolas 13 15 14 18 15 17 | |
| 311 Sam 11 9 14 11 16 13 | |
| 312 | |
| 313 4 rows selected. | |
| 314 | |
| 315 jrrt@orcl> set wild on | |
| 316 wildsql - was: False | |
| 317 now: True | |
| 318 jrrt@orcl> select *i* from party; | |
| 319 | |
| 320 INT WIS | |
| 321 --- --- | |
| 322 14 16 | |
| 323 12 10 | |
| 324 15 14 | |
| 325 9 14 | |
| 326 | |
| 327 4 rows selected. | |
| 328 | |
| 329 jrrt@orcl> select #1, #5 from party; | |
| 330 | |
| 331 NAME DEX | |
| 332 ------- --- | |
| 333 Frodo 15 | |
| 334 Gimli 11 | |
| 335 Legolas 18 | |
| 336 Sam 11 | |
| 337 | |
| 338 4 rows selected. | |
| 339 | |
| 340 jrrt@orcl> select !str from party; | |
| 341 | |
| 342 NAME INT WIS DEX CON CHA | |
| 343 ------- --- --- --- --- --- | |
| 344 Frodo 14 16 15 14 16 | |
| 345 Gimli 12 10 11 17 11 | |
| 346 Legolas 15 14 18 15 17 | |
| 347 Sam 9 14 11 16 13 | |
| 348 | |
| 349 4 rows selected. | |
| 350 | |
| 351 jrrt@orcl> select n*, !#3, !c* from party; | |
| 352 | |
| 353 NAME STR WIS DEX | |
| 354 ------- --- --- --- | |
| 355 Frodo 8 16 15 | |
| 356 Gimli 17 10 11 | |
| 357 Legolas 13 14 18 | |
| 358 Sam 11 14 11 | |
| 359 | |
| 360 4 rows selected. | |
| 247 | 361 |
| 282 | 362 Wild SQL symbols only work in the first SELECT statement in a query; they do not work in |
| 363 subqueries, subsequent UNIONed queries, etc. | |
| 364 | |
| 365 Python | |
| 366 ====== | |
| 367 | |
| 368 The `py` command allows the user to execute Python commands, either one-at-a-time (with | |
| 369 `py {command}`) or in an interactive environment (beginning with a bare `py` statement, | |
| 294 | 370 and continuing until Ctrl-D, `quit()`, or `exit()` is entered). |
| 282 | 371 |
| 372 A history of result sets from each query is exposed to the python session as the list `r`; | |
| 294 | 373 the most recent result set is `r[-1]`. Each row can be references as a tuple, or as an |
| 374 object with an attribute for each column. | |
| 375 | |
| 376 Bind variables are exposed as the dictionary `binds`. Each row from each result set has | |
| 377 a .bind() method that fills a bind varible for each column with that row's value. | |
| 282 | 378 |
| 379 Resultsets in `r` are read-only, but `binds` can be written as well as read, and will | |
| 380 be working bind variables in the SQL environment. | |
| 381 | |
| 294 | 382 SQL and sqlpython commands can be issued from the Python environment with `sql("{your SQL}")`. |
| 383 | |
| 384 All variables are retained each time the python environment is entered (whether interactively, | |
| 385 or with one-line `py` statements). | |
| 282 | 386 :: |
| 247 | 387 |
| 294 | 388 0:testschema@orcl> select title, author from play; |
| 282 | 389 |
| 390 TITLE AUTHOR | |
| 391 --------------- ----------- | |
| 392 Timon of Athens Shakespeare | |
| 393 Twelfth Night Shakespeare | |
| 394 The Tempest Shakespeare | |
| 395 Agamemnon Aeschylus | |
| 396 | |
| 397 4 rows selected. | |
| 398 | |
| 294 | 399 0:testschema@orcl> py import urllib |
| 400 0:testschema@orcl> py current_season = urllib.urlopen('http://cincyshakes.com/').read() | |
| 401 0:testschema@orcl> py | |
| 402 Python 2.5.2 (r252:60911, Jul 31 2008, 17:28:52) | |
| 403 [GCC 4.2.3 (Ubuntu 4.2.3-2ubuntu7)] on linux2 | |
| 404 Type "help", "copyright", "credits" or "license" for more information. | |
| 405 (mysqlpy) | |
| 406 | |
| 407 py <command>: Executes a Python command. | |
| 408 py: Enters interactive Python mode; end with `Ctrl-D`, `quit()`, or 'exit`. | |
| 409 Past SELECT results are exposed as list `r`; | |
| 410 most recent resultset is `r[-1]`. | |
| 411 SQL bind, substitution variables are exposed as `binds`, `substs`. | |
| 412 SQL and sqlpython commands can be issued with sql("your non-python command here"). | |
| 413 | |
| 282 | 414 >>> r[-1] |
| 415 [('Timon of Athens', 'Shakespeare'), ('Twelfth Night', 'Shakespeare'), ('The Tempest', 'Shakespeare'), ('Agamemnon', 'Aeschylus')] | |
| 294 | 416 >>> r[-1][0][0] |
| 417 'Timon of Athens' | |
| 282 | 418 >>> for row in r[-1]: |
| 294 | 419 ... print "%s, by %s" % (row.title, row.author) |
| 420 ... | |
| 421 Timon of Athens, by Shakespeare | |
| 422 Twelfth Night, by Shakespeare | |
| 423 The Tempest, by Shakespeare | |
| 424 Agamemnon, by Aeschylus | |
| 282 | 425 >>> [row.title for row in r[-1] if row.title in current_season] |
| 426 ['Timon of Athens', 'Twelfth Night'] | |
| 294 | 427 >>> binds['author'] = 'Shakespeare' |
| 428 >>> query = "SELECT title FROM play WHERE author = :author" | |
| 429 >>> sql(query) | |
| 430 | |
| 431 TITLE | |
| 432 --------------- | |
| 433 Timon of Athens | |
| 434 Twelfth Night | |
| 435 The Tempest | |
| 436 | |
| 437 3 rows selected. | |
| 438 | |
| 439 >>> r[-1] | |
| 440 [('Timon of Athens',), ('Twelfth Night',), ('The Tempest',)] | |
| 441 >>> r[-1][0] | |
| 442 ('Timon of Athens',) | |
| 443 >>> r[-1][0].bind() | |
| 444 >>> binds['title'] | |
| 445 'Timon of Athens' | |
| 446 >>> quit() | |
| 447 0:testschema@orcl> select title, author from play where title = :title; | |
| 282 | 448 |
| 449 TITLE AUTHOR | |
| 450 --------------- ----------- | |
| 451 Timon of Athens Shakespeare | |
| 452 | |
| 453 1 row selected. | |
| 294 | 454 |
| 281 | 455 Parameters |
| 456 ========== | |
| 457 | |
| 458 Several parameters control the behavior of sqlpython itself. | |
| 459 | |
| 283 | 460 ===================== =================================================== =============== |
| 461 parameter effect default | |
| 462 ===================== =================================================== =============== | |
| 463 autobind When True, single-row queries automatically `bind` False | |
| 464 commit_on_exit Automatically commits work at end of session True | |
| 465 continuation_prompt Prompt for second line and onward of long statement > | |
| 466 default_file_name The file opened by `edit`, if not specified afiedt.buf | |
| 467 echo Echo command entered before executing False | |
| 468 editor Text editor invoked by `edit`. varies | |
| 294 | 469 heading Print column names along with results True |
| 283 | 470 maxfetch Maximum number of rows to return from any query 1000 |
| 471 maxtselctrows Maximum # of rows from a tselect or \\n query 10 | |
| 472 prompt Probably unwise to change user@instance> | |
| 473 scan Interpret & as indicating substitution variables True | |
| 474 serveroutput Print DBMS_OUTPUT.PUT_LINE results True | |
| 475 sql_echo Print text of "behind-the-scenes" queries False | |
| 476 timeout In seconds 30 | |
| 477 timing Print time for each command to execute False | |
| 478 wildsql Accept *, %, #, and ! in column names False | |
| 479 ===================== =================================================== =============== | |
| 281 | 480 |
| 481 The user can change these with the `set {paramname} {new-value}` statement. | |
| 283 | 482 The True/False parameters accept new values permissively, recognizing "True", "False", |
| 483 "T", "F", "yes", "no", "on", "off", etc. | |
| 281 | 484 |
| 283 | 485 `set` and `show` both list the current values of the sqlpython parameters. They |
| 486 also recognize any abbreviated parameter name, so long as it is long enough to be | |
| 487 unique. That is, `show maxf` is recognized as `show maxfetch`, but `show max` is | |
| 488 too short to distinguish between `maxfetch` and `maxtselctrows`. | |
| 489 | |
| 281 | 490 `show parameter {param}` shows current Oracle parameters (from v$parameter), as it does |
| 491 in SQL\*Plus. | |
| 492 | |
| 493 Tuning | |
| 494 ====== | |
| 495 | |
| 496 In sqlpython, `explain {SQL ID}` shows the execution plan for the SQL statement with the | |
| 497 given ID. If SQL ID is omitted, it defaults to the most recent SQL executed. | |
| 498 (This is not necessarily the last statement `EXPLAIN PLAN` was issued against.) | |
| 499 | |
| 500 Other specialized sqlpython tuning commands include: | |
| 501 | |
| 502 load | |
| 503 Displays OS load on cluster nodes (10gRAC) | |
| 504 | |
| 505 longops | |
| 506 Displays long-running operations | |
| 507 | |
| 508 sessinfo | |
| 509 Reports session info for the given sid, extended to RAC with gv$ | |
| 510 | |
| 511 top, top9i | |
| 512 Displays active sessions | |
| 513 | |
| 514 | |
| 515 | |
| 516 | |
| 517 |