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

最新推荐文章于 2023-05-05 18:26:07 发布

Goldxwang

最新推荐文章于 2023-05-05 18:26:07 发布

阅读量2.3k

点赞数 3

分类专栏： python

python 专栏收录该内容

58 篇文章 1 订阅

订阅专栏

总体要求：

通过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-8



import sys
import cx_Oracle 
import datetime
import xlwt
import numpy as np  
import matplotlib.pyplot as plt

#邮件相关的库
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.image import MIMEImage
from email.mime.application import MIMEApplication
from 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) #创建sheet


i=0#定义从第一行开始存数据，0代表第一行
     

#定义表头格式	 
biaotou = ['区域','日期','小时','总流量','RRC连接建立成功率','ERAB建立成功率','S1信令建立成功率','无线接通率','切换成功率','小区可用率']
k=0
for 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+1
		
f.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=0
for 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 = qliuliang
yData2 = tliuliang
yData3 = sliuliang
yData4 = 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')

#RRC
xData = qriqi      
yData1 = qrrc
yData2 = trrc
yData3 = srrc
yData4 = 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 = qerab
yData2 = terab
yData3 = serab
yData4 = 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 = qs1
yData2 = ts1
yData3 = ss1
yData4 = 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 = qwuxian
yData2 = twuxian
yData3 = swuxian
yData4 = 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 = qqiehuan
yData2 = tqiehuan
yData3 = sqiehuan
yData4 = 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 = qkeyong
yData2 = tkeyong
yData3 = skeyong
yData4 = 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 = '295294372@qq.com'
mail_pwd = 'luyPPPPPooo'

#收信人

#抄送

mail_cc = [ 'rufei.zheng@qq.com', 'laoluo012@163.com' ]
#主送

mail_to = [ 'rufei.zheng@qq.com', '295294372@qq.com', 'laoluo012@163.com' ]
mail_bcc = '295294372@qq.com'

#表头信息
msg = MIMEMultipart('alternative')
msg['From'] = mail_user
msg['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)

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