Using SQLAlchemy to Run SQL Procedures

Some pandas doing alchemy leading to moving procedural gearsOccasionally you may want to invoke a stored procedure from your python code in order to manipulate data as part of a larger task. Naively you might think that since SQLAlchemy allows you to run SQL commands you could simply run a command in the normal SQLAlchemy, and for simple procedures with no parameters, this can work.

 
# Naive method using usual SQLAlchemy syntax
query = """CALL storedProcedure();"""
stmt = text(query)
result = self.connection.execute(stmt)

Unfortunately this is not the case when you need to pass parameters in or out of the procedure. ThenĀ  the way you have to call stored procedures in SQLAlchemy is more complex. However help is at hand. The code below allows for parameter passing.


import pandas as pd
from sqlalchemy import create_engine

# set your parameters for the database
user = "user"
password = "password"
host = "abc.efg.hij.rds.amazonaws.com"
port = 3306
schema = "db_schema"

# Connect to the database
conn_str = 'mysql+pymysql://{0}:{1}@{2}:{3}/{4}?charset=utf8mb4'.format(
    user, password, host, port, schema)
db = create_engine(conn_str, encoding='utf8')
connection = db.raw_connection()

# define parameters to be passed in and out
parameterIn = 1
parameterOut = "@parameterOut"
try:
    cursor = connection.cursor()
    cursor.callproc("storedProcedure", [parameterIn, parameterOut]
    # fetch result parameters
    results = list(cursor.fetchall())
    cursor.close()
    connection.commit()
finally:
    connection.close() 

And there you have it, a stored procedure called from SQLAlchemy. For other ways to achieve this aim you could look at this useful thread on Stack Overflow. If you are not familiar with writing SQL queries, a good place to start might be this tutorial on BrainBell

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.