Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1102
  • Last Modified:

Stored Procedure in Django Multi-DB

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
butterhook
Asked:
butterhook
1 Solution
 
clockwatcherCommented:
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
 
butterhookAuthor Commented:
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now