With MySQL Connector in Python how do I create a stored procedure then use it right away?

Question

Using mysql-connector in python, I'm trying to create a stored procedure and then create another one which calls the first with this code :

def ExecuteQueryFromFile(query_name, path):
  try:
    with open(path, "r") as file:
        sql_script = file.read()
    cursor.execute(sql_script)
    result_set = cursor.fetchall()
  except Exception as e:
    print("An error occurred while executing " + query_name + ": {}".format(e))
    cursor.close()
    sepix_db_conn.close()
    sys.exit(1)
  print(query_name + " completed successfully.")

ExecuteQueryFromFile("sp1.sql", sp1Path)
ExecuteQueryFromFile("sp2.sql", sp2Path)

I hit the exception in my function when my cursor executes the query to create the second stored procedure :

An error occurred while executing sp2.sql: 2014 (HY000): Commands out of sync; you can't run this command now

Because creating a stored procedure does not return any result set I thought mysql was still processing the first stored procedure creation when python shot it the second stored procedure creation (hence why I added the result_set = cursor.fetchall() to ensure the instruction was completed in mysql before moving to the next one) but my attempt failed and I have the same error.

My second attempt involved a sepix_db_conn.commit() followed by cursor.close() and creating a new cursor but instead I'm hitting the same exception.

What is the right way of doing this?

slaakso · Accepted Answer · 2023-09-20 13:13:06Z

1

What is the content of the file? Do you have multiple statements there (for example "drop procedure").

You may have to use

for result in cursor.execute(operation, multi=True):
....

answered Sep 20, 2023 at 13:13

slaakso

9,1452 gold badges19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jimmy Jacques Over a year ago

Yes there are multiple statements; my file started with a drop procedure if exists followed by create procedure and all its content. Your for loop seems to fix my issue; I guess I really needed to iterate through all the results after all.

slaakso Over a year ago

@JimmyJacques The execute function executes commands one at a time, allowing you to fetch possible result sets for each execution.

Collectives™ on Stack Overflow

With MySQL Connector in Python how do I create a stored procedure then use it right away?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related