Python -Error handling & Retry in SQL Server on Ubuntu No.93

How to implement the error handling and retry with some interval in SQL Server on Ubuntu by Python is shown in this blog.

▼1. Why do we need an error handling and an retry logic?

We often use a cloud service and a remote service through the network. if the network intermittently cause something problems, the client application cannot access these services and will stop. to avoid this case, we need to add an error handling and an retry logic in an client application.


▼2. Prerequisites

An error handling and a retry logic will be added into the code that was shown in the following blog.

Python – How to connect to SQL Server on Ubuntu No.92


▼3. Creating the Python code to implement the error handling & retry logic

3-1. Developing a python code for an error handling and a retry logic

This python code implements 3 retries with 15 seconds interval using while after a connection failure. To cause the connection failure, Server name is changed to incorrect “localhost1” from “localhost”.

# runquery_retry.py

import pyodbc
import time
import datetime

server = 'localhost1'
database = 'testdb'
username = 'sa'
password = 'yourpassword
driver= '{ODBC Driver 17 for SQL Server}'
success = False
retryc = 0
max_retries = 3

while not success and retryc < max_retries:
    try:
        cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+',1433;DATABASE='+database+';UID='+username+';PWD='+ password)
        cursor = cnxn.cursor()
        cursor.execute("SELECT * FROM testdb.dbo.sampletable")
        row = cursor.fetchone()
        
        while row:
            print (str(row[0]) + " " + str(row[1]))
            row = cursor.fetchone()
            
        success = True
    except pyodbc.Error as ex:
        retryc += 1
        print("Current Timestamps:" + str(datetime.datetime.now()) + "retry count: " + str(retryc))
        print("==========================")
        print("Error: " + str(ex))
        print("==========================")
        time.sleep(15)

3-2. Results for this code

Retry is executed after 45 seconds from the connection failure. the default connection timeout 15 seconds plus 15 seconds interval equals 45 seconds.

Current Timestamps:2022-12-05 10:42:06.523664retry count: 1
==========================
Error: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')
==========================
Current Timestamps:2022-12-05 10:42:51.536645retry count: 2
==========================
Error: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')
==========================
Current Timestamps:2022-12-05 10:43:36.552769retry count: 3
==========================
Error: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')
==========================

▼4. Reference

  1. Python in Visual Studio Code
  2. Python – How to connect to SQL Server on Ubuntu No.92
  3. Java – Error handling & Retry in SQL Server on Ubuntu No.6

That’s all. Have a nice day ahead !!!

Leave a Reply

Your email address will not be published. Required fields are marked *