'Speed up executemany query for inserting data into MySQL database from Python

I have the following (sample) set of data that I'm trying to insert into a MySQL database from Python:

matchups_data = [('Davis Love III', 1706, 1, -2.0, 124.0),
 ('Davis Love III', 1706, 2, -4.0, 22.0),
 ('Davis Love III', 1706, 3, 3.0, 135.0),
 ('Davis Love III', 1706, 4, 2.0, 87.0),
 ('Davis Love III', 1706, 5, 1.0, 68.0),
 ('Davis Love III', 1706, 6, 1.0, 118.0),
 ('Davis Love III', 1706, 7, 0.0, 103.0),
 ('Davis Love III', 1706, 8, -3.0, 35.0),
 ('Davis Love III', 1706, 9, -1.0, 131.0),
 ('Davis Love III', 1706, 10, 0.0, 100.0)]

The actual set of data I'm inserting is approximately 1.4 million rows and when I use the below query to insert it into my database is more time than I would expect (10+ minutes).

import mysql.connector

db = mysql.connector.connect(
host = db_host,
user = un,
password = pw,
database = 'golfstaging')

cursor = db.cursor()

matchups_insert_query = """
INSERT INTO MatchupSimResults (PlayerName, PlayerID, SimNum, R1Score, TourneyRank)
values(%s,%s,%s,%s,%s)
"""

matchups_data = list(zip(sim_results['player_name'], sim_results['player_id'],
                        sim_results['sim'], sim_results['R1'], sim_results['tourney_rank']))

cursor.executemany(matchups_insert_query, matchups_data)
db.commit()

Is there something I can be doing differently - either with the executemany function or with the dataset - to speed this up?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source