Solved

Stored Procedure in Django Multi-DB

Posted on 2015-01-16
2
882 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Flask is a microframework for Python based on Werkzeug and Jinja 2. This requires you to have a good understanding of Python 2.7. Lets install Flask! To install Flask you can use a python repository for libraries tool called pip. Download this f…
There is a huge demand for CodeIgniter among the PHP web developers due to its dynamic features and benefits these days. It is one of most popular and agile open source PHP framework for creating robust web applications in PHP web development field.…
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…

734 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