How do I create a database connection to SQL Server and send out connection error messages to an email in Python?

Question

I have tried writing Python 3 code to connect to an SQL server database with a few conditions as below: -

1. If the database connection has an error, then a retry for the connection will happen after 5 seconds of sleep.
2. The maximum number of retries are 2 for now. Can be changed to any other number as in the code.
3. Once, the maximum number of retries is reached and still a connection error exists, then the script can send me an email informing me of the error.

My code is below. It works for the maximum tries but there is a problem with the looking such that it starts to continuously send out emails until I stop running it. Seems like an endless loop and is likely to be in the second while statement. Can someone point out to me where the problem is and how I can fix the code.

import configparser 
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
import smtplib
import mimetypes
import email.mime.application

config = configparser.ConfigParser()  
Driver='SQL Server'
config.read('connection.ini')  
#Context is specific to my configuration.ini file that has the connection details
server = config['Context']['host']  
db = config['Context']['database']  
user = config['Context']['user']  
password = config['Context']['pwd'] 

retry_flag = True  
retry_count  = 0 
max_retries = 2 

while retry_flag and  retry_count < max_retries:
 try:
    cnxn_str = pyodbc.connect(driver=Driver, host=server, user=user,password=password, database=db)
    retry_flag = False
except Exception as e:
    error = str(e)
    print(error)
    print("Retry after  5 sec")
    retry_count = retry_count+1
          
    time.sleep(5)
   
    while retry_count == max_retries:
        
        smtp_ssl_host = config['Context']['smtp_ssl_host']
        smtp_ssl_port = config['Context']['smtp_ssl_port']
        email_user = config['Context']['email_user']
        email_to = config['Context']['email_to']
        email_pass =config['Context']['email_pass'] 


        msg = MIMEMultipart()
        msg['Subject'] = "Database Connection Error"
        msg['From'] = email_user
        msg['To'] = email_to
        s = smtplib.SMTP_SSL(smtp_ssl_host, smtp_ssl_port)
        s.login(email_user, email_pass)

        txt = MIMEText("Database connection failed earlier. Please re-run the script manually.")
        msg.attach(txt)
        s.send_message(msg)      
        s.quit()     

Answer 1

Correct, your second while runs forever since that condition is always true, when you enter that loop (do don't change retry_counts).

You can just change that to an if instead of while, e.g. (I have added an try/catch as well)

if retry_count == max_retries:
 try:
  smtp_ssl_host = config['Context']['smtp_ssl_host']
        smtp_ssl_port = config['Context']['smtp_ssl_port']
        email_user = config['Context']['email_user']
        email_to = config['Context']['email_to']
        email_pass =config['Context']['email_pass'] 

        msg = MIMEMultipart()
        msg['Subject'] = "Database Connection Error"
        msg['From'] = email_user
        msg['To'] = email_to
        s = smtplib.SMTP_SSL(smtp_ssl_host, smtp_ssl_port)
        s.login(email_user, email_pass)

        txt = MIMEText("Database connection failed earlier. Please re-run the script manually.")
        msg.attach(txt)
        s.send_message(msg)      
        s.quit()   
   except Exception as e:
        print(e)

or just increment retry_counts, at the end. That would not be as readable/"pretty" in my opinion though.