How to call stored procedure using sqlalchemy

Question

How can I call stored procedures of MySQL with sqlAlchemy?

I tried the following code:

import webapp2

from sqlalchemy import *
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import create_session
from sqlalchemy import create_engine

class TestSQL(webapp2.RequestHandler):

def get(self):
    Base = automap_base()

    engine = create_engine(('mysql://[email protected]:3306/testDB?unix_socket=/cloudsql/School=:mysqlserver'))
    # reflect the tables
    Base.prepare(engine, reflect=True)

    metadata = MetaData(bind=engine)
    users = Table('Student', metadata, autoload=True)
    student = metadata.tables ["Student"]

    session = create_session()

    pro = session.execute("p_get_teacher_requests", (1,0))
    self.response.write(pro)

Here the error that I got:

UnboundExecutionError: Could not locate a bind configured on SQL expression or this Session

Answer 1

After some research, and many trails it worked with the following code:

import webapp2

from sqlalchemy import *
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import create_session
from sqlalchemy import create_engine


class TestSQL(webapp2.RequestHandler):
    def get(self):
        self.response.headers["Content-Type"] = "application/json"

        Base = automap_base()

        engine = create_engine(('mysql://root@IP:3306/School?unix_socket=/cloudsql/school:mysqlserver'))

        # reflect the tables
        Base.prepare(engine, reflect=True)

        metadata = MetaData(bind=engine)

        users = Table('Student', metadata, autoload=True)
        student = metadata.tables ["Student"]

        session = create_session()

        connection = engine.raw_connection()
        cursor = connection.cursor()
        cursor.callproc("p_get_teacher_requests", [1,0])
        results = list(cursor.fetchall())
        cursor.close()
        connection.commit()

        self.response.write(results)