17:02

Using Server-Side PostgreSQL Cursors in Django

14 December 2010

This is a follow-up to the previous post, in which we talked about ways of handling huge result sets in Django.

Two commenters (thanks!) pointed out that psycopg2 has built-in support for server-side cursors, using the name option on the .cursor() function.

To use this in Django requires a couple of small gyrations.

First, Django wraps the actual database connection inside of the django.db.connection object, as property connection. So, to create a named cursor, you need:

cursor = django.db.connection.connection.cursor(name='gigantic_cursor')

If this is the first call you are making against that connection wrapper object, it’ll fail; the underlying database connection is created lazily. As a rather hacky solution, you can do this:

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')

You can then iterate over the results using the standard iterator or cursor.fetchmany() method, and that will grab results in from the server in the appropriate chunks.

There are 2 comments.