MySQL Forums
Forum List  »  Connector/Python

Python is slow when iterating over a cursor
Posted by: Yoann LE NY
Date: March 15, 2017 04:35PM

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 :[1], I tried :

- `usr_list= cursorSQL.fetchall()`
- `usr_list= list(cursorSQL.fetchall())`
- `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, since all the slow part appears at the fetch all() line, I would suggest it has more to do with the object returned than with the MySQL connection?
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?


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

[Original Message with better formating] :

Options: ReplyQuote

Written By
Python is slow when iterating over a cursor
March 15, 2017 04:35PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.