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

by

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.

More Related Contents:

  1. LEFT JOIN Django ORM
  2. How to delete a record in Django models?
  3. How to query as GROUP BY in django?
  4. How do I filter query objects by date range in Django?
  5. Programmatically saving image to Django ImageField
  6. Django FileField with upload_to determined at runtime
  7. how to import csv data into django models
  8. In Django, how does one filter a QuerySet with dynamic field lookups?
  9. How do I clone a Django model instance object and save it to the database?
  10. Django Aggregation: Summation of Multiplication of two fields
  11. How to limit the maximum value of a numeric field in a Django model?
  12. What is the most efficient way to store a list in the Django models?
  13. multiple databases and multiple models in django
  14. Is this the way to validate Django model fields?
  15. Django – How to rename a model field using South?
  16. How do I create a slug in Django?
  17. How to have a Python script for a Django app that accesses models without using the manage.py shell?
  18. How to display uploaded images in “Change List” page in Django Admin?
  19. How to monkey patch Django?
  20. models.py getting huge, what is the best way to break it up?
  21. matching query does not exist Error in Django
  22. Django admin file upload with current model id
  23. one-to-many inline select with django admin
  24. What is an efficient way of inserting thousands of records into an SQLite table using Django?
  25. How can I set a default value for a field in a Django model?
  26. Django filter JSONField list of dicts
  27. how to manually assign imagefield in Django
  28. Django ModelForm to have a hidden input
  29. Add custom form fields that are not part of the model (Django)
  30. Can a dictionary be passed to django models on create?

Leave a Comment