Python Storing and Retrieving Date into Sqlite3 database

Question

I am aware of this similar question on SO which is basically asking about the same thing. However, I seems to be getting an error message. Let me explain.

In order to test the idea of Storing and Retrieving Python Date object with Sqlite3 database, I have created this small test script:

import sqlite3
import datetime

conn = sqlite3.connect("test.db")
conn.execute('''CREATE TABLE TEST
            (
             ID     TEXT PRIMARY KEY     NOT NULL,
             DATE    TIMESTAMP
             );''')

conn.commit()

mydate = datetime.date(2014,4,28)
myid = 'test'

conn.execute("INSERT INTO TEST (ID,DATE)\
            VALUES(?,?)",[myid,mydate])
conn.commit()
conn.close()

conn = sqlite3.connect("test.db")
cursor = conn.execute("SELECT ID,DATE from TEST")
for row in cursor:
    retrievedDate = row[1]

print retrievedDate,type(retrievedDate)
conn.close()

The code works but the retrieved date is in unicode.

I found out from the above link that without the detect_types=sqlite3.PARSE_DECLTYPES parameters sqlite will return unicode. So, I have changed the code like this:

...
conn = sqlite3.connect("test.db",detect_types=sqlite3.PARSE_DECLTYPES)
cursor = conn.execute("SELECT ID,DATE from TEST")
for row in cursor:
    retrievedDate = row[1]

print retrievedDate,type(retrievedDate)
...

But now it's giving me this error message:

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

What is causing the problem?

score 3 · Accepted Answer · answered Apr 30 '14 at 15:57

You declared the column to be a timestamp:

conn.execute('''CREATE TABLE TEST
            (
             ID     TEXT PRIMARY KEY     NOT NULL,
             DATE    TIMESTAMP
             );''')

Make it type DATE instead; Python picks a converter based on the column type and TIMESTAMP triggers the wrong converter here.

Demo:

>>> import sqlite3
>>> import datetime
>>> conn = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
>>> conn.execute('''CREATE TABLE TEST (ID TEXT PRIMARY KEY NOT NULL, DATE DATE)''')
<sqlite3.Cursor object at 0x104a5adc0>
>>> conn.commit()
>>> conn.execute("INSERT INTO TEST (ID,DATE) VALUES (?, ?)",
...              ('foo', datetime.date(2014,4,28)))
<sqlite3.Cursor object at 0x104a5ae30>
>>> conn.commit()
>>> cursor = conn.execute("SELECT ID,DATE from TEST")
>>> for row in cursor:
...     print row
... 
(u'foo', datetime.date(2014, 4, 28))

Alternatively, include the type name in the SELECT query in square brackets:

cur.execute('SELECT ID, DATE [date] from TEST')

It works! The link to another SO question on my post used `timestamp` instead of `DATE`. But `DATE` seems to work fine for me. Thanks — Chris Aung, Apr 30 '14 at 16:01
@ChrisAung: `timestamp` is for `datetime.datetime` objects instead. — Martijn Pieters, Apr 30 '14 at 16:02

Python Storing and Retrieving Date into Sqlite3 database

1 Answers1