Thursday, March 19, 2009

sqlpython 1.6.1 puts the "python" in "sqlpython"

I've been asked several times, "Why is it called sqlpython?"

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...

7 comments:

Siew Kam Onn said...

dear Catherine,

Can't seem to get sqlpython to work.

eg


0:apps@newfieldtst2> get d.sql
Substitution variable &1 found in:
DEF TABLE=&1
Substitution for &1 (SET SCAN OFF to halt substitution): ^C


and



0:apps@newfieldtst2> @d gl_interface
Problem accessing script from d gl_interface:
[Errno 2] No such file or directory: 'd gl_interface.sql'
0:apps@newfieldtst2>

Siew Kam Onn said...

dear Catherine,

Can't seem to get sqlpython to work.

0:apps@newfieldtst2> @d gl_interface
Problem accessing script from d gl_interface:
[Errno 2] No such file or directory: 'd gl_interface.sql'
0:apps@newfieldtst2>


and


0:apps@newfieldtst2> get d.sql
Substitution variable &1 found in:
DEF TABLE=&1
Substitution for &1 (SET SCAN OFF to halt substitution):

Catherine said...

Hi, Siew! What is the d.sql file you're trying to run?

I don't support the DEF command in sqlpython - I've never used it, actually. In general, scripts written for SQL*Plus, that rely on SQL*Plus commands like DEF (as opposed to SQL commands), won't necessarily run unmodified in sqlpython. sqlpython supports a subset of SQL*Plus commands (as well as many commands of its own).

As for @d gl_interface, I don't understand what that is intended to do - run d.sql and pass in gl_interface to &1? I haven't written that into sqlpython yet, either.

Try commenting the DEF statements out of your script.

Siew Kam Onn said...

Here is d.sql

DEF TABLE=&1
spo d append

Prompt DESC &TABLE
DESC &TABLE

col object_type LIKE np NEW_VALUE object_type
col message FOR a30

SELECT object_name || ' IS ' || object_type message,
object_type
FROM user_objects
WHERE object_name = upper('&table');

col synonym_definition FOR a100

SELECT table_owner || '.' || table_name ||
decode(db_link, NULL, '', '@' || db_link) synonym_definition
FROM user_synonyms
WHERE '&object_type' = 'SYNONYM'
AND synonym_name = UPPER('&TABLE')
/

@ind &TABLE

spo off

Siew Kam Onn said...

I tried with cc.sql

== cc.sql =======
SELECT name
FROM EB_REPORT_CENTERS
WHERE FACILITY_UID IN (56674, 232653)
;
== end cc.sql =====

and got this:

0:apps@newfieldtst2> @cc

NAME
---------------
NORTH LUKUT OBO
PENARA OBO
ABU CLUSTER A
PUTERI 1

4 rows selected.

.
ORA-00900: invalid SQL statement

Catherine said...

Hmm... that script has lots of dependence on SQL*Plus-specific scripts, all right. It might be a long time before sqlpython has complete enough coverage of SQL*Plus commands to enable scripts like that to run without modification.

It also uses shortcuts like 'spo' for 'spool', which I don't think work in sqlpython.

I am working on supporting DEF and PROMPT, though - they should be in sqlpython 1.6.2, within the next week or two.

Dwayne King said...

I realize I'm responding to an old post, but it is somewhat related.

I'm looking for a piece of python code (before I go off and write my own) that will take a result set and print it out in a columnar format similar to sqlplus. You've obviously already tackled this problem since sqlpython does this.

Is the code that does this automatic sizing of columns etc modular enough to pluck out for use elsewhere? Or is there a way to call the piece of sqlpython code from my existing module?

Thanks Catherine! I've ended up at a lot of your posts recently :)