How can I use server cursors with django and psycopg2?

Question

I am trying to use server-side curser in psycop2 as described in this blog post . This is essentially achieved using

from django.db import connection if connection.connection is None: cursor = connection.cursor() # This is required to populate the connection object properly cursor = connection.connection.cursor(name='gigantic_cursor') 

When I execute the request:

 cursor.execute('SELECT * FROM %s WHERE foreign_id=%s' % (table_name, id)) 

I get a ProgrammingError :

 psycopg2.ProgrammingError: can't use a named cursor outside of transactions 

---

I naively tried to create a transaction using

 cursor.execute('BEGIN') 

before executing a SELECT . However, this results in the same error generated from the cursor.execute('BEGIN') .

I also tried using

 cursor.execute('OPEN gigantic_cursor FOR SELECT * FROM %s WHERE foreign_id=%s' % (table_name, id)) 

but I get the same results.

How to make a transaction in django?

Answer 1

Try the following:

 from django.db import transaction # prepare your cursor here ... with transaction.atomic(): cursor.execute('SELECT * FROM %s WHERE foreign_id=%s' % (table_name, id)) 

Also check out the white papers on this topic: https://docs.djangoproject.com/en/1.8/topics/db/transactions/

Answer 2

As you mentioned in your question, but I will repeat here for future readers: you can also use explicitly named cursors without going around the Django open API:

 from django.db import connection, transaction with transaction.atomic(), connection.cursor() as cur: cur.execute(""" DECLARE mycursor CURSOR FOR SELECT * FROM giant_table """) while True: cur.execute("FETCH 1000 FROM mycursor") chunk = cur.fetchall() if not chunk: break for row in chunk: process_row(row) 

Answer 3

Cursors must be used within transactions. You need to define a transaction and use the cursor inside it.

- you need to use a transaction to use cursors.

Taken from here .