StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POpypyodbc not throwing expected exception upon retry of connection
text
Body
copied!<p>I am attempting to insert some rows of data in to a SQL Server database using pypyodbc. The code I have written, catches any pypyodbc.Error and if this occurs, retries the DB operation up to a maximum of 3 times. In my tests I have managed to attempt to insert a duplicate key row of data - the exception is caught and the retry begins. The problem is that upon the retry the exception is not caught and the code hangs. Here is the code:</p> <pre><code># attempt to connect to the db - if unsuccessful, try up to 3 times for i in range (3): try: # connect to db db = pypyodbc.connect("DRIVER={SQL Server};SERVER=SERVER;DATABASE=DB;UID=user;PWD=password") cursor = db.cursor() # Loop round assigning the values in the lists at the given index to a new variable for j in range (0,sector): value1 = list1[j] value2 = list2[j] value3 = list3[j] # print each of the values, as a sanity check print('Value 1 = ' + value1) print('Value 2 = ' + value2) print('Value 3 = ' + value3+ "\n") #if (value1 is not None and value2 is not None and value3 is not None): cursor.execute('''INSERT INTO table(field1,field2,field3) VALUES (?,?,?)''', (value1,value2,value3)) cursor.commit() # close off the open connections cursor.close() db.close() # if successful, break out of the loop so there is no retry break # if there is a mysql exception thrown, print a message and retry except pypyodbc.Error, e: message = "Connection attempt " + str(i+1) + ": " + str(e) print(message) # if we reach this stage for a 3rd time, exit the program as no connection is possible and send an error email if i == 2: message = message + "\nThe script will now terminate." print (message) send_error_email(message) sys.exit() </code></pre> <p>Upon the first attempt the values are written to the console and the exception (u'23000', u"[23000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert duplicate key row in object....) is reported. The retry kicks in and the values are written to console again. This is where the program hangs. Why is the exception not being caught on the retry?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload