Optimizing MySQL Data Loading into Python Pandas/Numpy Array

In this blog post, we will explore the process of loading numeric data from a MySQL database into a Python Pandas/Numpy array. We’ll dive into the details of the problem and provide solutions using different libraries and approaches.

Problem Description

Given a MySQL table with approximately 200k rows and 9 columns, we want to load the numeric data (double precision) into a Python Pandas/Numpy array as efficiently as possible. The goal is to match or surpass the loading speed achieved in MATLAB.

Step 1: Understanding MySQL Data Types and Conversion

MySQL stores numeric data in various types, including DECIMAL, INTEGER, and others. When loading this data into Python, we need to consider how these types are represented and converted. The problem statement mentions that MySQLdb, pymysql, and pyodbc libraries perform type conversion from MySQL’s decimal type to Python’s decimal.Decimal.

Step 2: Identifying the Bottleneck

The profiler indicates that all the time spent is in reading the returned MySQL data element by element (row by row, column by column) and converting it to the data type previously inferred by the library. This suggests that the bottleneck lies in the type conversion process.

Step 3: Optimizing Type Conversion

To speed up the loading process, we can modify the converters.py file in MySQLdb to change the type conversion for DECIMAL and NEWDECIMAL fields from decimal.Decimal to float. This modification allows us to load numeric data directly into a Python float array.

Step 4: Loading Data into Numpy Array

Using the modified converter, we can now load the data into a Numpy array. The example code provided in the question demonstrates how to achieve this using MySQLdb:

import MySQLdb
import numpy

t = time.time()
conn = MySQLdb.connect(host='',...)
curs = conn.cursor()
curs.execute("select x,y from TABLENAME")
data = numpy.array(curs.fetchall(),dtype=float)
print(time.time()-t)

This code runs in less than a second, matching the loading speed achieved in MATLAB.

Step 5: Applying the Solution to Pymysql and Pyodbc

While modifying the MySQLdb source code is not necessary, we can achieve similar results using pymysql by applying the same type conversion modifications. Pyodbc requires recompiling the entire package, which is more involved.

The MySQLdb.converters module provides a convenient way to modify the type conversion for specific data types. By changing the converter values for DECIMAL and NEWDECIMAL, we can speed up the loading process.

Step 6: Loading Data into Pandas DataFrame

To load the numeric data directly into a Pandas DataFrame, we can use the pandas.io.sql.read_frame() function:

import MySQLdb
import pandas.io.sql as psql
from MySQLdb.converters import conversions
from MySQLdb.constants import FIELD_TYPE

conversions[FIELD_TYPE.DECIMAL] = float
conversions[FIELD_TYPE.NEWDECIMAL] = float
conn = MySQLdb.connect(host='',user='',passwd='',db='')
sql = "select * from NUMERICTABLE"
df = psql.read_frame(sql, conn)

This approach beats the MATLAB result by a factor of approximately 4.

Conclusion

Loading numeric data from a MySQL database into a Python Pandas/Numpy array requires attention to detail and understanding of type conversion. By modifying the converter values for DECIMAL and NEWDECIMAL, we can speed up the loading process using libraries like MySQLdb, pymysql, or pyodbc. This approach achieves results comparable to MATLAB’s native performance.

Additional Considerations

• Bulk Reading: Loading data in bulk using a library like pymysql or pyodbc can improve performance.
• Optimized Connections: Using an optimized connection pool and adjusting parameters like buffer size can also contribute to improved performance.
• Data Type Conversion: Carefully managing data type conversion is crucial when loading large datasets.

Last modified on 2024-08-01