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

You should remove the conn.close() from the try body, as it will always be called within the finally body. Other than that, it looks like you're releasing your connections properly. — lanzz
– lanzz, Commented Feb 12, 2014 at 7:51
Thanks Lanzz - sorry I made an error copying the code (was playing around without finally:, and had a conn.close() in the try body and the except body). Have fixed the question (still have the same problem, either way) — djmac
– djmac, Commented Feb 14, 2014 at 3:33

djmac · Accepted Answer · 2014-04-03 00:47:03Z

4

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

answered Apr 3, 2014 at 0:47

djmac

9155 gold badges13 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Correct exception handling with python MySQLdb connection

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related