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

472 Views Asked by At

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?

0

There are 0 best solutions below