python - 循环遍历索引和发送电子邮件的问题
问题描述
我有一个 python 代码,它根据列的值发送电子邮件。它遍历数据框中的每一行并检查列值是否为 1。这里的列是时间标志。问题是我无法阻止它多次发送,这意味着一旦发送了电子邮件并且如果 while 循环第二次运行,则执行 for 循环并再次发送电子邮件,而我没有'不想要。
而不是这个,我只想为时间标志为 1 的行发送一封电子邮件。下一封电子邮件应该在该行的一小时后发送。我想将索引存储在时间标志为 1 但之后无法继续的行的列表中。
def send_email(previousindex,index):
print('entering if loop')
fromaddr = "**********"
toaddr = "*********"
msg = MIMEMultipart()
msg['From'] = fromaddr
msg['To'] = toaddr
a = str(df.loc[df.index[index], 'Station ID'])
b = str(df.loc[df.index[index], 'Time Difference'])
print(a)
msg['Subject'] = "Timestamp Alarm for Station"+ " " + a + " "+"(No data since" +" "+b+")"
df1 = df.iloc[index,:]
#Adding styles to the html table
with open('cssfile.txt', 'r') as myfile:
style = myfile.read()
#Mail body
body = """<html><head></head>{1}<div>{0}</div></html>""".format(pd.DataFrame(df1).T.to_html(index=False),style)
part = MIMEText(body,'html')
msg.attach(part)
server = smtplib.SMTP("smtp.office365.com",587)
server.ehlo()
server.starttls()
server.login('*********', '**********')
server.sendmail(fromaddr,toaddr,msg.as_string())
server.quit()
else:
print('entering else loop')
time.sleep(3600)
while True:
previousindex = []
cnxn = pyodbc.connect(
'DRIVER=' + driver + ';SERVER=' + server + ';PORT=1433;DATABASE=' + database + ';UID=' + username + ';PWD=' + password)
df = pd.read_sql('SELECT * FROM source_reads', cnxn)
df['current_time'] = datetime.datetime.now() - pd.DateOffset(hours=8)
df['current_time'] = pd.to_datetime(df['current_time'], infer_datetime_format=True)
df['last_reported_at'] = pd.to_datetime(df['last_reported_at'], infer_datetime_format=True)
df['Current UTC Time'] = df['current_time'].dt.strftime('%d-%m-%Y %H:%M %p')
df['local time'] = datetime.datetime.now()
df['local time'] = df['local time'].dt.strftime('%d-%m-%Y %H:%M %p')
df['TD'] = df['current_time'] - pd.to_datetime(df['last_reported_at'])
TimeD = pd.DatetimeIndex(df['TD'])
df['TimeD'] = TimeD.hour * 60 + TimeD.minute
df['Time Difference'] = df['TimeD'].map(str) + " " "min(s)"
df['timeflag'] = df['TimeD'].apply(lambda x: '1' if x >= 15 else '0')
df['timeflag'] = df['timeflag'].astype(str).astype(int)
df = df.drop(
columns=['created_at', 'updated_at', 'source_read_id', 'current_time', 'TimeD', 'TD', 'Current UTC Time'])
condlist = [df['reporting_station_id'] == 1,
df['reporting_station_id'] == 2,
df['reporting_station_id'] == 3,
df['reporting_station_id'] == 4,
df['reporting_station_id'] == 5,
df['reporting_station_id'] == 6,
df['reporting_station_id'] == 7,
df['reporting_station_id'] == 8,
df['reporting_station_id'] == 9,
df['reporting_station_id'] == 10,
df['reporting_station_id'] == 11,
df['reporting_station_id'] == 12,
df['reporting_station_id'] == 13,
df['reporting_station_id'] == 14,
df['reporting_station_id'] == 15,
df['reporting_station_id'] == 16,
]
choicelist = [712,713,714,715,716,717,718,719,720,721,722,723,725,728,729]
df['Station_id'] = np.select(condlist, choicelist)
df.columns = ['id', 'Last Reported (UTC)', 'Reporting Station Id',
'Local Time', 'Time Difference', 'Time flag', 'Station ID', ]
for index in df.index: # row in df.iterrows():
if df.loc[index, 'Time flag'] == 0:
print('fail')
print(previousindex)
send_email(previousindex,index)
l = previousindex.append(index)
else:
time.sleep(10)
continue
time.sleep(10)
编辑:当我减去现在和最后发送的电子邮件时出现错误
Traceback (most recent call last):
File "C:/Users/KrishnamurthyKA/Desktop/Project/SQL.py", line 106, in <module>
elif df.loc[index, 'Time flag'] == 1 and (now - df.loc[df.index[index],'emailsent_time'] > hour):
TypeError: '>' not supported between instances of 'datetime.timedelta' and 'int'
解决方案
您可以在数据框中添加一个带有时间戳的列,该时间戳表示最后一封电子邮件的发送时间。然后,当您对其进行迭代时,您可以检查自上次发送电子邮件以来是否已经超过一个小时,如果是,您可以发送另一封电子邮件并保存新的时间戳。
伪代码示例:
import time
# initialize all 'email_sent_at' = 0
# time.time() returns seconds since epoch, and one hour is 60*60 seconds.
hour = 60*60
for row in dataframe:
now = time.time()
if row['time_flag'] == 1 and (now - row['email_sent_at'] > hour):
send_email()
# reset the timestamp, so that another 60*60 seconds need to pass
# before another email is sent.
row['email_sent_at'] = now
else:
# either the 'time_flag' is not set, or it is set and it's less
# than an hour since the last sent email.
pass
推荐阅读
- python - BS4 返回 [] 而不是想要的 HTML 标签
- python - 我不能让列表写成 txt (python)
- python - 如何使用 pandas 计算 groupby 函数的累积时间?
- c - 使用 OpenMP 任务指令计算 PI
- javascript - Javascript:从字符串数组中删除括号中的单词和单词
- aws-cli - 如何停止特定状态机的所有正在运行的 Step Functions?
- python - 如何自动选择数据进行曲线拟合
- vb.net - 有没有办法将文本从 textbox1.text 输入到网站的输入框?我在 Visual Basic 中使用铬浏览器
- html - 如何将 Quill 与 express/node 一起使用
- python - Python MultiProcessing PicklingError:无法腌制