Simpler SQL Queries

[ldo]

First of all, have you looked at APSW as an alternative to the standard Python sqlite3 module? I think it is nicer in some ways.

Secondly, when doing queries, there is this common sequence of

• create a cursor
• execute the query
• retrieve the results
• close the cursor

I like to wrap all these steps up into a single generator function, like

def db_iter(conn, cmd, values = None, mapfn = lambda x : x) :
    "executes cmd on a new cursor from connection conn and yields" \
    " the results in turn."
    for item in conn.cursor().execute(cmd, values) :
        yield mapfn(item)
    #end for
#end db_iter

Then you can loop over the query results as simply as

for entry in db_iter(db, query) :
... do something with entry ...
#end for

The mapfn option becomes useful for things like doing a dict-zip against the field names to turn the result tuple into a dict mapping field names to field values.

For more details about this sort of thing, check out the “Databases, Iterators & Looping” notebook in my python_topics_notebooks collection.