Solved

Stored Procedure in Django Multi-DB

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Recently I spent hours debugging an issue in a Rails project where ActiveRecord was causing MySQL errors trying to create a User object of a class at the top level of a Single Table Inheritance model structure.  It turns out `.create` behaves differ…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
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…

785 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