When using SQLite3 and Python (2.x), there are two important lessons that are not obvious (at least not to me).
1. Dictionaries and TimeStamps
Ideally, I would like to do two things. First, access data from a dictionary and not a list. It is far more intuitive to access by column name (or query name substitution) than by list index. Second, the datetime values should be correctly coerced even though SQLite has no implicit timestamp type. I believe these two simple requirements are expected by most people, especially those family with Microsoft SQL and ADO.NET.
Good news, Python supports this! However, there are some switches to set so to speak.
After importing sqlite3, the following connect statement will suffice for both needs:
conn = sqlite3.connect(dbname, detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
The PARSE_COLNAMES returns a dictionary for each row fetched instead of a list.
The PARSE_DECLTYPES empowers python to do a bit more type conversion based on the queries provided. For example, if you do this:
cur.execute('select mytime from sometable')
You will get mytime as a string. However, if you do this:
cur.execute('select mytime as "[timestamp]" from sometable')
You will get mytime as a datetime type in python. This is very useful.
One step further; let’s say you want a timestamp but substitute the column name in the query. Do this:
cur.execute('select mytime as "mytime [timestamp]" from sometable')
Not only with the data be returned as a datetime object, the dictionary will contain column name substitution provided in the query. Beware, if you don’t do this on aggregate functions, the python sqlite library will attempt to add to the dictionary with an empty string key. (Not sure why this is but beware.)
The adapters and converters live in the file dpapi2.py in your python library installation directory for sqlite3.
Refer to the documentation here.
2. Execute and Tuples
This is really a lesson on declaring implicit tuples.
The execute method on a cursor is great because it will safely convert strings for use in the database (reducing the possibility of SQL injection). It takes a query string and a substitution tuple.
However, I got caught again on the example below as will many who follow me.
This works:
cur.execute('insert into mytable values(?, ?, ?, ?)', (1, 2, 3, 4))
This doesn’t work and throws an exception:
cur.execute('insert into mytable values(?)', (1))
This works:
cur.execute('insert into mytable values(?)', (1,))
The comma after the one declares a tuple of one element. Argh!!!
I hope this helps.