Creating Stored Procedures with SQLAlchemy

Question

Hi,

I am writing a python script to create the postgres database using SQLAlchemy. I also want to create Stored Procedures by same way. I checked the SQL Alchemy Documentations but was not able to find whether I can create stored procedure using it or not. Is it Possible to do so? any Tutorials/Examples would help. i found some examples of how to call SP using SQLAlchemy but not about how to create.

Thanks in advance. Tara Singh

Answer 1

You can create stored procedures (actually, execute any valid SQL statement) by using sqlalchemy.sql.expression.text construct:

t = text("SELECT * FROM users WHERE id=:user_id")
result = connection.execute(t, user_id=12)

But this will be more of an appendix to the SQLAlchemy rather than designed usage.
Also this cannot be done in a DMBS-independent way, which is one of the benefits using ORM tools like SQLAlchemy.
If your aim is to version-control your database schema, you still can use it, but you need to take complete control this process and handle things like dependencies between stored procedures, UDFs, views and create/drop them in the proper order.