• 3
name

A PHP Error was encountered

Severity: Notice

Message: Undefined index: userid

Filename: views/question.php

Line Number: 191

Backtrace:

File: /home/prodcxja/public_html/questions/application/views/question.php
Line: 191
Function: _error_handler

File: /home/prodcxja/public_html/questions/application/controllers/Questions.php
Line: 433
Function: view

File: /home/prodcxja/public_html/questions/index.php
Line: 315
Function: require_once

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?

We (musicpictures.com / eviscape.com) wrote that django snippet but its not the whole story (actually that code was only tested on Oracle at that time).

Stored procedures make sense when you want to reuse tried and tested SP code or where one SP call will be faster than multiple calls to the database - or where security requires moderated access to the database - or where the queries are very complicated / multistep. We're using a hybrid model/SP approach against both Oracle and Postgres databases.

The trick is to make it easy to use and keep it "django" like. We use a make_instance function which takes the result of cursor and creates instances of a model populated from the cursor. This is nice because the cursor might return additional fields. Then you can use those instances in your code / templates much like normal django model objects.

def make_instance(instance, values):
    '''
    Copied from eviscape.com

    generates an instance for dict data coming from an sp

    expects:
        instance - empty instance of the model to generate
        values -   dictionary from a stored procedure with keys that are named like the
                   model's attributes
    use like:
        evis = InstanceGenerator(Evis(), evis_dict_from_SP)

    >>> make_instance(Evis(), {'evi_id': '007', 'evi_subject': 'J. Bond, Architect'})
    <Evis: J. Bond, Architect>

    '''
    attributes = filter(lambda x: not x.startswith('_'), instance.__dict__.keys())

    for a in attributes:
        try:
            # field names from oracle sp are UPPER CASE
            # we want to put PIC_ID in pic_id etc.
            setattr(instance, a, values[a.upper()])
            del values[a.upper()]
        except:
            pass

    #add any values that are not in the model as well
    for v in values.keys():
        setattr(instance, v, values[v])
        #print 'setting %s to %s' % (v, values[v])

    return instance

# Use it like this:

pictures = [make_instance(Pictures(), item) for item in picture_dict]

# And here are some helper functions:

def call_an_sp(self, var):
    cursor = connection.cursor()
    cursor.callproc("fn_sp_name", (var,))
    return self.fn_generic(cursor)


def fn_generic(self, cursor):
    msg = cursor.fetchone()[0]
    cursor.execute('FETCH ALL IN "%s"' % msg)
    thing = create_dict_from_cursor(cursor)
    cursor.close()
    return thing

def create_dict_from_cursor(cursor):
    rows = cursor.fetchall()
    # DEBUG settings (used to) affect what gets returned. 
    if DEBUG:
        desc = [item[0] for item in cursor.cursor.description]
    else:
        desc = [item[0] for item in cursor.description]
    return [dict(zip(desc, item)) for item in rows]    

cheers, Simon.

  • 28
Reply Report
    • i found this comment by russ magee: "We have specifically avoided adding obvious SQL-like features to Django's ORM, because at the end of the day, we're not trying to replace SQL - we're just trying to provide a convenient way to express simple queries. It is fully expected that you will fall back to just calling raw SQL for complex cases"
      • 1
    • I work on an immense system with a database that is accessed by multiple applications, some c++, some python, some perl, some php, some web are based, many are not. I love it when the business logic is in SPs because it means the logic is consistent across all the implementations, and in our case at least, makes maintenance much easier.

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.

  • 2
Reply Report
      • 2
    • @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.
      • 2
    • @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.
      • 2
    • @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.
      • 2
    • @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.
    • @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.

Cx_Oracle can be used. Also, It is fairly helpful when we do not have access to production deployed code and need arises to make major changes in database.

import cx_Oracle
try:
    db = dev_plng_con
    con = cx_Oracle.connect(db)
    cur = con.cursor()
    P_ERROR = str(error)
    cur.callproc('NAME_OF_PACKAGE.PROCEDURENAME', [P_ERROR])

except Exception as error:
    error_logger.error(message)
  • 0
Reply Report

Trending Tags