summary of Oracle/Python discussion

My OTN article on Oracle and Python was kept very brief, to be non-intimidating and to fit within OTN's preferred length. If you've come here, though, you're ready for the rest of the story! I'll use this post to summarize that discussion.

• Python+Oracle on other Linux distributions - see Andy Todd's blog entry
• alternatives to fetchone(): fetchmany(), fetchall(), and looping directly on the cursor - see my last entry and this comment
• Passing an argument to split(), to avoid errors on more complex init.ora parameters - see this comment

OTN article addendum

If you've read my new article at the Oracle Technology Network, Wrapping Your Brain Around Oracle + Python, thank you! I'd like to add a few more details about fetching rows with cx_Oracle that can make your code even cleaner.

Several times, I demonstrate getting rows from a cursor by means of the cursor's .fetchone() method. .fetchone() is used in loops like this:

curs.execute(<some query>, <bind variables>)
aRowOfData = curs.fetchone()
while aRowOfData:
    <commands>
    aRowOfData = curs.fetchone()

Another, more concise, alternative was not mentioned in the article. The cursor object itself can be iterated over, like this:

curs.execute(<some query>, <bind variables>)
for aRowOfData in curs:
    <commands>

The effect is the same, but it works with two fewer lines of code.

Finally, the .fetchall() method, bringing the entire result set into a list at once, was only briefly mentioned in the article, but it would probably be preferable to .fetchone() for the small result sets we'll find in places like v$parameter. Only when a result set is very large (or your computer is very memory-limited) do you need to worry about .fetchall()'s impact on your system's available memory.

Alice: corrupting the youth

I wish I could remember which PyCon delegate told me about Alice. It's a graphical environment for programming animations in a very kid-friendly fashion, yet full of solid, object-oriented programming goodness. This generation's LOGO, I suppose.

I was hoping it would fire our nine-year-old's interest in computers. I think it's working; Star Wars: Battlefront never produced such delighted shrieks and giggles. Maybe it works too well. He refused dessert to spend the extra couple minutes with Alice. Choosing code over food - I always thought of that as late-stage geekery.

Cheer: Alice works hard to be girl-friendly. Boo: Not available for Linux.

new and improved sqlWrap.py

After reading the Python Cookbook and attending PyCon, I have enormously improved sqlWrap.py, my Python module for handling DB-API 2.0 connections conveniently.

Also at PyCon, I learned that I could be accused of having duplicated projects like

• ll-orasql
• db_row
• dtuple

Like them, I allow tuple-like, dict-like, and object-like access to fields. I like mine better, though; it requires less preparation, and has really nice reporting methods. For example,

conn = sqlWrap.SqliteConnection('myDb.sqlite')
print conn.select('myTable').xml()

... is all you need to get an XML report on myTable. Similar reporting methods exist for

• tables in pp (prettyprint), xhtml, ReStructured Text
• transposed tables in pp, xhtml, ReStructuredText
• SQL INSERT statements

Hey, if everybody gets to write their own web app platform, why shouldn't I write my own DB-API wrapper?

PyCon2006

No, Guido doesn't know I have this... I snagged it from the badge reuse box. You keep your smelly old rock star T-shirts, I'm keeping this.

So... PyCon. Wow. It was wonderful, of course. What else could it be? Put 400 people with that much intelligence, creativity, and energy in one place, and it can hardly help but be wonderful.

What surprised me is that I found so many good ideas no matter what I was doing at PyCon. Whether I was in a talk that I expected to benefit hugely from, or a "Probably useless but I guess I'll go anyway" talk, or just chatting between or after sessions, I seemed to learn good stuff constantly just the same. The serendipity was worth more than the "planned" learning, and maybe that's what makes conventions so much more useful than formal books and classes and so forth.