Create Mysql Storec Procedure via Python using Mysql Connector

Question

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.

Answer 1

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()

Answer 2

A couple of suggestions to the code snippet:

1. Remove the DELIMITER statement because not all MySQL clients support DELIMITER statement e.g. mysql-connector-python.
2. multi option should be enabled for multiple SQL statements.
3. iterate the returned generator to ensure that results from the server are handled

for result in cursor.execute('''
DROP PROCEDURE IF EXISTS usp_myprocedure;


CREATE PROCEDURE usp_myprocedure()
BEGIN
  TRUNCATE TABLE test;
  INSERT INTO test
    SELECT id, CASE WHEN id=1 THEN 2 ELSE 2 END AS val
    FROM test2;
  SELECT * FROM test;
END
''', multi=True):
    pass

result = cursor.execute('''
CALL usp_myprocedure();
''')
print(result.fetchall())