Python 多线程查询 mysql 报错，求解决办法

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Date    : 2018-04-25 18:04:06
# @Author  : Your Name (you@example.org)
# @Link    : http://example.org
# @Version : $Id$

import threading,time,MySQLdb

class TestMysql():

	def __init__(self, ip, port, username, password, database):
		self.db = MySQLdb.connect(host=ip, port=port, user=username, passwd=password, db=database, charset="utf8")
		self.cursor = self.db.cursor()

	def select1(self,tablename):
		sql = "SELECT (gender & 15) AS gender1, f.image_data,f.json FROM intellif_face." + tablename + " f limit 100000;"
		self.cursor.execute(sql)
		data = self.cursor.fetchone()

		print(data)

	def select2(self,tablename):
		sql = "SELECT (race & 15) AS race1,f.image_data,f.json FROM intellif_face." + tablename + " f limit 100000;"
		self.cursor.execute(sql)
		data = self.cursor.fetchone()

		print(data)




if __name__ == '__main__':
	test = TestMysql("192.168.11.128", 3306, "root", 'introcks1234', "intellif_face")
	tablelist = ['t_face_24','t_face_25','t_face_26']  # 可以增加表名
	thread = []
	for tablename in tablelist:
		print(tablename)
		t1 = threading.Thread(target = test.select1, args = (tablename,))
		t2 = threading.Thread(target = test.select2, args = (tablename,))
		thread.append(t1)
		thread.append(t2)
	for t in thread:
                t.setDaemon(True)
		t.start()
	t.join()

============================output=========================================

Exception in thread Thread-1:
Traceback (most recent call last):
  File "C:\Python36\lib\threading.py", line 916, in _bootstrap_inner
    self.run()
  File "C:\Python36\lib\threading.py", line 864, in run
    self._target(*self._args, **self._kwargs)
  File "F:\code\pyProject\thread\thread_mysql_test.py", line 25, in select2
    self.cursor.execute(sql)
  File "C:\Python36\lib\site-packages\MySQLdb\cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "C:\Python36\lib\site-packages\MySQLdb\connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "C:\Python36\lib\site-packages\MySQLdb\cursors.py", line 247, in execute
    res = self._query(query)
  File "C:\Python36\lib\site-packages\MySQLdb\cursors.py", line 411, in _query
    rowcount = self._do_query(q)
  File "C:\Python36\lib\site-packages\MySQLdb\cursors.py", line 374, in _do_query
    db.query(q)
  File "C:\Python36\lib\site-packages\MySQLdb\connections.py", line 277, in query
    _mysql.connection.query(self, query)
_mysql_exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query')

.......

[Finished in 1.9s with exit code 3221226356]

xpresslink

2018-04-26 09:16:11 +08:00

要每个线程单独用一个连接，把 test = TestMysql("192.168.11.128", 3306, "root", 'introcks1234', "intellif_face")
放在 for 里面就可以了

if __name__ == '__main__':

□□□□tablelist = ['t_face_24','t_face_25','t_face_26'] # 可以增加表名
□□□□thread = []
□□□□for tablename in tablelist:
□□□□□□□□test = TestMysql("192.168.11.128", 3306, "root", 'introcks1234', "intellif_face")
□□□□□□□□print(tablename)
□□□□□□□□t1 = threading.Thread(target = test.select1, args = (tablename,))
□□□□□□□□t2 = threading.Thread(target = test.select2, args = (tablename,))
□□□□□□□□thread.append(t1)
□□□□□□□□thread.append(t2)
□□□□for t in thread:
t.setDaemon(True)
t.start()
□□□□t.join()

Minys

2018-04-27 00:11:10 +08:00

@xpresslink 自己写的线程池操作也可能有意想不到的危险，因为 Python 的 GIL，多线程的时候其实是通过 CPU 不断切换执行，让用户看起来像多线程。这就会出现 thread1 和 thread2 在对同一个对象操作的时候出现问题。

比如两个 thread1 和 thread2 都要执行对 A.a+=1 的操作，但是这个看似基本的操作其实分 3 步：
1. tmp=getattr(A, 'a')
2. tmp =tmp+1
3. setattr(A, 'a', tmp)

如果刚好在这中间切换线程，就会导致奇怪的 bug，根本无法 debug，感觉还是使用 multiprocessing 里面的 Pool 来直接多进程操作比较好。多线程的话还是得用 threading 库确保不会冲突，必要的时候还是得上锁。