Python : huge looping in SQL Table

Question

in mySql I have the following table (named "staff" )of 800 records (might be much higher):


     day          start_time              end_time     
   -----------------------------------------------------------------     
 2017-01-05 |   2017-01-05 08:00:00    2017-01-05 17:00:00  
 2017-01-06 |   2017-01-06 08:00:00    2017-01-06 17:00:00 
 2017-01-09 |   2017-01-09 08:00:00    2017-01-09 17:00:00 
  .....     |        ......

With a given datetime, my python function looks for the closest day in the table (col0) and returns col1 and col2:

from datetime import datetime
import MySQLdb
import MySQLdb.cursors as cursors
import time

conn = MySQLdb.connect("localhost","root","","FOO_DATABASE", cursorclass = cursors.SSCursor )

def foo(x):
    c.execute("SELECT start_time, end_time FROM staff WHERE Date >= %s  ORDER BY Date  LIMIT 1", (x,))
    results = c.fetchone()
    col1 = results[0]
    col2 = results[1]
    return col1, col2


#Date_time to look
date_time = datetime.strptime('2017-01-01 12:22:00', "%Y-%m-%d %H:%M:%S")

#The loop
start = time.time()
for i in range(60000):
    c = conn.cursor()
    foo(date_time)
    c.close()

end = time.time()
print round((end - start), 2)

For 60 000 loops (which is not so much regarding my program) it runs in 21 seconds.

I look forward to improve this.

EDIT 1

Well, just created an index on the "day" column and it goes 2 times faster, 9.13 seconds. Hope to improve it because it's not sufficient for my application

EDIT 2

To make in clearer the loop passes 60,000 different values, the use of the fixed value was for illustration purpose.

In my production scheduling algorithm I have to test many combinations of schedules and thus find out the working times for a given datetime. If the date is not in my table (a weekend for example) the function will return the next opening day working time range. Hope it's clear!

If it's purely on performance, opening and closing the connection are going to make a difference. Try commenting out the foo(date_time) bit and see how long that takes. — Nigel Ren, Jul 06 '17 at 09:38
Commenting out foo(date_time) make the loop compute in 0.45 seconds — Sebastien D, Jul 06 '17 at 10:03
Am I missing something as your loop passes the same datetime 60,000 times? What's the point of repeated values? What do you mean by *closest day*? Relative to what? — Parfait, Jul 06 '17 at 12:27

Ivan Bryzzhin · Accepted Answer · 2017-07-06T12:21:22.600

0

You should try prepared statement:

    from datetime import datetime
    import MySQLdb
    import MySQLdb.cursors as cursors
    import time

    conn = MySQLdb.connect("localhost","root","","FOO_DATABASE", cursorclass = cursors.SSCursor )
    #Date_time to look
    date_time = datetime.strptime('2017-01-01 12:22:00', "%Y-%m-%d %H:%M:%S")

    #The loop
    start = time.time()

    c = conn.cursor()
    for i in range(60000):
        c.execute("SELECT start_time, end_time FROM staff WHERE Date >= :p_date ORDER BY Date  LIMIT 1", {"p_date", date_time})
        col1, col2 = c.fetchone()
    c.close()

    end = time.time()
    print(round((end - start), 2))

Or even better try to write sql that you will get all 60k rows by one sql execution.

edited Jul 06 '17 at 12:21

answered Jul 06 '17 at 11:44

Ivan Bryzzhin

2,009
21
27

Thanks a lot but I have the following error : "AttributeError: 'Cursor' object has no attribute 'prepare'. Removing "cursorclass = cursors.SSCursor " doesn't help – Sebastien D Jul 06 '17 at 12:16
Yes, looks like mysql does not have prepared statements. But in this answer says that parametrized execution will be execute same way on driver leve. https://stackoverflow.com/questions/1947750/does-python-support-mysql-prepared-statements Did fix answer. – Ivan Bryzzhin Jul 06 '17 at 12:19
Just to give an overall context, I'm building a scheduling planning. I don't know in advance which values will go in the function. It depends on how the planning shows up in my algorithm – Sebastien D Jul 06 '17 at 12:24
:) our answers crossed ! Thanks a lot I will have a look – Sebastien D Jul 06 '17 at 12:24

Python : huge looping in SQL Table

1 Answers1