How do I execute a MySQL stored procedure in a sqlalchemy scoped session to return a single result set of data (for flask web app)?

Question

Most the examples I've found to call a MySQL stored proc and store results in python use the callproc method with a cursor, but the callproc method doesn't exist on a scoped_session object created with sqlalchemy. I'm using a scoped_session because I'm building a flask app that will use the session in different parts of the app (the scoped session works for simple select statement). The stored procedure makes one select and returns data. I'm using mysql.connector for the mysql driver.

I've tried the following inside of a flask route but I'm getting an error:

mysql_engine = create_engine(conn_string)
DbSession = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=mysql_engine))

@app.route('/')
def index():
    # register session
    DbSession()

    sql = 'call myStoredProc(:param);'

    # call stored procedure: getting error "Use multi=True when executing multiple statements"
    result = DbSession.execute(sql, {'param': 'param value'})
    data = [dict(r) for r in result]

    # remove session from register
    DbSession.remove()

    # pass data to template to render
    return render_template('index.html', data = data)

As shown in the code, I'm getting this error: "Use multi=True when executing multiple statements." I've learned that the mysq.connector assumes an out parameter for stored procedures so by default thinks it is a multi-statement even though the stored proc is simply running a single select query. The execute method on the DbSession does not accept options.

The suggestion in the error is to use cmd_query_iter for multiple statements but that also doesn't exist on a DbSession object.

Any suggestions on how to do this?

Somewhere along the way in my research I got the idea using a session was necessary for web apps when handling multiple requests. Is there another way? — moki, Oct 10 '20 at 17:15
you have a connection pool and you "grab" one por every communication, ok that normal procedure for multiple clients, but in your approach you establish one session in which you bind a mysql connection from the same pool, and you have to use rawconnection like described in the answer. i don't see , because every connection is already isolated, with his own mysql session, why you need another isolation level — nbk, Oct 10 '20 at 17:32
Thanks. I am starting to doubt if I need a scoped_session for my purpose. I may start moving towards using a connection pool. Curious what you might say about what the main use case of a scoped_session is. — moki, Oct 10 '20 at 17:50

score 2 · Accepted Answer · answered Oct 10 '20 at 11:08

SQLAlchemy doesn't, as far as I know, support calling stored procedures directly. The docs recommend using the raw DB-API connection's callproc method.

The connection can be accessed from the engine; it can also be accessed via a session, but this is still goes through the engine.

The example code below shows both methods. Note that the method of accessing the result of calling the procedure may vary depending on the parameters and the connector used - see this answer for some examples.

import mysql.connector

import sqlalchemy as sa
from sqlalchemy import orm

# Setup the database

DATA = [(1, 2, 3), (4, 5, 6), (7, 8, 9)]

DDL1 = """\
CREATE TABLE IF NOT EXISTS test_table (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  a INT,
  b INT,
  c INT)
"""

DDL2 = """\
CREATE PROCEDURE IF NOT EXISTS test_procedure (IN p1 INT)
  BEGIN
    SELECT a, b, c FROM test_table
    WHERE a > p1;
  END
"""

DML1 = """DELETE FROM test_table"""

DML2 = """INSERT INTO test_table (a, b, c) VALUES (%s, %s, %s)"""

CALL1 = """CALL test_procedure(:param)"""

conn = mysql.connector.connect(database='test')
cur = conn.cursor()
cur.execute(DDL1)
cur.execute(DDL2)
cur.execute(DML1)
for row in DATA:
    cur.execute(DML2, row)
conn.commit()
conn.close()


# Call the procedure

engine = sa.create_engine('mysql+mysqlconnector:///test')
Session = orm.scoped_session(orm.sessionmaker(autocommit=False, autoflush=False, bind=engine))
session = Session()

raw_conn = session.connection().engine.raw_connection()
cur = raw_conn.cursor()
cur.callproc('test_procedure', [1])
print('Using session')
for result in cur.stored_results():
    print(result.fetchall())
Session.remove()

print('Using engine directly')
raw_conn = engine.raw_connection()
cur = raw_conn.cursor()
cur.callproc('test_procedure', [1])
for result in cur.stored_results():
    print(result.fetchall())

One final observation: it seems to me that the raw connection accessed via the session is outside of the session's transaction context, so changes made using the session might not be visible to changes made using the connection, depending on transaction isolation settings.

this has basically nothing to do with the question. the user has a scoped_session and binds a mysql engine to it, and now asks how he can access the functions of that engine. I already ask for clarification, why he nedds scoped_session at all. i searched alreadyfor sqllite in hope someone has done something with it, but n luck — nbk, Oct 10 '20 at 12:08
@nbk I don't understand how this answer has "nothing to do with the question"? The OP has asked how they can call a stored procedure, given a session as a starting point, and the answer demonstrates how a way to do it (whether a session, or even SQLAlchemy itself, is the right tool for the job is certainly a good question, but that isn't what the OP asked). — snakecharmerb, Oct 10 '20 at 12:29
@snakecharmerb this is helpful, thank you. When establishing a connection from the session or from the engine directly, you would be creating a new connection for each web request, right? Is this the best way to do web apps? Perhaps the session is doing the same anyway. I wonder if a pool is the way to go? — moki, Oct 10 '20 at 17:37
@moki SQLAlchemy creates a connection pool by default when the engine is created, so generally the session or engine will get a connection from the pool. See [pooling](https://docs.sqlalchemy.org/en/13/core/engines.html#pooling). If you are using flask it might be worth using the flask-sqlalchemy package as well, as it will handle the mechanics of setting up sessions for requests. — snakecharmerb, Oct 10 '20 at 18:16
@moki [this](https://docs.sqlalchemy.org/en/13/orm/session_basics.html#session-faq-whentocreate), on sessions, is also worth reading if you haven't done so already. — snakecharmerb, Oct 11 '20 at 07:37

How do I execute a MySQL stored procedure in a sqlalchemy scoped session to return a single result set of data (for flask web app)?

1 Answers1