Login

Django Using Stored Procedure

Author:
iapain
Posted:
March 19, 2007
Language:
Python
Version:
Pre .96
Score:
4 (after 4 ratings)

Here is an clean example of using stored procedure using django. It sounds pretty weird "stored procedures in django" but for legacy database system we still need a clean approach to implement stored procedures using django.

In this example, I've implemented logic inside models.py by creating a dummy class, i.e a django table (which is comparable to package in your database) and inside this package/class i added stored procedure wrappers. I tested it with boulder-oracle-sprint branch, there is minor issues with LazyDate in db/backend/oracle/base.py but it still working after minor edits in base.py. It worked absolutely fine with MySQL and MSSQL.

View is pretty straight forward. Dont forget to create form.html as template and in body just put {{ form }}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
#Model
from django.db import models
from django.db import connection
class MY_UTIL():
'''
Its pretty clean and nice way to define stored procedures inside the class, especially models.py (I hope someone will write a automatic generation for all stored procedure calls soon.)
'''
    def log_message(self, control_in, message_in):
        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()
        return ret

#View
from django import newform as form # here we are using newforms, please note its not available NOW in django standard package.

class MessageForm(forms.Form):
    email = forms.EmailField(max_length=100, help_text="Please enter your email")
    subject = forms.CharField(max_length=50,help_text="Please provide a short desc")

def message(request):
    if request.method == 'POST':
        form = MessageForm(request.POST)
    else:
        form = MessageForm()
    if form.is_valid():
        my_util = MY_UTIL()
        ret = my_util.log_message(request.POST['email'], request.POST['subject'])
        return HttpResponseRedirect('/done/')
    return render_to_response('form.html', {'form':form})

More like this

  1. Django Settings Assignment Expressions aka Walrus Operator example by webology 3 weeks, 3 days ago
  2. codigo alto nivel by MrRocklion 2 months, 3 weeks ago
  3. Load template from specific app by Krzysiek555 3 months, 1 week ago
  4. PostgreSQL JSON subqueries by dolamroth 3 months, 2 weeks ago
  5. "Magic Link" Management Command by webology 8 months, 2 weeks ago

Comments

BrunoDesthuilliers (on October 23, 2019):

There are quite a few issues with this code.

In the models:

class MY_UTIL():

pep08: class names should be CamelCase.

    def log_message(self, control_in, message_in):
        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()
        return ret

If anything wrong happens during the callproc call, the cursor won't be properly closed. You want to use a with block ie:

    with connection.cursor() as cursor:
        return cursor.callproc("MY_UTIL.LOG_MESSAGE", (control_in, message_in))

or at least a try/finally block:

    cursor = connection.cursor()
    try:
        return cursor.callproc("MY_UTIL.LOG_MESSAGE", (control_in, message_in))
    finally:
        cursor.close()

In the view: first, you're trying to validate and process the form even on a GET request. All the if form.is_valid(): block should be under the if request.method == "POST branch. Then, you're using the unsanitized raw request.POST data instead of the safe, sanitized form.cleaned_data values. The correct implementation would be:

def message(request):
    if request.method == 'POST':
        form = MessageForm(request.POST)
        if form.is_valid():
            my_util = MY_UTIL()
            ret = my_util.log_message(form.cleaned_data['email'], form.cleaned_data['subject'])
            return HttpResponseRedirect('/done/')
    else:
        form = MessageForm()
    return render_to_response('form.html', {'form':form})

Also self is unused here so it could just be a classmethod or even a plain function in a module (hint: python modules are singletons).

#

Please login first before commenting.