How to handle MySQL connection(s) with Python multithreading

Question

I have a main Python script which connects to a MySQL database and pulls out few records from it. Based on the result returned it starts as many threads (class instances) as many records are grabbed. Each thread should go back to the database and update another table by setting one status flag to a different state ("process started").

To achieve this I tried to:

1.) Pass the database connection to all threads 2.) Open a new database connection from each thread

but none of them were working.

I could run my update without any issue in both cases by using try/except, but the MySQL table has not been updated, and no error was generated. I used commit in both cases.

My question would be how to handle MySQL connection(s) in such a case?

Update based on the first few comments:

MAIN SCRIPT
-----------

#Connecting to DB
db = MySQLdb.connect(host = db_host,
                         db = db_db,
                         port = db_port,
                         user = db_user,
                         passwd = db_password,
                         charset='utf8')

# Initiating database cursor
cur = db.cursor()

# Fetching records for which I need to initiate a class instance

cur.execute('SELECT ...')

for row in cur.fetchall() :
    # Initiating new instance, appending it to a list and
    # starting all of them 



CLASS WHICH IS INSTANTIATED
---------------------------

# Connecting to DB again. I also tried to pass connection
# which has been opened in the main script but it did not
# work either.

db = MySQLdb.connect(host = db_host,
                         db = db_db,
                         port = db_port,
                         user = db_user,
                         passwd = db_password,
                         charset='utf8')

# Initiating database cursor
cur_class = db.cursor()
cur.execute('UPDATE ...')
db.commit()

Answer 1

Looks like mysql 5.7 does support multithreading.

As you tried previously - absolutely make sure to pass the connection within the def worker(). defining the connections globally was my mistake

Here's sample code that prints 10 records via 5 threads, 5 times

import MySQLdb
import threading


def write_good_proxies():    
    local_db = MySQLdb.connect("localhost","username","PassW","DB", port=3306 )
    local_cursor = local_db.cursor (MySQLdb.cursors.DictCursor)
    sql_select = 'select http from zproxies where update_time is null order by rand() limit 10'
    local_cursor.execute(sql_select)
    records = local_cursor.fetchall()
    id_list = [f['http'] for f in records]
    print id_list
def worker():
    x=0
    while x< 5:
        x = x+1
        write_good_proxies()

threads = []


for i in range(5):
    print i
    t = threading.Thread(target=worker)
    threads.append(t)
    t.start()

Answer 2

Here is an example using multithreading deal mysql in Python, I don't know your table and data, so, just change the code may help:

import threading
import time
import MySQLdb

Num_Of_threads = 5

class myThread(threading.Thread):

    def __init__(self, conn, cur, data_to_deal):
        threading.Thread.__init__(self)
        self.threadID = threadID
        self.conn = conn
        self.cur = cur
        self.data_to_deal

    def run(self):

        # add your sql 
        sql = 'insert into table id values ({0});'
        for i in self.data_to_deal:
            self.cur.execute(sql.format(i))
            self.conn.commit()

threads = []
data_list = [1,2,3,4,5]

for i in range(Num_Of_threads):
    conn = MySQLdb.connect(host='localhost',user='root',passwd='',db='')
    cur = conn.cursor()
    new_thread = myThread(conn, cur, data_list[i])

for th in threads:
    th.start()

for t in threads:
    t.join()

Answer 3

It seems there's no problem with my code but with my MySQL version. I'm using MySQL standard community edition and based on the official documentation found here :

The thread pool plugin is a commercial feature. It is not included in MySQL community distributions.

I'm about to upgrade to MariaDB to solve this issue.