# 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.249", 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>=20250101 and stat_datetime<=20250331 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 >= 20250101 and stat_datetime <= 20250331 and project_id={project_id} and cost>0 )t where aa !='' group by image_name """.format(project_id=project_id) path = "/home/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()