python连接oracle并自动发送邮件

文章目录

- 1 导入所需模块
- 2连接ORACLE
- 3 抽取结果
- - 3.1 抽取员工形象
  - 3.2 抽取门店形象
  - 3.3 抽取明细汇总
- 4 合并结果
- 5 删除3中的结果
- 6 写入总表
- - 6.1 明细汇总总表
  - 6.2 大区排名总表
  - 6.3 扣款总表
- 7 自动发送邮件

1 导入所需模块

#用于连接oracle数据库
import cx_Oracle as cx
#用操作文件或文件夹
import osimport pandas as pd
import numpy  as np#用于操作Excel表格
import openpyxl
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl import Workbook
from openpyxl.styles import colors
from openpyxl.styles import Font
from openpyxl.styles import PatternFill
from openpyxl.styles import Border,Side
from openpyxl.styles import Alignment
from openpyxl.utils import get_column_letterimport shutil as st#用于发送邮件
import smtplib   
from email.mime.multipart import MIMEMultipart    
from email.mime.text import MIMEText    
from email.mime.image import MIMEImage 
from email.header import Header   
import schedule
import time

2连接ORACLE

#设置ORACLE驱动位置
os.environ['path'] =  r'E:\instantclient-basic-windows.x64-12.2.0.1.0\instantclient_12_2'
#ORACLE地址、端口、SID
dsn = cx.makedsn('地址',端口,'SID')
#ORACLE账号、密码、服务器
db = cx.connect('账号', '密码', 服务器)

3 抽取结果

3.1 抽取员工形象

1 从oracle数据库中抽取员工形象的结果，并转换为DataFrame；
表中包含的字段为


检查日期	架构日期	事业部	大区	区域	归属门店	被考核人姓名	被考核人工号	身份证号码	被考核人职位	不合格原因	扣款金额

2 在指定路径创建文件夹
3 在 2 中的文件夹中，根据 1 中的大区创建对应大区的文件夹
4 将 1 中的表按大区切分，写入到 3 对应大区的文件夹中

#创建游标 员工形象
curs_ygxx=db.cursor()#组织查询语句
curs_ygxx.execute(
"""
WITH CANSHU AS
(
SELECT    to_date(开始日期,'yyyymmdd') as 开始日期,to_date(结束日期,'yyyymmdd') as 结束日期,to_date(竞赛开始日期,'yyyymmdd') as 竞赛开始日期,to_date(竞赛结束日期,'yyyymmdd') as 竞赛结束日期
FROM   HR_参数表
where   项目='门店6S'
)select to_char(检查日期,'yyyy-mm-dd') as 检查日期,
to_char(架构日期,'yyyy-mm-dd') as 架构日期,
事业部,大区,区域,归属门店,被考核人姓名,工号 as  被考核人工号,身份证号码,职位 as 被考核人职位,不合格原因,扣款金额
from  CDM_HR_门店6S_员工形象
where trunc(检查日期,'mm')=(SELECT 开始日期 FROM CANSHU)
"""
)  data_ygxx=pd.DataFrame(curs_ygxx.fetchall())   #将查询结果转换为 DataFrame
colsname_ygxx=curs_ygxx.description #获取列名称
namelist_ygxx=[] 
for i in range(len(colsname_ygxx)):namelist_ygxx.append(colsname_ygxx[i][0])
data_ygxx.columns=namelist_ygxx #添加表名称  path=r'C:/Users/Administrator/Desktop/6S-线上版/'
for col in data_ygxx['大区'].unique(): #提取大区名称if not os.path.exists(path+col): #判断是否存在大区文件夹os.mkdir(path+col) #在指定路径 创建文件夹 data_ygxx[data_ygxx["大区"] == col].to_excel(path + col + "-员工形象.xlsx",index=False) #在指定路径 写入excelst.move(path +col + "-员工形象.xlsx",path+col + "/" + col + "-员工形象.xlsx")  #将指定路径的文件 移动到  指定路径的文件夹

3.2 抽取门店形象

步骤同上

