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

Question

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" )

Solution 1

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

Solution 2

Use the datetime class with the strftime function.

The datetime class is used to build an object which represents a specific date and time. The strftime function converts it to a specific string, according to the formatting you choose.

According to MySQL's documentation, the standard datetime formatting is YYYY-MM-DD HH:MM:SS.

Here's an example that should work:

day1 = datetime.datetime(2012, 1, 1).strftime('%Y-%m-%d %H:%M:%S')
day2 = datetime.datetime(2012, 1, 2).strftime('%Y-%m-%d %H:%M:%S')
con.execute("select * from table where date >= %s and date < %s", (day1, day2))

If you want to make additional queries, just create appropriate datetime.datetime objects on each round of your loop. For instance:

for i in xrange(1, 10):
    # ...
    day2 = datetime.datetime(2012, 1, i).strftime('%Y-%m-%d %H:%M:%S')
    # ...

Solution 3

Use datetime.date objects. They are wonderful things because with them you can:

• easily compute today (dt.date.today()),
• easily compute the next day (start + dt.timedelta(days = 1),
• compare dates (e.g. start < end)
• feed them directly into con.execute. There is no need to pre-format them as strings.

---

import datetime as dt
start = dt.date(2012,1,1)
end = dt.date.today()

while start < end:
    nextday = start + dt.timedelta(days = 1)
    con.execute("select * from table where date >= %s and date < %s",
                (start, nextday))

    start = nextday

Comments

[GodMan]

Remove the comma , and add a percent sign % in the con.execute line, as the existing code does not frame the sql properly

[unutbu]

I'm using the 2-argument form of con.execute. See PEP 249. This is the correct way of supplying arguments to a parametrized SQL statement. It is the preferred way of using con.execute, since it will protect you from sql injection attacks.

[JPC]

Actually , I am getting the syntax error " You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s and date < %s %(start, next)' the date is datetime format .. hmmm

[JPC]

found out that I have to change "%s" to "?" and this query will work. Based on stackoverflow.com/questions/9603616/…

[0x48piraj]

start = datetime.date(2012,01,01) gives SyntaxError: invalid token error. It should be start = datetime.date(2012,1,1)