先上已经单独抽取出来的测试代码段,附运行结果,最后说明情况:
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 语句呢?
2 uti6770werty OP @ruanimal 搞了 N 天,我是试着把 POOL = PooledDB( # creator=pymysql, # 使用链接数据库的模块 creator=MySQLdb, # 使用链接数据库的模块 换成了 MySQLdb 引擎后,SQL 语句的多句执行就正常了 搞不懂是怎么回事 目前执行语句量不大,也没看出会有什么幺蛾子。。。 |