python练手_特定邮箱收件后数据比对发送

“”"第一步定时执行 ok
第二步收取邮件 ok
收取指定邮箱的邮件：zibofandianzha@163.com，获取附件第一列是号码。
第三步数据导入
在134.35.10.10上布置服务端（因为需要使用生产系统数据库）
第四步数据生成

第五步邮件派发 ok
将表格作为附件给 zibofandianzha@163.com 反馈。
“”"
from email.parser import HeaderParser
import poplib,email,telnetlib
import datetime,time,sys,traceback
from email.parser import Parser
from email.header import decode_header
from email.header import Header
from email.utils import parseaddr
import openpyxl
import xlrd
import pandas as pd
import cx_Oracle #Oracle 数据库连接
import imaplib
import smtplib #发送邮件模块
from email.mime.text import MIMEText #定义邮件内容
from email.mime.multipart import MIMEMultipart #用于传送附件
import os

#时间计算
def get_current_time(input_date =‘0’):
#如果时间传入为空
if input_date ==‘0’:
ct = time.time()-246060
local_time = time.localtime(ct)
data_head = time.strftime(“%Y%m%d%H%M%S”, local_time)
data_secs = abs(ct - round(ct)) * 1000
time_stamp = “%s%03d” % (data_head, data_secs)
else:
time_stamp = input_date +‘120000001’
return time_stamp

#邮箱配置文件
def email_type():
wb = openpyxl.load_workbook(‘./xx数据/邮箱配置文件.xlsx’)
print(‘email_type’,‘邮箱配置文件’)
ws = wb.active
minr = ws.min_row
minc = ws.min_column
maxr = ws.max_row
maxc = ws.max_column
# 数据库内容
#print(minr, minc, maxr, maxc)
rngs = ws.iter_rows(min_row=minr, min_col=minc,
max_row=maxr, max_col=maxc)
row_cs = 0
for row in rngs:
if row_cs == 1:
value = [c.value for c in row]
user = value[0]
password = value[1]
eamil_server = value[2]
smtpserver = value[3]
receives = value[4].replace(" “,”“).replace(”[“,”“).replace(”]“,”“).replace(”'“,”“).split(”,")
from_id = value[5]
row_cs += 1
return user,password,eamil_server,smtpserver,receives,from_id

def mysql_execute(in_sql, leixing):
# 登录数据库
#conn = pymysql.connect(host=‘127.0.0.1’, port=3306, user=‘szc’, password=‘szcNSP850219’, database=‘szc_sql’,
# charset=‘utf8’)

dsn = "134.80.200.216/pdbzbjs1"
conn = cx_Oracle.connect(user="zbweb", password="zibo_533_03", dsn=dsn, encoding="UTF-8")

# 得到一个可以执行SQL语句的光标对象
cursor = conn.cursor()
# 数据库执行导入的语句
if leixing == '数量':
    # 反馈数量
    count = cursor.execute(in_sql)
elif leixing == '单条':
    # 反馈单条
    cursor.execute(in_sql)
    count = cursor.fetchone()[0]
elif leixing == '多条':
    # 反馈多条
    cursor.execute(in_sql)
    count = cursor.fetchall()
elif leixing == '编辑':
    count = cursor.execute(in_sql)
    conn.commit()

# 关闭光标对象
cursor.close()
# 关闭数据库连接
conn.close()
# 反馈
return count

#因为xx提供的数据是xls的，需要使用xlrd
def enclosure_handle():

wb = xlrd.open_workbook('./xx数据/xx数据.xls')

#读取第一个表格内容
ws = wb.sheet_by_index(0)
ccol_datas = ws.col_values(0)

ccol_cs = len(ccol_datas)

if ccol_cs == 0:ccol_cs=1
ccol_num = 0

