NewBytedanceAllMaterialFourD.py 3.9 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2021/12/20 11:18 上午
  4. # @Site :
  5. # @File : NewKuaiShouAllMaterialFourD.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 NewBytedanceAllMaterialFourD:
  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
  29. ( CASE WHEN t1.company_name IS NOT NULL THEN t1.company_name ELSE '-' END ) '公司',
  30. ifnull(t2.project_name,'-') AS '项目',
  31. ifnull(t2.signature,'-') AS '素材标识',
  32. ( CASE WHEN t1.material_innovate = 1 THEN '非创新素材' WHEN t1.material_innovate = 2 THEN '创新素材' ELSE '-' END ) AS '是否创新素材',
  33. ifnull(t1.media_time,'-') AS '上线时间',
  34. ifnull(t2.channel_name,'-') AS '渠道',
  35. sum( t2.cost ) AS '消耗',
  36. ifnull(t2.clip_name,'-') AS '剪辑',
  37. ifnull(t2.shot_name,'-') AS '拍摄',
  38. ifnull(t2.plan_name,'-') AS '编导',
  39. ifnull(t2.leader_name,'-') AS '负责人',
  40. ifnull(t2.video_name,'-') AS '素材名称',
  41. ifnull(t2.video_url,'-') AS '素材'
  42. from (select company_name,material_id,media_time,material_innovate
  43. from application.app_bytedance_material_info where media_time >= date_format(date_sub({stat_date}, INTERVAL 13 DAY),'%%Y%%m%%d') and media_time<={stat_date}) t1
  44. left join
  45. (select * from application.bytedance_material_video_report_daily_dw where stat_datetime>=date_format(date_sub({stat_date}, INTERVAL 13 DAY),'%%Y%%m%%d')) t2
  46. on t1.material_id=t2.signature where t2.signature is not null
  47. group by t1.material_id;
  48. """.format(stat_date=self.stat_date)
  49. print(sql)
  50. path = """/data/excel/NewBytedanceAllMaterialFourD_{stat_date}.xls""".format(stat_date=self.stat_date)
  51. print(path)
  52. pd.read_sql(sql, self.OuterConn).to_excel(path, index=False, engine='openpyxl')
  53. my_sender = 'huichuangsituo@c-top.com.cn' # 发件人邮箱账号
  54. my_pass = 'rfZV5qRfendzM3Ph' # 发件人邮箱授权码 / 腾讯企业邮箱请使用登陆密码
  55. recipients = ['wanglei@c-top.com.cn',
  56. 'shiyuke@s-hcst.com',
  57. 'lihuan@c-top.com.cn'] # 收件人邮箱账号
  58. # content = "Please check the attachment. "
  59. msg = MIMEMultipart()
  60. # [发件人的邮箱昵称、发件人邮箱账号], 昵称随便
  61. msg['From'] = my_sender
  62. # [收件人邮箱昵称、收件人邮箱账号], 昵称随便
  63. msg['To'] = ",".join(recipients)
  64. # 邮件的主题,也就是邮件的标题
  65. msg['Subject'] = "头条--近14天上线素材消耗全明细"
  66. att1 = MIMEText(
  67. open(path, 'rb').read(), 'base64', 'utf-8')
  68. att1["Content-Type"] = 'application/octet-stream'
  69. att1["Content-Disposition"] = 'attachment; filename="NewBytedanceAllMaterialFourD.xls"'
  70. msg.attach(att1)
  71. server = smtplib.SMTP_SSL("smtp.exmail.qq.com", port=465)
  72. server.login(my_sender, my_pass)
  73. try:
  74. server.sendmail(my_sender, recipients, msg.as_string())
  75. print("发送成功")
  76. except Exception as e:
  77. print("发送失败", e)
  78. if __name__ == '__main__':
  79. NewBytedanceAllMaterialFourD().handler()