Django + PostgreSQL Connection - Cannot use server side cursor

Question

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

You could try the create_cursor() method (new in Django 1.11), which has a name argument. The answers to this question might also help. — Alasdair
– Alasdair, Commented Sep 27, 2018 at 13:09
Excelent @Alasdair. Using create_cursor() I'm able to use and fetch the data from the server side cursor. — ahMarrone
– ahMarrone, Commented Sep 27, 2018 at 15:27
Glad it worked! It would be good if you add your solution as an answer, to help future readers. — Alasdair
– Alasdair, Commented Sep 27, 2018 at 15:41

ahMarrone · Accepted Answer · 2018-09-27 17:03:51Z

2

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)

answered Sep 27, 2018 at 17:03

ahMarrone

1911 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Christian Long Over a year ago

If your database connection has AUTOCOMMIT=True and you want a "without hold" server-side cursor, you have to do this inside a transaction.atomic() block. Django source

Collectives™ on Stack Overflow

Django + PostgreSQL Connection - Cannot use server side cursor

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related