Django With MSSQL using Pyodbc: Model Forms not being saved

Question

Django version 1.8.16 pyodbc Version: 3.0.11b16

I have been trying to make a view/submit form for a project. The basic aim I need to achieve is to view and edit/save the data in the form using stored procedure in MS SQL Server 2014. I am able to use stored procedure in the view page but have not been able to do so with the edit item and add a new item .

models.py

class procedures():
def view_patientsp(self, patid):
    cursor = connection.cursor()
    ret = cursor.execute("EXEC PR_PRES_viewpatient @uid=?  ", (patid))
    cursor.close()
    return ret


class Patient(models.Model):
    patientid = models.AutoField(db_column='PatientID', primary_key=True)  
    pyear = models.DecimalField(db_column='Pyear', max_digits=10, decimal_places=0, blank=True, null=True)  
    dref = models.DecimalField(db_column='DRef', max_digits=10, decimal_places=0, blank=True, null=True)  
    title = models.TextField(db_column='Title', blank=True, null=True)  
    fname = models.TextField(db_column='FName', blank=True, null=True)  
    lname = models.TextField(db_column='LName', blank=True, null=True)  
    dob = models.DateTimeField(db_column='DOB', blank=True, null=True)  
    pamonth = models.TextField(db_column='PAMonth', blank=True, null=True)  
    payear = models.TextField(db_column='PAYear', blank=True, null=True)  
    padays = models.TextField(db_column='PADays', blank=True, null=True)  
    sex = models.TextField(db_column='Sex', blank=True, null=True)

views.py

def view_patient(request):
    if request.method == 'POST':
        form = viewpatientform(request.POST)
        return render(request, 'lis/view.html', {'form': form})
    else:
        form = viewpatientform()
        if form.is_valid():
            procedure = procedures()
            ret = procedure.view_patientsp(request.POST['fields'])
        return render_to_response('lis/view.html', {'form': form})

urls.py

urlpatterns = [
url(r'^$', views.pat_list, name='index'),
url(r'^view/(?P<patid>\d+/)$', views.view_patient, name='viewpatient'), ]

view.html

{% block body %}
{% load materializecss %}
{{ form|materializecss }}
<button type="submit" class="btn btn-primary">Submit</button>
{% endblock  %}

Is there a reason you're using a stored procedure and not the Django ORM? Django is made to generate its own SQL through the ORM. Using stored procedures in this case is very much not recommended. Please also include which version of django-pyodbc you are using and a link to it, as there are quite a few versions. Also, are you running Django on Windows or Linux? — FlipperPA, Dec 13 '16 at 18:44
@FlipperPA I'm Using Windows 7 64 bit Django-Pyodbc : Version: 1.0.1 I'm Building an app on top of an existing windows application so using Django. I am using existing stored procedures so that any changes in the windows app will be covered in the web app as well — Romil Bahukhandi, Dec 14 '16 at 06:58

score 1 · Accepted Answer · answered Dec 14 '16 at 14:28

You have a few options. I'll try to outline them here, as I have a similar project where we're taking a very large site written in another language and backed by SQL Server with stored procedures, that we are slowly but surely migrating to Django.

We are aiming for a full rewrite over time. This means replacing the logic in stored procedures with Django's models and views, as the ORM is very powerful. We find it a cutting back on repetition and drastically improving reusability, thus reducing the total amount of code.

You can use features like Django's inspectdb to generate an initial set of Django models to interact with your database. It will need some touching up, but this has enabled us to use some features (like Django's admin) for crud operations on the old database, rather than coding up three layers to do simple lookup table updates. I wouldn't recommend customizing the admin, but using it for what it does well is a good way to start to get familiar with how Django does things.

You can use many of Django's features, like templates and forms, to call a stored procedure. For example, in a FormView, you can do things like this:

class MyFormView(FormView):
    template_name = 'home.html'
    form_class = MyForm
    success_url = '/hooray/'

    def form_valid(self, form):
        sender = form.cleaned_data['sender']
        message = form.cleaned_data['message']

        cursor = connections['default'].cursor()
        cursor.execute('EXEC usp_insert_message @sender = ?, message = ?', sender, message)

        return super(MyFormView, self).form_valid(form)

Please be wary of coding up a FrankenDjango, however. We have taken pains during our transition to ensure that both web sites can run in parallel, with authentication shared between the two. This allows us to move features over to the new Django site over time, while doing things in the new code base the right way. After you get in the flow of using the Models, Views and Templates the right way, you will likely be pleasantly surprised at how quickly you can start moving things over, especially with Django's rich ecosystem of ready-to-go packages.

One final note: I would highly recommend the using django-pyodbc-azure (it works with SQL Server or Azure) for your Django database engine. I've found over time that it is the most actively maintained, and just worked. You can install it for Django 1.8 like this:

pip install django-pyodbc-azure<1.9

More details can be found here: https://github.com/michiya/django-pyodbc-azure

Good luck!

Django With MSSQL using Pyodbc: Model Forms not being saved

1 Answers1