Saturday, June 03, 2006

sqlpython enhancements

I told you that Luca Canali's sqlpython is wonderfully easy to customize.

I probably should have also told you that it's dangerously addictive to customize. I kind of went out of control, and produced sqlpyPlus.py, a module of enhancements to sqlpython.

- SQL*Plus-style bind variables
- Query result stored in special bind variable ":_" if one row, one item
- SQL buffer with list, run, ed, get, etc.; unlike SQL*Plus, buffer stores session's full history
- @script.sql loads and runs (like SQL*Plus)
- ! runs operating-system command
- SQL*Plus-style describe, spool
- write sends query result directly to file
- comments shows table and column comments
- compare ... to ... graphically compares results of two queries
- commands are case-insensitive
- show and set to control sqlpython parameters

sqlpyPlus.py is not as clean and elegant as sqlpython - that's one reason I put it in a separate module, so that you can keep it separate from the original sqlpython and your own homemade enhancements. But it should cover pretty much everything you usually use SQL*Plus for, plus some goodies I hope you'll like.

[EDIT: Since I wrote this, Luca has wrapped an enhanced and debugged version of sqlpyplus into his distribution of sqlpython itself. Now you should simply go and get or upgrade sqlpython, and you'll have these goodies automatically.]

5 comments:

Anonymous said...

Great! I have just integrated into the current version of mysqlpy.

Cheers, Luca

Steve Allen said...

Very cool! My Python skills are, well, ok, practically non-existant, but I'm wondering how hard it would be to have "boxed" output like you get with the mysql client, e.g.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)

Thanks!
Steve

Jeff said...

Hi-
Any chance of doing a screencast of SQLPython? It sounds nifty but I'm just too lazy to learn about the stick till I see the sizzle..nothing like learning from someone who knows how to use a tool. I started using IPython after seeing this movie. My contribution was one about auto-running unit tests.

Thanks,
Jeff

Jeff said...

prevPost = prevPost.replace ('stick','steak')

Tim said...

Catherine,

Can it be made to display some of a clob in the results?

At the moment it chokes:

ProgrammingError: LOB variable no longer valid after subsequent fetch

Traceback (innermost last):

File "c:\Python25\Lib\site-packages\sqlpython\mysqlpy.py", line 1, in module
# MySqlPy V1.2
File "c:\Python25\Lib\site-packages\sqlpython\mysqlpy.py", line 180, in module
my.cmdloop()
File "c:\Python25\Lib\cmd.py", line 142, in cmdloop
stop = self.onecmd(line)
File "c:\Python25\Lib\cmd.py", line 219, in onecmd
return func(arg)
File "c:\Python25\Lib\site-packages\sqlpython\sqlpyPlus.py", line 368, in do_select
print '\n' + sqlpython.pmatrix(self.rows,self.desc)
File "c:\Python25\Lib\site-packages\sqlpython\sqlpython.py", line 104, in pmatrix
rowsi = map(str, rows[i]) # current row to process

Regards,

Tim