sqlite3 in Python 2.5b1: my out-of-the-box experience

[John Machin]

Apologies in advance if this is a bit bloggy, but I'd like to get 
comments on whether I've lost the plot (or, more likely, failed to 
acquire it) before I start reporting bugs etc.

 From "What's new ...":
"""
# Create table
c.execute('''create table stocks
(date timestamp, trans varchar, symbol varchar,
  qty decimal, price decimal)''')

# Insert a row of data
c.execute("""insert into stocks
           values ('2006-01-05','BUY','RHAT',100,35.14)""")
"""

Point 1: Maybe that "timestamp" type for the first column should be 
"date". More on this later.

Point 2: Maybe naming a column "date" wouldn't survive a real code 
review :-)

Query results:
(u'2006-01-05', u'BUY', u'RHAT', 100, 35.140000000000001)
(u'2006-03-28', u'BUY', u'IBM', 1000, 45.0)

Point 3: Column 1 neither looks nor quacks like a datetime.datetime 
instance.

Point 4: Column 5 is quacking like a float, not a 'decimal'.

Point 5: There are no docs for sqlite3 in the Windows docs gadget that's 
included in the 2.5b1 msi file [or the install didn't blow away the 
2.5a2 docs gadget]. There are docs however at 
http://docs.python.org/dev/lib/module-sqlite3.html

Looking for a way to get dates back instead of strings ... found 
12.13.4.4 Default adapters and converters

Point 6: The example works (produces instances of datetime.* instead of 
Unicode strings) but doesn't explain why *both* varieties of type 
detection are specified in the connect() call.

Wrote a script to check out points 1 and 6:

8<--- start of script ---
import sqlite3, datetime

CREATE = """
     create table stocks (
         trans_date %s,
         trans varchar,
         symbol varchar,
         qty decimal,
         price decimal
         )
     """

INSERT = """
     insert into stocks
         values ('2006-01-05','BUY','RHAT',100,35.14)
     """

def test(col1type, detect_types):
     conn = sqlite3.connect(":memory:", detect_types=detect_types)
     c = conn.cursor()
     c.execute(CREATE % col1type)
     c.execute(INSERT)
     c.execute('select * from stocks')
     for row in c:
         print row
     conn.close()

if __name__ == "__main__":
     for ty in ['timestamp', 'date']:
         for detective in [
             0,
             sqlite3.PARSE_COLNAMES,
             sqlite3.PARSE_DECLTYPES,
             sqlite3.PARSE_COLNAMES | sqlite3.PARSE_DECLTYPES,
             ]:
             print "\ntest(%r, %d):" % (ty, detective)
             test(ty, detective)
8<--- end of script ---

Results of running script:

test('timestamp', 0):
(u'2006-01-05', u'BUY', u'RHAT', 100, 35.140000000000001)

test('timestamp', 2):
(None, u'BUY', u'RHAT', 100, 35.140000000000001)

test('timestamp', 1):
(None, u'BUY', u'RHAT', 100, 35.140000000000001)

test('timestamp', 3):
(None, u'BUY', u'RHAT', 100, 35.140000000000001)

test('date', 0):
(u'2006-01-05', u'BUY', u'RHAT', 100, 35.140000000000001)

test('date', 2):
(datetime.date(2006, 1, 5), u'BUY', u'RHAT', 100, 35.140000000000001)

test('date', 1):
(datetime.date(2006, 1, 5), u'BUY', u'RHAT', 100, 35.140000000000001)

test('date', 3):
(datetime.date(2006, 1, 5), u'BUY', u'RHAT', 100, 35.140000000000001)

Point 7: Type detection on a "timestamp" column causes None to be 
retrieved after a date-only (yyyy-mm-dd) string is inserted. An 
exception (or maybe defaulting the time part to 00:00:00) would be much 
less astonishing.

Point 8: The test definitely doesn't use "... as [... date]" anywhere, 
but PARSE_COLNAMES used in isolation appears to cause retrieval as a 
datetime.date.

Point 9: IMHO the default should be to do both forms of type detection.

Comments on any of the above would be appreciated.

Cheers,
John