我已经准备了一个存储过程,如果我从MySQL控制台进行调用,它可以正常运行。但是当在python中运行mysql.connector
控制器时,正确执行插入过程。
然而,结果并没有带来与fectchall()
,因为以下错误:
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.`
下面是存储过程:
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 ;
下面是Python代码:
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()
2条答案
按热度按时间mfuanj7w1#
我自己发现了问题。我不得不换了句台词:
为此:
我不知道这是否是最好的解决办法,但它的工作
qkf9rpyu2#
我有这样的代码:
connection.cursor()
被调用两次。我把它改成:而且成功了