KuaishouMaterialToQingQMonth.py 3.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2021/12/20 11:18 上午
  4. # @Site :
  5. # @File : TouTiaoMaterialToQingQMonth.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 TouTiaoMaterialToQingQMonth:
  17. def __init__(self):
  18. self.OuterStr = "mysql+pymysql://%s:%s@%s:%d/%s" % (
  19. "hcst", parse.quote_plus("hcst@2024"), "192.168.0.249", 3390, "application")
  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 = """
  28. select a.project_name as `项目名称`,
  29. signature as `md5`,
  30. video_name as `视频名称`,
  31. photo_url as `视频链接`,
  32. a.leader_name as `设计负责人`,
  33. a.shot_name as `拍摄`,
  34. a.clip_name as `剪辑`,
  35. a.plan_name as `编导`,
  36. charge as `消耗`
  37. from (select project_name,
  38. project_id,
  39. signature,
  40. video_name,
  41. photo_url,
  42. leader_name,
  43. shot_name,
  44. clip_name,
  45. plan_name,
  46. sum(charge) as charge
  47. from application.kuaishou_material_video_report_daily_dw
  48. WHERE stat_date >= date_format(date_sub(NOW() , INTERVAL 1 MONTH), '%%Y%%m01')
  49. AND stat_date <= date_format(date_sub(DATE_FORMAT(NOW() , '%%Y-%%m-01'),interval 1 day),'%%Y%%m%%d')
  50. group by signature, project_id) a
  51. left join application.app_kuaishou_material_info b
  52. on a.signature = b.material_id
  53. where b.tag_id = 60155
  54. """
  55. print(sql)
  56. path = """/data/excel/快手真人素材_{stat_date}.xls""".format(stat_date=self.stat_date)
  57. print(path)
  58. pd.read_sql(sql, self.OuterConn).to_excel(path, index=False, engine='openpyxl')
  59. my_sender = 'huichuangsituo@c-top.com.cn' # 发件人邮箱账号
  60. my_pass = 'rfZV5qRfendzM3Ph' # 发件人邮箱授权码 / 腾讯企业邮箱请使用登陆密码
  61. recipients = ['keqingqing@c-top.com.cn', 'shiyuke@s-hcst.com','zhangwei@s-hcst.com'] # 收件人邮箱账号
  62. # content = "Please check the attachment. "
  63. msg = MIMEMultipart()
  64. # [发件人的邮箱昵称、发件人邮箱账号], 昵称随便
  65. msg['From'] = my_sender
  66. # [收件人邮箱昵称、收件人邮箱账号], 昵称随便
  67. msg['To'] = ",".join(recipients)
  68. # 邮件的主题,也就是邮件的标题
  69. msg['Subject'] = "快手真人素材_{stat_date}".format(stat_date=self.stat_date)
  70. att1 = MIMEText(
  71. open(path, 'rb').read(), 'base64', 'utf-8')
  72. att1["Content-Type"] = 'application/octet-stream'
  73. att1["Content-Disposition"] = 'attachment; filename="kuaishou_month.xls"'
  74. msg.attach(att1)
  75. server = smtplib.SMTP_SSL("smtp.exmail.qq.com", port=465)
  76. server.login(my_sender, my_pass)
  77. try:
  78. server.sendmail(my_sender, recipients, msg.as_string())
  79. print("发送成功")
  80. except Exception as e:
  81. print("发送失败", e)
  82. if __name__ == '__main__':
  83. TouTiaoMaterialToQingQMonth().handler()