#创建游标 门店形象
curs_mdxx=db.cursor()#组织查询语句
curs_mdxx.execute(
"""WITH CANSHU AS(SELECT  to_date(开始日期,'yyyymmdd') as 开始日期,to_date(结束日期,'yyyymmdd') as 结束日期,to_date(竞赛开始日期,'yyyymmdd') as 竞赛开始日期,to_date(竞赛结束日期,'yyyymmdd') as 竞赛结束日期FROM   HR_参数表where  项目='门店6S')select to_char(检查日期,'yyyy-mm-dd') as 检查日期,to_char(架构日期,'yyyy-mm-dd') as 架构日期,事业部,大区,区域,门店,被考核人区经 AS 区经,被考核人区经身份证号 AS 区经身份证号,被考核人区经职位 AS 区经职位,门前区域 as 门前区域有几处不合规,前台接待区 as 前台接待区有几处不合规,洽谈室 as 洽谈室有几处不合规,卫生间 as 卫生间有几处不合规,办公区 as 办公区有几处不合规,培训室 as 培训室有几处不合规,储物间和楼梯 as 储物间和楼梯有几处不合规,打分,门店星级from  CDM_HR_门店6S_门店形象where trunc(检查日期,'mm')=(SELECT 开始日期 FROM CANSHU)and   大区 is not null 
"""
)  data_mdxx=pd.DataFrame(curs_mdxx.fetchall())   #将查询结果转换为 DataFrame
colsname_mdxx=curs_mdxx.description #获取列名称
namelist_mdxx=[] 
for i in range(len(colsname_mdxx)):namelist_mdxx.append(colsname_mdxx[i][0])
data_mdxx.columns=namelist_mdxx #添加表名称path=r'C:/Users/Administrator/Desktop/6S-线上版/'
for col in data_mdxx['大区'].unique():if not os.path.exists(path+col):os.mkdir(path+col) # 在指定路径 创建文件夹 data_mdxx[data_mdxx["大区"] == col].to_excel(path + col + "-门店形象.xlsx",index=False) #在指定路径 写入excelst.move(path +col + "-门店形象.xlsx",path+col + "/" + col + "-门店形象.xlsx") #奖指定路径的文件 移动到  指定路径的文件夹

3.3 抽取明细汇总

步骤同上

#创建游标 明细汇总
curs_mxhz=db.cursor()#组织查询语句
curs_mxhz.execute(
"""WITH CANSHU AS(SELECT  to_date(开始日期,'yyyymmdd') as 开始日期,to_date(结束日期,'yyyymmdd') as 结束日期,to_date(竞赛开始日期,'yyyymmdd') as 竞赛开始日期,to_date(竞赛结束日期,'yyyymmdd') as 竞赛结束日期FROM   HR_参数表where  项目='门店6S')select to_char(月份,'yyyy-mm-dd') as 月份,事业部,大区,大区领导,区域,区域领导,区域领导身份证号,工号,职位,门店,着装不合格,五星门店,四星门店,三星门店,门店分数from  ADS_HR_门店6S_明细汇总where trunc(月份,'mm')=(SELECT 开始日期 FROM CANSHU)
"""
)  data_mxhz=pd.DataFrame(curs_mxhz.fetchall())   #将查询结果转换为 DataFrame
colsname_mxhz=curs_mxhz.description #获取列名称
namelist_mxhz=[] 
for i in range(len(colsname_mxhz)):namelist_mxhz.append(colsname_mxhz[i][0])
data_mxhz.columns=namelist_mxhz #添加表名称path=r'C:/Users/Administrator/Desktop/6S-线上版/'
for col in data_mxhz['大区'].unique():if not os.path.exists(path+col):os.mkdir(path+col) # 在指定路径 创建文件夹 data_mxhz[data_mxhz["大区"] == col].to_excel(path + col + "-明细汇总.xlsx",index=False) #在指定路径 写入excelst.move(path +col + "-明细汇总.xlsx",path+col + "/" + col + "-明细汇总.xlsx") #奖指定路径的文件 移动到  指定路径的文件夹

4 合并结果

