sqlite weirdness

[Tim Roberts]

kyosohma at gmail.com wrote:
>
>Thanks Duncan and John! That makes sense. But why does the official
>Python docs show an example that seems to imply that there is a "date"
>type? See link below:

You can certainly create fields of type "date" in sqlite, but sqlite
doesn't understand any types.  **ALL** types in sqlite are stored and
compared as strings.  You could declare your "date" field as type "frog"
and it would work exactly the same.

  C:\tmp>sqlite x.db
  SQLite version 2.8.6
  Enter ".help" for instructions
  sqlite> create table xyz (
     ...>   xxx frog
     ...> );
  sqlite> insert into xyz values (123);
  sqlite> select * from xyz;
  123
  sqlite>

Note that the example in the web page you quoted takes a Python variable of
type datetime.date and converts it to a string, which produces
"2007-12-12".  Also note that the conversion on the OTHER end has to use
the special sqlite adapter syntax ('select current_date as "d [date]"...').

In my opinion, it can be argued that the inclusion of sqlite in the Python
standard library was a mistake.  It is a great product, and I've used it
many times in my own Python apps, but it has a number of unexpected
idiosyncracies.  When you download and install it yourself, you can
evaluate the idiosyncracies and decide whether they are acceptable, but
when its in the standard library, you don't expect to go through that.

>I'll have to refactor my code somewhat to force it to use the 'YYYY-MM-
>DD' format.

Another possible solution is to use a real database.
-- 
Tim Roberts, timr at probo.com
Providenza & Boekelheide, Inc.