Solved

Stored Procedure in Django Multi-DB

Posted on 2015-01-16
2
696 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The purpose of this article is to demonstrate how we can use conditional statements using Python.
Creating and Managing Databases with phpMyAdmin in cPanel.
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now