CODEWITHSUNDEEP
pythondjangopostgresqlpsycopg2
daveoncode
daveoncode

Reputation: 19578

psycopg2 OperationalError: cursor does not exist

I'm trying to implement a server side cursor in order to "bypass" Django ORM weakness when it comes to fetch an huge amount of data from the database. But I don't understand how named cursor are supposed to be defined, since my current code seems not working properly. I define the cursor in this way:

id = 'cursor%s' % uuid4().hex
connection = psycopg2.connect('my connection string here')
cursor = connection.cursor(id, cursor_factory=psycopg2.extras.RealDictCursor)

The cursor seems to work in that it can be iterated and returns expected records as python dictionary, but when I try to close it (cursor.close()) I get the exception:

psycopg2 OperationalError: cursor *the generated cursor id* does not exist

WTF?! So what is the object I'm using to retrieve stuff from the database? Is psycopg2 using a fallback default (unnamed) cursor since the one I defined is not found in my database (and if so... my big question: it's mandatory to define a cursor at the db level before using psycopg2?) I'm a lot confused, can you help me?

Upvotes: 19

Views: 27775

Answers (4)

sebtheiler
sebtheiler

Reputation: 2557

I made a really simple and silly mistake of forgetting to run ./manage.py makemigrations and ./manage.py migrate before running ./manage.py test which caused this error.

(I'm aware this doesn't answer the original question, but since this is the first result from Google I thought I would contribute. Hopefully that's okay)

Upvotes: 28

Dmitry P.
Dmitry P.

Reputation: 384

I had similar problem and found the solution. Just disable server side cursors like described here: https://docs.djangoproject.com/en/2.2/ref/settings/#disable-server-side-cursors

        'default': {
            ...
            'USER': DB_USER,
            'PASSWORD': DB_PASSWORD,
            'NAME': DB_NAME,
            'DISABLE_SERVER_SIDE_CURSORS': True,
            ...
        },

Upvotes: 15

Sydney C.
Sydney C.

Reputation: 978

I've had this problems when playing around with my models and launching the test with Pytest.

What resolved the problem for me was to reset the database of my test unit. I used --create-db like so: 

pytest backend/test_projects/partners/test_actions.py --create-db

Upvotes: 12

Talvalin
Talvalin

Reputation: 7889

From the psycopg2 documentation:

"Named cursors are usually created WITHOUT HOLD, meaning they live only as long as the current transaction. Trying to fetch from a named cursor after a commit() or to create a named cursor when the connection transaction isolation level is set to AUTOCOMMIT will result in an exception."

Which is to say that these cursors do not need to be explicitly closed.

http://initd.org/psycopg/docs/usage.html#server-side-cursors

Upvotes: 5

Related Questions