Regularly get Got an error reading communication packets populating mysql from python3 using quota guard. Any advice?

Question

Every day we have a handful of python 3 scripts running to populate data to our application. The python scripts are on python anywhere. Then we use quota guard (qgtunnel) to get our data over to mysql. The data varies daily and on larger data days we are regularly getting mysql errors

pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

on mysql we get

2022-08-17T17:40:39.754094Z 77949 [Note] Aborted connection 77949 to db: 'db' user: 'user' host: 'IP' (Got an error reading communication packets)

This is the area of the code were erroring on. For instance 1 customer will have about 40,000 records to be created. I batch them by the 1,000 then sleep for 6 seconds. I also reset the db connection after each batch. However, we're still throwing the error regularly and I wonder if anyone has a similar setup and ran into this problem.

        ...
        for row in response:
        ...
            sql_query = "insert query"
            cursor.execute(sql_query, account_info)
            commit_rows = commit_rows + 1
            if commit_rows == 1000:
                print("committing 1000 records...")
                commit_rows = 0
                connection.commit()
                print("sleeping 6 seconds...")
                time.sleep(6.0)
                #close and reopen database connection
                connection.close()
                connection = pymysql.connect(user=os.getenv('mysqluser'),
                                         password=os.getenv('mysqlpwd'),
                                         host=os.getenv('mysqlhost'),
                                         database=os.getenv('mysqldb'))
                cursor = connection.cursor()
        connection.commit()

Here's some of the db version (5.7.25) settings...

max_allowed_packet: 1073741824
interactive_timeout: 31536000
wait_timeout: 31536000
connect_timeout: 31536000

Edit: Per @Rick James I got the error to go away by using executemany. Here is the updated code...

  ...
  itemBank = []
  for row in response:
  ...

  itemBank.append((myVal1, myVal2...))

  commit_rows = commit_rows + 1
  if commit_rows == 1000:
      print("committing 1000 records...")
      commit_rows = 0
      cursor.executemany(sql_query, itemBank)
      connection.commit()
      itemBank = []
      print("sleeping 3 seconds...")
      time.sleep(3.0)

Answer 1

Use executemany so that you can insert 1000 in a single query. This will run about 10 times as fast ("rows per second"); the 6 second can be shrunk.

If there are still problems, lower the 1000 to 100; it will still be about 10 times as fast.

Also check the setting of read_buffer_size.

Do not raise space configurations sok big that you cause swapping. So, keep an eye on swapping and OOM. Swapping is terrible for performance and may explain your error.