4

用 PooledDB 的 V 友,组装出来的 SQL 语句,不能一次执行?

 2 years ago
source link: https://www.v2ex.com/t/833880
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

V2EX  ›  Python

用 PooledDB 的 V 友,组装出来的 SQL 语句,不能一次执行?

  uti6770werty · 1 天前 · 501 次点击

先上已经单独抽取出来的测试代码段,附运行结果,最后说明情况:

    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 语句呢?


About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK