pymysql(0.9.2) + mysql(5.7.23 ) 调用存储过程,获取输出参数有时为空

Question

os : 16.4,
python : 3.6,
pymysql : 0.9.2,
mysql : 5.7.23

调用存储过程,获取输出参数有时为空
存储过程为

CREATE DEFINER=`root`@`%` PROCEDURE `P_TEST`(OUT aCode varchar(4), OUT aMsg varchar(16), IN aAppName varchar(16))
    COMMENT '测试'
BEGIN
    set aCode = '1';
    set aMsg = '错误信息';
        select aAppName;
  END

python端代码为:

def executeProc(aProcName, aParams):
    tmpDbConn = None
    tmpCursor = None
    try:
        tmpListData = list(aParams)
        tmpListData.insert(0, '')
        tmpListData.insert(0, '')
        aParams = tuple(tmpListData)
        print(aProcName, aParams)

        tmpDbConn = DBPools.connection()
        tmpCursor = DBPools.connection().cursor()
        tmpCursor.callproc(aProcName, aParams)
        tmpDatas1 = tmpCursor.fetchall()
        print(tmpDatas1)
        tmpCursor.execute('select @_%s_0, @_%s_1 ;' % (aProcName, aProcName))
        tmpDatas2 = tmpCursor.fetchall()
        print(tmpDatas2)
        code = tmpDatas2[0][0]
        msg = tmpDatas2[0][1]
        tmpCursor.close()
        tmpDbConn.close()
        return (code, msg, tmpDatas1)
    except InternalError as e:
        print(e)
        return (sqlServerInternalError, all_code[sqlServerInternalError])
    except ProgrammingError as e:
        print(e)
        return (sqlServerProgrammingError, all_code[sqlServerProgrammingError])
    except InterfaceError as e:
        print(e)
        return (sqlServerConnectFail, all_code[sqlServerConnectFail])
    except OperationalError as e:
        print(e)
        return (sqlServerInterfaceError, all_code[sqlServerInterfaceError])
    except Exception as e:
        print(e)
        return (sqlServerException, all_code[sqlServerException])
    finally:
        if tmpCursor:
            tmpCursor.close()
        if tmpDbConn:
            tmpDbConn.close()

if __name__ == "__main__":
    for i in range(100):
        executeProc('P_TEST', ('a'))

测试结果为:

P_TEST ('', '', 'a')
(('a',),)
(('1', '错误信息'),)
P_TEST ('', '', 'a')
(('a',),)
((None, None),)
P_TEST ('', '', 'a')
(('a',),)
((None, None),)
P_TEST ('', '', 'a')
(('a',),)
(('1', '错误信息'),)
P_TEST ('', '', 'a')
(('a',),)
(('1', '错误信息'),)

这种情况只是偶尔出现,单独调用存储过程也是没有问题的,请大佬帮忙解决一下,谢谢!

Answer 1

来个大佬,帮忙解决一下啊,别凉了啊
数据库是 utf-8的,我在存储过程中指定编码,问题还是存在

CREATE DEFINER=`root`@`%` PROCEDURE `P_TEST`(OUT aCode varchar(4), OUT aMsg varchar(16) CHARACTER SET UTF8  , IN aAppName varchar(16))
    COMMENT '测试'
BEGIN
    set aCode = '1';
    set aMsg = '错误信息';
        select aAppName;
  END

=----------------=-------------------=-----------------------
自己来结一下,最后发现不是pymysql 模块的问题,是DButils的锅,我的天,把它去了就解决了!