CODEWITHSUNDEEP
pythonmysqldatabasemysql-python
user2740614
user2740614

Reputation: 283

MySQLdb - when to commit and execute?

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

Upvotes: 4

Views: 7790

Answers (1)

mgilson
mgilson

Reputation: 309929

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.

Upvotes: 1

Related Questions