解决MySQL报ValueError(“Could not process parameters“)错误

    在使用Python连接MySQL数据库时，有时会报"ValueError(“Could not process parameters”)"错误，如图(1)所示：

图(1) MySQL报"ValueError("Could not process parameters)"错误

• 首先排查，代码里的字段是否与数据库的字段保持一致(区分大小写);
• 再排查，函数调用时的字段顺序，是否与声明时的字段顺序保持一致；

如果上面2项都是一致的(即已排除)，那么很可能是下面的原因:

    出现该问题的原因是：传参时没有使用元组的方式，造成MySQL无法解析而报错。
    解决方法：使用元组的方式传入数据库。

1、修改前

    def execute(self, sql, args=None, commit=False):
        conn = self.pool.get_connection()
        cursor = conn.cursor()
        if args:
            cursor.execute(sql, args) ## 此处有问题
        else:
            cursor.execute(sql)
        if commit is True:
            conn.commit()
            self.close(conn, cursor)
            return None
        else:
            res = cursor.fetchall()
            self.close(conn, cursor)
            return res

2、修复Bug

    def execute(self, sql, args=None, commit=False):
        conn = self.pool.get_connection()
        cursor = conn.cursor()
        if args:
            cursor.execute(sql, (args,) ) ## 要用元组的方式传参，即使用(arg,)
        else:
            cursor.execute(sql)
        if commit is True:
            conn.commit()
            self.close(conn, cursor)
            return None
        else:
            res = cursor.fetchall()
            self.close(conn, cursor)
            return res

附录

3.1 MySQL数据库连接池基类

    创建数据库连接池基类，命名为base.py，默认支持5个connect连接。
    //base.py

import pymysql
import mysql.connector.pooling


class DataBaseInfo(object):
    def __init__(self, host='127.0.0.1', port='3306', user='root', password='xxx', database='chair'):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database

    def start_mysql(self):
        db = pymysql.connect(self.host, self.port, self.user, self.password, self.dbname)
        cursor = db.cursor()
        table_list = [db, cursor]
        return table_list


class DataBasePool(object):
    def __init__(self, host='127.0.0.1', port='3306', user='root', password='xxx', database='chair',
                 pool_name='mypool', pool_size=5):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database

        res = {}
        res["host"] = self.host
        res["port"] = self.port
        res["user"] = self.user
        res["password"] = self.password
        res["database"] = self.database
        self.dbconfig = res
        self.pool = self.create_pool(pool_name=pool_name, pool_size=pool_size)

    def create_pool(self, pool_name="mypool", pool_size=5):
        pool = mysql.connector.pooling.MySQLConnectionPool(
            pool_name=pool_name,
            pool_size=pool_size,
            pool_reset_session=True,
            **self.dbconfig
        )
        return pool

    def close(self, conn, cursor):
        cursor.close()
        conn.close()

    def execute(self, sql, args=None, commit=False):
        conn = self.pool.get_connection()
        cursor = conn.cursor()
        if args:
            cursor.execute(sql, (args,))
        else:
            cursor.execute(sql)
        if commit is True:
            conn.commit()
            self.close(conn, cursor)
            return None
        else:
            res = cursor.fetchall()
            self.close(conn, cursor)
            return res

    def executemany(self, sql, args, commit=False):
        conn = self.pool.get_connection()
        cursor = conn.cursor()
        cursor.executemany(sql, args)
        if commit is True:
            conn.commit()
            self.close(conn, cursor)
            return None
        else:
            res = cursor.fetchall()
            self.close(conn, cursor)
            return res

    def executeRead(self, sql, args):
        conn = self.pool.get_connection()
        cursor = conn.cursor()
        cursor.executemany(sql, args)
        res = cursor.fetchall()
        self.close(conn, cursor)
        myData = []
        for row in res:
            myData.append(row)
        return myData

3.2 读写某个Table

    这里以读写chair数据库里的login表格为例，进行说明。
    //loginTable.py

from .base import DataBasePool


# dbconfig = {
#     "host": "127.0.0.1",
#     "port": "3306",
#     "user": "root",
#     "password": "xxx",
#     "database": "chair",
#     "pool_name": "mypool",
#     "pool_size": 5
# }

class LoginTable(object):
    def __init__(self, dbConf):
        self.host = dbConf['host']
        self.port = dbConf['port']
        self.user = dbConf['user']
        self.password = dbConf['password']
        self.database = dbConf['database']
        self.pool_name = dbConf['pool_name']
        self.pool_size = int(dbConf['pool_size'])
        self.dbPool = DataBasePool(self.host, self.port, self.user, self.password,
                                   self.database, self.pool_name, self.pool_size)

    def insertTable(self, Uname, Upasswd):
        args = Uname, Upasswd
        sql = "insert into login (Uname, Upasswd) values (%s, %s)"
        bRes = self.dbPool.execute(sql, args, commit=True)
        return bRes

    def hasTable(self):
        sql = "SELECT table_name FROM information_schema.TABLES WHERE table_name='login'"
        strLen = len(str(self.dbPool.execute(sql)))
        bRes = True if strLen > 4 else False
        return bRes

    def selectName(self, name):
        args = name
        sql = "SELECT * FROM login WHERE Uname=%s"
        res = self.dbPool.execute(sql, args,False)
        return res

    def selectAll(self):
        sql = "SELECT * FROM login"
        res = self.dbPool.execute(sql)
        return res

    def isExisted(self, Uname):
        strLen = len(str(self.selectTable(Uname)))
        if strLen > 4:
            return True
        else:
            return False

    def updatePassword(self, Uname, Upasswd):
        sql = "UPDATE login SET Upasswd=%s where Uname=%s;"
        args = Upasswd, Uname
        res = self.dbPool.execute(sql, args, commit=True)
        bRes = True if res is None else False
        return bRes

3.3 创建login表格

    在Navicat里，导入login.sql文件，即可创建login表格。
    //login.sql

/*
 Navicat Premium Data Transfer

 Source Server         : locaLWin10
 Source Server Type    : MySQL
 Source Server Version : 50733
 Source Host           : localhost:3306
 Source Schema         : chair

 Target Server Type    : MySQL
 Target Server Version : 50733
 File Encoding         : 65001

 Date: 11/04/2022 22:54:41
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for login
-- ----------------------------
DROP TABLE IF EXISTS `login`;
CREATE TABLE `login`  (
  `Uname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `Upasswd` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`Uname`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of login
-- ----------------------------
INSERT INTO `login` VALUES ('hua', '111');
INSERT INTO `login` VALUES ('Jerry', '222');
INSERT INTO `login` VALUES ('root', '333');
INSERT INTO `login` VALUES ('Tom', '555');

SET FOREIGN_KEY_CHECKS = 1;

3.4 测试login表格

    读取login表格里某个用户比如Tom，测试代码如下：
    //doUser.py

from .loginTable import LoginTable

dbconfig = {
    "host": "localhost",
    "port": "3306",
    "user": "root",
    "password": "xxx",
    "database": "chair",
    "pool_name": "mypool",
    "pool_size": 5
}

myTab = LoginTable(dbconfig)

res = myTab.selectName('Tom')
print('res:', res)

    效果如下：

图(2) 成功读取login里的Tom信息

3.5 MySQL连接池依赖包

    MySQL连接池有2个依赖包，如下:

pip install mysql-connector-python==8.0.27
pip install pymysql==1.0.2