How to retry after sql connection failed in python?

Question

What's the right way to retry when connection/write to a DB fails in python?

I'm 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 intervals but it won't reconnect when sql server is running again.

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

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)

Here's the 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
– Shiva, Commented Feb 13, 2020 at 7:57
@shiva Do you think that will solve my problem? i'v tried with no sucess.. :/ — Oscar Larsson
– Oscar Larsson, Commented Feb 13, 2020 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: stackoverflow.com/questions/18982610/… — Philip
– Philip, Commented Feb 14, 2020 at 12:03

Oscar Larsson · Accepted Answer · 2020-02-15 20:36:43Z

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

FaCoffee · Accepted Answer · 2025-05-22 14:36:53Z

I was getting Communication Link problems, so I implemented a retry logic, but made a mistake and ran into an infinite loop with the following:

    def run_insert_query(self, query: str, max_retries: int = 5, delay: int = 1) -> None:
        '''Run an insert SQL query on the database.'''

        attempts = 0
        while attempts < max_retries:
            try:
                print(f"Attempt no. {attempts + 1} to execute insert query: {query}")
                self.cursor.execute(query)
                self.conn.commit()
                print("Query executed successfully")
                return  # Exit if successful
            except pyodbc.Error as e:
                logging.error(f"Error executing insert query: {e}")
                attempts += 1
                print(f"Attempt {attempts} failed")
                if attempts >= max_retries:
                    print("Max retries reached, raising exception")
                    raise OSError(f"Failed to execute insert query after {max_retries} attempts: {query}.") from e
                else:
                    print(f"Retrying after {delay} seconds...")
                    time.sleep(delay)  # Wait before retrying

And the problem was the exception: I changed it from except pyodbc.Error to a generic Exception and it worked.

Collectives™ on Stack Overflow

How to retry after sql connection failed in python?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related