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()

Try to give each thread it's own connector.

Mace
– Mace

2020-12-12 14:33:24 +00:00
Commented Dec 12, 2020 at 14:33 — Mace
– Mace, Commented Dec 12, 2020 at 14:33
thanks @Mace I got it

Amit Sharma
– Amit Sharma

2020-12-12 15:40:07 +00:00
Commented Dec 12, 2020 at 15:40 — Amit Sharma
– Amit Sharma, Commented Dec 12, 2020 at 15:40

AmilaMGunawardana · Accepted Answer · 2020-12-12 14:58:58Z

5

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

answered Dec 12, 2020 at 14:58

AmilaMGunawardana

1,8752 gold badges16 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

multithreading in mysql and python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related