Tim Roberts <t...@probo.com> wrote: >>However, when it comes to writing-back data to the table, SQLite is >>very forgiving and is quite happy to store '25/06/2003' in a date >>field, > > SQLite is essentially typeless. ALL fields are stored as strings, > with no interpretation. You can store whatever you want in any > column. The column types are basically there to remind YOU how to > handle the data. >
Not all fields are stored as strings; they may also be stored as integer, floating point values or binary data. If you create a table with a column of type DATE or DATETIME then the column affinity is NUMERIC. See http://www.sqlite.org/datatype3.html It happens that Python stores datetime values in sqlite databases as text strings by default, but that means if you actually declare the column as DATE or DATETIME you are overriding the default type for each column. Not that it is normally noticeable but: >>> import sqlite3 >>> conn = sqlite3.connect('test.db') >>> c = conn.cursor() >>> c.execute('''CREATE TABLE t1 (d DATE, t TEXT);''') <sqlite3.Cursor object at 0x02A18350> >>> from datetime import datetime >>> d = datetime.now() >>> c.execute('INSERT INTO t1 (d,t) VALUES (julianday(?),julianday(?))', (d, d)) <sqlite3.Cursor object at 0x02A18350> >>> for row in c.execute('SELECT * FROM t1'): print(row) (2455527.0482040276, '2455527.04820403') >>> for row in c.execute("SELECT datetime(d), datetime(t) FROM t1"): print(row) ('2010-11-26 13:09:24', '2010-11-26 13:09:24') -- Duncan Booth http://kupuguy.blogspot.com -- http://mail.python.org/mailman/listinfo/python-list
