mysql 同时执行多个存储,MySQL存储过程，熊猫和“执行多个语句时使用multi = True”...

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

解决方案

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 =

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.