send_material_report_weekly.py 3.1 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2021/12/20 11:18 上午
  4. # @Site :
  5. # @File : send_material_report_weekly.py.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. # @Tel 1501435553
  9. import datetime
  10. from urllib import parse
  11. import pandas as pd
  12. import smtplib
  13. from email.mime.text import MIMEText
  14. from email.mime.multipart import MIMEMultipart
  15. from sqlalchemy import create_engine
  16. class send_material_report_weekly:
  17. def __init__(self):
  18. self.OuterStr = "mysql+pymysql://%s:%s@%s:%d/%s" % (
  19. "data", parse.quote_plus("hcst@2021"), "139.186.27.96", 3390, "jeecg-boot")
  20. self.OuterConn = create_engine(self.OuterStr,
  21. pool_size=10,
  22. max_overflow=5,
  23. pool_timeout=10,
  24. pool_recycle=3600)
  25. self.stat_date = (datetime.date.today() + datetime.timedelta(-1)).strftime("%Y-%m-%d")
  26. def handler(self):
  27. sql = """select a.company_id as '分公司id',a.company_name as '分公司名称',a.account_id as '账户id',a.account_name as'账户名称',a.project_name as'项目名称',
  28. a.signature as '素材id',b.state_date as '素材上线时间',a.video_name as '素材名称',sum(a.charge) as '消耗',
  29. a.clip_name as '剪辑',a.shot_name as '拍摄',a.plan_name as '编导',a.leader_name as '负责人',a.photo_url as '视频链接',a.channel_name as '渠道类型'
  30. from application.app_kuaishou_video_report_busi_m a
  31. left join `jeecg-boot`.ctop_etl_kuaishou_video_info b
  32. ON a.signature=b.video_code
  33. where stat_mon=202112
  34. group by a.account_id,a.signature""".format(stat_date=self.stat_date)
  35. print(sql)
  36. path = """/data/excel/material_week_{stat_date}.xls""".format(stat_date=self.stat_date)
  37. print(path)
  38. pd.read_sql(sql, self.OuterConn).to_excel(path, index=False, engine='openpyxl')
  39. my_sender = 'zhaohailiang@c-top.com.cn' # 发件人邮箱账号
  40. my_pass = 'Ab*75137' # 发件人邮箱授权码 / 腾讯企业邮箱请使用登陆密码
  41. recipients = ['renyupeng@c-top.com.cn',] # 收件人邮箱账号
  42. # content = "Please check the attachment. "
  43. msg = MIMEMultipart()
  44. # [发件人的邮箱昵称、发件人邮箱账号], 昵称随便
  45. msg['From'] = my_sender
  46. # [收件人邮箱昵称、收件人邮箱账号], 昵称随便
  47. msg['To'] = ",".join(recipients)
  48. # 邮件的主题,也就是邮件的标题
  49. msg['Subject'] = "快手-全量素材月报"
  50. att1 = MIMEText(
  51. open(path, 'rb').read(), 'base64', 'utf-8')
  52. att1["Content-Type"] = 'application/octet-stream'
  53. att1["Content-Disposition"] = 'attachment; filename="material_report_weekly.xls"'
  54. msg.attach(att1)
  55. server = smtplib.SMTP_SSL("smtp.exmail.qq.com", port=465)
  56. server.login(my_sender, my_pass)
  57. try:
  58. server.sendmail(my_sender, recipients, msg.as_string())
  59. print("发送成功")
  60. except Exception as e:
  61. print("发送失败",e)
  62. if __name__ == '__main__':
  63. send_material_report_weekly().handler()