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('''cr eate table stocks
(date timestamp, trans varchar, symbol varchar,
qty decimal, price decimal)''')

# Insert a row of data
c.execute("""in sert 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.140000000000 001)
(u'2006-03-28', u'BUY', u'IBM', 1000, 45.0)

Point 3: Column 1 neither looks nor quacks like a datetime.dateti me
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=de tect_types)
c = conn.cursor()
c.execute(CREAT E % col1type)
c.execute(INSER T)
c.execute('sele ct * from stocks')
for row in c:
print row
conn.close()

if __name__ == "__main__":
for ty in ['timestamp', 'date']:
for detective in [
0,
sqlite3.PARSE_C OLNAMES,
sqlite3.PARSE_D ECLTYPES,
sqlite3.PARSE_C OLNAMES | sqlite3.PARSE_D ECLTYPES,
]:
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.140000000000 001)

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

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

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

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

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

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

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

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]

John Machin wrote:

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]

John Machin wrote:

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.deif you still
think these are bugs.

Georg