Error Handling In mysqldb - Python

06 Oct 2013

Updated 2026-08-29: the original snippet interpolated the value into the SQL, closed the connection only on failure, and printed success from a finally block that runs on failure too.

I’ve had issues with queries failing and leaving connections open (enough to stall a server…). I know python’s oursql library supports using the WITH keyword, and I think it will close the connection when there is an unexpected error. However, I’m not sure if I’m ready to move to a different library for MySQL (it’s working well).

Here’s what I’m currently doing to close the cursor and connection, then re-raise the error:

import MySQLdb

conn = MySQLdb.connect(user="username", passwd="secret", db="database", charset='utf8')
cur = conn.cursor()

try:
    cur.execute("INSERT INTO testTable (userid) VALUES(%s);", (user_id,))
    conn.commit()
except Exception:
    conn.rollback()
    raise
finally:
    cur.close()
    conn.close()

Three things were wrong with what I had here before. The value went in with % string formatting, which builds the SQL by concatenation and leaves it open to injection, so it has to be passed as a parameter with a comma instead. The close only ran in except, so a successful insert leaked the connection, which is the exact problem I was trying to solve. And finally runs on both paths, so the old code printed “Insert Successful” even when the insert had just failed.