Five minutes at PyCon change everything

I gave a five-minute Lightning Talk on sqlpython on Saturday. I hoped it would pique the interest of some people who sometimes use Oracle, and give them a neat example of yet another cool thing being done with Python. It certainly did that, and I got lots of gratifying feedback.

I knew people would ask when it would be available for non-Oracle databases, so I said, tongue-in-cheek, that this was my distant-future ambition for "sqlpython 3000". What I didn't expect was that several of the people buttonholing me over the next two days would ask to collaborate to get multi-RDBMS support in place. Help? Uh, yeah... I guess help would help... I honestly hadn't even been thinking about that...

Brian Dorsey in particular wanted to see the code face-to-face with me, so I put a card on the Open Space board, just in case anybody else wanted to show up, and twittered about it one bare hour in advance.

Nine people came, all of them eager to get going on writing code, bringing great ideas to get started. All this for a project that was basically personal 36 hours before.

Noooo, now other people are going to be exposed to my squiggly code! Now I know what embarrassment-driven development really is.

If I'd had $1 million of startup funding to hire a staff to work on sqlpython, I couldn't have gotten a team that large or that talented. I figure that gives me better than a 1000-to-1 return on my PyCon investment. :)

So anyway, I'm setting up a mailing list for cooperation on sqlpython, and it looks like the far-future dream of multi-RDBMS sqlpython has suddenly become imminent. Stay tuned!

sqlpython lightning talk follow-up

This morning's lightning talk on sqlpython was an example of what's so great about PyCon - it instantly really good suggestions about how to go onward with sqlpython development, and offers of collaboration. Awesome!

Except that I forgot to show the instant graphs using \b / \l terminators. Rats! That's the most eye-catching part!

I wasn't prepared for the common question, though: "Where's the repository?" Well, it's here:
https://www.assembla.com/wiki/show/sqlpython It is crazy-unstable, and if you're actually trying to use it, use the PyPI version instead.

I mean to get a link to that into the docs as soon as possible, but I don't have Sphinx configured right on this machine, so that may have to wait.

Quick review of the lightning talk:

* Unix-like powers: cat, ls, grep, >, |
* Python interactive session; access to resultsets (`r`) and bind variables (`binds`)
* Special output formats with alternate terminators (see `help terminators`)
* The magic that makes sqlpython work:
- cmd
- cmd2
- pyparsing
- code (for the embedded Python interpreter)
- cx_Oracle

For further reference, see the sqlpython docs, particularly the comparative review of sqlpython vs. SQL*Plus vs. gqlplus vs. Senora vs. YASQL.

Ada Lovelace Day: Tech Women I Admire

I will publish a blog post on Tuesday 24th March about a woman in technology whom I admire but only if 1,000 other people will do the same.

I can't stop at one; sorry if that breaks the rules.

• As a frequent speaker at conventions and as co-author of the Python Cookbook, Anna Ravenscroft has a great talent for helping people understand problems, even the brain-bending ones.
• Dianne Marsh helps run a company that vigorously fosters good, innovative programming, both among their own developers and throughout the entire region. Her energy and dedication have been crucial to CodeMash, one of the most innovative things to happen in our area, and to several user groups in Michigan.
• Sarah Dutkiewicz, aka the Coding Geekette, is a programmer from northeast Ohio who's done great things in pulling together the geek community and teaching them new technologies (like IronPython on Mono) - often the sort of stuff that requires venturing into dragon-infested realms where the documentation is scattered or nonexistent.
• In her long-time leadership of the OOUG, Coreen Walker
has helped make it one of the best Oracle groups around.

PyCon begins...

... well, the tutorials and summits begin tomorrow. But some of the organizers are already in place, getting things set up. I wish I were there! I won't arrive until Thursday afternoon - I had to sacrifice all but the core conference days so that I could make the separate trip to IOUG Collaborate. For me, it feels like arriving at a family Christmas halfway through the gift unwrapping.

Have you seen the list of PyCon sponsors this year? It's amazing! It's practically a Who's Who... if your company isn't sponsoring PyCon, your corporate headquarters is probably roofed with thatch. :) It's a tribute to Van Lindberg, PyCon's sponsorship coordinator, but even more to the growing corporate recognition that Python has become a pillar of the IT world.

Where to host docs?

I'm working on some preliminary sqlpython docs using Sphinx, which is really really nice. No guarantees that they will remain there, though - they need a home without a raw IP address for a URL!

I'm trying to figure out a permanent home for the docs. Here are the possibilities I know of.

