Why mysql user defined variable is not work in python?

Question

I have a SQL ran in python and workbench and both of them return different result. I believe the main cause is because the @user defined variable doesn't cache when I pass it to python mysql connector, so it cannot do the counting function. Basically, the logic of the SQL is to rank the food in each supermarket(group) and return the first 10 food in each supermarket (example only). In workbench, the counter will output 1,2,3,4... while in python they return 1 which doesn't do the counting. How can I prevent it? Thanks!

Code:
    cnx= mysql.connector.connect(**config)
    cursor = cnx.cursor()

    query = f'''SELECT supermarket, food, counter
            FROM (
            SELECT supermarket, food, 
                @counter:=IF(@supermarket_id= supermarket, @counter+1,1) as counter, 
                @supermarket_id:= supermarket
            FROM supermkt_data 
            order by supermarket, food) tmp
            WHERE counter<= 10;
            '''
    log.info(query)
    cursor.execute(query)
    columns = [col[0] for col in cursor.description]
    result = [dict(zip(columns, row)) for row in cursor.fetchall()]

Answer 1

You must initialize the variables. If not then they are treated as constants which are not changed during query execution.

query = f'''SELECT supermarket, food, counter
            FROM ( SELECT supermarket, food, 
                          @counter:=IF(@supermarket_id=supermarket,@counter+1,1) counter, 
                          @supermarket_id:=supermarket
                   FROM supermkt_data 
                   CROSS JOIN (SELECT @supermarket_id:=0, @counter:=0) init_vars
                   ORDER BY supermarket, food) tmp
            WHERE counter <= 10;
         '''

Adjust starting values for variables if needed.