multithreading in mysql and python

Question

I am learning python and mysql and with that I want to use multythreading to write in mysql database

when I try to do that and try to make multiple threads It shows error like connection not found but If I try with 1 thread it works fine but It has lower speed i.e. 40 rows p/s

please help me to do that and if I am doing wrong please let me know if there is a good way to do that thanx

import mysql.connector
from queue import Queue
from threading import Thread

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="",
  database="list"
)


def do_stuff(q):
      while True:
            mycursor = mydb.cursor()
            a=  q.get()
            sql = "INSERT INTO demo1 (id, name, price, tmp) VALUES (%s, %s, %s, %s)"
            val = (a[0], a[1],a[3],a[2])
            mycursor.execute(sql, val)

            mydb.commit()
            q.task_done()

q = Queue(maxsize=0)
num_threads = 1 #if I try more then 1 it throw error "IndexError: bytearray index out of range"

for i in range(num_threads):
        worker = Thread(target=do_stuff, args=(q,))
        worker.setDaemon(True)
        worker.start()
        
        
def strt():
    mycursor = mydb.cursor()
    sql = f"SELECT * FROM demo ORDER BY id"
    mycursor.execute(sql)
    myresult = mycursor.fetchall()
    for x in myresult:
        q.put(x)
            
strt()

Answer 1

Hi in order to do the transactions you have to open connection to each thread. How this works is when you open a connection those connections are getting by a pool. If you open one connection that connection is always used by one process and not letting the other to connect.

It will not make any bottle necks because when one connection is free that connection will be chosen from the pool.

Source