python 无法执行mysql存储过程中的函数更新
CREATE DEFINER=`root`@`localhost` PROCEDURE `link_check`(IN xlh VARCHAR(64))
BEGIN
DECLARE xlhExist INT;
DECLARE jid INT;
DECLARE jcode INT;
DECLARE jmsg VARCHAR(256);
SELECT COUNT(id) , id INTO xlhExist, jid FROM table_client_state WHERE xuliehao=xlh;
IF xlhExist THEN
/*存在的话,是否超时*/
SELECT "", 0 INTO jmsg, jcode;
ELSE
/*不存在,*/
SELECT "", 2 , getRSId(1) INTO jmsg, jcode, jid;
END IF;
SELECT jid AS id, jmsg AS msg, jcode AS CODE;
/*写入日志*/
INSERT INTO table_log (xuliehao, random_service_id, msg )VALUE(xlh, jid, jmsg);
END$$
DELIMITER ;
getRSId(1)函数和 INSERT INTO没有执行。
CREATE DEFINER=`root`@`localhost` FUNCTION `getRSId`(aid BIGINT(20)) RETURNS BIGINT(20)
BEGIN
DECLARE jid BIGINT;
UPDATE table_admin SET rs_id=(@jid:=rs_id+1) WHERE id=aid;
RETURN @jid;
END$$
DELIMITER ;
db = pymysql.connect(self.host, self.user, self.pw, self.dbname, charset='utf8')
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
sql = 'CALL link_check("%s");'%key
# 使用 execute() 方法执行 SQL 查询
cursor.execute(sql)
# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()
print("data : %s " % data)
# 关闭数据库连接
cursor.close()
db.close()