Why doesn't the MySQLdb Connection context man

Question

MySQLdb Connections have a rudimentary context manager that creates a cursor on enter, either rolls back or commits on exit, and implicitly doesn't suppress exceptions. From the Connection source:

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()

So, does anyone know why the cursor isn't closed on exit?

---

At first, I assumed it was because closing the cursor didn't do anything and that cursors only had a close method in deference to the Python DB API (see the comments to this answer). However, the fact is that closing the cursor burns through the remaining results sets, if any, and disables the cursor. From the cursor source:

def close(self):
    """Close the cursor. No further queries will be possible."""
    if not self.connection: return
    while self.nextset(): pass
    self.connection = None

It would be so easy to close the cursor at exit, so I have to suppose that it hasn't been done on purpose. On the other hand, we can see that when a cursor is deleted, it is closed anyway, so I guess the garbage collector will eventually get around to it. I don't know much about garbage collection in Python.

def __del__(self):
    self.close()
    self.errorhandler = None
    self._result = None

---

Another guess is that there may be a situation where you want to re-use the cursor after the with block. But I can't think of any reason why you would need to do this. Can't you always finish using the cursor inside its context, and just use a separate context for the next transaction?

To be very clear, this example obviously doesn't make sense:

with conn as cursor:
    cursor.execute(select_stmt)

rows = cursor.fetchall()

It should be:

with conn as cursor:
    cursor.execute(select_stmt)
    rows = cursor.fetchall()

Nor does this example make sense:

# first transaction
with conn as cursor:
    cursor.execute(update_stmt_1)

# second transaction, reusing cursor
try:
    cursor.execute(update_stmt_2)
except:
    conn.rollback()
else:
    conn.commit()

It should just be:

# first transaction
with conn as cursor:
    cursor.execute(update_stmt_1)

# second transaction, new cursor
with conn as cursor:
    cursor.execute(update_stmt_2)

Again, what would be the harm in closing the cursor on exit, and what benefits are there to not closing it?

Answer 1

To answer your question directly: I cannot see any harm whatsoever in closing at the end of a with block. I cannot say why it is not done in this case. But, as there is a dearth of activity on this question, I had a search through the code history and will throw in a few thoughts (guesses) on why the close() may not be called:

1. There is a small chance that spinning through calls to nextset() may throw an exception - possibly this had been observed and seen as undesirable. This may be why the newer version of cursors.py contains this structure in close():

   def close(self):
       """Close the cursor. No further queries will be possible."""
       if not self.connection:
           return
   
       self._flush()
       try:
           while self.nextset():
               pass
       except:
           pass
       self.connection = None
   

2. There is the (somewhat remote) potential that it might take some time to spin through all the remaining results doing nothing. Therefore close() may not be called to avoid doing some unnecessary iterations. Whether you think it's worth saving those clock cycles is subjective, I suppose, but you could argue along the lines of "if it's not necessary, don't do it".

3. Browsing the sourceforge commits, the functionality was added to the trunk by this commit in 2007 and it appears that this section of connections.py has not changed since. That's a merge based on this commit, which has the message

   Add Python-2.5 support for with statement as described in http://docs.python.org/whatsnew/pep-343.html Please test

   And the code you quote has never changed since.

   This prompts my final thought - it's probably just a first attempt / prototype that just worked and therefore never got changed.

---

More modern version

You link to source for a legacy version of the connector. I note there is a more active fork of the same library here, which I link to in my comments about "newer version" in point 1.

Note that the more recent version of this module has implemented __enter__() and __exit__() within cursor itself: see here. __exit__() here does call self.close() and perhaps this provides a more standard way to use the with syntax e.g.

with conn.cursor() as c:
    #Do your thing with the cursor

---

End notes

N.B. I guess I should add, as far as I understand garbage collection (not an expert either) once there are no references to conn, it will be deallocated. At this point there will be no references to the cursor object and it will be deallocated too.

However calling cursor.close() does not mean that it will be garbage collected. It simply burns through the results and set the connection to None. This means it can't be re-used, but it won't be garbage collected immediately. You can convince yourself of that by manually calling cursor.close() after your with block and then, say, printing some attribute of cursor

---

N.B. 2 I think this is a somewhat unusual use of the with syntax as the conn object persists because it is already in the outer scope - unlike, say, the more common with open('filename') as f: where there are no objects hanging around with references after the end of the with block.