为什么从sqlalchemy调用的存储过程不起作用，但从工作台调用却起作用? [英] why the stored procedure called from sqlalchemy is not working but calling from workbench is working?

问题描述

我有一个存储过程.

通过MySQL工作台调用它，如下所示；

calling it via MySQL workbench as follows working;

CALL `lobdcapi`.`escalatelobalarm`('A0001');

但不是来自python程序. (意味着它不会引发任何异常，进程会以静默方式完成执行)如果我在列名中犯了任何错误，那么在python上我会得到一个错误.因此它调用了我的存储过程，但无法按预期工作. (这是一个更新查询.它需要安全更新)

But not from the python program. (means it is not throwing any exception, process finish execution silently) if I make any error in column names, then at python I get an error. So it calls my stored procedure but not working as expected. (it is an update query .it needs SAFE update )

为什么通过python sqlalchemy此更新没有更新任何记录?

Why through the python sqlalchemy this update didn't update any records?

CREATE DEFINER=`lob`@`%` PROCEDURE `escalatelobalarm`(IN client_id varchar(50))
BEGIN

 SET SQL_SAFE_UPDATES = 0;                                   
update lobdcapi.alarms
    set lobalarmescalated=1
where id in (

    SELECT al.id 
    from (select id,alarmoccurredhistoryid from lobdcapi.alarms where lobalarmpriorityid=1 and lobalarmescalated=0 and clientid=client_id 
            and alarmstatenumber='02' ) as al
    inner join lobdcapi.`alarmhistory` as hi on hi.id=al.alarmoccurredhistoryid
            and hi.datetimestamp<=  current_timestamp() )

);

SET SQL_SAFE_UPDATES = 1;

END

我这样称呼；

from sqlalchemy import and_, func,text


db.session.execute(text("CALL escalatelobalarm(:param)"), {'param': clientid})

我怀疑通过代码传递的参数没有正确绑定?

I suspect the param I pass via code didn't get bind properly?

推荐答案

我尚未从SQLAlchemy调用存储的proc，但是由于您正在使用会话，因此这似乎可能在事务内.也许在最后调用db.session.commit()会有所帮助吗?

I haven't called stored procs from SQLAlchemy, but it seems possible that this could be within a transaction because you're using the session. Perhaps calling db.session.commit() at the end would help?

如果失败，则SQLAlchemy调用调用存储procs .也许尝试使用callproc的方法.适应您的用例，例如:

If that fails, SQLAlchemy calls out calling stored procs here. Perhaps try their method of using callproc. Adapting to your use-case, something like:

connection = db.session.connection()
try:
    cursor = connection.cursor()
    cursor.callproc("escalatelobalarm", [clientid])
    results = list(cursor.fetchall())
    cursor.close()
    connection.commit()
finally:
    connection.close()

这篇关于为什么从sqlalchemy调用的存储过程不起作用，但从工作台调用却起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！