TidbKuaiShouAllMaterialEffeW.py 4.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101
  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 TidbKuaiShouAllMaterialEffeW:
  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 t1.company_name AS '公司',
  29. round(t2.single_week_cost, 3) AS '上线一周消耗',
  30. ifnull(t1.clip_name, '-') AS '剪辑',
  31. ifnull(t1.shot_name, '-') AS '拍摄',
  32. ifnull(t1.plan_name, '-') AS '编导',
  33. ifnull(t1.leader_name, '-') as '负责人',
  34. case
  35. when t1.material_innovate = 1 then '非创新素材'
  36. when t1.material_innovate = 2 then '创新素材'
  37. else '其它' end AS '是否创新素材',
  38. ifnull(t1.video_url, '-') AS '素材链接'
  39. from (select material_id,
  40. company_id,
  41. company_name,
  42. clip_name,
  43. shot_name,
  44. plan_name,
  45. leader_name,
  46. video_url,
  47. material_innovate
  48. from application.app_kuaishou_material_info
  49. where effect_date >= date_format(date_sub({stat_date}, INTERVAL 6 DAY), '%%Y%%m%%d') AND effect_date<={stat_date}
  50. and company_id is not null) t1
  51. left join
  52. (select a.signature, sum(c.charge) single_week_cost
  53. from application.effect_material a
  54. inner join application.material_farst_cost_time b
  55. on a.signature = b.signature
  56. inner join
  57. application.kuaishou_material_video_report_daily_dw c
  58. on a.signature = c.signature
  59. where c.stat_date >= b.frast_cost_time and c.stat_date<=date_format(date_add(b.frast_cost_time, interval 6 day), '%%Y%%m%%d')
  60. and a.media_id = 2
  61. group by a.signature) t2
  62. on t1.material_id = t2.signature
  63. order by single_week_cost desc;
  64. """.format(stat_date=self.stat_date)
  65. print(sql)
  66. path = """/data/excel/TidbKuaiShouAllMaterialEffeW_{stat_date}.xls""".format(stat_date=self.stat_date)
  67. print(path)
  68. pd.read_sql(sql, self.OuterConn).to_excel(path, index=False, engine='openpyxl')
  69. my_sender = 'renyupeng@c-top.com.cn' # 发件人邮箱账号
  70. my_pass = 'fcyJKkHUyPo6Zztw' # 发件人邮箱授权码 / 腾讯企业邮箱请使用登陆密码
  71. recipients = ['wanglei@c-top.com.cn',
  72. 'shiyuke@s-hcst.com',
  73. 'lihuan@c-top.com.cn'] # 收件人邮箱账号
  74. # content = "Please check the attachment. "
  75. msg = MIMEMultipart()
  76. # [发件人的邮箱昵称、发件人邮箱账号], 昵称随便
  77. msg['From'] = my_sender
  78. # [收件人邮箱昵称、收件人邮箱账号], 昵称随便
  79. msg['To'] = ",".join(recipients)
  80. # 邮件的主题,也就是邮件的标题
  81. msg['Subject'] = "快手-近一周有效素材全明细"
  82. att1 = MIMEText(
  83. open(path, 'rb').read(), 'base64', 'utf-8')
  84. att1["Content-Type"] = 'application/octet-stream'
  85. att1["Content-Disposition"] = 'attachment; filename="TidbKuaiShouAllMaterialEffeW.xls"'
  86. msg.attach(att1)
  87. server = smtplib.SMTP_SSL("smtp.exmail.qq.com", port=465)
  88. server.login(my_sender, my_pass)
  89. try:
  90. server.sendmail(my_sender, recipients, msg.as_string())
  91. print("发送成功")
  92. except Exception as e:
  93. print("发送失败", e)
  94. if __name__ == '__main__':
  95. TidbKuaiShouAllMaterialEffeW().handler()