I'm building a WSGI web app and I have a MySQL database. I'm using MySQLdb, which provides cursors for executing statements and getting results. What is the standard practice for getting and closing cursors? In particular, how long should my cursors last? Should I get a new cursor for each transaction?
I believe you need to close the cursor before committing the connection. 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? Is there a lot of overhead for getting new cursors, or is it just not a big deal?
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.
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.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
__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'
with closing(arg_obj) will not call the argument object's
__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
close methods containing simple
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.
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:
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