How can I use server-side cursors with django and psycopg2?

Question

I'm trying to use a server-side curser in psycop2 as detailed in this blog post. In essence, this is achieved with

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 query:

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've naively tried to create a transaction using

cursor.execute('BEGIN')

before executing the SELECT statement. However, that results in the same error generated from the cursor.execute('BEGIN') line.

I've 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 do I make a transaction in django?

Tso

From the error message it seems your cursor is outside of transactions. Try to use cursors inside transactions. – Traveller

See this answer for an example of how to set it up. – Facility

Answer 1

As you mention in your question but I'll reiterate here for future readers: it's also possible to use explicitly named cursors without bypassing Django's public 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)

Liable

Answer 2

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

-- need to be in a transaction to use cursors.

Taken from here.

Traveller

Answer 3

I was getting this due to isolation_level='AUTOCOMMIT' in my sqlalchemy.create_engine.

Crew