InterfaceError: no result to retrieve. with python and mysql.connector

Question

I prepared a stored procedure that works fine if I make a call from the MySQL console. But when working in python with the mysql.connector controller, the insert procedure is performed correctly.

However, the result does not bring with fectchall() , because the following error:

 File "/home/sis1/prueba/prueba.py", line 16, in <module> reg=conn.fetchall() File "/usr/lib/pymodules/python2.7/mysql/connector/cursor.py", line 551, in fetchall raise errors.InterfaceError("No result set to fetch from.") InterfaceError: No result set to fetch from.` 

Here's the stored procedure:

 DROP PROCEDURE IF EXISTS pr_prueba; CREATE DEFINER = rooter@localhost PROCEDURE pr_prueba(IN p_emp tinyint,OUT mensaje varchar(50),OUT registros integer) BEGIN DECLARE numreg INT (10); DECLARE tabla VARCHAR (30); DECLARE emp TINYINT(2); SET @tabla = CONCAT("emp",p_emp,".usuario"); SET @emp = CAST(p_emp AS UNSIGNED); SET @sql_text = CONCAT("INSERT INTO ",@tabla," ( name, lastname ) (SELECT UPPER(name), UPPER(lastname) FROM tablas GROUP BY tablas.operador);"); PREPARE stmt FROM @sql_text; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @mensaje="OK"; SET @sql_text = CONCAT("SELECT COUNT(*) INTO @numreg FROM ",@tabla,";"); PREPARE stmt FROM @sql_text; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @ registros=@numreg ; SELECT @mensaje as mensaje, @registros as registros; END ; 

Here's the python code:

 import sys import mysql.connector if (__name__=='__main__'): db = mysql.connector.connect(host="192.168.1.1",user="de",passwd="de2",database="dbim" ) conn = db.cursor() args=(1,"",0) conn.callproc("pr_prueba",args) reg=conn.fetchall() try: db.commit() except: db.rollback() print "error" conn.close() db.close() 

Answer 1

I found the problem myself. I had to change the line:

 reg=conn.fetchall() 

for this:

 for reg in conn.next_proc_resultset(): pass 

I don't know if this is the best solution, but it works