How can I batch together insert statements using Python and MySQL

Question

I have a mysql database, I am using python and the mysql.connector library

My problem set is I have a list of 21 True or False value which I want to insert into the table one at a time. The way I was doing it at first is by looping over the list and trying to write 1 at a time. This was very slow with each operation taking 0.3s

My questions specifically is how can I use the library to perform batch insert statements?

class DatabaseOperations:
    def __init__(self, *args):  # Establish the db connection
        for i in range(len(args)):  # Cycle through passed arguments and assign them to variables
            if i == 0:  # Assign the first argument as db_connection_user
                self.host = args[0]
            elif i == 1:  # Assign the second argument as db_connection_password
                self.user = args[1]
            elif i == 2:  # Assign the third argument as db_connection_database
                self.password = args[2]
            elif i == 3:  # Assign the fourth argument as db_connection_database
                self.database = args[3]
            elif i == 4:  # Assign the fifth argument as localhost_ip
                self.localhost = args[4]
        self.connection_check = True
        self.socket = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
        self.socket.connect((self.localhost, gvl.global_port_database))
        self.db_connection = mysql.connector.pooling.MySQLConnectionPool(
            pool_name="db_pool",
            pool_size=5,
            pool_reset_session=True,
            host=self.host,
            user=self.user,
            password=self.password,
            database=self.database)

My first attempt at trying to make things faster was by using a connection pool instead of just a single cursor. My confusion lies in the fact on how do I perform the batch operations. My attempt is to create a list of 'write_operations' and pass that along to do something

def batch_write_table(self, list_of_writes):
        try:
            db_connection = self.db_connection.get_connection()
            db_cursor = db_connection.cursor()
        db_cursor.execute(
                f"INSERT INTO table ("
                f"localhost_ip, "
                f"plc_ip, "
                f"address_name, "
                f"address_number, "
                f"address_value, "
                f"timestamp) "
                f"VALUES("
                f"'{localhost_ip}', "
                f"'{plc_ip}', "
                f"'{modbus_name}', "
                f"'{modbus_number}', "
                f"'{modbus_value}', "
                f"'{timestamp}');"
            )
        except Exception as e:
            print(f'Error => {e}')

I do not understand how to create singular write operation objects, or if I am simply supposed to create list of values and use that along with some batch_write function. Help and clarification are greatly appreciated!

EDIT:

Added how I'm writing from the main file

    for i in range(len(_list)):
        print('\ni -> ', i)
        print('_list[i] -> ', _list[i])
        match i:
            case 0:
                print(f'case 0 timestamp -> {timestamp_generator()}')
                write_table(
                    data
                )
    essentially there are 21 possible cases as the list is 21 in length

Do you really need the `for` loop and the `ifs`? Can't you simply do the assignments? Or will the program blow up if there are fewer than 4 args? — Rick James, Feb 16 '23 at 18:38

score 1 · Accepted Answer · answered Feb 15 '23 at 06:26

MySQL Connector supports batch updates through the cursor's executemany method. The documentation states that this a genuine batch insert, that is it generates insert statements with multiple values clauses, like this:

INSERT INTO tbl (col1, col2) VALUES (1, 2), (3, 4), (5, 6)

as opposed to generating multiple single insert statements.

Your code ought to look like this:

# Collect the values for each insert in a list of tuples, where each tuple contains the values for a single insert statement
list_arg_tuples = [    
    (localhost_ip1, plc_ip1, modbus_name1, modbus_number1, modbus_value1, timestamp1),    
    (localhost_ip2, plc_ip2, modbus_name2, modbus_number2, modbus_value2, timestamp2),     
    ...               
]                      
# Use DB-API parameter subsitution to ensure that values are correctly quoted.
# Don't use f-strings or similar for this, see 
# https://stackoverflow.com/questions/902408/how-to-use-variables-in-sql-statement-in-python           

stmt = """INSERT INTO table (localhost_ip, plc_ip, address_name, address_number, address_value, timestamp) VALUES(%s, %s, %s, %s, %s, %s)"""
db.cursor.execute(stmt, list_of_arg_tuples)

# Commit after execution. For large numbers of inserts, insert and commit in batches (say 10000 per batch, but you should measure performance and adjust).

db_connection.commit()

100 rows will insert 10 times as fast via a single batched insert (versus 100 individual inserts). — Rick James, Feb 16 '23 at 18:39

How can I batch together insert statements using Python and MySQL

1 Answers1