Occasionally 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
UPDATE – It has been pointed out to me that I did not mention which version of SQLAlchemy this pertains to. I believe this hint for SQLAlchemy was written for 1.4.7
Hey Justin, i came across your blog when searching for “Execute multiple stored procedure Python sqlalchemy”…. I was wondering if you have any insights to that… reason because I ran multiple stored procedure…
eg.
try:
connection = DB_CONN.raw_connection()
cursor = connection.cursor()
cursor.execute(“EXEC dbo.sp_abc1”)
cursor.execute(“EXEC dbo.sp_abc2”)
cursor.execute(“EXEC dbo.sp_abc3”)
cursor.close()
connection.commit()
print(“Commit done”)
finally:
connection.close()
Very odd that sometimes one of the stored procedure wasn’t executed. It works if I execute them one by one.
Does a commit needs to happen after each stored procedure?
Hi, cursor.execute may not always be the best way to go when you want to execute multiple commands as one commit. You may want to consider using a session or connection. You can then add the various transactions directly to the session or connection and then commit them. You can see these patterns demonstrated in the documentation for sqlalchemy here: https://docs.sqlalchemy.org/en/20/orm/session_transaction.html
Thanks for doing this!
Suggest you state whether this is SQLAlchemy 2 or prior (I believe it’s prior?), since things changed dramatically….
I have this is SQLAlchemy 1.4:
query_result = DB.engine.execute(sql_query, location=location)
result = query_result.fetchall()
response = {“result” : list(result)}
Note the named parameter… I am still searching for the SQLAlchemy2 equivalent
Hi Val,
you are indeed correct that this was written for SQLAlchemy 1.x. I have not had to handle this issue with SQLAlchemy 2, however reading the latest docs for the cursor class https://www.psycopg.org/docs/cursor.html#cursor.callproc it does appear that what I achieved with callproc may now be handled with call. It is possible that this approach buried in a comment on StackOverflow https://stackoverflow.com/questions/67689027/unable-to-call-stored-procedures-using-sqlalchemy may help you (though I have not tested it). If the comment is right, the correct syntax may be: connection.execute(‘CALL transfer (?, ?, ?)’, [1,2,1000])