Suggested practice for opening and closing cursors and connections

Question

The following is a common pattern I have in my code, and I was wondering more about the internals of cursors and connections.

cursor = connection.cursor()
cursor.execute("SET NAMES utf8")
cursor.execute(sql, args)
results = cursor.fetchall()
cursor.close()

What is the difference between a connection to a database and a cursor? Is there any downside of having an open connection (for example, for a few minutes?). What about have un-closed cursors, what is the effect? When executing multiple SQL statements in succession, should a new cursor be created each time?

If a cursor is going to be re-used I'd normally leave it open. It is an inactive session on the DB and as long as not too many cursors are left open then there's no impact on the server and you don't incur the over-head from continually opening and closing and re-opening again etc. — Ben

MarkR MarkR · Accepted Answer · 2012-06-05T21:05:14

It depends on the underlying implementation - what the Cursor object actually IS inside the driver.

In many DB-api implementations, the Cursor object isn't "interesting" (i.e. you can keep lots of them and let the GC worry about them), especially if you haven't done a query which returns result sets.

I've not used Python with Oracle, but I suspect (based on experience with JDBC and others) that this is not the case in Oracle. Oracle JDBC drivers have server-side cursors which it is vitally important that you close quickly (there is a fairly low default per-connection cursor limit; exceeding the limit causes a failure trying to open another).

In Oracle, relying on the GC to close your cursors might be hazardous if, for example, you open a new cursor in the loop and the GC keeps them all until the looping function returns.

if this is true, it might be helpful to use a with-statement construction to ensure that the cursor is closed in a timely fashion, even if an exception occurs.

UPDATE: You can use contextlib.closing as a context-manager such as

with contextlib.closing(myconnection.cursor()) as curs:
  curs.execute(...
  # even if exception happens, cursor is still closed immediately 
  # after this block

Suggested practice for opening and closing cursors and connections

2 Answers