19

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?

Community
  • 1
  • 1
jmilloy
  • 7,875
  • 11
  • 53
  • 86
  • 1
    A good explanation for this >>> http://stackoverflow.com/questions/5669878/when-to-close-cursors-using-mysqldb – Syed Mauze Rehan Jul 13 '15 at 04:24
  • @syed That is an old question of mine, which I also link to in this question! – jmilloy Jul 13 '15 at 05:30
  • Oops! Didn't notice that. I guess that explanation still holds. No concrete answer to this, other than this that you need to make sure on your own. – Syed Mauze Rehan Jul 13 '15 at 05:35
  • 1
    It might be worth pointing out that the `MySQLdb` source gives relatively little insight into the underlying `_mysql` module (distributed with `MySQLdb` as a `.pyd` binary) or [the API it implements](https://dev.mysql.com/doc/refman/5.0/en/c-api.html). I tend to assume that either the package author ran into a reason to do things this way, buried in one of those dependencies, or that it began as YAGNI and ended up as "ain't broke." – Air Oct 06 '15 at 19:47

1 Answers1

11

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.

J Richard Snape
  • 20,116
  • 5
  • 51
  • 79
  • Yes, `with conn.cursor() as c` does make more sense and I'm glad that it in fact is closed in that version. I will probably try switching over to that version. It's a moot issue at this point, but my note about garbage collection is that the close would be called during collection anyway so why bother (rather than whether and when it would be collected). If I had to guess, your #3 seems the most likely explanation. – jmilloy Jul 30 '15 at 12:09
  • OK - yeah - I see your point about `close` being called during garbage collection: you're right and I'd misunderstood the point you were making. Thanks for the bounty, though - sorry I couldn't give a definitive, but I agree #3 is probably the most likely. – J Richard Snape Jul 30 '15 at 12:39
  • I realize this is an old question and answer, but I think it's worth noting that the `__exit__` method shown in the question gets called on the `Connection` object, not on the `Cursor` object that was returned from the `__enter__` method. This is unlike Python's file objects, which return `self` from `__enter__`. Because `Connection.__enter__` doesn't save a reference to the `Cursor` it returns (at least, not as part of `__enter__` directly), there's no obvious way for `__exit__` to be able to close it. Doing `self.close()` would probably not be appropriate! – Blckknght Jun 22 '18 at 00:28