Reproduce pysqlite's row_factory on apsw

Question

I have been trying to migrate away from pysqlite to apsw but I can't find a way to reproduce its row_factory function.

this is my original code:

connection = sqlite3.connect("db.db3")
connection.row_factory = sqlite3.Row
cursor = connection.cursor()

and I use it like this:

query = """ SELECT wbcode, Year, """+query_name+""" 
            FROM innovotable WHERE commodity='"""+commodity_name+"""'  and 
            """+query_name+""" != 'NULL' """
rows = cursor.execute(query)
for row in rows:
    s[str(row[0])+str(row[1])] = float(row[2])

Is it possible to use apsw for this same purpose?

Answer 1

Disclosure: I am the author of APSW

Yes, very easily. APSW has something called a row tracer documented at http://apidoc.apsw.googlecode.com/hg/execution.html#tracing

The row tracer is called with each row. You can skip the row completely by returning None, or make whatever data type you want based on what was passed in (this is great for testing). If the row tracer is installed on a Connection then it affects all cursors. If on a cursor then only that cursor is affected. Cursor.getdescription will let you get the column names and declared types.

Your code above doesn't actually use the row_factory as you index the row by number which works as is in pysqlite and APSW. sqlite3.Row does let you index by name so the last line of code would be:

s[str(row.wbcode)+str(row.Year)]=float(row[query_name])

BTW there is also a Python SQLite group run by the pysqlite author and myself linked to from the pysqlite and APSW sites.