CODEWITHSUNDEEP
pythonmysqlstored-procedures
Echarbeto
Echarbeto

Reputation: 171

InterfaceError: No result set to fetch from. with python and mysql.connector

I have prepared a stored procedure that runs fine if I make the call from a MySQL console. But when running in python with mysql.connector controller, performs the insertion procedure 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 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()

Upvotes: 4

Views: 7304

Answers (2)

parsecer
parsecer

Reputation: 5106

I had this code:

        with self.connect_specific() as connection:
            with connection.cursor() as cursor:
                cursor.execute(query)
                result = connection.cursor().fetchall()

the connection.cursor() was called twice. I changed it to:

        with self.connect_specific() as connection:
            with connection.cursor() as cursor:
                cursor.execute(query)
                result = cursor.fetchall()

and it worked!

Upvotes: 0

Echarbeto
Echarbeto

Reputation: 171

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 do not know if it is the best solution but it works

Upvotes: 1

Related Questions