Correct exception handling with python MySQLdb connection

Question

I created a small/basic python script to insert data into a MySQL database. I included some error handling - mainly to close the connection and/or prevent hanging connections in the case of an error (...but also to ignore some errors).

I thought what I had (see below) was right - it seemed to be working okay. But occasionally I have been getting "Too many connection" errors - which I assumes means I am not actually closing the connection correctly at all (or perhaps error handling isn't right).

conn=MySQLdb.connect(host=####, user=####, passwd=####, db=####)
curs=conn.cursor()
try:
    curs.execute(sql)
    conn.commit()           

except MySQLdb.Error as e:
    if e[0]!= ###:
        raise

finally: 
    curs.close()    
    conn.close()

(I also tried without finally:)

The other (I think important) point is that it is that the MySQL database uses an InnoDB storage engine. This is the first time I have used InnoDB engine and perhaps there are some differences to MyISAM that are relevant here, that I am not aware of (like conn.commit(), but for an error).... That seems to be the source of all my other problems!

Thanks in advance

Answer 1

I believe the issue was I wasn't invoking conn.rollback() in the except clause (and consequently, the connection was not closing properly). One line (see below) seemed to fix the issue (I can't be exactly sure if that was this issue - if someone could confirm that would be great).

conn=MySQLdb.connect(host=####, user=####, passwd=####, db=####)
curs=conn.cursor()
try:
    curs.execute(sql)
    conn.commit()           

except MySQLdb.Error as e:
    conn.rollback()              #rollback transaction here
    if e[0]!= ###:
        raise

finally: 
    curs.close()    
    conn.close()