[Tutor] date matching with python and sqlite3

[Che M]

I'm trying to allow users to select data from an sqlite database using
Python by choosing either a date or a range of dates. I'm stuck at just
allowing the to select data that entered the database "today" and return
values from a column called duration. I have this mess at the moment:

#assume they have already chosen this self.datechoice to be today

if self.datechoice == "today":
todaystring = str(datetime.datetime.today())
today = todaystring[0:10]
cur.execute('SELECT duration FROM datatable WHERE date =' + '"' + today
+ '"')

The 3rd line is a way to take just the first part 10 chars of the
datetime.today string, so
instead of "2007-09-01 12:00:03" it would be just "2007-09-01", since I just
want
to match it to today, not a particular time during today. But this only
works if the
dates have been saved that way--typically they are saved with the time as
well, so
this method is not good and obviously not the right way to do it.

I can tell I am going about this totally wrongly and that I should be able
to use either
the Python datetime functions or SQLite's date functions much better, so any
insight
would be appreciated. Thank you.

_________________________________________________________________
A place for moms to take a break!
http://www.reallivemoms.com?ocid=TXT_TAGHM&loc=us

[Righard/Riku van Roy]

I sorry, maybe I am stupid at the moment but I cannot follow your
question,....

1) User can select a date, or a range of dates.

2) At the moment you are only able to let the user select a date that
has entered the database today.

3) A value from a table called duratation will be returned for the
selected date?

4) It will only work if the date is saved in this? "2007-09-01", way.,
What other way can the date be saved?

5) You want to now how you can improve your program?

Can you give some more info please, again sorry if others understeand it
perfectly,
Righard

Post by Che M
I'm trying to allow users to select data from an sqlite database using
Python by choosing either a date or a range of dates. I'm stuck at just
allowing the to select data that entered the database "today" and return
#assume they have already chosen this self.datechoice to be today
todaystring = str(datetime.datetime.today())
today = todaystring[0:10]
cur.execute('SELECT duration FROM datatable WHERE date =' + '"' + today
+ '"')
The 3rd line is a way to take just the first part 10 chars of the
datetime.today string, so
instead of 2007-09-01it would be just "2007-09-01", since I just
want
to match it to today, not a particular time during today. But this only
works if the
dates have been saved that way--typically they are saved with the time as
well, so
this method is not good and obviously not the right way to do it.
I can tell I am going about this totally wrongly and that I should be able
to use either
the Python datetime functions or SQLite's date functions much better, so any
insight
would be appreciated. Thank you.
_________________________________________________________________
A place for moms to take a break!
http://www.reallivemoms.com?ocid=TXT_TAGHM&loc=us
_______________________________________________
http://mail.python.org/mailman/listinfo/tutor

[Che M]

Re: date matching with python and sqlite3

Post by Righard/Riku van Roy
I sorry, maybe I am stupid at the moment but I cannot follow your
question,....

I'm sorry, I didn't describe that too clearly...have coffee now, let's
try that again.

The user will have a choicebox that has options of "today", "this week",
"this month",
etc., as well as a way to just provide a range of dates, e.g. "Jan 1
2007--Apr 15 2007".
The point is I want them to be able to get data from the database for
whatever time
period they want. Let's say the data is type of fruit they ate on various
dates. So
they would put in "this month" and it would return all the fruit that was
eaten that
month. And every time they indicate they ate a fruit, they enter it into
the database
and Python will save it as a datetime object, which has the format
2007-09-01 12:00:00.
My trouble is in how to write the SQL statements to match the date(s) they
need
while ignoring the time part of the datetime.

For example, the code I have for matching to "today" doesn't work because it
will
match a date saved as "2007-09-01" but not "2007-09-01 12:03:03", and it is
this
2nd format that the datetime object takes. I also prefer that format,
actually, in
case I later want to sort by time of day.

Again, this is the non-working and inelegant code:

if self.datechoice == "today":
todaystring = str(datetime.datetime.today())
today = todaystring[0:10]
cur.execute('SELECT duration FROM datatable WHERE date =' + '"' + today
+ '"')

I'm sure there is an easy way to do this since both Python and SQLite have
date functions, but I have just had trouble understanding the SQlite
documentation.
Sorry if this is more an SQLite concern than a Python concern, but it is
sort of on
the border. If this is still unclear I will try again. Thanks!

_________________________________________________________________
Get a FREE small business Web site and more from Microsoft® Office Live!
http://clk.atdmt.com/MRT/go/aub0930003811mrt/direct/01/