Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > Re: SQLite date fields

Reply
Thread Tools

Re: SQLite date fields

 
 
Dennis Lee Bieber
Guest
Posts: n/a
 
      11-25-2010
On Thu, 25 Nov 2010 00:45:41 +0000, Alan Harris-Reid
<(E-Mail Removed)> declaimed the following in
gmane.comp.python.general:

> 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.
>


Why?

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...

http://docs.pysqlite.googlecode.com/...lite-databases
"""
The sqlite3 module has two default adapters for Python’s built-in
datetime.date 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
SQLite.

The default converters are registered under the name “date” for
datetime.date 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
[timestamp]"')
"""

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
http://www.velocityreviews.com/forums/(E-Mail Removed) HTTP://wlfraed.home.netcom.com/

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQLite date fields Alan Harris-Reid Python 3 11-27-2010 01:39 AM
Ruby sqlite/gem error: Could not load sqlite adapter jhs408@gmail.com Ruby 4 04-18-2009 12:53 AM
Installing sqlite-ruby when sqlite is in non-standard location Carl Youngblood Ruby 1 04-09-2005 03:32 AM
Date, date date date.... Peter Grison Java 10 05-30-2004 01:20 PM
Given a date, how to find the beginning date and ending date of that week Matt ASP .Net 1 11-08-2003 09:14 PM



Advertisments