How to query data for one week per time in mySQL
I am using python 3.4 to query the data in mysql. I would like to query one year's worth of data from a large database so I plan to split the query to query for one week per time.
This is the query I am using to query the data.
SELECT id, event_title, event_deadline FROM job WHERE country_id = 15 AND (event_deadline >= '2015-01-01' AND event_deadline <= '2015-01-07')
Is there an easier way to grab all the data for all 52 weeks in a year without manually listing out the dates for each week like below?
week 1: 2015-01-01 - 2015-01-07 week 2: 2015-01-08 - 2015-01-14 week 3: 2015-01-15 - 2015-01-21
You could use the MySQL WEEK() function, something like this: (Forgive me if this looks like php; I haven't done much python. Nonetheless, the principle is the same.)
query = ("SELECT id, event_title, event_deadline FROM job " "WHERE country_id = 15 " "AND WEEK(event_deadline) = %s") for weeknum in [1..53] cursor.execute(query, weeknum) do_stuff_with(cursor)
Check the MySQL docs for details on how Mysql's WEEK() function defines a calendar week.
Hope that makes enough sense to be useful.