MySQL Stored Procedures, Pandas, and "Use multi=True when executing multiple statements"

Question

Note - as MaxU suggested below, the problem is specific to mysql.connector and does not occur if you use pymysql. Hope this saves someone else some headaches

Using Python, Pandas, and mySQL and cannot get a stored procedure to return results at all, let alone into a data frame.

I keep receiving errors about multiple queries, but the stored procedures I am running are extremely simple parameter driven queries.

It doesn't matter what stored procedure I use, it is always the same result

In fact, the test procedure below (sp_test) is the following query -

select * from users;

If I run the same statement with

df=pd.read_sql("select * from users", cnx,index_col=None, coerce_float=True)

Instead of

df=pd.read_sql("call sp_test()", cnx,index_col=None, coerce_float=True)

It works fine, even though sp_test is select * from users

Why do I keep getting multi=true error messages and how do I go about fixing the problem and getting the results of my stored procedure? I don't understand how a simple select statement would return multiple result sets.

If there is another way to do this, happy to try it.

Following is the simple code I am using

import pandas as pd
from pandas.io.data import DataReader
from pandas import DataFrame
import mysql.connector

cnx = mysql.connector.connect(user='jeff', password='password', database='testdatabase', host='xx.xxx.xxx.xx')
df=pd.read_sql("call sp_test()", cnx,index_col=None, coerce_float=True)

When I get to pd.read_sql, I get the following error message

InterfaceError                            Traceback (most recent call last)
C:\Users\User\AppData\Local\Continuum\Anaconda3\lib\site-    packages\mysql\connector\cursor.py in execute(self, operation, params, multi)
    506             try:
--> 507                 self._handle_result(self._connection.cmd_query(stmt))
    508             except errors.InterfaceError:

C:\Users\User\AppData\Local\Continuum\Anaconda3\lib\site-packages\mysql\connector\connection.py in cmd_query(self, query)
725             raise errors.InterfaceError(
--> 726                 'Use cmd_query_iter for statements with multiple queries.')
727 

InterfaceError: Use cmd_query_iter for statements with multiple queries.

During handling of the above exception, another exception occurred:

InterfaceError                            Traceback (most recent call last)
C:\Users\User\AppData\Local\Continuum\Anaconda3\lib\site-    packages\pandas\io\sql.py in execute(self, *args, **kwargs)
   1563             else:
-> 1564                 cur.execute(*args)
   1565             return cur

C:\Users\User\AppData\Local\Continuum\Anaconda3\lib\site-    packages\mysql\connector\cursor.py in execute(self, operation, params, multi)
    510                     raise errors.InterfaceError(
--> 511                         "Use multi=True when executing multiple statements")
    512                 raise

InterfaceError: Use multi=True when executing multiple statements

Answer 1

Hopefully this will be of some help. Using some concept pointers from here, and a little trial / error, I was able to make this work using mysql.connector and pandas.

# CONNECT TO DB AND GET CURSOR OBJECT
conn = <do db connecty stuff>
cur = conn.cursor()

# CALL THE STORED PROCEDURE
cur.callproc('stored_proc_name', ['my', 'usp', 'parameters'])

# EXTRACT RESULTS FROM CURSOR
for i in cur.stored_results(): results = i.fetchall()

# LOAD INTO A DATAFRAME
df = pd.DataFrame(results, columns=['my', 'column', 'headers'])

This worked perfectly for me ... I hope it does for you too.