I wrote in a previous article about SQLite is great for small-to-medium web projects and also prototyping. Its not very hard to port a SQLite implementation to a more robust and scalable RDBMS such as PostgreSQL. Anyway, if you have used SQLite in any capacity, you have no doubt noticed that it does not have very strict type enforcement. You can put pretty much whatever you want into a column. This isn't a big deal when you are working with a dynamically-typed language such as Python. Its pretty trivial to convert an integer to a string or vice-versa. One exception to this is with datetime.date and datetime.datetime objects. Date objects map nicely to the SQLite 'DATE' type and datetime objects map nicely to the SQLite 'TIMESTAMP' type. Its very common that you will want, in your Python code, to deal with real datetime or date objects, for the purposes of arithmetic or formatting. It can be a real pain in the ass to manually convert your datetime/date objects to and from SQLite-compatible string representations - both for results going out of the database and for values going into the database. Luckily, you don't have to! Python's sqlite3 module has native converters for both these types. You simply need to ensure that your SQLite schema has the correct types specified for the columns, and sqlite3 can do it for you. For example, here is a basic table definition:
CREATE TABLE rateable_scale(
rateable_scale_id INTEGER PRIMARY KEY,
rateable_scale_creator TEXT,
rateable_scale_created_date TIMESTAMP,
rateable_id INTEGER,
scale_id INTEGER
);
In this example, I expect the column 'rateable_scale_created_date' to map to a datetime.datetime object in Python - which maps to a 'TIMESTAMP' type for SQLite. Once you have your column types specified correctly, you simply set up your SQLite connection in Python with a couple of extra options:
def connect_file(self, filename):
''' Connect to the provided DB file '''
self.conn = sqlite3.connect(filename, detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
# this row factory makes the results objects accessible both by index
# and by column name
self.conn.row_factory = sqlite3.Row
self.connected = True
The important part of the above snippet is the connect line:
self.conn = sqlite3.connect(filename, detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
Notice the detect_types parameter. This is what instructs the sqlite3 module to magically convert date and datetime objects for you!
Niall O'Higgins is an author and software developer. He wrote the O'Reilly book MongoDB and Python. He also develops Strider Open Source Continuous Deployment and offers full-stack consulting services at FrozenRidge.co.