经过3，我们在桌面上获得了一个文件夹，该文件夹中包含了每个大区对应的文件夹，每个大区的文件夹包含了3个表，分别是员工形象，门店形象，明细汇总。接下来我们需要将这3个表汇总合并在一个EXCEL中，每个表变成一个sheet

# 读取各文件夹中文件 合并成一个SHEET
path=r'C:/Users/Administrator/Desktop/6S-线上版/'
for col in data_mxhz['大区'].unique():#创建工作簿wb=Workbook()#创建多个sheetws_ygxx=wb.activews_mdxx=wb.create_sheet()ws_mxhz=wb.create_sheet()#更改sheet的名称ws_ygxx.title="员工形象"ws_mdxx.title="门店形象"ws_mxhz.title="明细汇总"#获取文件夹下的所有文件名 name_list=os.listdir(path+col)#for循环遍历读取for i in name_list:if "员工形象" in i:df_ygxx = pd.read_excel(path + col +"/" + i)df_ygxx["身份证号码"] = df_ygxx["身份证号码"].apply(lambda x: "	" + str(x))for r0 in dataframe_to_rows(df_ygxx.fillna(' '),index=False,header=True):  # 缺失值处理 填补为null ws_ygxx.append(r0)         for c0 in ws_ygxx["A:L"]:for r0 in c0:r0.font=Font(name="微软雅黑",size=10)  #对单元格字体的设置r0.fill=PatternFill(fill_type=None) #单元格填充r0.border=Border(left=Side(border_style="thin"), #边框线设置right=Side(border_style="thin"),top=Side(border_style="thin"),bottom=Side(border_style="thin"))r0.alignment=Alignment(horizontal="center",vertical="center") #对齐方式        for c in  ws_ygxx[1]: #首行c.font=Font(name="微软雅黑",size=12,bold=True)c.fill=PatternFill(fill_type='solid',start_color="FFFFFF00")   for c in ["A","B","C","D","E","F","G","H","I","J","K","L"]:ws_ygxx.column_dimensions[c].width = 20.0  if "门店形象" in i:df_mdxx = pd.read_excel(path + col +"/" + i)df_mdxx["区经身份证号"] = df_mdxx["区经身份证号"].apply(lambda x: "	" + str(x))for r3 in dataframe_to_rows(df_mdxx.fillna(' '),index=False,header=True):  ## 缺失值处理 填补为null ws_mdxx.append(r3)  for c3 in ws_mdxx["A:R"]:for r3 in c3:r3.font=Font(name="微软雅黑",size=10)  #对单元格字体的设置r3.fill=PatternFill(fill_type=None) #单元格填充r3.border=Border(left=Side(border_style="thin"), #边框线设置right=Side(border_style="thin"),top=Side(border_style="thin"),bottom=Side(border_style="thin"))r3.alignment=Alignment(horizontal="center",vertical="center") #对齐方式        for c in  ws_mdxx[1]: #首行c.font=Font(name="微软雅黑",size=12,bold=True)c.fill=PatternFill(fill_type='solid',start_color="FFFFFF00")   for c in ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R"]:ws_mdxx.column_dimensions[c].width = 20.0  if "明细汇总" in i:df_mxhz = pd.read_excel(path + col +"/" + i)df_mxhz["区域领导身份证号"] = df_mxhz["区域领导身份证号"].apply(lambda x: "	" + str(x))for r4 in dataframe_to_rows(df_mxhz.fillna(' '),index=False,header=True):  ## 缺失值处理 填补为null ws_mxhz.append(r4) for c4 in ws_mxhz["A:O"]:for r4 in c4:r4.font=Font(name="微软雅黑",size=10)  #对单元格字体的设置r4.fill=PatternFill(fill_type=None) #单元格填充r4.border=Border(left=Side(border_style="thin"), #边框线设置right=Side(border_style="thin"),top=Side(border_style="thin"),bottom=Side(border_style="thin"))r4.alignment=Alignment(horizontal="center",vertical="center") #对齐方式        for c in  ws_mxhz[1]: #首行c.font=Font(name="微软雅黑",size=12,bold=True)c.fill=PatternFill(fill_type='solid',start_color="FFFFFF00")   for c in ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O"]:ws_mxhz.column_dimensions[c].width = 20.0wb.save(path+col+"/"+col+"-总表.xlsx")   curs_ygxx.close()
curs_mdxx.close()
curs_mxhz.close()

