send_bytedance_material_month.py 4.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2022/02/07 14:43 下午
  4. # @Site :
  5. # @File : send_bytedance_material_month.py
  6. # @Software: PyCharm
  7. # @contact: zhaohailiang@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_bytedance_material_month:
  17. def __init__(self):
  18. self.OuterStr = "mysql+pymysql://%s:%s@%s:%d/%s" % (
  19. "hcst", parse.quote_plus("hcst@2024"), "192.168.0.101", 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 (CASE
  29. WHEN a.clip_company_id = 'd57fecdcf7a94d009736d9c850731582' THEN
  30. '北京汇创思拓数字科技有限公司'
  31. WHEN a.clip_company_id = '6608ed13c0dd42de93c790dbdf124234' THEN
  32. '广州汇创思拓数字科技有限公司'
  33. WHEN a.clip_company_id = '4b10089775c040119e139087517aed88' THEN
  34. '上海汇创思拓数字科技有限公司'
  35. WHEN a.clip_company_id = '5e46b5dea3d740eeb8ff1a2895015a4b' THEN
  36. '北京骄阳数字科技有限公司'
  37. WHEN a.clip_company_id = '1648c7ceb4d449a9a13fbed7a8d9976c' THEN
  38. '杭州妙构思数字科技有限公司'
  39. WHEN a.clip_company_id = '3321d5893bcd430cab1238a99bc7db11' THEN
  40. '北京次元像素'
  41. WHEN a.clip_company_id='ace886fa51a244818e918b5d6d1bcc62' then
  42. '北京聚意创量文化传媒有限公司'
  43. when a.clip_company_id='3f362f527a804e8a9db6552ea12f7574'
  44. then '石家庄汇创思拓数字科技有限公司'
  45. else '-'
  46. END) as '公司',
  47. a.project_name AS '项目',
  48. a.signature AS '素材标识',
  49. b.frast_cost_time AS '上线时间',
  50. '-' 渠道,
  51. a.charge AS '消耗',
  52. trim(a.clip_name) AS '剪辑',
  53. trim(a.shot_name) AS '拍摄',
  54. trim(shot_leader_name) as `拍摄负责人`,
  55. trim(a.plan_name) AS '编导',
  56. trim(a.leader_name) AS '负责人',
  57. a.video_name AS '素材名称',
  58. tag_name as `素材标签`
  59. from
  60. (select clip_company_id,account_id,shot_leader_name,company_name,project_name,signature,channel_name,sum(charge)charge,clip_name,shot_name,plan_name,a.leader_name,video_name,photo_url from
  61. application.kuaishou_material_video_report_daily_dw a
  62. where stat_date >= 20240101 and stat_date<=20240131
  63. group by signature) a
  64. left join
  65. application.material_farst_cost_time b
  66. on a.signature=b.signature
  67. left join application.app_kuaishou_material_info c
  68. on a.signature=c.material_id""".format(stat_date=self.stat_date)
  69. print(sql)
  70. path = """/data/excel/send_bytedance_material_month.xls""".format(stat_date=self.stat_date)
  71. print(path)
  72. pd.read_sql(sql, self.OuterConn).to_excel(path, index=False, engine='openpyxl')
  73. my_sender = 'renyupeng@c-top.com.cn' # 发件人邮箱账号
  74. my_pass = 'fcyJKkHUyPo6Zztw' # 发件人邮箱授权码 / 腾讯企业邮箱请使用登陆密码
  75. recipients = ['niuxiaoyu@c-top.com.cn'] # 收件人邮箱账号
  76. # content = "Please check the attachment. "
  77. msg = MIMEMultipart()
  78. # [发件人的邮箱昵称、发件人邮箱账号], 昵称随便
  79. msg['From'] = my_sender
  80. # [收件人邮箱昵称、收件人邮箱账号], 昵称随便
  81. msg['To'] = ",".join(recipients)
  82. # 邮件的主题,也就是邮件的标题
  83. msg['Subject'] = "头条-全量素材月报"
  84. att1 = MIMEText(
  85. open(path, 'rb').read(), 'base64', 'utf-8')
  86. att1["Content-Type"] = 'application/octet-stream'
  87. att1["Content-Disposition"] = 'attachment; filename="send_bytedance_material_month.xls"'
  88. msg.attach(att1)
  89. server = smtplib.SMTP_SSL("smtp.exmail.qq.com", port=465)
  90. server.login(my_sender, my_pass)
  91. try:
  92. server.sendmail(my_sender, recipients, msg.as_string())
  93. print("发送成功")
  94. except Exception as e:
  95. print("发送失败",e)
  96. if __name__ == '__main__':
  97. send_bytedance_material_month().handler()