def dataTo_matchedVideoSequence(): logger.info("load data target matchedVideoSequence start") sendToMe(subject="matchedVideoSequence start", body="matchedVideoSequence start") #delete from matchedVideoSequence target_server_section = "target_server_staging" target_host, target_user, target_passwd, target_port, target_db = getConfMysqlInfo( target_server_section) target_mysql = MySQLHelper(host=target_host, user=target_user, passwd=target_passwd, \ db_name = "FOX_DASHBOARD", port = target_port, charset = 'utf8') target_mysql.queryNoData("delete from matchedVideoSequence") target_mysql.commit() target_mysql.closeConn() target_mysql.closeCur() # get arch matchedVideo id and insert into matchedVideoSequence get_data_arch_sql = """ select id, company_id from archTracker2.matchedVideo where company_id = 10 and created_at >= "2015-03-01" """ arch_data = getData(get_data_arch_sql, section="vtweb-arch") insert_arch_sql = """insert into matchedVideoSequence (id, company_id) values(%s, %s)""" dataToTarget(arch_data, "FOX_DASHBOARD", insert_arch_sql) # get matchedVideo id and insert into matchedVideoSequence get_data_sql = """ select id, company_id from tracker2.matchedVideo where company_id = 10 and created_at >= "2015-03-01" """ data = getData(get_data_sql, section="vtweb") insert_sql = """ insert into matchedVideoSequence (id, company_id) values(%s, %s) ON DUPLICATE KEY UPDATE id = values(id), company_id = values(company_id) """ dataToTarget(data, "FOX_DASHBOARD", insert_sql) sendToMe(subject="matchedVideoSequence end", body="matchedVideoSequence end") logger.info("load data target matchedVideoSequence end")
def dataTo_matchedVideoSequence(): logger.info("load data target matchedVideoSequence start") sendToMe(subject = "matchedVideoSequence start", body = "matchedVideoSequence start") #delete from matchedVideoSequence target_server_section = "target_server_staging" target_host, target_user, target_passwd, target_port, target_db= getConfMysqlInfo(target_server_section) target_mysql = MySQLHelper(host=target_host, user=target_user, passwd=target_passwd, \ db_name = "FOX_DASHBOARD", port = target_port, charset = 'utf8') target_mysql.queryNoData("delete from matchedVideoSequence") target_mysql.commit() target_mysql.closeConn() target_mysql.closeCur() # get arch matchedVideo id and insert into matchedVideoSequence get_data_arch_sql = """ select id, company_id from archTracker2.matchedVideo where company_id = 10 and created_at >= "2015-03-01" """ arch_data = getData(get_data_arch_sql, section = "vtweb-arch") insert_arch_sql = """insert into matchedVideoSequence (id, company_id) values(%s, %s)""" dataToTarget(arch_data, "FOX_DASHBOARD", insert_arch_sql) # get matchedVideo id and insert into matchedVideoSequence get_data_sql = """ select id, company_id from tracker2.matchedVideo where company_id = 10 and created_at >= "2015-03-01" """ data = getData(get_data_sql, section = "vtweb") insert_sql = """ insert into matchedVideoSequence (id, company_id) values(%s, %s) ON DUPLICATE KEY UPDATE id = values(id), company_id = values(company_id) """ dataToTarget(data, "FOX_DASHBOARD", insert_sql) sendToMe(subject = "matchedVideoSequence end", body = "matchedVideoSequence end") logger.info("load data target matchedVideoSequence end")
group by 1, 2, 3, 4 """ %date_para_TitleBasedRemoveNum_dict #vtweb_tracker2_section = "vtweb_tracker2" vtweb_tracker2_section = "vtweb_staging" try: vt_host, vt_user, vt_passwd, vt_port, vt_db = getConfMysqlInfo(vtweb_tracker2_section) vtweb_mysql = MySQLHelper(host=vt_host, user=vt_user,passwd=vt_passwd, port = vt_port, db_name = vt_db) vtweb_mysql.queryCMD("set time_zone = '-5:00'") result = vtweb_mysql.queryCMD(vt_TitleBasedRemoveNum_SQL) except Exception, e: logger.debug(": extract data from vt for TitleBasedRemoveNum, %s" %e) sys.exit(0) finally: vtweb_mysql.closeCur() vtweb_mysql.closeConn() logger.info(": extract data from tracker2 end") logger.info(":load data to TitleBasedRemoveNum start") target_server_section = "target_server_staging" target_host, target_user, target_passwd, target_port, target_db= getConfMysqlInfo(target_server_section) try: target_mysql = MySQLHelper(host=target_host, user=target_user, passwd=target_passwd, db_name = target_db, port = target_port, charset = 'utf8') insert_SQL = """ INSERT INTO TitleBasedRemoveNum(reportDate, takeoffDate, trackingWebsite_id, trackingMeta_id, removedNum, complianceTime, ETLDate) VALUES(%s, %s, %s, %s, %s, %s, %s) """ target_mysql.executeManyCMD(insert_SQL, result) target_mysql.commit()
title, sum(ReportedViews) as reportedViews, current_timestamp as ETLDate from DM_VIACOM.SelfService_Aggregate_ByNoticedDate as a, TitleBasedTrackingWebsite as b where a.trackingWebsite_id = b.trackingWebsite_id and a.WebsiteType = 'ugc' and b.WebsiteType = 'ugc' and a.Date_ID >= '%(date_para_reportedViews_min)s' and a.Date_ID < '%(date_para_reportedViews_max)s' group by 1, 2, 3, 4, 5 """ %date_para_reportedViews_dict reportedViews_result = target_mysql.queryCMD(aggregate_reportedViews_SQL) insert_reportedViews_SQL = """ INSERT INTO TitleBased1 (reportDate, trackingWebsite_id, websiteName, websiteType, title, reportedViews, ETLDate) VALUES (%s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE reportedViews = VALUES(reportedViews), ETLDate = VALUES(ETLDate) """ target_mysql.insertUpdateCMD(insert_reportedViews_SQL, reportedViews_result) target_mysql.commit() except Exception, e: logger.debug(": load data to TitleBased1, %s" %e) sys.exit(0) finally: target_mysql.closeCur() target_mysql.closeConn() logger.info(" aggregate data from DM_VIACOM.SelfService_Aggregate_ByNoticedDate to TitleBased1 end")
and a.Date_ID < '%(date_para_reportedViews_max)s' group by 1, 2, 3, 4, 5 """ % date_para_reportedViews_dict reportedViews_result = target_mysql.queryCMD(aggregate_reportedViews_SQL) insert_reportedViews_SQL = """ INSERT INTO TitleBased1 (reportDate, trackingWebsite_id, websiteName, websiteType, title, reportedViews, ETLDate) VALUES (%s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE reportedViews = VALUES(reportedViews), ETLDate = VALUES(ETLDate) """ target_mysql.insertUpdateCMD(insert_reportedViews_SQL, reportedViews_result) target_mysql.commit() except Exception, e: logger.debug(": load data to TitleBased1, %s" % e) sendToMe(subject="titleBased_views ERROR", body=e) sys.exit(0) finally: target_mysql.closeCur() target_mysql.closeConn() logger.info( " aggregate data from DM_VIACOM.SelfService_Aggregate_ByNoticedDate to TitleBased1 end" ) sendToMe( subject="titleBased_views End", body= "aggregate data from DM_VIACOM.SelfService_Aggregate_ByNoticedDate(views) to TitleBased1 end" )
data = vtweb_mysql.queryCMD(get_data_sql) target_mysql = MySQLHelper(host=target_host, user=target_user, passwd=target_passwd, \ db_name = target_db, port = target_port, charset = 'utf8') insert_sql = """insert into matchedVideo (matchedVideo_id, trackingMeta_id, trackingWebsite_id, firstSendNoticeDate, reportDate, ETLDate) values(%s, %s, %s, %s, %s, %s) """ target_mysql.executeManyCMD(insert_sql, data) target_mysql.commit() except Exception, e: logger.debug("extral data from tracker2.matchedVideo, %s" %e) sendToMe(subject = "titleBased_infringAllViews ERROR", body = str(e).replace("\"", "").replace("'", "").replace("!", "")) sys.exit(0) finally: vtweb_mysql.closeConn() vtweb_mysql.closeCur() target_mysql.closeCur() target_mysql.closeConn() logger.info(" extral data from tracker2.matchedVideo end") def main(): sendToMe(subject = "titleBased_infringAllViews start", body = "titleBased_infringAllViews start") matchedVideo_start_date = getMinDatePara("matchedVideo", "firstSendNoticeDate") if not matchedVideo_start_date: matchedVideo_start_date = "2015-02-28" while True: if str(matchedVideo_start_date) >= str(time.strftime('%Y-%m-%d',time.localtime(time.time() - 24 * 60 * 60 * 365))): break