When to close cursors using MySQLdb

mysql, mysql-python, python

Solution

Instead of asking what is standard practice, since that's often unclear and subjective, you might try looking to the module itself for guidance. In general, using the `with` keyword as another user suggested is a great idea, but in this specific circumstance it may not give you quite the functionality you expect.

As of version 1.2.5 of the module, `MySQLdb.Connection` implements the context manager protocol with the following code (github):

def __enter__(self):
    if self.get_autocommit():
        self.query("BEGIN")
    return self.cursor()

def __exit__(self, exc, value, tb):
    if exc:
        self.rollback()
    else:
        self.commit()

There are several existing Q&A about `with` already, or you can read Understanding Python's "with" statement, but essentially what happens is that `__enter__` executes at the start of the `with` block, and `__exit__` executes upon leaving the `with` block. You can use the optional syntax `with EXPR as VAR` to bind the object returned by `__enter__` to a name if you intend to reference that object later. So, given the above implementation, here's a simple way to query your database:

connection = MySQLdb.connect(...)
with connection as cursor:            # connection.__enter__ executes at this line
    cursor.execute('select 1;')
    result = cursor.fetchall()        # connection.__exit__ executes after this line
print result                          # prints "((1L,),)"

The question now is, what are the states of the connection and the cursor after exiting the `with` block? The `__exit__` method shown above calls only `self.rollback()` or `self.commit()`, and neither of those methods go on to call the `close()` method. The cursor itself has no `__exit__` method defined – and wouldn't matter if it did, because `with` is only managing the connection. Therefore, both the connection and the cursor remain open after exiting the `with` block. This is easily confirmed by adding the following code to the above example:

try:
    cursor.execute('select 1;')
    print 'cursor is open;',
except MySQLdb.ProgrammingError:
    print 'cursor is closed;',
if connection.open:
    print 'connection is open'
else:
    print 'connection is closed'

You should see the output "cursor is open; connection is open" printed to stdout.

I believe you need to close the cursor before committing the connection.

Why? The MySQL C API, which is the basis for `MySQLdb`, does not implement any cursor object, as implied in the module documentation: "MySQL does not support cursors; however, cursors are easily emulated." Indeed, the `MySQLdb.cursors.BaseCursor` class inherits directly from `object` and imposes no such restriction on cursors with regard to commit/rollback. An Oracle developer had this to say:

cnx.commit() before cur.close() sounds most logical to me. Maybe you can go by the rule: "Close the cursor if you do not need it anymore." Thus commit() before closing the cursor. In the end, for Connector/Python, it does not make much difference, but or other databases it might.

I expect that's as close as you're going to get to "standard practice" on this subject.

Is there any significant advantage to finding sets of transactions that don't require intermediate commits so that you don't have to get new cursors for each transaction?

I very much doubt it, and in trying to do so, you may introduce additional human error. Better to decide on a convention and stick with it.

Is there a lot of overhead for getting new cursors, or is it just not a big deal?

The overhead is negligible, and doesn't touch the database server at all; it's entirely within the implementation of MySQLdb. You can look at `BaseCursor.__init__` on github if you're really curious to know what's happening when you create a new cursor.

Going back to earlier when we were discussing `with`, perhaps now you can understand why the `MySQLdb.Connection` class `__enter__` and `__exit__` methods give you a brand new cursor object in every `with` block and don't bother keeping track of it or closing it at the end of the block. It's fairly lightweight and exists purely for your convenience.

If it's really that important to you to micromanage the cursor object, you can use contextlib.closing to make up for the fact that the cursor object has no defined `__exit__` method. For that matter, you can also use it to force the connection object to close itself upon exiting a `with` block. This should output "my_curs is closed; my_conn is closed":

from contextlib import closing
import MySQLdb

with closing(MySQLdb.connect(...)) as my_conn:
    with closing(my_conn.cursor()) as my_curs:
        my_curs.execute('select 1;')
        result = my_curs.fetchall()
try:
    my_curs.execute('select 1;')
    print 'my_curs is open;',
except MySQLdb.ProgrammingError:
    print 'my_curs is closed;',
if my_conn.open:
    print 'my_conn is open'
else:
    print 'my_conn is closed'

Note that `with closing(arg_obj)` will not call the argument object's `__enter__` and `__exit__` methods; it will only call the argument object's `close` method at the end of the `with` block. (To see this in action, simply define a class `Foo` with `__enter__`, `__exit__`, and `close` methods containing simple `print` statements, and compare what happens when you do `with Foo(): pass` to what happens when you do `with closing(Foo()): pass`.) This has two significant implications:

First, if autocommit mode is enabled, MySQLdb will `BEGIN` an explicit transaction on the server when you use `with connection` and commit or rollback the transaction at the end of the block. These are default behaviors of MySQLdb, intended to protect you from MySQL's default behavior of immediately committing any and all DML statements. MySQLdb assumes that when you use a context manager, you want a transaction, and uses the explicit `BEGIN` to bypass the autocommit setting on the server. If you're used to using `with connection`, you might think autocommit is disabled when actually it was only being bypassed. You might get an unpleasant surprise if you add `closing` to your code and lose transactional integrity; you won't be able to rollback changes, you may start seeing concurrency bugs and it may not be immediately obvious why.

Second, `with closing(MySQLdb.connect(user, pass)) as VAR` binds the connection object to `VAR`, in contrast to `with MySQLdb.connect(user, pass) as VAR`, which binds a new cursor object to `VAR`. In the latter case you would have no direct access to the connection object! Instead, you would have to use the cursor's `connection` attribute, which provides proxy access to the original connection. When the cursor is closed, its `connection` attribute is set to `None`. This results in an abandoned connection that will stick around until one of the following happens:

- All references to the cursor are removed

- The cursor goes out of scope

- The connection times out

- The connection is closed manually via server administration tools

You can test this by monitoring open connections (in Workbench or by using `SHOW PROCESSLIST`) while executing the following lines one by one:

with MySQLdb.connect(...) as my_curs:
    pass
my_curs.close()
my_curs.connection          # None
my_curs.connection.close()  # throws AttributeError, but connection still open
del my_curs                 # connection will close here

Problem

Original source