How to retry after sql connection failed in python?

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

Answer

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