Python Storing and Retrieving Date into Sqlite3 database
Question: I am aware of this [similar question on
SO](http://stackoverflow.com/questions/1829872/read-datetime-back-from-sqlite-
as-a-datetime-in-python) 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?


Answer: 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')
    

