What is the best approach to use stored procedures in Django?

Using Django
1

Hello,

i want to build a Django Application which is using stored procedures for saving and deleting the data in the database and using views for reading the data. At the moment i override the save and delete method in the model class like this:

def save(self, *args, **kwargs):
    with connection.cursor() as cursor:
        print(connection['DATABASES'])
        if self.mdt_id:
            query = "SELECT * FROM fnc_fdb_mandator_update(ROW(%s,%s,%s,%s,%s,%s,%s))"
        else:
            query = "SELECT * FROM fnc_fdb_mandator_insert(ROW(%s,%s,%s,%s,%s,%s,%s))"

        cursor.execute(query,
                       [self.mdt_id,
                        self.mdt_guid,
                        self.mdt_name,
                        self.mdt_insert_date,
                        self.mdt_insert_user,
                        self.mdt_update_date,
                        self.mdt_update_user])
        row = cursor.fetchone()
        self.mdt_id = row[0]
        self.mdt_guid = row[1]
        self.mdt_name = row[2]
        self.mdt_insert_date = row[3]
        self.mdt_insert_user = row[4]
        self.mdt_update_date = row[5]
    return True

def delete(self, *args, **kwargs):
    with connection.cursor() as cursor:
        query = "SELECT * FROM fnc_fdb_mandator_delete(%s)"

        cursor.execute(query,
                       [self.mdt_id])
        row = cursor.fetchone()
        return row[0]

This approach works. But i read a little bit more and find out that the save method makes much more, like pre and post events. And after i had a look into the source code from Django, i’am not sure that my approach is good. I saw that the Django model using a manager and maybe it would be better to write an own manager?!

Has anybody experience with this? What would be the best approach for this problem?

2

<opinion>
Don’t try to mix the ORM with Stored Procedures.

If you absolutely must use a Stored Procedure, then use plain SQL like you’ve been doing, but don’t even do it in the context of a Model - strictly speaking, it’s not related to a model instance.
If I still needed to use any SPs, I’d create a utility function to keep that call separate, and then if necessary create an interface layer in the model that could call it.

Maybe it’s just an overabundance of caution on my part, but I would be really worried about mixing the two too closely.

For example, notice that the documentation specifically states that when you override save, you should still call super in your method. Also note that it states that bulk operations don’t call save(), which means that a bulk update would try to write to the table directly. This implies that you would also need to create a model manager to handle those situations.

To make sure I had control over this, I’d probably go so far as to make that table read-only - and possibly even create a read-only view and associate that with the model. (What I have done in a couple of cases, is create a custom router that throws an error on any write-attempt. Yes, for data-integrity purposes, I am a “belt and suspenders” kind of guy.)
</opinion>

Ken

1 Like
3

Thank you very much for your suggestions. I played a little bit around with it but i am not firm enough with Django and would take too much time to realize it secure in this way. I think i will try it another time.

I decided now to use another framework where it is easier to have only the GUI part and everything else in the database. I use now OpenXava with JPA.

But thanks again for your help!