CODEWITHSUNDEEP
djangopython-3.xpostgresql
ahMarrone
ahMarrone

Reputation: 191

Django + PostgreSQL Connection - Cannot use server side cursor

I have a stored procedure in PostgreSQL that returns a refcursor (its name can be passed as an argument):

-- Example stored procedure....
CREATE OR REPLACE FUNCTION example_stored_procedure(ref refcursor, gid_number integer) RETURNS refcursor AS $$
DECLARE
ref refcursor;
BEGIN
 OPEN $1 for SELECT * FROM lucca_routes where gid = gid_number;
 RETURN $1;
END;
$$ LANGUAGE plpgsql;

Then, I can get the result set from postgres console with no problems in this way:

BEGIN;
select example_stored_procedure('customcursor', 1);
FETCH ALL IN "customcursor";
COMMIT;

But, I need to get the result set from inside a Django app (using its postgreSQL connection). According to this, I tried:

from django.db import connections
from rest_framework.response import Response
from rest_framework.decorators import api_view

@api_view(['GET'])
def testing_procedure(request):
    connection = connections['default']
    with connection.cursor() as cursor:
        cursor.execute("BEGIN")
        cursor.callproc("example_stored_procedure", ['customcursor', 1])
        # "steal" the cursor - ERROR HERE!
        cursor2 = connection.cursor('customcursor')
        # fetch here the data from cursor2...
        return Response(result)

When I try to "steal" the new cursor (cursor2 creation) returned by callproc(), I have the error:

TypeError: cursor() takes 1 positional argument but 2 were given

What I'm doing wrong? How can I fetch the data from the refcursor returned by callproc()?

I'm using psycopg2 2.7.5

Upvotes: 2

Views: 1221

Answers (1)

ahMarrone
ahMarrone

Reputation: 191

I post the solution for future readers, based on @Alasdair comment.

You can use the function create_cursor(self, name=None) from the DatabaseWrapper object to use a server side cursor.

In my example:

def testing_procedure(request):
    connection = connections['default']
    with connection.cursor() as cursor:
        cursor.execute("BEGIN")
        cursor.callproc("example_stored_procedure", ['customcursor', 1])
        cursor2 = connection.create_cursor('customcursor')
        # fetch here the data from cursor2...
        result = cursor2.fetchall() # works!
        return Response(result)

Upvotes: 2

Related Questions