Mysql Question posted in
The official MySQL documentation can be found here.

Can I adjust this SELECT query without using a WHERE clause in python using MySQL?

I have the following function is python, which is not working:

    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;
                """
            else:
                query = """
                    SELECT Date, Type, SUM(Price * Quantity) AS TotalAmount
                    FROM ExpenseTable
                    GROUP BY Date, Type;
                """
            cursor.execute(query, (current_user,))
            results = cursor.fetchall()
            cursor.close()
            return results
        except Exception as error:
            raise error

When I run the code, if the user level is greater than 0, I get the following error raised:

mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

Could anyone help please?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I realised the solution was within how I was executing the query. In either scenario, I was executing the query and using the parameter of the current user: cursor.execute(query, (current_user,)). The solution was to have two execute statements, one in each selection:

    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;
            """
            cursor.execute(query)
        results = cursor.fetchall()
        cursor.close()

        # Convert the query results into a dictionary
        return results
    except Exception as error:
        raise error

    This worked as intended.


  2. 0

    You can move the query parameters into another variable. Then you can make this an empty tuple when you don’t have a WHERE clause.

    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;
            """
            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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search