How to retry after sql connection failed in python?

Question

Whats the right way to retry when connection/write to a DB fails in python?

Im trying to use this code and it works until i restart my sql server and python tries to connect to it i get "retry after 30 sec" for 10 times with 30 secs intervalls but it wont reconnect when sql server is running agian.

EDIT: It reconnects when retry_count1 = 10 but at next cycle counter needs to count up to 10 before it connects agian.

Does anyone have a idea why it wont reconnect?

 retry_flag1 = True
        retry_count1 = 0
        while retry_flag1 and retry_count1 < 10:
          try:
            cursor = cnxn.cursor()
            cursor.execute('SELECT too_id FROM [TTMM].[dbo].[Machines] WHERE MachineID = {}'.format (machineid,))
            too_id = cursor.fetchone()[0]
            cursor.execute('INSERT INTO [TTMM].[dbo].[{}](counter, effectively, too_id) VALUES ({},{},{})'.format (machineid, counter, effectively, too_id,))
            cnxn.commit()
            cursor.close()
            retry_flag1 = False
          except as e:
            print(e)
            print("Retry after 30 sec")
            retry_count1 = retry_count1 + 1
            time.sleep(30)

Heres console output after restarting sql server.

('08S01', '[08S01] [FreeTDS][SQL Server]Write to the server failed (20006) (SQLExecDirectW)')
Retry after 1 sec
('08S01', '[08S01] [FreeTDS][SQL Server]Communication link failure (0) (SQLExecDirectW)')
Retry after 1 sec
('08S01', '[08S01] [FreeTDS][SQL Server]Communication link failure (0) (SQLExecDirectW)')
Retry after 1 sec
141222 Cykel
('08S01', '[08S01] [FreeTDS][SQL Server]Communication link failure (0) (SQLExecDirectW)')
Retry after 1 sec
('08S01', '[08S01] [FreeTDS][SQL Server]Communication link failure (0) (SQLExecDirectW)')

It is possible another exception not related to SQL Server connection is occurring. Instead of just `except`, catch the specific connection related exception. — Shiva, Feb 13 '20 at 07:57
@shiva Do you think that will solve my problem? i'v tried with no sucess.. :/ — Oscar Larsson, Feb 13 '20 at 19:45
@OscarLarsson, you need to change the line `except` to `except Exception as e:` and then print `e`. That will give you the error that is being thrown. Update your question with that information, and it will be easier to give you input on the actual error. Find more information here: https://stackoverflow.com/questions/18982610/difference-between-except-and-except-exception-as-e-in-python — Philip, Feb 14 '20 at 12:03

score 4 · Accepted Answer · answered Feb 15 '20 at 20:36

I found a solution by adding cnxn.close and create a new connection.

    retry_flag = True
    retry_count = 0
    cursor = cnxn.cursor()
    while retry_flag and retry_count < 5:
        try:
            cursor.execute('SELECT too_id FROM [TTMM].[dbo].[Machines] WHERE MachineID = {}'.format (machineid,))
            too_id = cursor.fetchone()[0]
            cursor.execute('INSERT INTO [TTMM].[dbo].[{}](counter, effectively, too_id) VALUES ({},{},{})'.format (machineid, counter, effectively, too_id,))
            retry_flag = False
            print("Printed To DB - Counter = ", counter, ", Effectively = ", effectively, ", too_id = ", too_id,)

        except Exception as e:
            print (e)
            print ("Retry after 5 sec")
            retry_count = retry_count + 1
            cursor.close()
            cnxn.close()
            time.sleep(5)
            cnxn = pyodbc.connect('DRIVER=FreeTDS;SERVER=*;PORT=*;DATABASE=*;UID=*;PWD=*;TDS_Version=8.7;', autocommit=True)
            cursor = cnxn.cursor()

    cursor.close()

How to retry after sql connection failed in python?

1 Answers1

Linked