#数据写入前先清空数据表，以免有其他号码掺杂其中
sql = "delete from shzc.lw_xdjl_zhsy_hm_drb "
mysql_execute(sql, '编辑')
#遍历数据内容导入数据库
for ccol_data in ccol_datas:
    if ccol_data is not None or ccol_data != '':
        xls_to_sql(ccol_data[0:11])
        ccol_num += 1
        if ccol_num in (1,round(ccol_cs*0.33,0),round(ccol_cs*0.66,0),round(ccol_cs*1,0)) :
            print('xx总数据:', ccol_cs,' 已完成:',ccol_num,' 完成比例:',str(round(ccol_num/ccol_cs,2)*100)+'%')
            continue
#最后一步将导入号码时间改为当前时间
sql = "update shzc.lw_xdjl_zhsy_hm_drb a set a.in_time = sysdate, a.servnumber = trim(a.servnumber) where a.in_time is null"
mysql_execute(sql, '编辑')
#执行完成后返回 成功
return 'ok'

#专门用来将号码导入数据库的函数
def xls_to_sql(servnumber):
sql = "insert into shzc.lw_xdjl_zhsy_hm_drb (servnumber,user_id) values(‘%s’,‘%s’) " % (servnumber, ‘liuwei’)
mysql_execute(sql, ‘编辑’)

#号码数据写入数据库后，就可以运行计算过程，生成表格等待发送
def sql_handle():

try:
    sql = "drop table SHZC.lw_xdjl_zhsy_hm_sja "
    mysql_execute(sql, '编辑')
except:
    pass

#根据 shzc.lw_xdjl_zhsy_hm_drb 表里号码生成数据
sql = "create table SHZC.lw_xdjl_zhsy_hm_sja as select t.user_id,t.in_time,t.SERVNUMBER,b.subsid,b.acctid,pp.prodname,B.STATUS,zt.dictname 状态,b.statusdate 状态时间,b.STOPKEY,b.createdate 入网时间,b.settleday 账期日,B.ENUM,B.IMSI,B.REGISTERORGID,B.OWNERORGID,row_number() over (partition by b.servnumber order by nvl(b.createdate,sysdate-9999) desc ) 排名 from shzc.lw_xdjl_zhsy_hm_drb t,(select * from  zhyw.subscriber  b where b.status not in ('US26','US28')  ) B ,(select * from tbcs.dict_item@bcv where groupid='US') zt ,tbcs.product@bcv pp where zt.dictid(+)=b.status and pp.prodid(+)=b.prodid and t.servnumber=b.servnumber(+)"
mysql_execute(sql, '编辑')

try:
    sql = "drop table SHZC.lw_xdjl_zhsy_hm_sjb"
    mysql_execute(sql, '编辑')
except:
    pass

sql = "create table SHZC.lw_xdjl_zhsy_hm_sjb as select * from SHZC.lw_xdjl_zhsy_hm_sja a where a.排名 = 1 "
mysql_execute(sql, '编辑')

#清空导入表
sql = "delete shzc.lw_xdjl_zhsy_hm_drb "
mysql_execute(sql, '编辑')

try:
    sql = "drop table SHZC.lw_xdjl_zhsy_hm_sjc "
    mysql_execute(sql, '编辑')
except:
    pass

sql = "create table SHZC.lw_xdjl_zhsy_hm_sjc as select a.user_id, a.in_time, a.servnumber, a.subsid, a.prodname, a.状态, a.入网时间, a.ownerorgid, b.huadan_typename, b.roamcity_id, c.provincename, c.areaname, b.COUNTY_ID,b.lac_id, b.cell_id, b.bts_name, b.roamcity_name, b.geo_longitude, b.geo_latitude from SHZC.lw_xdjl_zhsy_hm_sjb a,zibo.lw_sasz_xdjl_zhsy_bd b,shzc.yqjk_fxmyd_sjzb c where a.servnumber = b.product_no(+) and b.roamcity_id = c.areaid(+)"
mysql_execute(sql, '编辑')

try:
    sql = "drop table SHZC.lw_xdjl_zhsy_hm_sjd "
    mysql_execute(sql, '编辑')
