MySQLdb - when to commit and execute?

Question

I'm using MySQLdb in Python 2, and I have a question about executing queries into the database. Say I have some connection con, and I instantiate a cursor with cur = con.cursor(). Which of the following is the proper way to commit changes to the database? Bonus points if you could explain the theory behind the correct answer :)

Method 1:

try:
    cur.execute('command 1')
    con.commit()
    cur.execute('command 2')
    con.commit()
except MySQLdb.Error as e:
    con.rollback()

Method 2:

try:
    cur.execute('command 1')
    cur.execute('command 2')
    con.commit()
except MySQLdb.Error as e:
    con.rollback()

Method 3:

try:
    cur.execute('command 1')
    try:
        cur.execute('command 2')
    except MySQLdb.Error as e:
        con.rollback()
    con.commit()
except MySQLdb.Error as e:
    con.rollback()

According to [this answer](http://stackoverflow.com/questions/8099902/should-i-reuse-the-cursor-in-the-python-mysqldb-module), it is best practice to _NOT_ reuse your cursor for multiple transactions -- So I guess that would mean that the best method is "None of the above" :-) — mgilson, Jun 27 '16 at 22:06
@mgilson So I should create a new cursor each query? Also, I read the article you linked to (well, the article linked to by the article you linked to :)), but I haven't found any good examples of code that extends the MySQLdb module as the article suggests. Any github links that you know of that would give me a better picture of what "good" MySQLdb code looks like? — user2740614, Jun 27 '16 at 22:22
Yes, I think you should use a separate cursor for each query. They should be cheap to create so there's no real performance advantage to reusing them anyway. As for the rest of that post (e.g. the link), I'm not completely sure how they apply for your particular situation. Also FWIW, I believe that connections _are_ safe to reuse. — mgilson, Jun 27 '16 at 22:36

score 1 · Answer 1 · edited May 23 '17 at 12:08

for MySQLdb, I'd probably do something like this:

import contextlib

connection = get_connection_somehow()
with contextlib.closing(connection) as con:
    with con as cursor:
        cursor.execute(query1)
    with con as cursor:
        cursor.execute(query2)
    ...

And of course use a loop if you have more than 1 or two queries to execute.

A few things to note here:

Creating connections is somewhat expensive.
Creating cursors is really cheap.
A MySQLdb.Connection when used as a context manager gives you a new cursor.
- When the context is exited, the connection is rolled back if there was an error, otherwise the changes are committed.
- See https://stackoverflow.com/a/22840557/748858

MySQLdb - when to commit and execute?

1 Answers1