Creating stored procedures using SQLAlchemy

Question

I am writing a python script to create a postgres database using SQLAlchemy. I also want to create Stored Procedures in the same way. I checked SQL Alchemy Documentations, but could not find if I can create a stored procedure using it or not. Is it possible to do this? any tutorials / examples will help. I found some examples of invoking SP using SQLAlchemy, but not about how to create one.

Thanks in advance. Tara Singh

Answer 1

You can create stored procedures (in fact, execute any valid SQL statement ) 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 addition to SQLAlchemy, rather than a designed use.
Also, this cannot be done using the DMBS-independent method, which is one of the benefits of using ORM tools such as SQLAlchemy.
If your goal is to control your database schema, you can still use it, but you need to completely control this process and handle things like dependencies between stored procedures , UDFs , views and create / delete them in the correct order .