之前做过一个功能将Oracle结果以Html的列表形式展现在邮件中,这次给大家分享下Python项目源码,实现后的结果图如下所示:
这相当于对异常数据一个监控,每天都会把对应的异常数据通过邮件显示出来。如果有异常数据的及时预警,就会采取对应的对异常数据的处理。
主要是几个方法:
1、查询Oracle数据库的方法:
def querystr(key, sql):
datalist = []
try:
global starttime
global endtime
#print(starttime + '\n' + endtime)
connection = cx_Oracle.connect(switch_case(key), nencoding='utf8')
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.AL32UTF8'
cursor = connection.cursor()
#cursor.execute(sql, {'starttime': starttime, 'endtime': endtime})
print(sql)
cursor.execute(sql)
while (1):
rs = cursor.fetchone()
if rs == None:
print('out!')
break
strs = ''
print(rs)
for item in rs:
strs = strs + ('' if str(item) == 'None' else str(item)) + '|'
print(strs[:-1])
# print(rs[0]+'|'+rs[1]+'|'+str(rs[2])+'|'+str(rs[3])+'|'+str(rs[4]))
datalist.append(strs[:-1])
cursor.close()
connection.close()
return datalist
except cx_Oracle.DatabaseError as msg:
print(msg)
return datalist
2、初始化列表方法
tr,td等不知道的可以搜索一下h5的一些基础知识,里面有非常详细的说明。
def initializeForHtml(context, key):
rows = ''
datalist = querystr(key, sqlList[key])
if (len(datalist) < 0):
print('datalist is empty')
return ''
for item in datalist:
if (len(item.split('|')) < 0):
break
#如果异常数据不为0则以黄色标记
if item.__contains__('|0'):
rows = rows + '<tr>'
else:
rows = rows + '<tr bgcolor = "#F9F900">'
for subitem in item.split('|'):
rows = rows + '<td>' + subitem + '</td>'
rows = rows + '</tr>'
print(context % (rows))
return context % (rows)
3、发送邮件
def sendmail(mailcontext):
sender = 'X'
receivers = [x] # 接收邮件,可设置为你的QQ邮箱或者其他邮箱
# receivers = ['wujun9@tcl.com', 'yanweifeng@tcl.com']
# 三个参数:第一个为文本内容,第二个 plain 设置文本格式,第三个 utf-8 设置编码
message = MIMEText(mailcontext, 'html', 'utf-8')
message['From'] = Header("异常数据监控", 'utf-8') # 发送者
message['To'] = Header("制造执行系统科", 'utf-8') # 接收者
subject = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') + ' MES PROD Abnormal Data Monitoring Report'
message['Subject'] = Header(subject, 'utf-8')
try:
smtpObj = smtplib.SMTP('whmail.csot.tcl.com')
smtpObj.sendmail(sender, receivers, message.as_string())
print ("执行时间:"+str(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
print
"邮件发送成功"
except smtplib.SMTPException:
print
"Error: 无法发送邮件"
4、另外因为采用了多个不同DB的检索,所以通过字典获取对应的不同数据库的配置信息。
def switch_case(value):
switcher = {
't3modsql': t3modproddb,
't4modsql': t4modproddb,
't3fabsql': t3fabproddb,
't4fabsql': t4fabproddb,
}
return switcher.get(value, 'wrong value')
5、最后就是主函数,主函数基本就是调用。然后通过定时任务去触发,就会实现定时监控报告的功能。一下sql内容已经删除,可以按需用自己的sql就好了。
if __name__ == "__main__":
sql = '''XXX
'''
sql1 = '''
XXX
'''
context = '''
<table border="1" cellspacing="0" cellpadding="0">
<tr bgcolor="#AEEEEE">
<th>ServerGroup</th>
<th>Messagename</th>
<th>OK</th>
<th>NG</th>
<th>SUM</th>
</tr>%s
</table>
'''
context1 = '''
<table border="1" cellspacing="0" cellpadding="0">
<tr bgcolor="#AEEEEE">
<th>Machinename</th>
<th>Areaname</th>
<th>SuperMachinename</th>
<th>ResourceState</th>
<th>E10STATE</th>
<th>Communicationstate</th>
<th>Count</th>
</tr>%s
</table>
'''
t4modsql='''
xxx
'''
contextCommon = '''
<table border="1" cellspacing="0" cellpadding="0">
<tr bgcolor="#AEEEEE">
<th>ExceptionType</th>
<th>Count</th>
</tr>%s
</table>
'''
speclialContextCommon = '''
<table border="1" cellspacing="0" cellpadding="0">
<tr bgcolor="#AEEEEE">
<th>ExceptionType</th>
<th>Count</th>
</tr>%s
</table>
'''
t3fabsql = '''
xxx
'''
t3modsql = '''
xxx
'''
sqlList = {'t4fabsql': t4fabsql, 't3modsql': t3modsql, 't3fabsql': t3fabsql,'t4modsql': t4modsql}
#input_func()
# h = int(input('InputHour'))
# m = int(input('InputMin'))
# main(h, m)
sendmail('t4MOD Abnormal Data:</br>' + initializeForHtml(contextCommon, 't4modsql')+ '</br>t3FAB Abnormal Data:</br>' \
+ initializeForHtml(contextCommon, 't3fabsql') + '</br>t3MOD Abnormal Data:</br>' + initializeForHtml(contextCommon, 't3modsql')\
+'</br>t4FAB Abnormal Data:</br>' + initializeForHtml(contextCommon, 't4fabsql'))