Question 21048 in Mysql Question posted in
The official MySQL documentation can be found here.

Calling several SQL procedures at once with Python – Mysql

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.

Tags:

2

Answers


  1. 0

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

    mycursor = mydb.cursor(multi=True)
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search