Create Mysql Storec Procedure via Python using Mysql Connector

I Am builing a small application. where I Need to create MySQL Stored procedure via python using mysql Connector.

I have tried following code. But getting an following error that

"Commands out of sync; you can't run this command now."

I Am using MySQL Version 5.7.40-43-log

Below is the python code snippet what I have tried.

import mysql.connector

mydb = mysql.connector.connect(host=HOST,user=USERNAME,passwd=PASSWORD,database=DBNAME,port=PORT,allow_local_infile=True)
cursor = mydb.cursor()

sqlquery = """DROP PROCEDURE IF EXISTS usp_myprocedure;

             DELIMITER //
                    
             CREATE PROCEDURE usp_myprocedure()
             BEGIN
                  TRUNCATE TABLE mytable;
                        
                   INSERT INTO mytable
                   SELECT col1
                          ,col2
                          ,col3
                          ,col4
                          ,CASE WHEN New_col='%_%' THEN logic1 
                                                   ELSE logic2 
                                END AS mylogic
                    FROM oldtable;
                        
                    SELECT * FROM mytable;
                        
             END //
                        
             DELIMITER ; 
             """

 cursor.execute(sqlquery)
 mydb.commit()

Above piece of MySQL code is working fine when running via Mysql Workbench. But while executing via python mysql connector getting an error Commands out of sync; you can't run this command now.

Any help would be Appreciated.

This typically happens when you execute stored procedures or multiple statements that generate multiple result sets without fetching all of the rows of the previous result set(-s) - you can adjust your code to fetch all the result sets:

import mysql.connector

mydb = mysql.connector.connect(
    host=HOST, user=USERNAME, passwd=PASSWORD, database=DBNAME, port=PORT, allow_local_infile=True
)
cursor = mydb.cursor()

# define sql query for creating the stored procedure
sqlquery = """
DROP PROCEDURE IF EXISTS usp_myprocedure;
CREATE PROCEDURE usp_myprocedure()
BEGIN
    TRUNCATE TABLE mytable;

    INSERT INTO mytable
    SELECT col1, col2, col3, col4,
        CASE
            WHEN New_col='%_%' THEN logic1
            ELSE logic2
        END AS mylogic
    FROM oldtable;

    SELECT * FROM mytable;
END;
"""

# execute the query to create the stored procedure
cursor.execute(sqlquery)

# commit changes
mydb.commit()

# close the cursor and connection
cursor.close()
mydb.close()