stored procedures with sqlAlchemy

context: I use flask-sqlalchemy with MySQL and without ORM-mapping. Usually, I use:

# in the init method
_db = SqlAlchemy(app)

#... somewhere in my code ...
_db.session.execute(query)

Calling stored procedures is not supported out of the box: the callproc is not generic, but specific to the mysql connector.

For stored procedures without out params, it is possible to execute a query like

_db.session.execute(sqlalchemy.text("CALL my_proc(:param)"), param='something')

as usual. Things get more complicated when you have out params...

One way to use out params is to access the underlying connector is through engine.raw_connection(). For example:

conn = _db.engine.raw_connection()
# do the call. The actual parameter does not matter, could be ['lala'] as well
results = conn.cursor().callproc('my_proc_with_one_out_param', [0])
conn.close()   # commit
print(results) # will print (<out param result>)

This is nice since we are able to access the out parameter, BUT this connection is not managed by the flask session. This means that it won't be committed/aborted as with the other managed queries... (problematic only if your procedure has side-effect).

Finally, I ended up doing this:

# do the call and store the result in a local mysql variabl
# the name does not matter, as long as it is prefixed by @
_db.session.execute('CALL my_proc_with_one_out_param(@out)')
# do another query to get back the result
result = _db.session.execute('SELECT @out').fetchone()

The result will be a tuple with one value: the out param. This is not ideal, but the least dangerous: if another query fails during the session, the procedure call will be aborted (rollback) as well.

Another workaround:

query = f'call Procedure ("{@param1}", "{@param2}", "{@param3}")'    
sqlEngine = sqlalchemy.create_engine(jdbc)
conn = sqlEngine.connect() 
df = pd.read_sql(query,conn,index_col=None)