Saving ORM with stored procedures

Question

I am developing a Python web application using sqlalchemy to communicate with a mysql database. So far, I have mainly used the sqlalchemy ORM level to talk to the database. The biggest benefit for me of ORM is development speed, without having to write all these sql queries and then map them to models.

However, recently I had to change my design in order to communicate with the database using stored procedures. Does anyone know if there is a way to use the sqlalchemy ORM layer to work with my models using stored procedures? Is there another Python library that would allow me to do this?

As I see it, I should be able to write my own select, insert, update, and delete commands, attach them to the model, and let the library do the rest. I have looked through the sqlalchemy documentation repeatedly but cannot find a way to do this.

Any help with this would be great!

Answer 1

SQLAlchemy has no good way to convert insert, update, and delete stored procedure calls. It would probably not have been so difficult to add the ability to add {{update, insert, delete} extensions to mappers instead, but so far no one has bothered. I find it necessary that simple DML instructions go through stored procedures rather stupidly. It really does not offer anything that you could not do with triggers.

If you cannot avoid stupidity, there are several ways you can use SQLAlchemy to go along with this. However, you will lose some ORM features. You can create ORM objects from the results of the stored procedure using the query (Obj) .from_statement (text ("...")), just so that the column labels in the expression match the column names that you specified in SQLAlchemy for matching.

One way to handle DML operations is to turn off autorun and instead of clearing, run the .new, .dirty, and .deleted sessions to see what has changed, return the corresponding statements as calls to stored procedures, and unload objects before committing.

Or you can simply refuse to monitor SQLAlchemy status and directly call stored procedure calls.