How to use date range in python to pull /query data in mySQL

database, date, mysql, python

Solution

You need to `datetime` module:-

import datetime
start = datetime.date(2012,01,01) 
next = start + datetime.date.resolution

while next <= datetime.date.today():
    print start, next

    con.execute("""
        select * from table where date >= %s and date < %s
    """, (start, next))

    start = next
    next = start + datetime.date.resolution

IMPORTANT NOTICE: I updated the answer to fix a serious problem. Never ever use string formatting (a.k.a. `%`) for building SQL queries since it is open to serious problems including SQL injection. Use `Python-<db_driver>` api where nearly all RDMBes offers the same syntax

execute("select * from blah where x=%s AND y=%s", (x, y))
                                     ^       ^  ^
                                     1       1  2

1] No quote, 2] No string formatting

Problem

How can I pull data in mySQL by day using python date? Say I want `day1` and `day2` ( or a day after `day1` ) iterate for `n` times So I need the date in "where" SQL statement to look like below list in each iteration (`n` times ) ``` day1 >= '2012-01-01' and day2 < '2012-01-02' ( n = 1 ) day1 >= '2012-01-02' and day2 < '2012-01-03' ( n = 2 ) . . day1 >= yesterday and day2 < today ( n times ) ``` . ``` Start_date = '2012-01-01' <- How can I write this in python End_date = Today() <- and this ``` So as to write: ``` for each iteration .. con.execute("select * from table where date >= day1 and date < day2" ) ```

Original source