Python is slow when iterating over a MySQL connector cursor

Question

I have the following code that runs very slowly (6.5sec for iterating over 57,390 rows) :

import mysql.connector

cnx=mysql.connector.connect(host=***, ***)
cursorSQL = cnx.cursor()

for dt in date_vect:
        cursorSQL.execute(query2, ((dt[0] + '-00:00:00'), (dt[0] + '-23:59:59'))) 
          #Takes about 0.25sec per iteration
        usr_list = list(cursorSQL.fetchall())  
          #takes about 6.20sec per iteration

As recommended here : Stackoverflow : Python is slow when..., I tried :

• usr_list= cursorSQL.fetchall()
• usr_list= list(cursorSQL.fetchall())

And as @postoronnim suggested, I also tried :

• usr_list= cursorSQL.fetchmany(57390)

With no success.

However, there is some caching effect, since the same iteration takes only 0.5sec when ran a 2nd time over an iteration already ran on, then slows back to 6.5sec.

1. Any Idea where that might come from?
2. Could you confirm that it has nothing to do with my database, since all the import from MySQL is done at the cursor.execute line, and fetchall() slowness is just due to list processing?
3. Could you explain why there is a caching effect?

Thanks.

Specs : Python 3.5 | 8-core i7 | 16go Ram

Attitudinize

what is the operation performed in the loop? – Rebecarebecca

'' cursorSQL.execute() '' queries a mysql table and returns a 57,000 rows object. date_vect is a vector of days dates. Then ''fetchall()'' transforms this object into a list. Is that clear? – Attitudinize

sorry, I didn't read your question properly. I thought the loop to iterate the fetched data takes higher time. Now I get it. – Rebecarebecca

No worries. Any idea? – Attitudinize

execute method is what's slowing you down, you need to use executemany. It reduces the overhead by preparing the database for the operation. – Isacco

@Isacco :are you sure, because execute() method only takes .25sec, and that's the fetchall() methods that takes 6.20sec to run... I don't get it. – Attitudinize

Actually, you are right. I confused it with fetchmany, you can specify the size parameter with it to allocate resources. Try this syntax: rows = cursor.fetchmany(size=1). Just put in however many rows you expect. – Isacco

@postoronnim: Thanks a lot, but unfortunately, when I tried cursorSQL.fetchmany(size=57390), the line took 6.2 sec to run as before... Is my code correct? – Attitudinize

It's not your code, something else is going on. Did you try the suggestion from jkerian in the post you referenced? Apparently, you are not getting an object you are expecting - if that does not work that it could be a driver issue, in which case I would just post it on the Database Administrators forum with probably a better chance getting an answer. – Isacco