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

Mp0int · Accepted Answer · 2015-10-26 06:53:50Z

5

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

edited Oct 26, 2015 at 6:53

Mp0int

18.8k16 gold badges88 silver badges119 bronze badges

answered Sep 21, 2012 at 16:08

GodMan

2,6312 gold badges26 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JPC Over a year ago

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 Over a year ago

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

0x48piraj Over a year ago

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

Lior · Accepted Answer · 2012-09-21 16:01:30Z

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')
    # ...

unutbu · Accepted Answer · 2012-09-21 16:07:57Z

1

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

edited Sep 21, 2012 at 16:07

answered Sep 21, 2012 at 16:02

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

2 Comments

GodMan Over a year ago

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

unutbu Over a year ago

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.

Collin Roland · Accepted Answer · 2025-10-02 16:22:28Z

0

You may need to wrap the dates with # symbols, see this discussion. For example, in the rendered query, #11/01/2016#

answered Oct 2 at 16:22

Collin Roland

11 bronze badge

2 Comments

jsotola Oct 2 at 16:49

please edit your answer with an explanation of why the # delimiter is required

Community Oct 2 at 16:49

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

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

4 Answers 4

3 Comments

Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related