Sqlite3 dbapi2.py Date Conversion Issue

Question

I am seeing the strangest behavior in a script of mine. I'm getting a ValueError from dbapi2.py where it seems to be trying to do a conversion on a datetime.date object as if it were a datetime.datetime object:

  File "C:\Python27\Lib\sqlite3\dbapi2.py", line 66, in convert_timestamp
    datepart, timepart = val.split(" ")
  ValueError: need more than 1 value to unpack

I am not doing anything special, in fact I have several other scripts doing the exact same thing with slightly different tSql and no problems at all. Here's the Class to build the Sql:

class TransactionQuery:

    def __init__(self, account_iter, date):
        self.account_iter = tuple(account_list)
        self.date = date
        placeholder = ("?" for item in self.account_list)
        placeholders = ', '.join(item for item in placeholder)
        self.query = """select * from transactions where account_cd in (%s) and 
                        effected_dt = ?""" % placeholders

    @property
    def params(self):
        return (self.account_list) + (self.date,)

The code that calls it:

with Sqlite() as curs:
    print mquery.query
    print mquery.params
    curs.execute(mquery.query, mquery.params)
    return curs.fetchall()

And the outputs from the print statements:

select * from transactions where account_cd in (?, ?, ?, ?) and effected_dt = ?
('713271', '71204', '713311', '713471', datetime.date(2012, 12, 17))

Any ideas why Sqlite would have trouble with a datetime.date object in this case?

Answer 1

You're missing the timepart:

(datepart, timepart) = str( datetime.date(2012, 12, 17) ).split( ' ' )
## ValueError: need more than 1 value to unpack

sqlite3 is hitting the convert_timestamp converter.

Default Adapters and Converters documentation page for sqlite3 shows some examples. From what is known I can try and assume that effected_dt is a timestamp which expects a complete datetime.datetime string instead.

So what you'd do is on of the following:

• use datetime.datetime instead
• register a new converter for the timestamp type that accepts dates (returning "2012-12-17 00:00:00" for example)
• change your column type

Hope this makes sense and helps.