Python自动化办公小程序：实现报表自动化和自动发送到目的邮箱

import pandas as pd
import JSON
import pyMysql
from sqlalchemy import create_engine
# 打开数据库连接
conn = pymysql.connect(host='localhost',
port=3306,
user='root',
passwd='xxxx',
charset = 'utf8'
)
engine=create_engine('mysql+pymysql://root:xxxx@localhost/mysql?charset=utf8')
def read_excel(file):
 df_excel=pd.read_excel(file)
 return df_excel
def read_json(file):
 with open(file,'r')as json_f:
 df_json=pd.read_json(json_f)
 return df_json
def read_sql(table):
 sql_cmd ='SELECT * FROM %s'%table
 df_sql=pd.read_sql(sql_cmd,engine)
 return df_sql
def read_csv(file):
 df_csv=pd.read_csv(file)
 return df_csv

#将城市空值的一行删除
df=df[df['city_num'].notna()]
#删除error
df=df.drop(df[df['city_num']=='error'].index)
#统计df = df.city_num.value_counts()

def pie_chart(df):
 #将城市空值的一行删除
 df=df[df['city_num'].notna()]
 #删除error
 df=df.drop(df[df['city_num']=='error'].index)
 #统计
 df = df.city_num.value_counts()
 df.head(10).plot.pie(subplots=True,figsize=(5, 6),autopct='%.2f%%',radius = 1.2,startangle = 250,legend=False)
pie_chart(read_csv('user_info.csv'))

plt.savefig('fig_cat.png')

pie = Pie()
pie.add("",Words)
pie.set_global_opts(title_opts=opts.TitleOpts(title="前十地区"))
#pie.set_series_opts(label_opts=opts.LabelOpts(user_df))
pie.render_notebook()

def echart_pie(user_df):
 user_df=user_df[user_df['city_num'].notna()]
 user_df=user_df.drop(user_df[user_df['city_num']=='error'].index)
 user_df = user_df.city_num.value_counts()
 name=user_df.head(10).index.tolist()
 value=user_df.head(10).values.tolist()
 words=list(zip(list(name),list(value)))
 pie = Pie()
 pie.add("",words)
 pie.set_global_opts(title_opts=opts.TitleOpts(title="前十地区"))
 #pie.set_series_opts(label_opts=opts.LabelOpts(user_df))
 return pie.render_notebook()
user_df=read_csv('user_info.csv')
echart_pie(user_df)

from snapshot_selenium import snapshot
make_snapshot(snapshot,echart_pie(user_df).render(),"test.png")

echart_pie(user_df).render('problem.html')
os.system('problem.html')

import smtplib
from email import encoders
from email.header import Header
from email.utils import parseaddr,fORMataddr
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
#发件人邮箱
asender="fanstuck@163.com"
#收件人邮箱
areceiver="1079944650@qq.com"
#抄送人邮箱
acc="fanstuck@163.com"
#邮箱主题
asubject="谢谢关注"
#发件人地址
from_addr="fanstuck@163.com"
#邮箱授权码
password="####"
#邮件设置
msg=MIMEMultipart()
msg['Subject']=asubject
msg['to']=areceiver
msg['Cc']=acc
msg['from']="fanstuck"
#邮件正文
body="你好，欢迎关注fanstuck，您的关注就是我继续创作的动力！"
msg.attach(MIMEText(body,'plain','utf-8'))
#添加附件
htmlFile = 'C:/Users/10799/problem.html'
html = MIMEApplication(open(htmlFile , 'rb').read())
html.add_header('Content-Disposition', 'attachment', filename='html')
msg.attach(html)
#设置邮箱服务器地址和接口
smtp_server="smtp.163.com"
server = smtplib.SMTP(smtp_server,25)
server.set_debuglevel(1)
#登录邮箱
server.login(from_addr,password)
#发生邮箱
server.sendmail(from_addr,areceiver.split(',')+acc.split(','),msg.as_string())
#断开服务器连接
server.quit()