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 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.

Just execute procedure object created with func:

from sqlalchemy import create_engine, func
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite://', echo=True)
print engine.execute(func.upper('abc')).scalar() # Using engine
session = sessionmaker(bind=engine)()
print session.execute(func.upper('abc')).scalar() # Using session

The easiest way to call a stored procedure in MySQL using SQLAlchemy is by using callproc method of Engine.raw_connection(). call_proc will require the procedure name and parameters required for the stored procedure being called.

def call_procedure(function_name, params):
       connection = cloudsql.Engine.raw_connection()
       try:
           cursor = connection.cursor()
           cursor.callproc(function_name, params)
           results = list(cursor.fetchall())
           cursor.close()
           connection.commit()
           return results
       finally:
           connection.close()