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?