5 删除3中的结果

删除员工形象门店形象明细汇总表，仅保留总表

path=r'C:/Users/Administrator/Desktop/6S-线上版/'
for col in data_mxhz['大区'].unique():#获取文件夹下的所有文件名 name_list=os.listdir(path+col)#for循环遍历读取for i in name_list:if "员工形象" in i:os.remove(path+col+"/"+col+"-员工形象.xlsx")if "门店形象" in i:os.remove(path+col+"/"+col+"-门店形象.xlsx")if "明细汇总" in i:os.remove(path+col+"/"+col+"-明细汇总.xlsx")

6 写入总表

经过上述步骤，我们在桌面上获得了一个文件夹，该文件夹中包含了每个大区对应的文件夹，每个大区的文件夹包含了1个总表，这个总表中有3个sheet,分别是员工形象、门店形象、明细汇总。
接下来有个需求，我们想要明细汇总、大区排名、扣款三个总表，而不是分大区的表。

6.1 明细汇总总表

创建明细汇总总表，并调整其格式，微软雅黑，首行填充黄色背景，单元格间距20

#创建工作簿
wb=Workbook()
ws_mxhzzb=wb.active
##更改sheet的名称
ws_mxhzzb.title="明细汇总总表"curs_mxhzzb=db.cursor()#组织查询语句
curs_mxhzzb.execute(
"""
WITH CANSHU AS
(
SELECT  to_date(开始日期,'yyyymmdd') as 开始日期,to_date(结束日期,'yyyymmdd') as 结束日期,to_date(竞赛开始日期,'yyyymmdd') as 竞赛开始日期,to_date(竞赛结束日期,'yyyymmdd') as 竞赛结束日期
FROM   HR_参数表
where  项目='门店6S'
)select 月份,事业部,大区,大区领导,大区领导身份证号,区域,区域领导,区域领导身份证号,工号,职位,门店,着装不合格,
五星门店,四星门店,三星门店,门店分数
from  ADS_HR_门店6S_明细汇总
where trunc(月份,'mm')=(SELECT 开始日期 FROM CANSHU)
"""
)  data_mxhzzb=pd.DataFrame(curs_mxhzzb.fetchall())   #将查询结果转换为 DataFrame
colsname_mxhzzb=curs_mxhzzb.description #获取列名称
namelist_mxhzzb=[] 
for i in range(len(colsname_mxhzzb)):namelist_mxhzzb.append(colsname_mxhzzb[i][0])
data_mxhzzb.columns=namelist_mxhzzb #添加表名称for r in dataframe_to_rows(data_mxhzzb.fillna(' '),index=False,header=True):  ## 缺失值处理 填补为null ws_mxhzzb.append(r)    for col in ws_mxhzzb["A:P"]:for r in col:r.font=Font(name="微软雅黑",size=10)  #对单元格字体的设置r.fill=PatternFill(fill_type=None) #单元格填充r.border=Border(left=Side(border_style="thin"), #边框线设置right=Side(border_style="thin"),top=Side(border_style="thin"),bottom=Side(border_style="thin"))r.alignment=Alignment(horizontal="center",vertical="center") #对齐方式        for c in  ws_mxhzzb[1]: #首行c.font=Font(name="微软雅黑",size=12,bold=True)c.fill=PatternFill(fill_type='solid',start_color="FFFFFF00")   for col in ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P"]:ws_mxhzzb.column_dimensions[col].width = 20.0  wb.save(r'C:\Users\Administrator\Desktop\6S-线上版\6S-明细汇总总表.xlsx')

6.2 大区排名总表

大区排名该表为总表，不进行切分，处理同上

