CODEWITHSUNDEEP
pythonmysqlsql
David542
David542

Reputation: 110143

Suggested practice for opening and closing cursors and connections

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?

Upvotes: 1

Views: 3795

Answers (2)

MarkR
MarkR

Reputation: 63538

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

Upvotes: 3

Marko Kevac
Marko Kevac

Reputation: 2982

Cursor is something similar to iterator in python. It enables you to traverse result set without keeping it whole in memory. Cursor can be implemented differently for each RDBMS you are using.

Unclosed cursor will use some memory if garbage collector doesn't delete it.

You can open more than one cursor inside one connection.

You can keep connection open. Depending on database you are using, open connection will use some resources and there can be limit of how much counnections can be opened at one time.

Upvotes: 1

Related Questions