| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193 | # Author renyupeng# coding=utf-8# @Time    : 2021/11/12 10:20 上午# @Site    :# @File    : AppKuaishouVideoList.py# @Software: PyCharm# @contact: renyupeng@c-top.com.cn# @Tel 1501435553import datetimeimport loggingfrom math import ceilfrom Apietl.conn.TidbConn import TidbConnclass AppKuaishouVideoList:    def __init__(self):        self.conn = TidbConn.get_connection()        self.date_time = (datetime.date.today() + datetime.timedelta(-1)).strftime("%Y-%m-%d")    def execSql(self):        try:            sql = """select account_id from media_api.kuaishou_video_list where  date_format(stat_date, '%Y-%m-%d') >= date_sub('{date_time}', INTERVAL 13 DAY)              group by account_id """.format(date_time=self.date_time)            account_list = TidbConn.quary(self.conn, sql)            for account_id in account_list:                try:                    sql = """            replace into application.app_kuaishou_video_list            select account_id,                   status,                   photo_id,                   width,                   height,                   url,                   cover_url,                   material_type,                   channel_type,                   v.stat_date,                   case when v.signature is null then '' else v.signature end as signature,                   photo_name,                   case when tag_category_id is null then '' else tag_category_id end as tag_category_id,                   tag_category_name,                   case when parent_id is null then '' else parent_id end as parent_id,                   parent_name,                   case when tag_code is null then '' else tag_code end as tag_code,                   tag_name,                   clip_id,                   clip_name,                   shot_id,                   shot_name,                   plan_id,                   plan_name,                   leader_id,                   leader_name,                   material_innovate,                   hot.stat_date,                   effect.stat_date            from (                     select `account_id`,                            `status`,                            `photo_id`,                            `width`,                            `height`,                            `url`,                            `cover_url`,                            `material_type`,                            `channel_type`,                            `stat_date`,                            `signature`,                            case when t.material_name is null then a.photo_name else t.material_name end as photo_name,                            clip_id,                            clip_name,                            shot_id,                            shot_name,                            plan_id,                            plan_name,                            leader_id,                            leader_name,                            material_innovate                     from (select `account_id`,                                  `status`,                                  `photo_id`,                                  `width`,                                  `height`,                                  `url`,                                  `cover_url`,                                  `material_type`,                                  `channel_type`,                                  `stat_date`,                                  `signature`,                                  `photo_name`                           from media_api.kuaishou_video_list                            where date_format(stat_date, '%Y-%m-%d') >= date_sub('{date_time}', INTERVAL 13 DAY)                           and account_id ={account_id}                          ) a                              left join                          (select material_id,                                  clip_id,                                  clip_name,                                  shot_id,                                  shot_name,                                  plan_id,                                  plan_name,                                  leader_id,                                  leader_name,                                  material_name,                                  material_innovate                           from (select cma.material_id,                                        clip_id,                                        clip.realname   as clip_name,                                        shot_id,                                        shot.realname   as shot_name,                                        plan_id,                                        plan.realname   as plan_name,                                        cma.leader_id,                                        leader.realname as leader_name,                                        material_name,                                        material_innovate                                 from `jeecg-boot`.ctop_material_ascription cma                                          left join                                      `jeecg-boot`.ctop_material_info b                                      on cma.material_id = b.id                                          left join `jeecg-boot`.sys_user clip on clip.id = cma.clip_id                                          left join `jeecg-boot`.sys_user shot on shot.id = cma.shot_id                                          left join `jeecg-boot`.sys_user plan on plan.id = cma.plan_id                                          left join `jeecg-boot`.sys_user leader on leader.id = cma.leader_id                                ) v) t                          on a.signature = t.material_id) v                     left join                 (select code,                         tag_category_id,                         tag_category_name,                         parent_id,                         parent_name,                         group_concat(tag_code order by tag_code separator '-') tag_code,                         group_concat(tag_name order by tag_code separator '-') tag_name                  from (select a.code, b.tag_category_id, b.tag_category_name, b.parent_id, b.parent_name, b.tag_code, b.tag_name                        from `jeecg-boot`.ctop_material_tag_info a                                 left join                             (select tag_category_id,                                     tag_category_name,                                     case                                         when parent_id is not null then parent_id                                         when tag_category_id = 2 then null                                         else tag_code end parent_id,                                     case                                         when parent_id = 60155 then '真人'                                         when parent_id = 60156 then '制作'                                         when parent_id = 60160 then '外拍场景'                                         when parent_id = 60161 then '公司内景'                                         when tag_category_id = 2 then null                                         else '其他' end     parent_name,                                     tag_code,                                     tag_name                              from `jeecg-boot`.ctop_tag_info) b                             on a.tag_code = b.tag_code) c                  group by code, tag_category_id, tag_category_name, parent_id, parent_name) m                 on v.signature = m.code                     left join (                                select signature,effect_date as stat_date from application.effect_material where media_id =2            ) effect                               on effect.signature = v.signature                     left join (           select signature,hot_date as stat_date from application.hot_material where media_id =2            ) hot                               on hot.signature = v.signature            group by account_id, photo_id, v.stat_date, v.signature, tag_category_id, parent_id, tag_code                                """.format(date_time=self.date_time, account_id=account_id[0])                    print(sql)                    TidbConn.upsert(conn=self.conn, sql_buff=sql)                    self.conn.commit()                except Exception as e:                    logging.error(e)                    return -1            TidbConn.conn_close(conn=self.conn)            return 0        except Exception as e:            logging.error(e)            return -1if __name__ == '__main__':    status = AppKuaishouVideoList().execSql()    if status == 0:        print("作业执行成功")        exit(0)    else:        print("作业执行失败")        exit(-1)
 |