Python操作MySQL时，无WHERE子句的SELECT查询因参数未全使用报错求助

嗨，这个问题的原因其实很明确：当用户级别大于0的时候，你生成的SQL语句里没有任何占位符（%s），但调用cursor.execute()时还是传入了(current_user,)这个参数，MySQL连接器检测到你传递了参数但SQL语句里没用到，所以抛出了ProgrammingError。

解决这个问题很简单，只需要让SQL语句的占位符数量和传递的参数数量保持一致就行，这里有两种常用的写法：

写法一：根据分支定义对应的参数组 #

def get_expenses_by_type(self, current_user):
    try:
        cursor = self.main_db.cursor(dictionary=True)
        if self.check_user_level(current_user) == 0:
            query = """
                SELECT Date, Type, SUM(Price * Quantity) AS TotalAmount
                FROM ExpenseTable
                WHERE UserID = %s
                GROUP BY Date, Type;
            """
            # 需要传递用户ID参数
            params = (current_user,)
        else:
            query = """
                SELECT Date, Type, SUM(Price * Quantity) AS TotalAmount
                FROM ExpenseTable
                GROUP BY Date, Type;
            """
            # 不需要参数，传空元组
            params = ()
        cursor.execute(query, params)
        results = cursor.fetchall()
        cursor.close()
        return results
    except Exception as error:
        # 这里可以考虑添加日志记录，方便后续排查问题
        raise error

写法二：无参数时直接省略第二个参数 #

def get_expenses_by_type(self, current_user):
    try:
        cursor = self.main_db.cursor(dictionary=True)
        if self.check_user_level(current_user) == 0:
            query = """
                SELECT Date, Type, SUM(Price * Quantity) AS TotalAmount
                FROM ExpenseTable
                WHERE UserID = %s
                GROUP BY Date, Type;
            """
            cursor.execute(query, (current_user,))
        else:
            query = """
                SELECT Date, Type, SUM(Price * Quantity) AS TotalAmount
                FROM ExpenseTable
                GROUP BY Date, Type;
            """
            # 没有占位符，直接调用execute，不传参数
            cursor.execute(query)
        results = cursor.fetchall()
        cursor.close()
        return results
    except Exception as error:
        raise error

两种写法都能解决问题，另外小建议：捕获异常后可以加入日志打印，方便后续排查其他潜在问题哦。

备注：内容来源于stack exchange，提问作者Phantom