Bookmark and Share

Lurking within the normal Python 2.x distribution is the sqlite3 import, which is an amazingly powerful, no-server-required, mostly SQL compatible database engine that can be used in any project without restriction.

That’s the up side. The down side is, as a fairly complete database, there are many options and varied ways it can be used, and managing actual database transactions isn’t all that simple — to do it right, even a single query takes about sixteen lines of code. And yes, if you want maximum flexibility and the ability to use every feature in sqlite3, that’s how you should do it.

But. Most database operations are very straightforward. You want to issue a single command to the database, or a query. Perhaps you want to write a bunch of data and then commit it all at once so that the database doesn’t contain part of the data from a more complex transaction. You need to know if something went wrong, and if it did, what it was. Those are by far the most common use cases for me, and I suspect that’s true for others as well.

Frankly, it’s difficult enough dealing with the SQL query language itself. Why make actually using it harder than it has to be?

editdb.py

If you’re using SqLite with Python, likely there will be times when you want to just get in there and mess with a database. Rather than write a specific Python program to do that, perhaps editdb.py will do what you need.

Another thing that can be inconvenient is the customary approach most database engines, including sqlite, take towards retrieving query data. It’s done line by line, with the justification that this saves memory. Ok, sure it does. But in these days of machines with gigabytes of RAM available, does your database really need to be that conservative? The answer is, not unless it is handling a very, very large amount of data.

There’s a related issue as well. When a database fetches query results one at a time, you can’t check to see how many results you actually got, because it actually doesn’t know until it has fetched them all.

So aa_sqlite.py makes it easy to retrieve everything at once, complete with an accurate count of row(s) retrieved. If you want to. It’s just as easy to use row by row, too.

Lastly, wouldn’t it be nice if you had really solid error handling, and informative diagnostics that told you what was going on even if things are going fine? Working with sqlite3 and achieving these goals is quite a challenge. Well, it was, anyway. I present to you my aa_sqlite.py library, a toolkit I wrote to address precisely these issues.

Instead of many lines, here’s a complete query and printout of database columns:

import aa_sqlite a = dbl('myDatabase',"SELECT mycol FROM mytable ORDER BY mycol") for tup in a.tuples: print str(tup[0])

The above will not error out, and it cannot fail. It will either bring tuples back, or it won’t. If there is an error and you want to catch it, dbl() will set .err to the number of errors encountered, and all you need to do is:

import aa_sqlite a = dbl('myDatabase',"SELECT mycol FROM mytable ORDER BY mycol") if a.err == 0: for tup in a.tuples: print str(tup[0]) else: print a

That will dump a complete diagnostic of everything that went on if there were errors. But you don’t even have to check for errors if you don’t want to; in that case, .tuples is empty, and the subsequent for statement won’t do a thing. Note that returning zero rows from a valid query is not an error.

You can check the most important thing easily — did the query return anything? You’d do that this way (including the above error dump):

import aa_sqlite a = dbl('myDatabase',"SELECT mycol FROM mytable ORDER BY mycol") if a.err == 0: if a.rows > 0: for tup in a.tuples: print str(tup[0]) else: print 'no results for query' else: print a

Issuing commands is super easy:

import aa_sqlite a = dbl('mydatabase',"DELETE FROM mytable WHERE mycolumn = 'Jones'")

Deferred operations are very easy as well, just one line of setup:

import aa_sqlite a = dbl('mydatabase','',True,False) a.dbl('SQLcommandString1') a.dbl('SQLcommandString2') a.dbl('SQLcommandStringN') a.cmt()

If you wanted to abandon the above changes, you’d just replace the .cmt() call with .close() and that’s it, the DB won’t reflect any of the prior actions.

Of course, you can spice all this up with your own exceptions and so on to any degree you need to. But you can write some very bulletproof code without working very hard at all if you prefer to keep things simple.

Here’s a quick example of traditional, row-by-row retrieval:

import aa_sqlite b = dbl(mydatabase,"SELECT mycolumn FROM mytable",lean=True) for tup in b.tup(): print str(tup[0])

There are several utility functions in aa_sqlite.py. These functions help you to clean up input from the outside world so it can’t be used against you — and your database! See the library python source code for details.

So, enough of that. The latest version of the library is here, within which you’ll find documentation for both class dbl() and the various utilities, as well as some executable examples that run when you type the following at a shell prompt:

    python aa_sqlite.py

Release History:

Revision Changes
1.0.9 Adds case-sensitive LIKE capability
1.0.8 Initial public release
1.0.6 through 1.0.7 Internal versions