No more Python (at least for programs that need SQL)

On Thusday 18 April I decided to stop writing new programs in Python if they need to interact with a SQL database.

It’s not that Python is a bad language. It’s very easy to read and quite maintainable. I like its exception handling and its basic string handling (even if it is a bit funky.) The standard library is thorough and has a lot of useful functionality. Even the one thing critics usually dislike about the language, namely its forced indentation style, I appreciate and don’t find it to be an undue burden when editing.

But …

First there’s the incompatibility between Python 2 and Python 3. Python 3 has been around for a decade now, but distributions are still shipping Python 2 as the default. The caused me all sorts of issues when I tried writing Python 3 code but started my source file with:

1

#!/usr/bin/python

That selected the Python 2 interpreter, which royally screwed up string handling. Python 2 uses ASCII strings, while Python 3 has two types: UTF-8 “strings” and ASCII “bytes”. But a lot of functions that use strings don’t work with bytes, requiring constant conversion between the two.

In an attempt to get around the Python2/Python3 problem, I symlinked /usr/bin/python to Python 3. But that broke gpodder, which was written in Python 2.

Then there’s the primitive SQL interface. Its primary problem is it doesn’t do prepared statements, so if you’re updating a lot of rows the interpreter has to re-parse the INSERT or UPDATE statement every time.

But the final straw came when I was working on a program to create a mock-up of a journal “tape” (the paper tape in a cash register.) To ease programming, I used the base SQL interface, which can return query results in a dictionary. So I can use, for example, row['customer_name] instead of row[2]. That’s important if you change the fields returned by the query.

Except that interface handles only one query at a time. I can’t start out with a query on a header table and then do a secondary query to the details table–not unless I want to process the whole header table at once, or process it in discrete chunks. (Have Python programmers never encountered tables with hundreds of thousands of rows?)

To get around this problem the Python SQL interface has cursors, but they can’t return dictionaries, only tuples. So we’re back to row[2] instead of row['customer_name'].

There’s no standard module in Python to convert a tuple returned from a SQL cursor query to a dictionary; it is left as an exercise to the user.

Well, screw all of that. I’m going back to Perl–it’s well defined, stable, and mature. Python is too much of an adolescent for my liking.