Re: SQLite date fields

Discussion in 'Python' started by Dennis Lee Bieber, Nov 25, 2010.

  1. On Thu, 25 Nov 2010 00:45:41 +0000, Alan Harris-Reid
    <> declaimed the following in

    > 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, but
    > this is not ideal because a) I could be left with a mixture of date
    > formats in the same column, b) SQLite's date functions only work with
    > ISO format.
    > Therefore I need to convert the date string back to ISO format before
    > committing, but then I would need a generic function which checks data
    > about to be written in all date fields and converts to ISO if
    > necessary. That sounds a bit tedious to me, but maybe it is inevitable.


    I believe the SQLite3 db-api adapter includes cognizance of datetime
    objects and will convert such to a string of the form SQLite wants...
    Reading back may require parsing back into a datetime object -- unless a
    suitable converter has been defined...
    The sqlite3 module has two default adapters for Python’s built-in and datetime.datetime types.
    So if the input date/time data is in the form of a datetime object,
    the input to SQLite3 should produce the correct SQLite format.

    There are default adapters for the date and datetime types in the
    datetime module. They will be sent as ISO dates/ISO timestamps to

    The default converters are registered under the name “date” for and under the name “timestamp” for datetime.datetime.

    This way, you can use date/timestamps from Python without any additional
    fiddling in most cases. The format of the adapters is also compatible
    with the experimental SQLite date/time functions.
    cur.execute('select current_date as "d [date]", current_timestamp as "ts

    And so by supplying the data type hint (in []), the data is
    converted on read back to datetime objects too... As long as you use
    datetime objects instead of strings in your Python code, it should be a
    minimal, nearly transparent, factor WRT SQLite.

    Yes, you'll still need to validate user input -- but that is
    independent of the database, and should be done for ANY user input, not
    just dates.

    Wulfraed Dennis Lee Bieber AF6VN
    Dennis Lee Bieber, Nov 25, 2010
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Matt
    Matthew Speed
    Nov 8, 2003
  2. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Michael Borgwardt
    May 30, 2004
  3. Alan Harris-Reid

    SQLite date fields

    Alan Harris-Reid, Nov 25, 2010, in forum: Python
    Alan Harris-Reid
    Nov 27, 2010
  4. Carl Youngblood
    Carl Youngblood
    Apr 9, 2005
  5. Replies: