| 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162 | # Author renyupeng# coding=utf-8# @Time    : 2021/11/17 3:40 下午# @Site    : # @File    : MaterialFristCostTime.py# @Software: PyCharm# @contact: renyupeng@c-top.com.cn# @Tel 1501435553import loggingfrom math import ceilfrom Apietl.conn.TidbConn import TidbConnfrom Apietl.conn.TidbProConn import TidbProConnclass MaterialFristCostTime:    def __init__(self):        self.conn = TidbProConn.get_connection()        self.Innerconn = TidbConn.get_connection()    def TaskHandler(self):        try:            sql = """            select count(1) from (select signature,stat_datefrom (         select signature, stat_date, row_number() over (partition by signature order by stat_date) as rn         from application.kuaishou_material_video_report_daily_dw     )twhere t.rn=1)t            """            totalNum = TidbConn.quary(self.Innerconn, sql)[0][0]            pageCount = ceil(totalNum / 10000)            for i in range(pageCount):                try:                    sql = """                    replace into application.material_farst_cost_time                    select signature,stat_datefrom (         select signature, stat_date, row_number() over (partition by signature order by stat_date) as rn         from application.kuaishou_material_video_report_daily_dw where signature is not null     )twhere t.rn=1order by signature,stat_date limit {num},10000             """.format(num=i * 10000)                    print(sql)                    TidbConn.upsert(conn=self.Innerconn, sql_buff=sql)                    #TidbProConn.upsert(conn=self.conn, sql_buff=sql)                    self.conn.commit()                    self.Innerconn.commit()                except Exception as e:                    logging.error(e)                    return -1            TidbConn.conn_close(conn=self.Innerconn)            #TidbProConn.conn_close(conn=self.conn)            return 0        except Exception as e:            logging.error(e)if __name__ == '__main__':    MaterialFristCostTime().TaskHandler()
 |