#创建工作簿
wb=Workbook()
ws_dqpm=wb.active
##更改sheet的名称
ws_dqpm.title="大区排名"#创建游标  
curs_dqpm=db.cursor()#组织查询语句
curs_dqpm.execute(
"""
WITH CANSHU AS
(
SELECT  to_date(开始日期,'yyyymmdd') as 开始日期,to_date(结束日期,'yyyymmdd') as 结束日期,to_date(竞赛开始日期,'yyyymmdd') as 竞赛开始日期,to_date(竞赛结束日期,'yyyymmdd') as 竞赛结束日期
FROM   HR_参数表
where  项目='门店6S'
)select 月份,事业部,大区,大区领导,查店数,总人数,着装不合格人数,员工形象合格率,五星门店,四星门店,三星门店,门店形象合格率,
大区合格率,升序排名,判断,扣款金额
from  ADS_HR_门店6S_大区排名
where trunc(月份,'mm')=(SELECT 开始日期 FROM CANSHU)
"""
)  data_dqpm=pd.DataFrame(curs_dqpm.fetchall())   #将查询结果转换为 DataFrame
colsname_dqpm=curs_dqpm.description #获取列名称
namelist_dqpm=[] 
for i in range(len(colsname_dqpm)):namelist_dqpm.append(colsname_dqpm[i][0])
data_dqpm.columns=namelist_dqpm #添加表名称for r in dataframe_to_rows(data_dqpm.fillna(' '),index=False,header=True):  #缺失值处理 填补为null ws_dqpm.append(r)    for col in ws_dqpm["A:P"]:for r in col:r.font=Font(name="微软雅黑",size=10)  #对单元格字体的设置r.fill=PatternFill(fill_type=None) #单元格填充r.border=Border(left=Side(border_style="thin"), #边框线设置right=Side(border_style="thin"),top=Side(border_style="thin"),bottom=Side(border_style="thin"))r.alignment=Alignment(horizontal="center",vertical="center") #对齐方式        for c in  ws_dqpm[1]: #首行c.font=Font(name="微软雅黑",size=12,bold=True)c.fill=PatternFill(fill_type='solid',start_color="FFFFFF00")   for col in ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P"]:ws_dqpm.column_dimensions[col].width = 20.0  wb.save(r'C:\Users\Administrator\Desktop\6S-线上版\6S大区排名.xlsx')

6.3 扣款总表

扣款该表为总表，不进行切分，处理同上

#创建工作簿
wb=Workbook()
ws_kk=wb.active
#更改sheet的名称
ws_kk.title="扣款"#创建游标  
curs_kk=db.cursor()#组织查询语句
curs_kk.execute(
"""
WITH CANSHU AS
(
SELECT  to_date(开始日期,'yyyymmdd') as 开始日期,to_date(结束日期,'yyyymmdd') as 结束日期,to_date(竞赛开始日期,'yyyymmdd') as 竞赛开始日期,to_date(竞赛结束日期,'yyyymmdd') as 竞赛结束日期
FROM   HR_参数表
where  项目='门店6S'
)select 检查月份,事业部,大区,区域,检查门店,被考核人姓名,身份证号码,职位,不合格原因,扣款金额
from  ADS_HR_门店6S_扣款
where trunc(检查月份,'mm')=(SELECT 开始日期 FROM CANSHU)
"""
)  data_kk=pd.DataFrame(curs_kk.fetchall())   #将查询结果转换为 DataFrame
colsname_kk=curs_kk.description #获取列名称
namelist_kk=[] 
for i in range(len(colsname_kk)):namelist_kk.append(colsname_kk[i][0])
data_kk.columns=namelist_kk #添加表名称for r in dataframe_to_rows(data_kk.fillna(' '),index=False,header=True):  #缺失值处理 填补为null ws_kk.append(r)    for col in ws_kk["A:J"]:for r in col:r.font=Font(name="微软雅黑",size=10)  #对单元格字体的设置r.fill=PatternFill(fill_type=None) #单元格填充r.border=Border(left=Side(border_style="thin"), #边框线设置right=Side(border_style="thin"),top=Side(border_style="thin"),bottom=Side(border_style="thin"))r.alignment=Alignment(horizontal="center",vertical="center") #对齐方式        for c in  ws_kk[1]: #首行c.font=Font(name="微软雅黑",size=12,bold=True)c.fill=PatternFill(fill_type='solid',start_color="FFFFFF00")   for col in ["A","B","C","D","E","F","G","H","I","J"]:ws_kk.column_dimensions[col].width = 20.0  wb.save(r'C:\Users\Administrator\Desktop\6S-线上版\扣款.xlsx')