except:
    pass

sql = "create table SHZC.lw_xdjl_zhsy_hm_sjd as select a.user_id,a.in_time,a.servnumber,a.subsid,a.prodname,a.状态,a.入网时间,a.ownerorgid,a.huadan_typename,a.provincename,nvl(a.roamcity_name, a.areaname) roamcity_name,a.COUNTY_ID,a.lac_id,a.cell_id,nvl(a.bts_name, b.name) bts_name,a.geo_longitude,a.geo_latitude from SHZC.lw_xdjl_zhsy_hm_sjc a,zhyw.rpt_county b where substr(a.ownerorgid,8,1)=b.county_id(+)"
mysql_execute(sql, '编辑')
#插入记录表
sql = "insert into SHZC.lw_xdjl_zhsy_hm_sjbd select A.USER_ID, A.IN_TIME,A.SERVNUMBER,A.SUBSID,A.PRODNAME,A.状态,A.入网时间,A.OWNERORGID,A.HUADAN_TYPENAME,A.PROVINCENAME,A.ROAMCITY_NAME,a.COUNTY_ID,A.LAC_ID,A.CELL_ID,A.BTS_NAME,A.GEO_LONGITUDE,A.GEO_LATITUDE from SHZC.lw_xdjl_zhsy_hm_sjd a"
mysql_execute(sql, '编辑')

#结果数据生成表格准备发送
sql =  sql = "select A.SERVNUMBER 号码,A.状态,nvl(case when a.roamcity_name is null then '淄博' end,a.roamcity_name)||'_'||nvl(case when nvl(a.状态,'0')<>'正使用' then nvl(a.county_id,a.bts_name) when nvl(a.roamcity_name,'0')='淄博' then nvl(a.county_id, a.bts_name) end,'不详') 使用区县 from SHZC.lw_xdjl_zhsy_hm_sjd a"
#上面语句部分代码MySQL不支持，等正式使用在测试
#sql = "select A.SERVNUMBER 号码,A.状态,a.roamcity_name 使用区县 from lw_xdjl_zhsy_hm_sjd a"

#conn_new = pymysql.connect(host='127.0.0.1', port=3306, user='szc', passwd='szcNSP850219', db='szc_sql',charset='utf8')

dsn = "134.80.200.216/pdbzbjs1"
conn = cx_Oracle.connect(user="zbweb", password="zibo_533_03", dsn=dsn, encoding="UTF-8")

df = pd.read_sql("""%s""" % sql, con=conn)
df.to_excel("./xx数据/xx结果下载.xlsx", index=False)

return '数据下载完成'

#邮件下载
class down_email():

def __init__(self,user,password,eamil_server,input_date,from_id):
    # 输入邮件地址, 口令和POP3服务器地址:
    self.user = user
    # 此处密码是授权码,用于登录第三方邮件客户端
    self.password = password
    self.pop3_server = eamil_server
    self.input_date = input_date
    self.from_id = from_id
    print('邮箱用户：',self.user,'邮箱密码：',self.password,'邮箱服务：', self.pop3_server)

def parseBody(self,message):
    # 解析邮件内容
    # 循环信件中的每一个mime的数据块
    for part in message.walk():
        # 这里要判断是否是multipart，是的话，里面的数据是一个message 列表
        if not part.is_multipart():
            charset = part.get_charset()
            contenttype = part.get_content_type()
            name = part.get_param("name")  # 如果是附件，这里就会取出附件的文件名
            #print('如果是附件，这里就会取出附件的文件名',name)
            if name:  # 在这里额外添加中国移动的条件，judgedate(name)==1
                fh = email.header.Header(name)
                fdh = email.header.decode_header(fh)
                fname = fdh[0][0]
                attach_data = part.get_payload(decode=True)  # 解码出附件数据，然后存储到文件中
                try:
                    f = open('./xx数据/xx数据.xls', 'wb')  # 注意一定要用wb来打开文件，因为附件一般都是二进制文件
                except:
                    f = open('./xx数据/xx数据.xls', 'wb')
                f.write(attach_data)
                f.close()
            else:
                fname = 'a.xls'
    return str(fname).split("'")[1]

