先上已经单独抽取出来的测试代码段,附运行结果,最后说明情况:
from dbutils.pooled_db import PooledDB
import pymysql
sqlinfo = {'ip': '192.168.xx.xx',
'port': 3306,
'user': 'xxx',
'password': 'xxx',
'database': 'ppp',
'charset': 'utf8'
}
POOL = PooledDB(
creator=pymysql, # 使用链接数据库的模块
maxconnections=600, # 连接池允许的最大连接数,0 和 None 表示不限制连接数
mincached=2, # 初始化时,链接池中至少创建的空闲的链接,0 表示不创建
maxcached=5, # 链接池中最多闲置的链接,0 和 None 不限制
blocking=True, # 连接池中如果没有可用连接后,是否阻塞等待。True ,等待; False ,不等待然后报错
maxusage=None, # 一个链接最多被重复使用的次数,None 表示无限制
setsession=[], # 开始会话前执行的命令列表。
ping=1, # ping MySQL 服务端,检查是否服务可用。
host=sqlinfo['ip'],
port=sqlinfo['port'],
user=sqlinfo['user'],
password=sqlinfo['password'],
database=sqlinfo['database'],
charset=sqlinfo['charset']
)
DBconn = POOL.connection()
cur = DBconn.cursor()
sqlCMDtext = "DELETE FROM `addmc` WHERE `mcid`='7981';DELETE FROM `mc` WHERE `mcid`='7981';"
ret = cur.execute(sqlCMDtext)
print(ret)
DBconn.close()
报错提示:
Traceback (most recent call last):
File "D:/test/test.py", line 46, in <module>
ret = cur.execute(sqltext)
File "C:\Users\Administrator\venv\lib\site-packages\dbutils\steady_db.py", line 598, in tough_method
result = method(*args, **kwargs) # try to execute
File "C:\Users\Administrator\venv\lib\site-packages\pymysql\cursors.py", line 170, in execute
result = self._query(query)
File "C:\Users\Administrator\venv\lib\site-packages\pymysql\cursors.py", line 328, in _query
conn.query(q)
File "C:\Users\Administrator\venv\lib\site-packages\pymysql\connections.py", line 517, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "C:\Users\Administrator\venv\lib\site-packages\pymysql\connections.py", line 732, in _read_query_result
result.read()
File "C:\Users\Administrator\venv\lib\site-packages\pymysql\connections.py", line 1075, in read
first_packet = self.connection._read_packet()
File "C:\Users\Administrator\venv\lib\site-packages\pymysql\connections.py", line 684, in _read_packet
packet.check_error()
File "C:\Users\Administrator\venv\lib\site-packages\pymysql\protocol.py", line 220, in check_error
err.raise_mysql_exception(self._data)
File "C:\Users\Administrator\venv\lib\site-packages\pymysql\err.py", line 109, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELETE FROM `mc` WHERE `mcid`='7981'' at line 1")
1 、莫关心 SQL 语句的目的实现,虽然一条语句就完成删除两张表的内容目标,但问题的本身:是为啥认不出第二条语句而报错? 这个组装语句在 MySQL 执行没毛病
2 、用 thSQLconn = MySQLdb.connect()
thSQLCursor = thSQLconn.cursor
thSQLCursor.execute(sqlCMDtext)
这种一般 MySQLdb 的执行方式,组装的 SQL 妥妥没毛病。
3 、因为是在多线程环境,sqlCMDtext 是来自于 SQLQueue = multiprocessing.Manager.Queue()队列,SQLQueue.get()获得,在多线程里共享队列,PooledDB+队列,目前这个方式处理生产消费模型,PooledDB 支持多线程,也能在共享队列里取到 SQL 语句,所以我也没找其它的方式。。。
4 、PooledDB 为啥不能处理这个的 SQL 语句呢?
这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。
V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。
V2EX is a community of developers, designers and creative people.