CODEWITHSUNDEEP
pythonmysqlsqlalchemyflask-sqlalchemy
Ratha
Ratha

Reputation: 9692

why the stored procedure called from sqlalchemy is not working but calling from workbench is working?

I have a stored procedure.

calling it via MySQL workbench as follows working;

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

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 )

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

I call it like;

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?

Upvotes: 2

Views: 2354

Answers (1)

Kirk
Kirk

Reputation: 1845

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?

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()

Upvotes: 3

Related Questions