如何创建到SQL Server的数据库连接，并用Python向电子邮件发送连接错误消息

Question

我已经尝试编写Python 3代码来连接到SQL server数据库，条件如下：-

1. 如果数据库连接有错误，则将在睡眠5秒后重试连接
2. 目前最大重试次数为2次。可以更改为代码中的任何其他数字
3. 一旦达到最大重试次数，仍然存在连接错误，则脚本可以向我发送一封电子邮件，通知我该错误

我的代码如下。它可以最大限度地尝试，但外观有问题，它开始不断地发送电子邮件，直到我停止运行它。看起来像是一个无休止的循环，很可能在第二个while语句中。有人能告诉我问题出在哪里，以及我如何修复代码吗。

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

正确，您的第二个while将永远运行，因为当您进入该循环时，该条件始终为true(不要更改retry_counts(。

你可以把它改为if而不是while，例如(我也添加了一个try/catch(

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)

或者在末尾仅递增retry_counts。这将不那么可读/"；漂亮的"；不过在我看来。