python - Necessity of explicit cursor.close()

Question

From time to time, I'm executing raw queries using connection.cursor() instead of using ORM (since it is definitely not a silver bullet).

I've noticed that in several places I don't call explicit cursor.close() after I'm done with database. So far, this hasn't result into any errors, or performance issues. I'm wondering what kind of problems could I possibly have without closing the cursor explicitly, what can go wrong?

As far as I understand, connection and cursor in Django follow "Python Database API Specification v2.0" (PEP-249). And, according to it, cursor would be automatically closed whenever __del__() method is called. I guess the question could be also: Is there a use case when it is not called?

FYI, I'm using Python 2.7 and Django 1.6.5.

question from:https://stackoverflow.com/questions/24661754/necessity-of-explicit-cursor-close

Answer 1

__del__/.close():

1. __del__ is not guaranteed to be called
2. some databases don't call cursor.close() in their __del__ (bad practice, but true)
3. some databases don't actually create connections in the connection function, but in the cursor function instead (e.g. for 2&3: pyhive's presto [maybe they've since patched it])

On server connections in general

Most servers have an idle timeout configuration property (let's call that T). If a connection is idle for more than T seconds, the server will remove the connection. Most servers also have properties to set the size of the worker thread pool (W). If you already have W connections to your server, it will likely hang when a new connection is attempted. For a second imagine that you don't have the option to explicitly close connections. In that situation, you have to set the timeout to be small enough that your worker pool is never completely used, which is a function of how many concurrent connections you have.

However, if you do close your cursors/connections (even when not equivalent by [3] above, they behave in a similar way), then you don't have to manage these server configuration properties and your thread pool simply needs to be large enough to manage all concurrent connections (with the option for an occasional wait for new resources). I've seen some servers (e.g. Titan on Cassandra) unable to recover from running out of workers in the thread pool, so the whole server goes down until restarted.

TL/DR If you're using very well-developed libraries, like the ones dano mentions, you won't have an issue. If you're using less pristine libraries, you may end up blocking on the server acquiring a worker thread if you don't call .close(), depending on your server config and access rates.