MySQL, Pandas and "Use multi = True stored procedures when executing multiple statements"

Question

Note. As stated in MaxU, the problem is specific to mysql.connector and does not occur if you use pymysql. Hope this saves someone else from headaches.

Using Python, Pandas, and mySQL cannot force a stored procedure to return results at all, not to mention a data frame.

I keep getting errors regarding multiple queries, but the stored procedures that I run are extremely simple queries with parameters.

It doesn’t matter which stored procedure I use, it is always the same result

In fact, the testing procedure below (sp_test) is the following request -

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

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

, sp_test *

multi = true ? , select .

, .

,

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)

pd.read_sql,

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

, . , /, , mysql.connector 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'])

... , .