• Host the files on my own server. Buy a domain name (but not like last time). This is obviously the best option... unless I ever decide to quit paying for the domain, or the server, or forget to, or get hit by a bus. So really, I'd prefer something that wasn't so dependent on, well, me.
• Sourceforge, Assembla, Google Code, and probably everybody else who hosts projects also let you create and host wiki-style documentation. As far as I know, though, there's no way to upload Sphinx documentation into any of them; the wiki form and the Sphinx form are not compatible.
• Google Pages gives you free webpages with a reasonable URL - but no folders. Sphinx depends on a folder structure - it creates folders "html", "doctrees", "_sources", "_static", and maybe more once I get serious.
• Creating a Google App Engine to host the docs - now this is an intriguing possibility. I'm going to check it out. Still, it would be nice if posting the docs for a technical project were not, itself, a technical project.

Any possibilities I'm missing? A convenient way to host Sphinx docs, freely available, would be a really nice service to FOSS developers. I wonder if the Google App Engine approach could be extended to provide that... hmm hmm hmm... just what I needed, one more project...

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

Senora 1.0.1, and mutual FOSS goading

Martin Drautzburg just published version 1.0.1 of Senora, a command-line client for Oracle that makes a very attractive alternative to Oracle's SQL*Plus.

The timing - seven weeks before I present a review of Senora, sqlpython, and others at IOUG Collaborate - is not a coincidence. I asked Martin to review my draft paper submission for the conference. He graciously did, and supplied several crucial corrections and additions regarding Senora.

He also mentioned that he'd been continuing Senora development for in-house use, but that it had been years since he'd released the updates. My upcoming review spurred him to do the release.

And what a release it is - it's full of awesome! Mighty new flag options! Multiple sessions! Automatic generation of Senora commands from SQL scripts!

It sent me to scheming about how I can copy this fresh goodness to sqlpython.

Plus, of course, the paper reminded me painfully of some of sqlpython's shortcomings. I'm starting to use the sqlpython trac seriously now, as a token to myself of my goodwill. This will probably lead to a spurt of Embarrassment-Driven Development before the conference.

FOSS people know what the ancient Romans knew: money is a feeble motivator compared to glory. (Half the time, people only want money so they can buy things that will make them feel glorious...)

using TurboGears 2 model outside TurboGears

Until/unless this ticket gets incorporated into the TurboGears 2 docs, I (for one) need a reminder about how I can make use of a sqlalchemy model, set up from a TurboGears 2 instance, for projects that don't actually start or use TurboGears. (I want a single, canonical sqlalchemy model for my database, for its web-based and non-web-based applications alike.)

Including this function in my model/__init__.py does the trick.

import paste.deploy, os.path
def externally_usable_session(configfile = 'development.ini'):
    tg_home_directory = '/path/to/tg2instancehomedirectory'
    conf_dict = paste.deploy.appconfig('config:%s' % os.path.join(tg_home_directory, configfile))
    engine = create_engine(conf_dict['sqlalchemy.url'])
    init_model(engine)
    return DBSession()

sqlpython 1.6.0 with Wild SQL

I just released sqlpython 1.6.0.

SELECTing a limited, but large, set of columns from a table is a real pain. What if you could use wildcards in the column list of the SELECT statement itself? Wouldn't that be wild?

OK, then, let's SET WILD ON.

jrrt@orcl> cat party

NAME    STR INT WIS DEX CON CHA
------- --- --- --- --- --- ---
Frodo     8  14  16  15  14  16
Gimli    17  12  10  11  17  11
Legolas  13  15  14  18  15  17
Sam      11   9  14  11  16  13

4 rows selected.

jrrt@orcl> set wild on
wildsql - was: False
now: True
jrrt@orcl> select *i* from party;

INT WIS
--- ---
 14  16
 12  10
 15  14
  9  14

4 rows selected.

You can also call columns out by number...

jrrt@orcl> select #1, #5 from party;

NAME    DEX
------- ---
Frodo    15
Gimli    11
Legolas  18
Sam      11

4 rows selected.

... or use ! as NOT.

jrrt@orcl> select !str from party;

NAME    INT WIS DEX CON CHA
------- --- --- --- --- ---
Frodo    14  16  15  14  16
Gimli    12  10  11  17  11
Legolas  15  14  18  15  17
Sam       9  14  11  16  13

4 rows selected.

... and you can mix it all together.

jrrt@orcl> select n*, !#3, !c* from party;

NAME    STR WIS DEX
------- --- --- ---
Frodo     8  16  15
Gimli    17  10  11
Legolas  13  14  18
Sam      11  14  11

4 rows selected.

A bunch of limitations:

• Wild SQL is not yet a widely-accepted industry standard. Actually, I just made it up. If ANSI hears about it, they will hunt me down with dogs. That's why you need to SET WILDSQL ON to turn it on.
• Wild SQL only works on the column list - the part between the SELECT and the FROM. It doesn't work in the WHERE clause, or in subqueries.
• Wild SQL only works in SELECT statements. What, you were thinking about using it in DML? Are you crazy?
• Do I really have to say that it's very alpha? Well, it is. Expect a trickle of bugfixes over the next few months.