WARNING: Horrible hack ahead.

I wanted pysqlite to use the standard datetime types but I also don't want to replace the python-sqlite package my Linux distribution lovingly cares for, so I created this traversty of a module to patch sqlite.

Patching messes around with the internals of pysqlite (hurray for source code!) so it may stop working at any moment. In fact, it may not even work with the version of pysqlite you have installed. You have been warned.

Save the following as sqlitepatch.py and import it before creating any connections.

"""
Patch pysqlite to use the standard datetime types available in
Python 2.3+.

To use this, simply "import sqlitepatch" **before** you make any
sqlite connections.

Note: The new connect() function assumes you only use keyword args
(as any sane person would do anyway).
"""

import datetime

import sqlite
import sqlite.main


# Tell sqlite it didn't really find mx installed ;-)
sqlite.main.have_datetime = False


# Replace sqlite's _quote function to include the standard datetime types.
def quote(oldfn):
    def _(value):
        if isinstance(value, (datetime.date, datetime.time,
        datetime.datetime)):
            return "'%s'"  % value
        return oldfn(value)
    return _
sqlite.main._quote = quote(sqlite.main._quote)


# datetime type converters and parsers

def dateTupleFromString(value):
    year, month, day = value.split('-')
    return int(year), int(month), int(day)

def timeTupleFromString(value):
    hours, minutes, seconds = value.split(':')
    seconds, micro = seconds.split('.')
    return int(hours), int(minutes), int(seconds), int(micro)

def dateConverter(value):
    return datetime.date(*dateTupleFromString(value))

def timeConverter(value):
    return datetime.time(*timeTupleFromString(value))

def timestampConverter(value):
    date, time = value.split()
    return datetime.datetime(*(dateTupleFromString(date)+timeTupl
    eFromString(time)))


# Replace sqlite's connect to add the datetime converters
def connect(oldfn):
    def _(*args, **kwargs):
        kwargs.setdefault('converters', {}).update(
            {'date': dateConverter,'time':
            timeConverter,'timestamp':timestampConverter})
        return oldfn(*args, **kwargs)
    return _
sqlite.connect = connect(sqlite.connect)

From discussions on the mailing list, it looks like there is a patch waiting to be tested and committed. As soon as I can, I will ditch this too ;-).