Instructobit
Inserting records into a table using the MySQL connector in Python is generally quite straight forward and routine, however when inserting large amounts of records into a table without the proper optimisations, you will definitely start to see in increase in processing time and you could start putting a lot of strain on your servers.
Generating some test records
For this test we'll just generate a thousand *random md5 records, this will be enough test data to perform benchmarks at every point in the code.
from hashlib import md5
param_list = []
print("Generating test data")
for x in range(0, 1000):
param_list.append(md5(str(x).encode()).hexdigest())
We will be inserting these records into the following simple table:
create database testdb;
use testdb;
create table test_table(test_field varchar(150));
Performing a baseline test (non-optimised code)
For our basline test we'll use the least optimal code we can think of, simply looping through the list and executing each insert and commit immediately afterwards.
from time import time
import mysql.connector
host_args = {
"host": "localhost",
"user": "testuser",
"password": "testpass",
"database": "testdb"
}
con = mysql.connector.connect(**host_args)
# First clear the table
cur = con.cursor()
cur.execute('TRUNCATE test_table')
sql = "INSERT INTO test_table(test_field) VALUES(%s)"
start_time = time()
print("Inserting data into table the slow way")
for param in param_list:
cur.execute(sql, (param, ))
con.commit()
print(f"Time taken {time() - start_time}")
The output of the above should be something like:
Generating test data
Inserting data into table the slow way
Time taken 7.864834308624268
As you can see it took quite some time to insert those records, it would take over a minute to do 10 000 of those, let's improve those numbers a bit!
Optimisation 1 Stop commiting for every transaction
The first and most obvious mistake in the first example was to commit for each insert we do, this adds a significant amount of overhead as each insert is treated as a separate transaction. For our next example we'll make a small modification to our main loop:
start_time = time()
print("Inserting data into table slightly faster")
for param in param_list:
cur.execute(sql, (param, ))
con.commit()
print(f"Time taken {time() - start_time}")
The output should now look something like this:
Generating test data
Inserting data into table the slow way
Time taken 0.27290964126586914
Just by moving the commit out of the loop and only commiting once at the end of the loop has taken our time down from ~7 seconds to ~0.2 seconds, a major improvement! Just watch out when using this method, if you plan to insert millions of records into a table, you may want to commit every couple thousands of records as it would be devastating to get half way through importing your data only for there to be a network outage or something like that.
Optimisation 2 Use MySQL connector's executemany method
The executemany() cursor method can be used to bulk insert records, it is probably the fastest method that Python's MySQL connector has to offer for this purpose. It works by combining all the records provided in you parameter list and building 1 big insert, this allows you to essentially send all of records through in one go, doing away with all the network overhead of inserting them one-by-one.
start_time = time()
print("Inserting data into table the fastest way")
bulk_params = [(x, ) for x in param_list] # Convert our parameter list to a list of tuples (eg. [(1, ), (2, ), (3, )])
cur.executemany(sql, bulk_params)
con.commit()
print(f"Time taken {time() - start_time}")
The output should now look something like this:
Generating test data
Inserting data into table the fastest way
Time taken 0.10141563415527344
So here we've managed to halve the time taken from the previous example! These savings will be even more apparent when using even more records. Again, if you are inserting millions of records into a table, you may want to split them up into chunks of a couple thousand when doing this just to be safe...
An important caveat to remember about the executemany() method is that the optimisation of clumping your records into one insert only works for "INSERT INTO ... VALUES ..." Statements, it uses a regular expression to find these. IT WILL NOT WORK FOR "INSERT IGNORE..." or "REPLACE INTO..." STATEMENTS. One way around this caveat is to rather use a "INSERT INTO ... VALUES ... ON DUPLICATE KEY UPDATE..." statement, this will allow it to still be picked up by the regular expression and optmised.
If you have any questions or would like to add anything to this tutorial feel free to leave a comment below!
