MySQL存储过程， pandas 和“在执行多个语句时使用multi = True" [英] MySQL Stored Procedures, Pandas, and "Use multi=True when executing multiple statements"
问题描述
注意-正如下面的MaxU所建议的，该问题特定于mysql.connector，如果使用pymysql则不会发生.希望这可以使其他人免于头痛
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
使用Python，Pandas和mySQL，它们根本无法获得存储过程来返回结果，更不用说将其存储到数据框中了.
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
实际上，下面的测试过程(sp_test)是以下查询-
In fact, the test procedure below (sp_test) is the following query -
select * from users;
如果我使用相同的语句
df=pd.read_sql("select * from users", cnx,index_col=None, coerce_float=True)
代替
df=pd.read_sql("call sp_test()", cnx,index_col=None, coerce_float=True)
即使 sp_test 是从用户中选择*
为什么我不断收到multi = true错误消息，如何解决问题并获得存储过程的结果?我不明白简单的select语句将如何返回多个结果集.
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)
当我进入pd.read_sql时，收到以下错误消息
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
推荐答案
希望这会有所帮助.使用此处的一些概念指针，以及一些尝试/错误，我能够使用mysql.connector和pandas来完成这项工作.
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.
这篇关于MySQL存储过程， pandas 和“在执行多个语句时使用multi = True"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
