代码说明
- 主要是为了记录下，程序使用是对比大量的数据是否一样，把数据都丢到redis中，然后通过redis来判断是否重复了，把异常的文件名写入到xls文件中，然后使用HTMLTable 模块生成表格。
#!/usr/bin/python3
"""
1. 该脚本用途是对比话单,异常的写入xls中，然后通过邮件发送html中表格格式
"""
import time, os, configparser, logging.config,sys,redis
from openpyxl import Workbook
from HTMLTable import HTMLTable
import smtplib
from email.header import Header
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication
#链接redis
def redisconn():
try:
client = redis.StrictRedis(host=RedisHost, port=int(RedisPort), db=int(DBname))
# logger.info("Redis List Info: " + str(client.client_list()))
logger.info("redis conn ok...")
except redis.ConnectError as err:
logger.error("Conn redis error: " + str(err))
return client
#获取对应文件时间
def filetime(filename):
getfiletimeStamp= int(str(os.path.getmtime(filename)).split(".")[0])
timeArray = time.localtime(getfiletimeStamp)
otherStyleTime = time.strftime("%Y-%m-%d", timeArray)
logger.info("get " + filename + " file modify time: " + otherStyleTime)
return otherStyleTime
#筛选出对应时间不对的文件
def Filterfile(filenamelist):
returnfilelist = []
for file in filenamelist:
getfilemodifytime = filetime(file)
if GetNowTime == getfilemodifytime:
returnfilelist.append(file)
return returnfilelist
def listfile(path):
list_name = []
for file in os.listdir(path):
file_path = os.path.join(path, file)
if os.path.isdir(file_path):
listDir(file_path)
else:
list_name.append(file_path)
return list_name
def writejifeirediskeys(filelist):
logger.info("write jifei sdr and cdr key")
for file in filelist:
with open(file,'r') as File:
for line in File.readlines():
line = line.strip("\n")
linelist =line.split(",")
pfname = linelist[0]
huadantype = linelist[1]
huadanname = linelist[2]
KeyName = "jifei_" + pfname +"_" + huadantype
if redisclient.sismember(KeyName,huadanname) == False:
redisclient.sadd(KeyName,huadanname)
def writec1c2odrediskeys(filelist):
logger.info("write c1c2od sdr and cdr key")
for file in filelist:
with open(file,'r') as File:
for line in File.readlines():
line = line.strip("\n")
linelist =line.split(",")
pfname = linelist[0]
huadantype = linelist[1]
huadanname = linelist[2]
KeyName = "c1c2od_" + pfname +"_" + huadantype
if redisclient.sismember(KeyName,huadanname) == False:
redisclient.sadd(KeyName,huadanname)
# logger.info( file + " write redis key ok, Count Key element: " + str(CountKey))
#查询redis中的key
def SelectredisKeys():
keyslist = []
getkeys = redisclient.keys()
for key in getkeys:
key = str(key, encoding = 'utf-8')
keyslist.append(key)
return keyslist
#删除key
def DelredisKeys(keylist):
for key in keylist:
if "_SDR" in key or "_CDR" in key:
logger.info("redis del key " + key )
redisclient.delete(key)
#计费话单对应和c1c2od话单差异
def JifeiContrastDifferenceScdr():
logger.info("diif jifei & c1c2od csdr....")
writexlslist = []
jifeikeylist = [key for key in GetAllKey if "jifei" in key ]
for jifeikey in jifeikeylist:
#查询出key中全部的集合
# logger.info("Now Key: " + jifeikey )
c1c2odkeyname = jifeikey.replace("jifei","c1c2od")
platform = jifeikey.replace("jifei_","").split("_")[0]
csdrtype = jifeikey.split("_")[-1]
for keys in redisclient.smembers(jifeikey):
keys = str(keys, encoding = 'utf-8')
indexkey = keys.strip("\n")
IfRedisMsg = redisclient.sismember(c1c2odkeyname,indexkey)
if IfRedisMsg == False:
writexlslist.append(platform)
writexlslist.append(csdrtype)
writexlslist.append(indexkey)
ws1.append(writexlslist)
# logger.error("ERROR: " + str(writexlslist) )
writexlslist = []
#c1c2od和计费话单差异
def c1c2odContrastDifferenceScdr():
logger.info("diif c1c2od & jifei csdr....")
writexlslist = []
c1c2odkeylist = [key for key in GetAllKey if "c1c2od_" in key ]
for c1c2odkey in c1c2odkeylist:
c1c2odkeyname = c1c2odkey.replace("c1c2od","jifei")
platform = c1c2odkey.replace("c1c2od_","").split("_")[0]
csdrtype = c1c2odkey.split("_")[-1]
for keys in redisclient.smembers(c1c2odkey):
keys = str(keys, encoding = 'utf-8')
indexkey = keys.strip("\n")
IfRedisMsg = redisclient.sismember(c1c2odkeyname,indexkey)
if IfRedisMsg == False:
writexlslist.append(platform)
writexlslist.append(csdrtype)
writexlslist.append(indexkey)
ws2.append(writexlslist)
# logger.error("ERROR: " + str(writexlslist) )
writexlslist = []
#编写html邮件
def WriteHtml():
# 标题样式
table.caption.set_style({
'font-size': '18px',
})
# 表格样式，即<table>标签样式
table.set_style({
'border-collapse': 'collapse',
'word-break': 'keep-all',
'white-space': 'nowrap',
'font-size': '14px',
})
# 统一设置所有单元格样式，<td>或<th>
table.set_cell_style({
'border-color': '#000',
'border-width': '1px',
'border-style': 'solid',
'padding': '5px',
})
# 表头样式
table.set_header_row_style({
'font-size': '15px',
})
# 覆盖表头单元格字体样式
table.set_header_cell_style({
'padding': '15px',
})
mail_body = table.to_html()
return mail_body
#查询对应平台话单区分梳理
def Comparisondifferencenumber():
# 表头行
table.append_header_rows((
('日期','平台编码', '话单类型','xx采集文件数','采集文件数','xx文件数差异','文件数差异'),
))
for pt in all_list_ptname:
dpc1c2odsdrkey = "c1c2od_" + pt + "_SDR"
dpc1c2odcdrkey = "c1c2od_" + pt + "_CDR"
dpjifeisdrkey = "jifei_" + pt + "_SDR"
dpjifeicdrkey = "jifei_" + pt + "_CDR"
c1c2odsdrnum = redisclient.scard(dpc1c2odsdrkey)
jifeisdrnum = redisclient.scard(dpjifeisdrkey)
c1c2odcdrnum = redisclient.scard(dpc1c2odcdrkey)
jifeicdrnum = redisclient.scard(dpjifeicdrkey)
if c1c2odsdrnum != 0 and jifeisdrnum != 0 :
table.append_data_rows(((GetNowTime,pt,'SDR', jifeisdrnum,c1c2odsdrnum,str(jifeisdrnum -c1c2odsdrnum) , str(c1c2odsdrnum - jifeisdrnum )),))
if c1c2odcdrnum != 0 and jifeicdrnum != 0:
table.append_data_rows(((GetNowTime,pt,'CDR', jifeicdrnum,c1c2odcdrnum ,str( jifeicdrnum - c1c2odcdrnum ) , str( c1c2odcdrnum - jifeicdrnum ) ),))
if __name__ == '__main__':
curpath = os.path.dirname(os.path.realpath(__file__))
cfgpath = os.path.join(curpath, "./cfg/config.ini")
conf = configparser.ConfigParser()
conf.read(cfgpath)
all_list_ptname = conf.get('main', 'ptname').split(',')
jifei_path = conf.get('main', 'jifeipath')
c1c2od_path = conf.get('main', 'c1c2odpath')
datapaht = conf.get('main', 'datapaht')
RedisHost = conf.get('main', 'RedisHost')
RedisPort = conf.get('main', 'RedisPort')
DBname = conf.get('main', 'DBname')
Is_send_mail = conf.get('sendmail', 'ormail')
CCpersonnel = conf.get('sendmail', 'CCpersonnel')
senderpeople = conf.get('sendmail', 'sender')
smtpserver = conf.get('sendmail', 'smtpserver')
username = conf.get('sendmail', 'username')
password = conf.get('sendmail', 'password')
receiverpeople = conf.get('sendmail', 'rootmail')
LogRunSentence = " [ ! -d ./log ] && mkdir -p ./log"
os.system(LogRunSentence)
logging.config.fileConfig("./cfg/logger.conf")
logger = logging.getLogger("rotatfile")
logger.info("加载配置文件信息完成...")
GetNowTime = str(time.strftime("%Y-%m-%d", time.localtime()))
GetTowdata = str(time.strftime("%d", time.localtime()))
#对比异常的话单列表
AbnormalsdrList = []
AbnormaljifeiList = []
jifeilist = listfile(jifei_path)
c1c2odlist = listfile(c1c2od_path)
logger.info("get jifei name list : " + str(jifeilist))
logger.info("get c1c2od name list : " + str(c1c2odlist))
getjifeitimeoklist = Filterfile(jifeilist)
getc1c2odtimeoklist = Filterfile(c1c2odlist)
logger.info("get jifei ok name list : " + str(getjifeitimeoklist))
logger.info("get c1c2od ok name list : " + str(getc1c2odtimeoklist))
redisclient = redisconn()
#查询key然后再删除历史的key
GetKey = SelectredisKeys()
logger.info("redis keys ==> " + str(GetKey) )
if "2" == GetTowdata:
logger.info("Now time is The 2nd day of each month Exe del key")
DelredisKeys(GetKey)
else:
logger.info("Not Run Del Redis Key")
#写入数据到redis中
writejifeirediskeys(getjifeitimeoklist)
writec1c2odrediskeys(getc1c2odtimeoklist)
#查询key
GetAllKey = SelectredisKeys()
logger.info("redis keys ==> " + str(GetAllKey) )
if GetAllKey:
#初始化写入表格
xlsfilename = datapaht + "/文件对比差异"+GetNowTime + ".xlsx"
wb = Workbook()
ws1 = wb.active
ws2 = wb.create_sheet('c1c2od&计费差异')
ws1.title = 'xx&xx明细'
ws1['A1'] = '平台编码'
ws1['B1'] = '话单类型'
ws1['C1'] = 'xx文件名'
ws2.title = 'xx&xx'
ws2['A1'] = '平台编码'
ws2['B1'] = 'xx类型'
ws2['C1'] = 'xx文件名'
JifeiContrastDifferenceScdr()
c1c2odContrastDifferenceScdr()
wb.save(xlsfilename)
#表格标题
table = HTMLTable(caption='xx&xx完整性对比')
Comparisondifferencenumber()
mail_body = WriteHtml()
#判断是否发送邮件
if int(Is_send_mail) == 0:
# 邮件主题
mail_title = 'xx&xx完整性对比'
# 邮件内容, 格式, 编码
message = MIMEText(mail_body, 'html', 'utf-8')
message['From'] = senderpeople
message['To'] = receiverpeople
message['Cc'] = CCpersonnel
message['Subject'] = Header(mail_title, 'utf-8')
smtp = smtplib.SMTP()
smtp.connect('smtp.channelsoft.com')
smtp.login(username, password)
logger.info("is send html table info to Emial.")
msg = smtp.sendmail(senderpeople, receiverpeople.split(',')+ CCpersonnel.split(',') , message.as_string())
sendfilename = xlsfilename
msg = MIMEMultipart()
msg['From'] = senderpeople
msg['To'] = receiverpeople
msg['Cc'] = CCpersonnel
msg['Subject'] = Header(mail_title, 'utf-8')
xlsxpart = MIMEApplication((open(sendfilename, 'rb').read()))
xlsxpart.add_header('Content-Disposition', 'attachment',filename=sendfilename)
msg.attach(xlsxpart)
msg = smtp.sendmail(senderpeople,receiverpeople.split(',') + CCpersonnel.split(',') , msg.as_string())
logger.info("is send xls file to Emial.")
smtp.quit()
redisclient.connection_pool.disconnect()
else:
logger.info("Not Send Email Info")
else:
logger.info("upload file time error, exit Run app!!!")
[main]
#平台标识
ptname = ALY,JSDX,CQGX
#主文件绝对路径
jifeipath = /home/devops/Python/csdrcomparison/data/upload
#提供的话单文件路径
c1c2odpath = /home/devops/Python/csdrcomparison/data/down
#数据存放路径
datapaht= /home/devops/Python/csdrcomparison/data/csdrcomparison
#链接redis
RedisHost = 127.0.0.1
RedisPort = 6379
DBname = 0
[sendmail]
#是否发送邮件,0是发送,1不发送
ormail = 0
rootmail = xxxx@qq.com,xxxxx@163.com
CCpersonnel = xxxx@qq.com,xxxxx@163.com
sender = xx@xxx.com
smtpserver = smtp.xxx.com
username = xx@xxx.com
password = xxxx
###############################################
[loggers]
keys=root,rotatfile
[logger_root]
level=DEBUG
handlers=hand01,hand02
[logger_rotatfile]
handlers=hand01,hand02
qualname=rotatfile
propagate=0
###############################################
[handlers]
keys=hand01,hand02
[handler_hand01]
class=handlers.RotatingFileHandler
level=DEBUG
formatter=form01
args=('./log/csdr.log', 'a', 50*1024*1024, 100)
[handler_hand02]
class=StreamHandler
level=DEBUG
formatter=form01
args=(sys.stderr,)
###############################################
[formatters]
keys=form01
[formatter_form01]
format=%(asctime)s %(thread)d %(filename)s:%(lineno)d %(levelname)s %(message)s
datefmt=%Y-%m-%d %H:%M:%S
网友评论