python连接oracel数据库，提取数据后制图并通过邮件发送

总体要求：

               通过python自动从oracle提取原始数据，对原始数据制图，并发送邮件。发邮件时，通过oracle提取的数据保存为excel作为附件发送，图片及相关文字内容通过邮件正文发送。

====================================================================================================================================================================================================

我的电脑系统需要是 ：W-amd64  
 
1、先安装python-3.6.0
安装的时候注意勾选 add to path

2、安装orcael软件

winx64_12102_data  总共两部分，需要全部下载，然后两个都要解压，解压的路径要相同。
安装的时候选择桌面版

3、安装vs_community
安装的时候记得勾选tools for python。
安装过程中会提示6ee7bbee8435130a869cf971694fd9e2.cab找不到。把6ee7bbee8435130a869cf971694fd9e2.cab下载下来，提示
报错的时候选择从本地导入。

4、安装各种模块

主要有numpy xlwt matplotlib cx_Oracle

安装命令为：pip install xlwt     其它类似

除了cx_Oracle 其它的均可在cmd下 通过pip install 命令安装
cx_Oracle  需要从本地安装，下载cx_Oracle-5.2.1+oci12c-cp36-cp36m-win_amd64.whl到本地，通过pip install 路径\cx_Oracle-5.2.1+oci12c-cp36-cp36m-win_amd64.whl进行安装

云盘链接: https://pan.baidu.com/s/1nvjZH2L 密码: hn4y

====================================================================================================================================
====================================================================================================================================

代码，代码备注已经很详细了，这里不再赘述。