def run_ing(self):
    str_day = str(datetime.date.today())  # 日期赋值
    print('日期赋值',str_day)
    if self.pop3_server[0:3] == 'ima' or self.pop3_server[0:3] == 'IMA':
        try:
            telnetlib.Telnet(self.pop3_server, 993)
            self.server = imaplib.IMAP4_SSL(self.pop3_server, 993)
        except:
            time.sleep(50)
            self.server = imaplib.IMAP4(self.pop3_server, 110)
        self.server.login(self.user, self.password)
        self.server.select()

        # 搜索邮件内容 shazhicheng@sd.chinamobile.com
        typ, data = self.server.search(None, 'ALL')
        # 搜索邮件内容
        data_a =str(data[0]).replace('b','').replace("'","").replace(' ',',').split(",")
        count = len(data_a)
        data_b = 9999
        pcount = 0
        subject_cs = 0
        for num in data_a[::-1]:
            #print('num',num)
            if num == '' or num is None:
                continue
            typ, data_id = self.server.fetch(num, '(RFC822)')
            text = data_id[0][1]
            message = email.message_from_bytes(text)  # 转换为email.message对象

            yj_from = message.get('From')
            yj_from_name = yj_from[yj_from.index('<')+1:-1]

            print('发件人邮箱地址：',yj_from_name,'目标邮箱地址：',self.from_id)
            try:
                #防止邮件时间报错
                yj_date = str(message.get('Date')[0:28])
            except:
                yj_date =get_current_time(self.input_date)[0:28]
            # 检查 发送人邮箱地址，符合的反馈,self.from_id 提取自配置文件的目标邮箱
            if yj_from_name == self.from_id:

                # 检测是否已经发送过邮件，没有发送过的话才发送
                sql = sql = "SELECT count(Email_title)  FROM zbweb.customer_service_title where Email_title='%s' and in_time='%s'" % ( 'ga_sasz_mail_lcxh', yj_date)
                num = mysql_execute(sql, '单条')
                print('数据库中该邮件是否已发送过', num)

                #没处理过的提取附件，附件提取成功的就修改 data_b 使程序进行
                if num == 0:
                    # 没处理过的话，先插入表中，如果没有附件也不处理了，以免邮箱检索卡住
                    # 将标题与时间计入数据库，以防下次再处理
                    sql = "insert into zbweb.customer_service_title (Email_title,IN_TIME) values('%s','%s') " % (
                        'ga_sasz_mail_lcxh', yj_date)
                    num = mysql_execute(sql, '编辑')
                    # subject_cs 赋值
                    subject_cs += 1
                    f = self.parseBody(message)  # 返回值是为了把文件名返回主函数
                    if f.strip() != '':
                        data_b = subject_cs
                    break
            # 如果检索邮件数大于总邮件数就跳出，或者大于50就跳出，以免异常
            pcount += 1
            #print('pcount', pcount, 'count', count)
            if pcount > count or pcount >= 10:
                break
            else:
                continue

        self.server.close()
        self.server.logout()

        #如果 data_b 没有经过重新赋值，说明没有附件，就给他0，让下面附件处理不运行
        if data_b == 9999:
            data_b = 0

        return data_b  # 返回值是为了把文件名返回主函数

#邮件推送
def email_out(user,password,smtpserver,sender,receives,title,content):
‘’’
:param user: 发送邮箱用户名
:param password:发送邮箱密码
:param smtpserver:发送服务器
:param sender:发送方邮箱
:param receives:接收方邮箱
:param title:邮件标题
:param content:邮件正文
:return:
‘’’

#邮件附件最后带日期，如果附件没有带点，就原名发出

# 发送邮件主题和内容
subject = title

