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
--
http://mail.python.org/mailman/listinfo/python-list
