Keeping ORM with stored procedures

Question

Hello,

I am developing a Python web app using sqlalchemy to communicate with mysql database. So far I have mostly been using sqlalchemy's ORM layer to speak with the database. The greatest benefit to me of ORM has been the speed of development, not having to write all these sql queries and then map them to models.

Recently, however, I've been required to change my design to communicate with the database through stored procedures. Does any one know if there is any way to use sqlalchemy ORM layer to work with my models through the stored procedures? Is there another Python library which would allow me to do this?

The way I see it I should be able to write my own select, insert, update and delete statements, attach them to the model and let the library do the rest. I've gone through sqlalchemy's documentation multiple times but can't seem to find a way to do this.

Any help with this would be great!

Answer 1

SQLAlchemy doesn't have any good way to convert inserts, updates and deletes to stored procedure calls. It probably wouldn't be that hard to add the capability to have instead_{update,insert,delete} extensions on mappers, but no one has bothered yet. I consider the requirement to have simple DML statements go through stored procedures rather silly. It really doesn't offer anything that you couldn't do with triggers.

If you can't avoid the silliness, there are some ways that you can use SQLAlchemy to go along with it. You'll lose some of the ORM functionality though. You can build ORM objects from stored procedure results using query(Obj).from_statement(text("...")), just have the column labels in the statement match the column names that you told SQLAlchemy to map.

One option to cope with DML statements is to turn autoflush off and instead of flushing go through the sessions .new, .dirty and .deleted attributes to see what has changed, issue corresponding statements as stored procedure calls and expunge the objects before committing.

Or you can just forgo SQLAlchemy state tracking and issue the stored procedure calls directly.