- 先编写python脚本
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
import datetime
import pandas as pd
from email.header import Header
from email.mime.application import MIMEApplication
import pyodbc
import os
需要在linux查看数据库驱动DRIVER(vi /etc/odbcinst.ini)
connect = pyodbc.connect( 'DRIVER={ODBC Driver 17 for SQL Server};SERVER=数据库连接地址,端口号;DATABASE=数据库名;UID=用户名;PWD=密码')
cursor = connect.cursor()
today = datetime.datetime.today()
day = str(today.day)
month = str(today.month)
dt=month+"月"+day+"日"
df = pd.read_sql('''select top 100 * from TABLE''',con=connect)
df.to_excel("database"+month+"."+day+".xlsx",index=False)
connect.close()
tolist = ['******325@qq.com','******325@qq.com']# 收件人
#tolist_cc=['******325@qq.com','******325@qq.com']# 抄送人
smtpHost='smtp.office365.com'
smtpPort='587'
msg = MIMEMultipart()
msg["From"] = "******325@qq.com" #发件人
subject=dt+"数据表已更新"
msg["Subject"] = Header(subject, 'utf-8').encode() #邮件主题
msg['To'] = ", ".join(tolist) #收件人
#self.msg["Cc"] = ", ".join(tolist_cc) #抄送人
text = MIMEText("你好!附件为"+dt+"明细数据,请注意查收!’+\n+‘有问题随时沟通,谢谢!", _subtype='plain', _charset='utf-8')
msg.attach(text)
part_attach1 = MIMEApplication(open("database"+month+"."+day+".xlsx",'rb').read()) #打开附件
part_attach1.add_header('Content-Disposition','attachment',filename="数据表"+month+"."+day+".xlsx") #为附件命名
msg.attach(part_attach1) #添加附件
smtp = smtplib.SMTP(smtpHost,smtpPort)
smtp.set_debuglevel(True)
smtp.ehlo()
smtp.starttls()
smtp.login("******325@qq.com","123456")#邮箱名和密码
smtp.sendmail(msg["From"],tolist, msg.as_string())
#添加抄送人 smtp.sendmail(msg["From"],tolist+tolist_cc, msg.as_string())
smtp.quit()
os.remove("database"+month+"."+day+".xlsx")
- 登录服务器(以下所有的l为字母l)
pwd //查看当前目录
ll //显示当前目录全部文件
mkdir email_data //新建文件夹
ls //查看文件
cd email_data//当前路径进入文件夹目录
vim data.py//
chmod u+x data.py//将文件修改为可执行文件
编辑完成后按“ESC"(重新编辑按”i"),然后退出输入:wq
执行python脚本
./ data.py
或者
python3 data.py
crontab -l //查看定时任务
crontab -e //编辑定时任务