What is the best way to access stored procedures in Django's ORM

Question

I am designing a fairly complex database, and know that some of my queries will be far outside the scope of Django's ORM. Has anyone integrated SP's with Django's ORM successfully? If so, what RDBMS and how did you do it?

Solution 1

You have to use the connection utility in Django:

from django.db import connection

with connection.cursor() as cursor:
    cursor.execute("SQL STATEMENT CAN BE ANYTHING")
    data = cursor.fetchone()

If you are expecting more than one row, use cursor.fetchall() to fetch a list of them.

More info here: http://docs.djangoproject.com/en/dev/topics/db/sql/

Solution 2

Don't.

Seriously.

Move the stored procedure logic into your model where it belongs.

Putting some code in Django and some code in the database is a maintenance nightmare. I've spent too many of my 30+ years in IT trying to clean up this kind of mess.

Solution 3

There is a good example : https://djangosnippets.org/snippets/118/

from django.db import connection


cursor = connection.cursor()
ret = cursor.callproc("MY_UTIL.LOG_MESSAGE", (control_in, message_in))# calls PROCEDURE named LOG_MESSAGE which resides in MY_UTIL Package
cursor.close()

Solution 4

If you want to look at an actual running project that uses SP, check out minibooks. A good deal of custom SQL and uses Postgres pl/pgsql for SP. I think they're going to remove the SP eventually though (justification in trac ticket 92).

View more solutions

Comments

[Chad]

What he's asking is about integrating the ORM with SPs. This probably isn't possible, and using stored procedures probably requires you to just access django.db.connection directly like in the other answers, but it would be interesting if you could automatically move common queries the ORM makes into stored procedures, to save on query generation time, and do it transparently, as an optimization. It wouldn't work on every database, and the performance gain probably isn't worthwhile, but it would be fun to investigate.

[user1066101]

@Chad: "What he's asking is about integrating the ORM with SPs." Understood. Hence my answer. SP's fragment your application logic between proper application code and the database. They often create more problems than they solve. I think that SP's are not helpful under any circumstances and should not be used.

[Chad]

@S.Lott I think you misunderstood the point I made. I'm talking about an imaginary/future Django ORM. Stored procedures will not be written by developers. This ORM will dynamically/transparently convert commonly executed ORM queries into stored procedures, so that it can save on SQL string generation time and make use of the pre-compiled nature of SP. Again, I'm not claiming to think this is even possible, or that it would be worth the speedup. Just pointing out an interesting idea his question spawned for me. This approach could leave all the logic in the code and have SP performance.

[user1066101]

@Chad: I think you misunderstood the point I made. I'm talking about all SP's as being uniformly a bad idea. It's not "interesting". It's a mistake. SP's don't magically create high performance.

[Chad]

@S. Lott it isn't "magical". It's faster to generate "EXEC some_sp_name(with, params)" than it is to generate a big SQL statement. You might say, "well thats just strings, it's super fast". Yeah, but if you've peaked into django's ORM SQL generation I think you'd see it's a little more frightening than that. Plus, stored procedures take advantage of the SQL being precompiled, like a parameterized query. I agree that stored procedures totally suck but you have to admit that it's an interesting idea to have the ORM transparently generate them for you instead of generating the SQL every time.

[user1066101]

@Chad: I'm talking about all SP's as being uniformly a bad idea. Uniformly. Overall. The code is now in two places. It never seems to work out well in the long run to fragment the code like that.

[Abdul Saqib]

@S.Lott So after 30 years you can say that Uniformly and Overall stored procedures are a bad idea? Wow that covers lots of cases and I don't buy it for one second. There are many cases out there and I can't imagine you personally can attest to all of them. Just my 2cents. I can come up with many cases they make PERFECT sense and other cases where they don't. Just for the record in some of the cases I can imagine or have worked on I completely agree with you and on others I am on the other side of the universe from your view point.

[Chad]

@S.Lott you're still misunderstanding what I was trying to say. The code is still in ONE place. All of the code only exists in ORM logic in Python. The stored procedures I'm talking about are not written by a programmer. When the ORM notices a certain exact query is being sent a lot, as a micro-optimization, the ORM dynamically creates a new stored procedure and uses that instead of generate SQL each time. No fragmenting code! Developers only write Python code and all of the "benefits" of SPs are gained transparently without having to write any SPs or "fragment" your business logic.