My answer used to be, "Ask Luca [Canali]; he wrote it."
Not anymore. Now, witness the power of this fully armed and operational hybrid SQL/Python working environment.
[EDIT: As of sqlpython 1.6.2, you use quit(), exit(), or Ctrl-D/Ctrl-Z to return from
interactive Python mode. See docs.]
0:testschema@eqtest> select title, author from play;
TITLE AUTHOR
--------------- -----------
Timon of Athens Shakespeare
Twelfth Night Shakespeare
The Tempest Shakespeare
Agamemnon Aeschylus
4 rows selected.
0:testschema@eqtest> py import urllib
0:testschema@eqtest> py current_season = urllib.urlopen('http://cincyshakes.com/').read()
0:testschema@eqtest> py
Now accepting python commands; end with `end py`
>>> r[-1]
[('Timon of Athens', 'Shakespeare'), ('Twelfth Night', 'Shakespeare'), ('The Tempest', 'Shakespeare'), ('Agamemnon', 'Aeschylus')]
>>> 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['nowplaying'] = [row.title for row in r[-1] if row.title in current_season][0]
>>> end py
0:testschema@eqtest> print
:nowplaying = Timon of Athens
0:testschema@eqtest> select title, author from play where title = :nowplaying;
TITLE AUTHOR
--------------- -----------
Timon of Athens Shakespeare
1 row selected.
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]`. Bind variables are exposed as the dictionary `binds`. All variables are retained each time the python environment is entered (whether interactively, or with one-line `py` statements).
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.
Oh, the possibilities...
