stored procedures with sqlAlchemy

Engines and Connections have an execute() method you can use for arbitrary sql statements, and so do Sessions. For example:

results = sess.execute('myproc ?, ?', [param1, param2])

You can use outparam() to create output parameters if you need to (or for bind parameters use bindparam() with the isoutparam=True option)

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 wellresults = conn.cursor().callproc('my_proc_with_one_out_param', [0])conn.close()   # commitprint(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 resultresult = _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.

Just execute procedure object created with func:

from sqlalchemy import create_engine, funcfrom sqlalchemy.orm import sessionmakerengine = create_engine('sqlite://', echo=True)print engine.execute(func.upper('abc')).scalar() # Using enginesession = sessionmaker(bind=engine)()print session.execute(func.upper('abc')).scalar() # Using session