python : huge looping in SQL Table

Question

在 mySql 中，我有下表(名为“staff”)，其中包含 800 条记录(可能更多):

     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 
  .....     |        ......

对于给定的日期时间，我的 python 函数在表 (col0) 中查找最近的一天并返回 col1 和 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)

对于 60 000 次循环(这与我的程序无关)它在 21 秒内运行。

我期待着改进这一点。

编辑 1

好吧，刚刚在“day”列上创建了一个索引，速度快了 2 倍，9.13 秒。希望改进它，因为它对我的应用程序来说还不够

编辑 2

为了更清楚地说明循环传递了 60,000 个不同的值，使用固定值是为了说明目的。

在我的生产调度算法中，我必须测试许多调度组合，从而找出给定日期时间的工作时间。如果日期不在我的表中(例如周末)，该函数将返回下一个开放日工作时间范围。希望一切都清楚!

Answer 1

你应该尝试准备好的语句:

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

或者甚至更好地尝试编写 sql，您将通过一次 sql 执行获得所有 60k 行。