python连接数据库自动发邮件

python连接数据库实现自动发邮件

1.运行环境

redhat6 + python3.6 + crontab + Oracle客户端

2.用到的模块

3.操作步骤

（1）安装python3.6参考步骤https://www.cnblogs.com/zhur/p/12044171.html

（2）安装python模块，pip安装

4.安装Oracle客户端https://oracle.github.io/odpi/doc/installation.html#linux

5.配置环境变量 ~/.bash_profile

（1）LD_LIBRARY_PATH=[oracle客户端安装目录]:$LD_LIBRARY_PATH

（2）export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK(数据库中的)

6.编写配置文件和执行脚本

（1）配置文件：

##数据库相关参数：主机、数据库用户、数据库密码、数据库sid、数据库字符集nls_lan

ora_host = '192.168.168.168'
ora_user = '**********'
ora_pwd = '**********'
ora_sid = '****'
##nls_lan = 'AMERICAN_AMERICA.ZHS16GBK'

##数据库共享库目录
##lib_path = '/opt/oracle/instantclient_11_2'

##执行脚本(py,sql)目录
exe_path = '/root/python/prod/cstest/bin'

##数据库导出sql语句文件,多个文件用逗号隔开;如果sql文件在执行脚本目录下，可以使用相对路径，否则使用绝对路径;
##注意:标点符号都是英文的,sql文件中的时间变量必须是 &date 或 &yyyymmdd
sql_files = '/root/python/prod/cstest/bin/测试.sql'

## sql语句中的导数日期 yesterday ：表示昨天，目前只支持这一种情况，以后可以酌情增加其它日期
exp_date = 'yesterday'

##导出excel目录
excel_path = "/root/python/prod/cstest/data"

##导出excel文件sheet名，sheet名称与上面参数sql_files中的每个sql文件中sql条数一一对应，如果没有设置对应名称，则sheet名称默认
excel_sheet_names = [['测试']]

##导出exel文件是否压缩 1：压缩 0：不压缩删除此参数
##is_zip = 1

##压缩后文件名前缀，压缩文件名=前缀+导数日期
zipfile_name = '测试'

##是否发送邮件 1: 发送 0:不发送
is_mail = 1

##邮箱发送服务器
mail_server = 'xxxxxxxxx.@com'

##邮箱发送服务器端口
mail_port = '**'

##发送邮件
mail_from = 'xxxxxxxx@qq.com'

##发送邮件密码
mail_pass = '********'

##接收邮件
mail_to = 'xxxxxxxx@qq.com'

#抄送邮件
mail_cc = 'xxxxxxxxx@qq.com'

##邮件标题
mail_subject = '测试'

##邮件内容
mail_body = """
您好:
     数据已提取，详见附件，请查收; 


-------------------------------------------------------------------------------------
 可视化运维：朱瑞
 热线电话：18834195657 
 运维邮箱：xxxxxxxxx@qq.com
"""

(2)执行脚本

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import cx_Oracle as cx
from openpyxl import Workbook
import datetime
import os
import zipfile
from config_ora2excel import *
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email import encoders
from email.header import Header
from email.mime.base import MIMEBase

#os.environ['LD_LIBRARY_PATH'] = lib_path
#os.environ['NLS_LANG'] = nls_lan

#os.environ.setdefault("LD_LIBRARY_PATH", lib_path)
#os.environ.setdefault("NLS_LANG", nls_lan)

def get_yesterday():
today = datetime.date.today()
oneday = datetime.timedelta(days=1)
yesterday = today - oneday
return yesterday

def get_data_date():
# 获取数据日期
##data_date = input('请输入数据日期，格式yyyymmdd，默认为昨天:')
data_date = exp_date
if data_date.strip().lower() == 'yesterday':
yesterday = get_yesterday()
data_date = yesterday.strftime("%Y%m%d")
##print(data_date)
return data_date

def get_weeks():
#获取周次
data_date = get_data_date()
y = int(data_date[:4])
m = int(data_date[4:6])
d = int(data_date[6:])
weeks = datetime.date(y,m,d).isocalendar()[1]
return weeks

def ora_to_excel(data_date):
try:
ret = 0

#print(os.environ['LD_LIBRARY_PATH'])
#print(os.environ['NLS_LANG'])

# 连接数据库
# connect = cx.connect(user+'/'+pwd+'@'+host+'/'+sid)
connect = cx.connect('{}/{}@{}/{}'.format(ora_user, ora_pwd, ora_host, ora_sid))
print('连接数据库成功!!')

# 获取数据日期
data_date = data_date
print(data_date)

##切换到脚本执行目录
os.chdir(exe_path)

# 获取sql执行并导出到excel,可能是多条sql，需要分别执行
fi = 0
for f_sql in sql_files.split(','):
start = datetime.datetime.now()

##print(type(f_sql))
cursor = connect.cursor()
print(f_sql)
with open(f_sql, 'r', encoding='utf-8') as f:
sqls = f.read()

j = 0

wb = Workbook()
for sql in sqls.split(';'):
sql = sql.strip()
if not sql:
break

##替换变量 sql中的变量必须是 &date 或 &yyyymmdd
# sql = sql.lower()
sql = sql.replace('&date', data_date)
sql = sql.replace('&yyyymmdd', data_date)
##sql = sql.decode('utf-8')
print('-----------------------------------------开始执行sql:\n', sql)
cursor.execute(sql)
print('-----------------------------------------执行sql成功\n')

print('-----------------------------------------开始导出excel\n')

sht = wb.create_sheet(title='Mysheet'+str(j),index=j)

## 如果变量设置了sheet名称，则修改
if len(excel_sheet_names) >= fi + 1:
if len(excel_sheet_names[fi]) >= j + 1 and excel_sheet_names[fi][j].strip():
sht.title = excel_sheet_names[fi][j]
print(sht.title)