#文字描述中 \n 替换成 <br/>，头尾
content_head = '<html><strong>尊敬的领导，您好：</strong><br/><br/>        '
content_tail = '<br/><h4>        此致<br/>敬礼<br/></h4> <div  align=right> <strong> 山东移动淄博分公司 </strong> </div>'
#content.replace('\n','<br/>').replace('^', ' ') 正文描述中的\n替换成换行，^还原成空格
content_new = content_head + content.replace('\\n','<br/>').replace('^', ' ') + content_tail
send_file_path = r".\xx数据\xx结果下载.xlsx"
send_file_path = open(send_file_path, 'rb').read()  # 'rb'表示r读取，b表示二进制方式读取
att = MIMEText(send_file_path, 'base64', 'utf-8')  # 调用传送附件模块，传送附件
att["Content-Type"] = 'application/octet-stream'
# file_name 主题名加原来文件名后缀
file_name = 'xx查询结果反馈.xlsx'
filename = Header(file_name,'utf-8').encode()
att["Content-Disposition"] = 'attachment;filename=%s' % Header(file_name,'utf-8').encode() # 附件描述外层要用单引号
# 构建发送与接收信息
msgRoot = MIMEMultipart()  # 发送附件的方法定义为一个变量
msgRoot.attach(MIMEText(content_new, 'html', 'utf-8'))  # 发送附件的方法中嵌套发送正文的方法
msgRoot['subject'] = subject
#接收人显示内容
msgRoot['From'] = sender
msgRoot['Bcc'] = ','.join(receives)
msgRoot.attach(att)  # 添加附件到正文中
# SSL协议端口号要使用465
smtp = smtplib.SMTP_SSL(smtpserver, 465)
# HELO 向服务器标识用户身份
smtp.helo(smtpserver)
# 服务器返回结果确认
smtp.ehlo(smtpserver)
# 登录邮箱服务器用户名和密码
smtp.login(user, password)
smtp.sendmail(sender, receives, msgRoot.as_string())
smtp.quit()
return "发送成功"

if name == ‘main’:

input_date = '0'

# 循环执行
while True:
    in_time = get_current_time(input_date)
    in_day = in_time[0:8]

    # 输入邮件地址, 口令和POP3服务器地址:
    user, password, eamil_server, smtpserver, receives,from_id = email_type()
    #获取邮箱类
    email_class = down_email(user=user, password=password, eamil_server=eamil_server, input_date=input_date,from_id=from_id)
    #调用 email_class.run_ing() 获取邮件附件
    try:
        # 接收邮件并判断是否有符合的结果
        email_list = email_class.run_ing()
        #print('正常 email_list:', email_list)
    except:
        email_list = 0
        #print('异常 email_list:', email_list)

    #临时邮件接收不执行，先调试处理部分。正式运行时 下面 email_list = 1 需屏蔽
    #email_list = 1

    #定义一个状态，看看附件内容是否正常处理了，没处理就不执行下面内容
    handle_statr = 'not'
    #如果获取一个正常附件
    if email_list == 1 :
        print('对附件内容进行解析处理')
        try:
            handle_statr = enclosure_handle()
        except:
            pass
    #查看 状态
    print('handle_statr',handle_statr)
    #handle_statr == 'ok'

    #执行结果正常，可以处理后续数据，这里也单独弄一个函数运行
    sql_statr = '数据下载失败'
    if handle_statr =='ok':
        try:
            sql_statr = sql_handle()
        except:
            sql_statr = '数据下载失败'
    # 查看 状态
    print('sql_statr', sql_statr)

    if sql_statr =='数据下载完成':
        title ='淄博移动防电诈号码数据查询'
        content = '淄博移动防电诈号码数据查询，请您过目'
        try:
            email_out_statr = email_out(user,password,smtpserver,user,receives,title,content)
            print(in_time,'email_out_statr',email_out_statr)
        except:
            print(in_time,'email_out_statr','发送失败')
    # 执行后延时60秒
    time.sleep(60)