Mercurial > sqlpython

comparison docs/source/capabilities.rst @ 282:4eef08cfaf25

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
copied documentation from blog
author catherine@dellzilla
date Thu, 19 Mar 2009 16:56:36 -0400
parents 701f0aae837a
children 80484bc60bdd
comparison
equal deleted inserted replaced
281:701f0aae837a 282:4eef08cfaf25
241 241
242 Wild SQL 242 Wild SQL
243 ======== 243 ========
244 244
245 Wild SQL is a nonstandard SQL feature that must be enabled with `set wildsql on`. When it is 245 Wild SQL is a nonstandard SQL feature that must be enabled with `set wildsql on`. When it is
246 enabled, column names in a SELECT statement do not need to be explicitly typed. 246 enabled, column names in a SELECT statement do not need to be explicitly typed; they can be
247 247 specified with special Wild SQL symbols: wildcards (`*`, `%`, `_`); column numbers (`#{N}`);
248 * % or \* as wildcards:: 248 and NOT-style exclusion (`!`). The symbols can even be combined.
249 249
250 SELECT d* FROM v$database; 250 ::
251 251
252 SELECT 252 jrrt@orcl> cat party
253 253
254 Wild SQL can only be used in the primary column list of straightforward SELECT statements, 254 NAME STR INT WIS DEX CON CHA
255 not in subqueries, `UNION`ed queries, etc. 255 ------- --- --- --- --- --- ---
256 Frodo 8 14 16 15 14 16
257 Gimli 17 12 10 11 17 11
258 Legolas 13 15 14 18 15 17
259 Sam 11 9 14 11 16 13
260
261 4 rows selected.
262
263 jrrt@orcl> set wild on
264 wildsql - was: False
265 now: True
266 jrrt@orcl> select *i* from party;
267
268 INT WIS
269 --- ---
270 14 16
271 12 10
272 15 14
273 9 14
274
275 4 rows selected.
276
277 jrrt@orcl> select #1, #5 from party;
278
279 NAME DEX
280 ------- ---
281 Frodo 15
282 Gimli 11
283 Legolas 18
284 Sam 11
285
286 4 rows selected.
287
288 jrrt@orcl> select !str from party;
289
290 NAME INT WIS DEX CON CHA
291 ------- --- --- --- --- ---
292 Frodo 14 16 15 14 16
293 Gimli 12 10 11 17 11
294 Legolas 15 14 18 15 17
295 Sam 9 14 11 16 13
296
297 4 rows selected.
298
299 jrrt@orcl> select n*, !#3, !c* from party;
300
301 NAME STR WIS DEX
302 ------- --- --- ---
303 Frodo 8 16 15
304 Gimli 17 10 11
305 Legolas 13 14 18
306 Sam 11 14 11
307
308 4 rows selected.
309
310 Wild SQL symbols only work in the first SELECT statement in a query; they do not work in
311 subqueries, subsequent UNIONed queries, etc.
312
313 Python
314 ======
315
316 The `py` command allows the user to execute Python commands, either one-at-a-time (with
317 `py {command}`) or in an interactive environment (beginning with a bare `py` statement,
318 and continuing until `end py` is entered).
319
320 A history of result sets from each query is exposed to the python session as the list `r`;
321 the most recent result set is `r[-1]`. Bind variables are exposed as the dictionary `binds`.
322 All variables are retained each time the python environment is entered (whether interactively,
323 or with one-line `py` statements).
324
325 Resultsets in `r` are read-only, but `binds` can be written as well as read, and will
326 be working bind variables in the SQL environment.
327
328 ::
329
330 0:testschema@eqtest> select title, author from play;
331
332 TITLE AUTHOR
333 --------------- -----------
334 Timon of Athens Shakespeare
335 Twelfth Night Shakespeare
336 The Tempest Shakespeare
337 Agamemnon Aeschylus
338
339 4 rows selected.
340
341 0:testschema@eqtest> py import urllib
342 0:testschema@eqtest> py current_season = urllib.urlopen('http://cincyshakes.com/').read()
343 0:testschema@eqtest> py
344 Now accepting python commands; end with `end py`
345 >>> r[-1]
346 [('Timon of Athens', 'Shakespeare'), ('Twelfth Night', 'Shakespeare'), ('The Tempest', 'Shakespeare'), ('Agamemnon', 'Aeschylus')]
347 >>> for row in r[-1]:
348 ... print '%s by %s' % (row.title, row.author)
349 Timon of Athens by Shakespeare
350 Twelfth Night by Shakespeare
351 The Tempest by Shakespeare
352 Agamemnon by Aeschylus
353 >>> [row.title for row in r[-1] if row.title in current_season]
354 ['Timon of Athens', 'Twelfth Night']
355 >>> binds['nowplaying'] = [row.title for row in r[-1] if row.title in current_season][0]
356 >>> end py
357 0:testschema@eqtest> print
358 :nowplaying = Timon of Athens
359 0:testschema@eqtest> select title, author from play where title = :nowplaying;
360
361 TITLE AUTHOR
362 --------------- -----------
363 Timon of Athens Shakespeare
364
365 1 row selected.
256 366
257 Parameters 367 Parameters
258 ========== 368 ==========
259 369
260 Several parameters control the behavior of sqlpython itself. 370 Several parameters control the behavior of sqlpython itself.