mysql 存储过程 delete_从PYTHON调用MySQL存储过程DELETE时不工作

我有一个MySQL存储过程，它将数据库记录从指定的时间间隔备份到csv文件，然后删除相同的记录。在

当我从phpMyAdmin或MySQL Workbench调用该过程时，它工作得非常好。它创建存档文件，并删除记录。在

但是，当我从Python应用程序中调用存储过程时，它将创建存档文件，但不会删除记录。在

输入和会话变量也会输出到特定的日志文件以进行调试。这一切都很好，我在日志文件中看到了我的期望。在

这是我的存储过程：CREATE DEFINER=`ctuser`@`%` PROCEDURE `sp_TableDump`(

IN db VARCHAR(50),

IN tbl VARCHAR(50),

IN dateCol VARCHAR(20),

IN intrvl VARCHAR(20),

IN allowArchive boolean,

IN allowPurge boolean

)

BEGIN

#I know my input fields are correct because of the log1.log created below

SELECT db, tbl, dateCol, intrvl, allowArchive, allowPurge into outfile '/var/lib/mysql-files/log1.log';

SET @defMaxLength = (SELECT @@group_concat_max_len);#get the default group_concat limit

SET SESSION group_concat_max_len = 1000000;#set the Group Concat Max Limit for tables with a lot of columns

SET @date = CURDATE();

SET @path = CONCAT('/var/lib/mysql-files/', db, '.', tbl, '_ARCHIVE_', @date, '.csv');

SET @tbl = CONCAT(db, '.', tbl);

SET @where = CONCAT(' WHERE ', dateCol, ' < CURDATE() - INTERVAL ', intrvl);

SET @orderBy = CONCAT(' ORDER BY ', dateCol);

#I know my session variables are correct because of the log2.log created below

SELECT @date, @path, @tbl, @where, @orderBy into outfile '/var/lib/mysql-files/log2.log';

IF allowArchive THEN

#I know we get in here because log2.log gets created

#archive the records

#get the columns from the table

SET @cols = (SELECT GROUP_CONCAT(QUOTE(`column_name`)) AS columns FROM information_schema.columns WHERE table_schema = db AND table_name = tbl);

SET @colQry = CONCAT('(SELECT ', @cols, ')');

SET @outfileQry = CONCAT('(SELECT * FROM ', @tbl, @where, @orderBy, ' INTO OUTFILE \'', @path,

'\' FIELDS ENCLOSED BY \'\\\'\' TERMINATED BY \'\t\' ESCAPED BY \'\' LINES TERMINATED BY \'\n\')');

SET @outfileSQL = CONCAT(@colQry, ' UNION ALL ', @outfileQry);

SELECT @cols, @colQry, @outfileQry, @outfileSQL into outfile '/var/lib/mysql-files/log2.log';

PREPARE outStmt FROM @outfileSQL;

EXECUTE outStmt;#This works every time

DEALLOCATE PREPARE outStmt;

END IF;

IF allowPurge THEN

#I know we get in here because log3.log gets created

#delete the records

SET @delSQL = CONCAT('DELETE FROM ', @tbl, @where);

SELECT @delSQL into outfile '/var/lib/mysql-files/log3.log'; # @delSQL looks correct in log3.log

PREPARE delStmt FROM @delSQL;

EXECUTE delStmt; # This does not seem to happen when I run in Python. It works perfectly in MySQL Workbench or phpMyAdmin

DEALLOCATE PREPARE delStmt;

END IF;

SET SESSION group_concat_max_len = @defMaxLength;#reset the default group_concat limit

END

下面是Python函数：

^{pr2}$

我已经用这个把头撞到墙上好几个小时了。任何帮助都将不胜感激。谢谢。在