Calling several SQL procedures at once with Python-CodePudding

I have made a stored procedure in MySQL which accepts several arguments and does its things. And while I have no problem to execute the following query in MySQL

CALL my_pr(var1, var2, var3); CALL my_pr(var4, var5, var6);

When I try to execute it (or any other 2 statements at once) via Python I get the following error:

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

But when I am trying to execute them one by one - everything works smoothly.

I am adding each statement to a list and then execute it via:

    for stm in sql_stms:
        mycursor.execute(stm)
        mydb.commit()

Where I set each stm to be a single query or a multiple statement query in some code above. And my sql_stms contain several INSERT, SELECT and DELETE queries and tens (or sometimes hundreds) of queries for a stored procedure.

My goal is to speed up the running process and currently the slowest part in my code is submitting queries to SQL, so I believe that when I submit multiple queries at once it will work slightly faster.

Any ideas and suggestions are welcomed.

CodePudding user response：

Probably not expecting more than one resultSet, try setting the multi result to true before executing

mycursor = mydb.cursor(multi=True)

CodePudding user response：

The interface is not designed to easily get two "result sets" at once.

There is very little advantage in trying to run two statements together. Simply run them one at a time.

You can, on the other hand, build a third SP that makes those two CALLs. But, again, why bother.