Python Hangs On Fetchall Using MySQL Connector

I am fairly new to Python and MySQL. I am writing code that queries 60 different tables each containing records for each second in a five minute period. The code executes every fi

Solution 1:

You can alleviate this by setting the fetch size:

mncdb = mysql.connector.connect(option_files=ENV_MCG_MYSQL_OPTION_FILE, option_groups=ENV_MCG_MYSQL_OPTION_GROUP,host=ut_get_workstation_hostname(,database=ENV_MNC_DATABASE_NAME, cursorclass = MySQLdb.cursors.SSCursor)

But before you do this, you should also use the mysql excuse for prepared statements instead of string concatenation when building your statement.

Solution 2:

Hanging could involve the MySQL tables themselves and not specifically the Python code. Do they contain many records? Are they very wide tables? Are they indexed on the datetime_field?

Consider various strategies:

1. Specifically select the needed columns instead of the asterisk, calling all columns.

2. Index on the DBR_DATETIME_FIELD being used in the where clause (i.e., implicit join).

3. Diagnose further with printed timers print(datetime.datetime.now()) to see which are the bottleneck tables. In doing so, be sure to import the datetime module.