7 自动发送邮件

发送多个邮件（每个邮件多个附件）给多个人

#设置smtplib所需的参数
#下面的发件人，收件人是用于邮件传输的
smtpserver = 'smtp.maitian.cn'    #指定发送邮箱的格式smtp.163.com   smtp.qq.com  使用的邮箱的smtp服务器地址，这里是qq的smtp地址,其他同理
username = '发件人用户名'#发件人用户名
password='发件人授权码'#发件人授权码,注意对于网页版邮箱，需要的不是密码，而是授权码，授权码要合着写在一起，中间不能有空格
sender='发件人用户名'#发件人，即发件人用户名
#receiver='接收人'#一个接收人
#receiver=['******@126.com','******@126.com'] #多个收件人，用列表表示；同一个邮件给多个收件人#读取邮件发送列表 
df=pd.read_excel(r'C:/Users/Administrator/Desktop/6S-线上版/大区政委邮箱.xlsx',sheet_name = 'Sheet1')
for i in zip(df["大区"],df["收件人姓名"],df["收件人邮箱"]):receiver=i[2] #接收人 不同的邮件给各自对应的收件人#构造邮件主题subject = i[0]+'-6S明细核对-202304'#邮件主题为中英文均可#构造邮件对象MIMEMultipart对象，用于写邮件的即显示出来的邮件#下面的主题，发件人，收件人，日期是显示在邮件页面上的msg = MIMEMultipart()#声明邮件对象msg['Subject'] = subject #这里的subject即为上面构造的邮件主题msg['From'] = sendermsg['To'] = receivermsg['Accept-Language'] = 'zh-CN'msg['Accept-Charset'] = 'ISO-8859-1,utf-8'msgAlternative = MIMEMultipart('alternative')msg.attach(msgAlternative)#构造正文内容mail_msg = """<p> 您好：<br>\t附件为本月【门店6S】检查结果及明细，因涉及各层级扣款，烦请查收并与区域确认！<br>\t如有问题，请于今天(25号)18:00前反馈***，不反馈则默认信息无误，感谢配合！ </p>"""msgAlternative.attach(MIMEText(mail_msg, 'html', 'utf-8'))    #构造附件currentPath = r'C:/Users/Administrator/Desktop/6S-线上版'+'/' +i[0]sqlFileList = os.listdir(currentPath)sqlFilePath = ''sqlFilePathList = []#发送多个附件的邮件，这里发送指定目录下所有类型一致的文件for fileName in sqlFileList:sqlFilePath = os.path.join(currentPath,fileName)#print(sqlFilePath)sqlFilePathList.append(sqlFilePath)#print(sqlFilePathList)with open(sqlFilePath,'rb') as h:content1=h.read()#设置txt参数part1 = MIMEText(content1,'base','utf-8')#附件设置内容类型，方便起见，设置为二进制流part1['Content-Type'] = 'application/octet-stream'#设置附件头，添加附件名part1.add_header('Content-Disposition', 'attachment', filename=fileName)#将内容附加到邮件主体中msg.attach(part1)#发送邮件try:smtp = smtplib.SMTP()    #实例smtp.connect('smtp.maitian.cn')#我们用set_debuglevel(1)就可以打印出和SMTP服务器交互的所有信息。#smtp.set_debuglevel(1)  smtp.login(username, password)    smtp.sendmail(sender, receiver, msg.as_string())    print (subject+"邮件发送成功")time.sleep(3) #每封邮件发送间隔3sexcept smtplib.SMTPException:print (subject+"Error: 无法发送邮件")except:print("Error: 无法发送邮件")```