How to handle MySQL connections using Python multithreading

Question

I have a main Python script that connects to a MySQL database and extracts several records from it. Based on the returned result, as many threads (class instances) begin as many records are captured. Each thread should return to the database and update another table by setting one state flag to another state ("process started").

For this, I tried:

1.) Transfer the database connection to all streams 2.) Open a new database connection from each stream

but none of them worked.

I can start my update without any problems in both cases using try / except, but the MySQL table was not updated, and there were no errors. I used commit in both cases.

The question is how to handle MySQL connections in this 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

It seems I have no problem with my code, but with my version of MySQL. I am using the standard version of the MySQL community and based on the official documentation found here :

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

I am going to upgrade to MariaDB to solve this problem.

Answer 2

Here is an example of using mysql for multithreading in Python, I don’t know your table and data, so just changing the code can 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

Mysql 5.7 seems to support multithreading.

As you tried earlier - absolutely be sure to skip the connection in def def (). defining global connections was my mistake

Here is an example code that prints 10 records across 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()