toutiaoproject_material_image_report.py 2.3 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2023/8/9 上午11:22
  4. # @Site :
  5. # @File : project_material_report.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. from sqlalchemy import create_engine
  13. from Apietl.conn.TidbConn import TidbConn
  14. class project_material_report:
  15. def __init__(self):
  16. self.conn = TidbConn.get_connection()
  17. self.OuterStr = "mysql+pymysql://%s:%s@%s:%d/%s" % (
  18. "hcst", parse.quote_plus("hcst@2024"), "192.168.0.249", 3390, "application")
  19. self.OuterConn = create_engine(self.OuterStr,
  20. pool_size=10,
  21. max_overflow=5,
  22. pool_timeout=10,
  23. pool_recycle=3600)
  24. def Project_Handler(self):
  25. sql = """
  26. select project_id,project_name from application.bytedance_advertiser_report_daily_dw where stat_datetime >= 20250601
  27. and stat_datetime <= 20250630
  28. and cost>0 and project_id is not null
  29. group by project_id
  30. """
  31. project_info_list = TidbConn.quary(self.conn, sql)
  32. for project_info in project_info_list:
  33. project_id = project_info[0]
  34. project_name = project_info[1]
  35. sql = """
  36. select project_name,image_url,image_name,aa,sum(cost) as cost from (
  37. select project_name,
  38. image_name,
  39. image_url,
  40. REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
  41. REPLACE(REPLACE(REPLACE(image_name, '0', ''), '1', ''), '2', ''),
  42. '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8',
  43. ''), '9', '') as aa,
  44. cost
  45. from application.bytedance_material_image_report_daily_dw
  46. where stat_datetime >= 20250601
  47. and stat_datetime <= 20250630
  48. and project_id={project_id}
  49. and cost>0
  50. )t
  51. where aa !=''
  52. group by image_name
  53. """.format(project_id=project_id)
  54. path = "/home/excel/toutiao/tupian/{project_name}.xls".format(project_name=project_name)
  55. pd.read_sql_query(sql, self.OuterConn).to_excel(path, index=False,engine='openpyxl')
  56. if __name__ == '__main__':
  57. project_material_report().Project_Handler()