By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,786 Members | 1,131 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,786 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Jul 4 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a

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?)

Jul 4 '06 #2

P: n/a
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
Jul 4 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.