python多线程读取数据库数据,如何使用Python多线程处理MySQL连接

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

解决方案

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.