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

 

[Avizoa]

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.

These are not, in fact, bugs. One of SQLite's features is that it does
not enforce type, meaning that with the exception of INTEGER PRIMARY
KEY you can stick whatever you want into whatever field you want. The
philosophy of SQLite is that type checking is a "mis-feature" that goes
against some of the principles of the relational model.

Taking this into consideration, how would pysqlite handle opening a
database that mixes strings and integers in datetime fields? The short
answer is that it can't, so instead it is up to the developer to ensure
proper handling of type (sounds like python duck-typing, doesn't it?)

 

[Georg Brandl]

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.

Please forward to Gerhard Haering <gh at ghaering.de> if you still
think these are bugs.

Georg