Solved

Stored Procedure in Django Multi-DB

Posted on 2015-01-16
2
833 Views
Last Modified: 2015-01-19
I'm trying to execute a stored procedure in Django in order to get a complex result set from a cursor and multiple queries.   I can't seem to get the result set from the stored procedure.   I have a multi-db setup, and the stored procedure is on the second (unmanaged by Django) one that I am in charge of showing views into.  The stored procedure does not need any arguments.

In view.py
    from django.db import connections
    def index(request):
        cur = connections["ServiceDB"].cursor()
        cur.callproc("sptest")
        res = cur.fetchall()
        cur.close()
        return render(request, 'test.tmpl', {'result':res})

Open in new window

This results in the error: (-1, 'No result set to fetch from.', None)

I have also tried:
    from ServiceDB import models
    def index(request):
        res = models.Location.objects.raw("CALL sptest();")
        return render(request, 'test.tmpl', {'result':res})

Open in new window

This results in the error: (-1, 'Use multi=True when executing multiple statements', None).  I can't work out how to enable multi=True for this error...

Is there anything else I can try?  I know these stored procedures work as I have called them using PHP and python (non-Django), and they return result sets of about 76 rows.
0
Comment
Question by:butterhook
2 Comments
 
LVL 25

Accepted Solution

by:
clockwatcher earned 500 total points
ID: 40554852
The mysql connector callproc() method's resultsets should be available through the stored_results() iterator.   See:  http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-callproc.html

I think you'll have better luck with the following:

    from django.db import connections
    def index(request):
        cur = connections["ServiceDB"].cursor()
        cur.callproc("sptest")
        res = next(cur.stored_results()).fetchall()
        cur.close()
        return render(request, 'test.tmpl', {'result':res})

Open in new window

0
 
LVL 1

Author Closing Comment

by:butterhook
ID: 40557931
This solution returned a result set which I could use to parse results.  Excellent as I've spent hours on this one reading loads of how to articles with no luck.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Swadhin
Introduction of Lists in Python: There are six built-in types of sequences. Lists and tuples are the most common one. In this article we will see how to use Lists in python and how we can utilize it while doing our own program. In general we can al…
The purpose of this article is to demonstrate how we can use conditional statements using Python.
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question