# 处理表头
i = 1

for col in cursor.description:
sht.cell(1, i).value = col[0]
i += 1

##print('11')
# 写数据
##offset = 2
while True:
results = cursor.fetchmany(10000)
if not results:
break
resLen = len(results)
for res in results:
sht.append(res)
##在开头插入一列
###print('1111')
sht.insert_cols(1)
###print('22')
###print(sht.max_row)
# 给第一列写入行标
for k in range(2, sht.max_row+1):
sht.cell(k,1).value = k - 1

# 获取导出文件目录及文件名
exl_file = f_sql.split(os.sep)[-1].split('.')[0] + '_' + data_date + '.xlsx'
if excel_path.strip():
exl_file = excel_path + os.sep + exl_file
print(exl_file)
j += 1

wb.save(exl_file)
##wb.close()
cursor.close()
print('-----------------------------------------导出excel成功！\n')

print('导出excel 花费时间:', datetime.datetime.now() - start, '\n\n')

fi += 1
ret = 1
except Exception as e:
print('Error:', e)
##raise e
finally:
connect.close()
return ret

def get_zip_file(input_path, wd, results):
"""
在目录下查找包含关键字的文件
input_path: 目录
wd: 关键字
results:查找结果列表
"""
try:
files = os.listdir(input_path)
for filename in files:
fp = os.path.join(input_path, filename)
if os.path.isfile(fp) and wd in filename:
# 使用相对路径
results.append('.' + os.sep + filename)
except Exception as e:
print(e)
raise e

def zip_files(input_path, output_zip_name, data_date):
"""
压缩文件
input_path：目录
output_zip_name：zip文件名
data_date :数据日期
"""
try:
ret = None
start = datetime.datetime.now()
filelists = []
wd = data_date + '.xlsx'
get_zip_file(input_path, wd, filelists)

if len(filelists) > 0:
print('-----------------------------------------开始压缩文件')
f = zipfile.ZipFile(input_path + os.sep + output_zip_name, 'w', zipfile.ZIP_DEFLATED)

# 切换到打包目录
os.chdir(input_path)
for file in filelists:
f.write(file)

print(input_path + os.sep + output_zip_name)
print('-----------------------------------------压缩文件完成')
print('压缩文件花费时间:', datetime.datetime.now() - start, '\n\n')
f.close()
print('压缩文件名:',input_path + os.sep + output_zip_name)
ret = output_zip_name
return ret
except Exception as e:
print('Error:', e)
raise e
finally:
return ret

def send_mail(zipfile):
# 构造一个MIMEMultipart对象代表邮件本身
msg = MIMEMultipart()

msg['From'] = mail_from
msg['To'] = mail_to
msg['Cc'] = mail_cc
weeks = str(get_weeks())
mail_sub = mail_subject.replace('{weeks}',weeks)
msg['Subject'] = Header(mail_sub,'utf-8').encode()
msg.attach(MIMEText(mail_body,'html','utf-8'))

# 切换到zip文件目录
os.chdir(excel_path)
#二进制模式读取zip文件
with open(zipfile,'rb') as f:
zipf = zipfile.split('.')[0]
# 设置附件的MIME和文件名，这里是zip类型:
mime = MIMEBase('zip', 'zip', filename=zipfile)
# 加上必要的头信息:
#mime.add_header('Content-Disposition', 'attachment', file_name=Header(zipfile,'utf-8').encode())
#mime.add_header('Content-Disposition', 'attachment', filename=('gbk', '', zipfile))
#mime.add_header('Content-Disposition', 'attachment', filename=('utf-8', '', zipfile)) ##这种方式发送，邮件客户端收到邮件，中文附件名还是可能有乱码
mime.add_header('Content-Disposition', 'attachment', filename= Header(zipfile,'gbk').encode() ) ## ##中文附件名称outlook,网页，foxmail均显示正常
#mime.add_header('Content-ID', '<0>')
#mime.add_header('X-Attachment-Id', '0')
# 把附件的内容读进来:
mime.set_payload(f.read())
# 用Base64编码
encoders.encode_base64(mime)
#添加到MIMEMultipart
msg.attach(mime)
try:
print('-----------------------------------------发送邮件开始')
s = smtplib.SMTP()
s.connect(mail_server,mail_port)
s.login(mail_from,mail_pass)
##sendmail的第2个参数邮件地址是个list,所以将字符串用split转为list
s.sendmail(mail_from,mail_to.split(',')+mail_cc.split(','),msg.as_string())
s.quit()
print('-----------------------------------------发送邮件结束')
except smtplip.SMTPException as e:
print('发送失败：',e)

return

def main():
try:
print('--Program begin',datetime.datetime.now(),'--------------------------------------')
print(os.environ['LD_LIBRARY_PATH'])
print(os.environ['NLS_LANG'])
data_date = get_data_date()
oret = ora_to_excel(data_date)
##oret = 1

weeks = str(get_weeks())

zfile_name = zipfile_name.replace('{weeks}',weeks)

output_zip_name = zfile_name + '_' + data_date + '.zip'

if oret == 1:
zfile = zip_files(excel_path, output_zip_name, data_date)
###zfile = output_zip_name
if zfile:
if is_mail == 1:
send_mail(zfile)

print('--Program End', datetime.datetime.now(), '--------------------------------------')
except Exception as e:
print(e)
## finally:
## end = input('请输入回车键结束!')

if __name__ == '__main__':
main()

7.测试看是否发送成功用行脚本就ok

8.配置定时任crontab -e

9.遇到的问题

（1）.执行脚本时无法调用模块,用pip 安装python模块报错