[FIXED] Python MySQL - thread paused After executing a delete query

Issue

I am runnig a threading pool in python. in every thread I execute a delete query, even though the query worked successfully the thread paused.

 mydb = mysql.connector.connect(host="localhost",user="user",passwd="1",database="workers")
 cursor = mydb.cursor()
 query = "DELETE FROM `workers` WHERE id = %s"
 cursor.execute(query, (i,))
 mydb.commit()
 cursor.close()
 mydb.close()   

The occurrence of these issues is a result of executing these method:

 mydb.commit()

If I remove this row, everything functions properly. However, the delete query is not executed.

Solution

When you call mydb.commit(), it's committing changes to the database and closing the connection. If you remove that line, the changes aren't committed, and the connection isn't closed, which might explain why the threads aren't pausing.

Instead of opening and closing the database connection in each thread, consider using a connection pool. With a connection pool, you create a set of connections at the start and reuse them among multiple threads, which can be more efficient and avoid some potential issues.

import mysql.connector.pooling

dbconfig = {
    "host": "localhost",
    "user": "user",
    "passwd": "1",
    "database": "workers"
}

pool = mysql.connector.pooling.MySQLConnectionPool(pool_name="mypool", pool_size=5, **dbconfig)

def delete_worker(worker_id):
    try:
        connection = pool.get_connection()
        cursor = connection.cursor()

        query = "DELETE FROM `workers` WHERE id = %s"
        cursor.execute(query, (worker_id,))
        connection.commit()

        cursor.close()
        connection.close()
    except Exception as e:
        print("Error:", e)

# Example usage in your threads
worker_id_to_delete = 123
delete_worker(worker_id_to_delete)

Answered By - Amit Mohanty