如何使用 Python 多线程处理 MySQL 连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27912048/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to handle MySQL connection(s) with Python multithreading
提问by g0m3z
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").
我有一个主要的 Python 脚本，它连接到 MySQL 数据库并从中提取一些记录。根据返回的结果，它启动与抓取的记录一样多的线程（类实例）。每个线程应该返回到数据库并通过将一个状态标志设置为不同的状态（“进程已启动”）来更新另一个表。
To achieve this I tried to:
为了实现这一点，我试图：
1.) Pass the database connection to all threads 2.) Open a new database connection from each thread
1.) 将数据库连接传递给所有线程 2.) 从每个线程打开一个新的数据库连接
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.
在这两种情况下，我都可以使用 try/except 运行我的更新而没有任何问题，但 MySQL 表尚未更新，也没有生成错误。我在这两种情况下都使用了 commit。
My question would be how to handle MySQL connection(s) in such a case?
我的问题是在这种情况下如何处理 MySQL 连接？
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()
采纳答案by g0m3z
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:
我的代码似乎没有问题，但我的 MySQL 版本似乎没有问题。我正在使用 MySQL 标准社区版，并基于此处找到的官方文档：
The thread pool plugin is a commercial feature. It is not included in MySQL community distributions.
线程池插件是一个商业功能。它不包含在 MySQL 社区发行版中。
I'm about to upgrade to MariaDB to solve this issue.
我即将升级到 MariaDB 来解决这个问题。
回答by lqhcpsgbl
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:
这是在 Python 中使用多线程处理 mysql 的示例，我不知道您的表和数据，因此，只需更改代码可能会有所帮助：
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()
回答by FlyingZebra1
Looks like mysql 5.7 does support multithreading.
看起来 mysql 5.7 确实支持多线程。
As you tried previously - absolutely make sure to pass the connection within the def worker(). defining the connections globally was my mistake
正如您之前尝试过的 - 绝对确保在 def worker() 中传递连接。全局定义连接是我的错误
Here's sample code that prints 10 records via 5 threads, 5 times
这是通过 5 个线程打印 10 条记录的示例代码，5 次
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()
