| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263 | 
							- # Author renyupeng
 
- # coding=utf-8
 
- # @Time    : 2023/8/9 上午11:22
 
- # @Site    :
 
- # @File    : project_material_report.py
 
- # @Software: PyCharm
 
- # @contact: renyupeng@c-top.com.cn
 
- # @Tel 1501435553
 
- import datetime
 
- from urllib import parse
 
- import pandas as pd
 
- from sqlalchemy import create_engine
 
- from Apietl.conn.TidbConn import TidbConn
 
- class project_material_report:
 
-     def __init__(self):
 
-         self.conn = TidbConn.get_connection()
 
-         self.OuterStr = "mysql+pymysql://%s:%s@%s:%d/%s" % (
 
-             "hcst", parse.quote_plus("hcst@2024"), "192.168.0.101", 3390, "application")
 
-         self.OuterConn = create_engine(self.OuterStr,
 
-                                        pool_size=10,
 
-                                        max_overflow=5,
 
-                                        pool_timeout=10,
 
-                                        pool_recycle=3600)
 
-     def Project_Handler(self):
 
-         sql = """
 
-     select project_id,project_name from application.bytedance_advertiser_report_daily_dw  where  stat_datetime>=20240101 and stat_datetime<=20240331
 
- and cost>0 and project_id is not null
 
- group by project_id
 
-         """
 
-         project_info_list = TidbConn.quary(self.conn, sql)
 
-         for project_info in project_info_list:
 
-             project_id = project_info[0]
 
-             project_name = project_info[1]
 
-             sql = """
 
- select project_name,image_url,image_name,aa,sum(cost) as cost from (
 
- select project_name,
 
-        image_name,
 
-      image_url,
 
-        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
 
-                                                                REPLACE(REPLACE(REPLACE(image_name, '0', ''), '1', ''), '2', ''),
 
-                                                                '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8',
 
-                        ''), '9', '') as aa,
 
-         cost
 
- from application.bytedance_material_image_report_daily_dw
 
- where stat_datetime >= 20240101
 
-   and stat_datetime <= 20240331
 
-   and project_id={project_id}
 
-     and cost>0
 
- )t
 
-  where aa !=''
 
- group by image_name
 
-             """.format(project_id=project_id)
 
-             path = "/data/excel/toutiao/tupian/{project_name}.xls".format(project_name=project_name)
 
-             pd.read_sql_query(sql, self.OuterConn).to_excel(path, index=False,engine='openpyxl')
 
- if __name__ == '__main__':
 
-     project_material_report().Project_Handler()
 
 
  |