Search code examples
pythonmysqlflaskflask-mysql

Why is this query not executing properly when using flaskext.mysql, when it executes correctly using MySQL workbench?


So I have this query

query =  "SET @rownumber = 0; UPDATE students SET roll_no = (@rownumber := @rownumber+1) WHERE standard='{}' AND division='{}' ORDER BY fname, lname".format(standard, division)

When I try to run this query using cursor.execute(query), I get this following error using

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE students SET roll_no = (@rownumber := @rownumber+1) WHERE standard='4' AN' at line 1")

But this same query works fine when I run it on MYSQL workbench ? Why is it not working and how can I get it to work ?


Solution

  • UPDATE students 
    SET roll_no = (@rownumber := @rownumber+1) 
    WHERE standard='{}' 
      AND division='{}' 
      AND (1 + (@rownumber := 0))
    ORDER BY fname, lname
    

    What happens?

    Execution plan builder finds constant condition AND (1 + (@rownumber := 0)) (it does not include any column) and evaluates it once before query execution for to convert it to a constant. The value obtained is 1 which is treated as TRUE constant, so this condition is removed. But the user-defined variable initializes during evaluation.

    This method allows both initialize the variable and store ORDER BY usage, because it do not convert the query to multi-table form (like when initialize the variable in subquery) in which ORDER BY clause is not allowed.