#!/usr/bin/python#coding=utf-8import sysimport cx_Oracle import datetimeimport xlwtimport numpy as np  import matplotlib.pyplot as plt#邮件相关的库import smtplibfrom email.mime.text import MIMETextfrom email.mime.multipart import MIMEMultipartfrom email.mime.image import MIMEImagefrom email.mime.application import MIMEApplicationfrom email.header import Header#制图相关的库#根据系统时间定义相关变量ha=(datetime.datetime.now()-datetime.timedelta(days=7)).strftime("%Y%m%d")#把2017020200格式的时间赋值给ha，后边sql中需要依据时间进行输入he=datetime.datetime.now().strftime("%Y-%m-%d")ho=datetime.datetime.now().strftime('%m-%d-%H')#连接数据库配置ip 用户名 密码conn = cx_Oracle.connect('gzhxx/GZhxx@100.66.94.100/oss')    cursor = conn.cursor ()printHeader = True#定义sql语句sql = "select city 城市,substr(RiQi,5,8) 日期,XiaoShi 小时,Total_GB 总流量,RRC_Setup_Succ_Rate RRC连接建立成功率,Erab_Setup_Succ_Rate ERAB建立成功率,S1_Setup_Succ_Rate 信令建立成功率,Radio_Setup_Succ_Rate 无线接通率,HO_Succ_Rate_Prep 切换成功率 ,Cell_Availabilty 小区可用率 FROM omc.kpi2 where city <> 'HKGaoSsu' and  ShiDuan = 'Whole_Day' and (QuXian = 'GZ_WholeNetwork' or QuXian = 'CityLevel') and ShiJian >=%s order by city,substr(RiQi,5,8)" % (ha)cursor.execute (sql)#执行sql语句rows = cursor.fetchall()#读取sql结果count=len(rows)f = xlwt.Workbook() #创建工作簿sheet1 = f.add_sheet(u'sheet1',cell_overwrite_ok=True) #创建sheeti=0#定义从第一行开始存数据，0代表第一行     #定义表头格式 biaotou = ['区域','日期','小时','总流量','RRC连接建立成功率','ERAB建立成功率','S1信令建立成功率','无线接通率','切换成功率','小区可用率']k=0for k in range(10):    sheet1.write(0,k,biaotou[k])    k+1#f.save('%s.xls' % (he))#保存文件,这里就用了上文生成的时间变量作为文件名            i=0#把sql取的数据存到excel中for i in range(len(rows)):    p=i+1    k=0#每进行一次for循环就重置一次k值，确保数据写入时（如果这列有数据的话）总是从第一列开始    if type(rows[i])==tuple:#表格的第一列开始写数据，第一列，第二例。。。。（如果改行只有一个单元格的数据，将不会触发该动作）        for k in range(len(rows[i])):            sheet1.write(p,k,rows[i][k])            k+1            else:        sheet1.write(p,k,rows[i])#表格的第一行开始写。第一行，第二行。。。。        i+1f.save('D:\kpi\%s.xls' % (he))#保存文件,这里就用了上文生成的时间变量作为文件名#临时参数qriqi = []qliuliang = []qrrc = []qerab = []qs1 =[]qwuxian = []qqiehuan = []qkeyong = []triqi = []tliuliang = []trrc = []terab = []ts1 =[]twuxian = []tqiehuan = []tkeyong = []griqi = []gliuliang = []grrc = []gerab = []gs1 =[]gwuxian = []gqiehuan = []gkeyong = []sriqi = []sliuliang = []srrc = []serab = []ss1 =[]swuxian = []sqiehuan = []skeyong = []#读取数据到对应格式s=0for s in range(len(rows)):    if rows[s][0]=='GZQDN                    ':        qriqi.append(rows[s][1])        qliuliang.append(rows[s][3])        qrrc.append(rows[s][4])        qerab.append(rows[s][5])        qs1.append(rows[s][6])        qwuxian.append(rows[s][7])        qqiehuan.append(rows[s][8])        qkeyong.append(rows[s][9])    elif rows[s][0]=='GZTR                     ':        triqi.append(rows[s][1])        tliuliang.append(rows[s][3])        trrc.append(rows[s][4])        terab.append(rows[s][5])        ts1.append(rows[s][6])        twuxian.append(rows[s][7])        tqiehuan.append(rows[s][8])        tkeyong.append(rows[s][9])            elif rows[s][0]=='GZ_WholeNetwork          ':         griqi.append(rows[s][1])        gliuliang.append(rows[s][3])        grrc.append(rows[s][4])        gerab.append(rows[s][5])        gs1.append(rows[s][6])        gwuxian.append(rows[s][7])        gqiehuan.append(rows[s][8])        gkeyong.append(rows[s][9])    elif rows[s][0]=='SmallCell                ':        sriqi.append(rows[s][1])        sliuliang.append(rows[s][3])        srrc.append(rows[s][4])        serab.append(rows[s][5])        ss1.append(rows[s][6])        swuxian.append(rows[s][7])        sqiehuan.append(rows[s][8])        skeyong.append(rows[s][9])    else:        s+1#制图 #流量 xData = qriqi      yData1 = qliuliangyData2 = tliuliangyData3 = sliuliangyData4 = gliuliang#plt.ylim(ymin = 97.00,ymax = 100) plt.figure(num=1, figsize=(6, 4))  plt.title('7days-throughput', size=20)  plt.xlabel('Date', size=14)  plt.ylabel('Gb', size=14)#plt.ylabel('y-axis', size=14)  plt.plot(xData, yData1, color='b', linestyle='-', marker='o', label='QDN')  plt.plot(xData, yData2, color='r', linestyle='-', marker='o', label='TR')plt.plot(xData, yData3, color='g', linestyle='-', marker='o', label='Smallcell')plt.plot(xData, yData4, color='y', linestyle='-', marker='o', label='Whole_network')  plt.legend(loc='lower right')  plt.savefig('D:\\kpi\\1.png', format='png')#RRCxData = qriqi      yData1 = qrrcyData2 = trrcyData3 = srrcyData4 = grrc#plt.ylim(ymin = 97.00,ymax = 100) plt.figure(num=2, figsize=(6, 4))  plt.title('7days-RRC_Setup_Succ_Rate', size=20)  plt.xlabel('Date', size=14)  plt.ylabel('percentage', size=14)#plt.ylabel('y-axis', size=14)  plt.plot(xData, yData1, color='b', linestyle='-', marker='o', label='QDN')  plt.plot(xData, yData2, color='r', linestyle='-', marker='o', label='TR')plt.plot(xData, yData3, color='g', linestyle='-', marker='o', label='Smallcell')plt.plot(xData, yData4, color='y', linestyle='-', marker='o', label='Whole_network')  plt.legend(loc='lower right')  plt.savefig('D:\\kpi\\2.png', format='png')#ERAB建立成功率xData = qriqi      yData1 = qerabyData2 = terabyData3 = serabyData4 = gerab#plt.ylim(ymin = 97.00,ymax = 100) plt.figure(num=3, figsize=(6, 4))  plt.title('7days-Erab_Setup_Succ_Rate', size=20)  plt.xlabel('Date', size=14)  plt.ylabel('percentage', size=14)#plt.ylabel('y-axis', size=14)  plt.plot(xData, yData1, color='b', linestyle='-', marker='o', label='QDN')  plt.plot(xData, yData2, color='r', linestyle='-', marker='o', label='TR')plt.plot(xData, yData3, color='g', linestyle='-', marker='o', label='Smallcell')plt.plot(xData, yData4, color='y', linestyle='-', marker='o', label='Whole_network')  plt.legend(loc='lower right')  plt.savefig('D:\\kpi\\3.png', format='png')#S1信令建立成功率xData = qriqi      yData1 = qs1yData2 = ts1yData3 = ss1yData4 = gs1#plt.ylim(ymin = 97.00,ymax = 100) plt.figure(num=4, figsize=(6, 4))  plt.title('7days-S1_Setup_Succ_Rate', size=20)  plt.xlabel('Date', size=14)  plt.ylabel('percentage', size=14)#plt.ylabel('y-axis', size=14)  plt.plot(xData, yData1, color='b', linestyle='-', marker='o', label='QDN')  plt.plot(xData, yData2, color='r', linestyle='-', marker='o', label='TR')plt.plot(xData, yData3, color='g', linestyle='-', marker='o', label='Smallcell')plt.plot(xData, yData4, color='y', linestyle='-', marker='o', label='Whole_network')  plt.legend(loc='lower right')  plt.savefig('D:\\kpi\\4.png', format='png')#无线接通率xData = qriqi      yData1 = qwuxianyData2 = twuxianyData3 = swuxianyData4 = gwuxian#plt.ylim(ymin = 97.00,ymax = 100) plt.figure(num=5, figsize=(6, 4))  plt.title('7days-Radio_Setup_Succ_Rate', size=20)  plt.xlabel('Date', size=14)  plt.ylabel('percentage', size=14)#plt.ylabel('y-axis', size=14)  plt.plot(xData, yData1, color='b', linestyle='-', marker='o', label='QDN')  plt.plot(xData, yData2, color='r', linestyle='-', marker='o', label='TR')plt.plot(xData, yData3, color='g', linestyle='-', marker='o', label='Smallcell')plt.plot(xData, yData4, color='y', linestyle='-', marker='o', label='Whole_network')  plt.legend(loc='lower right')  plt.savefig('D:\\kpi\\5.png', format='png')#切换成功率xData = qriqi      yData1 = qqiehuanyData2 = tqiehuanyData3 = sqiehuanyData4 = gqiehuan#plt.ylim(ymin = 97.00,ymax = 100) plt.figure(num=6, figsize=(6, 4))  plt.title('7days-HO_Succ_Rate_Prep', size=20)  plt.xlabel('Date', size=14)  plt.ylabel('percentage', size=14)#plt.ylabel('y-axis', size=14)  plt.plot(xData, yData1, color='b', linestyle='-', marker='o', label='QDN')  plt.plot(xData, yData2, color='r', linestyle='-', marker='o', label='TR')plt.plot(xData, yData3, color='g', linestyle='-', marker='o', label='Smallcell')plt.plot(xData, yData4, color='y', linestyle='-', marker='o', label='Whole_network')  plt.legend(loc='lower right')  plt.savefig('D:\\kpi\\6.png', format='png')#小区可用率xData = qriqi      yData1 = qkeyongyData2 = tkeyongyData3 = skeyongyData4 = gkeyong#plt.ylim(ymin = 97.00,ymax = 100) plt.figure(num=7, figsize=(6, 4))  plt.title('7days-Cell_Availabilty', size=20)  plt.xlabel('Date', size=14)  plt.ylabel('percentage', size=14)#plt.ylabel('y-axis', size=14)  plt.plot(xData, yData1, color='b', linestyle='-', marker='o', label='QDN')  plt.plot(xData, yData2, color='r', linestyle='-', marker='o', label='TR')plt.plot(xData, yData3, color='g', linestyle='-', marker='o', label='Smallcell')plt.plot(xData, yData4, color='y', linestyle='-', marker='o', label='Whole_network')  plt.legend(loc='lower right')  plt.savefig('D:\\kpi\\7.png', format='png')#发邮件#stmp服务器设置mail_host = 'smtp.qq.com'mail_user = '[e-mail]'mail_pwd = 'luyPPPPPooo'#收信人#抄送mail_cc = [ '[e-mail]', '[e-mail]' ]#主送mail_to = [ '[e-mail]', '[e-mail]', '[e-mail]' ]mail_bcc = '[e-mail]'#表头信息msg = MIMEMultipart('alternative')msg['From'] = mail_usermsg['Subject'] = 'KPI日报%s' % (he)msg['To'] = ",".join( mail_to )msg['Cc'] = ",".join( mail_cc )msg['Bcc'] = mail_bcc#text = "Hi!\n How are you?\nHere is the link you wanted:\nhttp://www.python.org-0000"  html = """ <html>   <head></head>   <body>     <p>Hi:各位领导、同事<br> 以下是区域KPI，请阅    <table border="1" width="1000">    <tr>       <td><img src="cid:1"></td>   <td><img src="cid:2"></td>   <td><img src="cid:3"></td>    </tr>    <tr>       <td><img src="cid:4"></td>   <td><img src="cid:5"></td>   <td><img src="cid:6"></td></tr><tr>    <td><img src="cid:7"></td></tr>     </table>      </p>   </body> </html> """#fp = open(r'C:\Users\fly\Documents\5211game\1.jpg', 'rb')  #打开文件#msgImage = MIMEImage(fp.read()) #读入 msgImage 中#fp.close() #关闭文件#msgImage.add_header('Content-ID', '<0>')#msg.attach(msgImage)#把图片添加到邮件正文-html-多个图片，采用了for循环for j in range(7):#我总共要用    j=j+1    fp = open(r'D:\kpi\%d.png' % (j), 'rb')    msgImage = MIMEImage(fp.read())    fp.close()    msgImage.add_header('Content-ID', '<%d>' % (j))#把图片添加到头信息    msg.attach(msgImage)#定义邮件内容的格式#part1 = MIMEText(text, 'plain')  part2 = MIMEText(html, 'html')#把part2生成到邮件中去#msg.attach(part1)  这里没用到part1  part1是纯文本msg.attach(part2)#构造图片类型的附件，本次未使用---开头已经进行了声明，生效如下命令即可直接使用  #att = MIMEText(open(r'C:\Users\fly\Documents\5211game\新建文本文档.jpg', 'rb').read(), 'base64', 'utf-8')  #att["Content-Type"] = 'application/octet-stream'  #att["Content-Disposition"] = 'attachment; filename="新建文本文档.jpg"'  #msg.attach(att)#构造附件xls类型的附件xlspart = MIMEApplication(open(r'D:\kpi\%s.xls' % (he), 'rb').read())xlspart.add_header('Content-Disposition', 'attachment', filename='%s.xls' % (he))msg.attach(xlspart)#把附件添加到邮件中try:    s = smtplib.SMTP()    s = smtplib.SMTP_SSL(mail_host, 465 )    s.connect(mail_host)    #login    s.login(mail_user, mail_pwd )    #send mail    s.sendmail(mail_user, mail_to, msg.as_string())    s.close()    print ('success')except Exception as e:    print ('Exception: ', e)

========================================================================================================================================================================================================================================================================效果图：