def getInsightViews(start_date, end_date): logger.info(" extral data from Dashboard.matchedVideoViewCountCompletionAll start") date_dict ={"start_date": start_date, "end_date": end_date} target_server_section = "target_server_staging" insight_server_section = "insight" target_host, target_user, target_passwd, target_port, target_db = getConfMysqlInfo(target_server_section) insight_host, insight_user, insight_passwd, insight_port, insight_db = getConfMysqlInfo(insight_server_section) try: get_data_sql = """ select matchedVideo_id, trackingWebsite_id, trackingMeta_id, company_id, report_at, view_count, hide_flag, current_timestamp as ETLDate from matchedVideoViewCountCompletionAll where report_at > "%(start_date)s" and report_at <= "%(end_date)s" """ %date_dict insight_mysql = MySQLHelper(host=insight_host, user=insight_user,passwd=insight_passwd, port = insight_port, db_name = insight_db) data = insight_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 matchedVideoViewCountCompletionAll (matchedVideo_id, trackingWebsite_id, trackingMeta_id, company_id, report_at, view_count, hide_flag, ETLDate) values(%s, %s, %s, %s, %s, %s, %s, %s) """ commitInTurn(commit_num=100000, data=data, executeFun=target_mysql.executeManyCMD, \ commitFun=target_mysql.commit, executeSQL=insert_sql) #target_mysql.executeManyCMD(insert_sql, data) #target_mysql.commit() except Exception, e: logger.debug("extral data from Dashboard.matchedVideoViewCountCompletionAll, %s" %e) sendToMe(subject = "titleBased_matchedVideoViewCountCompletionAll ERROR", body = re.sub(r'\'|"|!', "", str(e))) sys.exit(0)
def loadDataTo123(vtweb_data): logger.info(":load data to TitleBasedTrackingWebsite start") target_server_section = "target_server_staging" try: 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=target_db, port=target_port, charset='utf8') insertUpdate_SQL = """ INSERT INTO TitleBasedTrackingWebsite (trackingWebsite_id, websiteType, websiteName, websiteDomain, country_id, ETLDate) VALUES(%s, %s, %s, %s, %s, %s) on duplicate key update websiteDomain = values(websiteDomain), websiteType = values(websiteType), ETLDate = values(ETLDate), websiteName = values(websiteName), country_id = values(country_id) """ result1 = [(item[0], item[1], item[2], re.sub(r"_ugc|_hybrid|_Hybrid|_cyberlocker|_UGC|_Cy|_cy", '', item[3]), item[4], item[5]) for item in vtweb_data] commitInTurn(commit_num = 50000, data = result1, executeFun = target_mysql.insertUpdateCMD, \ commitFun = target_mysql.commit, executeSQL = insertUpdate_SQL) except Exception, e: logger.debug(": load data to TitleBasedTrackingWebsite, %s" % e) sendToMe(subject="TitleBasedTrackingWebsite ERROR", body=re.sub(r'\'|"|!', "", str(e))) sys.exit(0)
def dataToTarget(data, db): logger.info("load data target metaExtraInfo start") try: 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 = db, port = target_port, charset = 'utf8') insert_sql = """ insert into metaExtraInfo (meta_id, company_id, display_name, priority_type, created_at, updated_at) values(%s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE display_name = values(display_name), priority_type = values(priority_type), created_at = values(created_at), updated_at = values(updated_at) """ commitInTurn(commit_num = 50000, data = data, executeFun = target_mysql.executeManyCMD, \ commitFun = target_mysql.commit, executeSQL =insert_sql) #target_mysql.executeManyCMD(insert_sql, data) #target_mysql.commit() except Exception, e: logger.debug("extral data from tracker2.metaExtraInfo, %s" % e) sendToMe(subject="metaExtraInfo ERROR", body=re.sub(r'\'|"|!', "", str(e))) sys.exit(0)
def dataToTarget(data, db): logger.info("load data target matchedVideo start") try: 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 = db, port = target_port, charset = 'utf8') insert_sql = """ insert into matchedVideo (id, company_id, trackingMeta_id, trackingWebsite_id, view_count, count_send_notice, first_send_notice_date, takeoff_time, hide_flag, clip_duration, matchedFile_id, meta_title, created_at, updated_at) values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE trackingMeta_id = values(trackingMeta_id), trackingWebsite_id = values(trackingWebsite_id), view_count = values(view_count), count_send_notice = values(count_send_notice), first_send_notice_date = values(first_send_notice_date), takeoff_time = values(takeoff_time), hide_flag = values(hide_flag), clip_duration = values(clip_duration), matchedFile_id = values(matchedFile_id), meta_title = values(meta_title), created_at = values(created_at), updated_at = values(updated_at) """ commitInTurn(commit_num = 50000, data = data, executeFun = target_mysql.executeManyCMD, \ commitFun = target_mysql.commit, executeSQL =insert_sql) #target_mysql.executeManyCMD(insert_sql, data) #target_mysql.commit() except Exception, e: logger.debug("extral data from tracker2.matchedVideo, %s" % e) sendToMe(subject="matchedVideo ERROR", body=re.sub(r'\'|"|!', "", str(e))) sys.exit(0)
def dataToTarget(data, db): logger.info("load data target matchedVideo start") try: 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 = db, port = target_port, charset = 'utf8') insert_sql = """ insert into Test (id, company_id, trackingMeta_id, trackingWebsite_id, view_count, count_send_notice, first_send_notice_date, takeoff_time, hide_flag, clip_duration, matchedFile_id, meta_title, created_at, updated_at) values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE trackingMeta_id = values(trackingMeta_id), trackingWebsite_id = values(trackingWebsite_id), view_count = values(view_count), count_send_notice = values(count_send_notice), first_send_notice_date = values(first_send_notice_date), takeoff_time = values(takeoff_time), hide_flag = values(hide_flag), clip_duration = values(clip_duration), matchedFile_id = values(matchedFile_id), meta_title = values(meta_title), created_at = values(created_at), updated_at = values(updated_at) """ commitInTurn(commit_num = 50000, data = data, executeFun = target_mysql.executeManyCMD, \ commitFun = target_mysql.commit, executeSQL =insert_sql) #target_mysql.executeManyCMD(insert_sql, data) #target_mysql.commit() except Exception, e: logger.debug("extral data from tracker2.matchedVideo, %s" %e) sendToMe(subject = "matchedVideo ERROR", body = re.sub(r'\'|"|!', "", str(e))) sys.exit(0)
def getInsightViews(start_date, end_date): logger.info(" extral data from Dashboard.matchedVideoViewCountCompletionAll start") date_dict ={"start_date": start_date, "end_date": end_date} target_server_section = "target_server_staging" insight_server_section = "insight_master" target_host, target_user, target_passwd, target_port, target_db = getConfMysqlInfo(target_server_section) insight_host, insight_user, insight_passwd, insight_port, insight_db = getConfMysqlInfo(insight_server_section) try: get_data_sql = """ select matchedVideo_id, trackingWebsite_id, trackingMeta_id, company_id, report_at, view_count, hide_flag, current_timestamp as ETLDate from matchedVideoViewCountCompletionAll_fox137 where report_at > "%(start_date)s" and report_at <= "%(end_date)s" """ %date_dict insight_mysql = MySQLHelper(host=insight_host, user=insight_user,passwd=insight_passwd, port = insight_port, db_name = insight_db) data = insight_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 matchedVideoViewCountCompletionAll (matchedVideo_id, trackingWebsite_id, trackingMeta_id, company_id, report_at, view_count, hide_flag, ETLDate) values(%s, %s, %s, %s, %s, %s, %s, %s) """ commitInTurn(commit_num=100000, data=data, executeFun=target_mysql.executeManyCMD, \ commitFun=target_mysql.commit, executeSQL=insert_sql) #target_mysql.executeManyCMD(insert_sql, data) #target_mysql.commit() except Exception, e: logger.debug("extral data from Dashboard.matchedVideoViewCountCompletionAll, %s" %e) sendToMe(subject = "titleBased_matchedVideoViewCountCompletionAll ERROR", body = re.sub(r'\'|"|!', "", str(e))) sys.exit(0)
def loadDataToTitleBasedRemoveNum1(): logger.info(":extract data from 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') aggregate_SQL = """ select a.reportDate, a.takeoffDate, a.trackingWebsite_id, ifnull(c.displayName, a.websiteName) as websiteName, a.websiteType, ifnull(b.mapTitle, a.title) as title, sum(removedNum) as removedNum, sum(complianceTime) as complianceTime, current_timestamp as ETLDate from (select a.reportDate, a.takeoffDate, a.trackingWebsite_id, c.websiteName, c.websiteType, b.title, sum(removedNum) as removedNum, sum(complianceTime) as complianceTime from TitleBasedRemoveNumTmp as a, TitleBasedMeta as b, TitleBasedTrackingWebsite as c where a.trackingWebsite_id = c.trackingWebsite_id and a.trackingMeta_id = b.trackingMeta_id group by 1, 2, 3, 4, 5, 6) as a left join MetaTitleMapTitle as b on a.title = b.metaTitle left join SiteMap as c on a.trackingWebsite_id = c.trackingWebsite_id group by 1, 2, 3, 4, 5, 6 """ target_mysql.queryNoData("delete from TitleBasedRemoveNum") aggregate_result = target_mysql.queryCMD(aggregate_SQL) insertUpdate_SQL = """ INSERT INTO TitleBasedRemoveNum (reportDate, takeoffDate, trackingWebsite_id, websiteName, websiteType, title, removedNum, complianceTime, ETLDate) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE removedNum = VALUES(removedNum), complianceTime = VALUES(complianceTime), ETLDate = VALUES(ETLDate) """ target_mysql.queryNoData("delete from TitleBasedRemoveNum") commitInTurn(commit_num = 50000, data = aggregate_result, executeFun = target_mysql.insertUpdateCMD, \ commitFun = target_mysql.commit, executeSQL = insertUpdate_SQL) #target_mysql.insertUpdateCMD(insertUpdate_SQL, aggregate_result) #target_mysql.commit() except Exception, e: logger.debug(" load data to TitleBasedRemoveNum, %s" %e) sendToMe(subject = "TitleBasedRemove ERROR", body = re.sub(r'\'|"|!', "", str(e))) sys.exit(0)
def updateViews(start_date, end_date): logger.info(" aggregate data from matchedVideoViewCountCompletion start") target_server_section = "target_server_staging" target_host, target_user, target_passwd, target_port, target_db = getConfMysqlInfo(target_server_section) date_dict = {"start_date": start_date, "end_date": end_date} try: get_data_sql = """ select a.reportDate, a.trackingWebsite_id, ifnull(c.displayName, a.websiteName) as websiteName, a.websiteType, ifnull(b.mapTitle, a.title) title, sum(a.infringingViews) as infringingViews, sum(a.reportedViews) as reportedViews, current_timestamp as ETLDate from (select a.report_at as reportDate, a.trackingWebsite_id, c.websiteName, c.websiteType, b.title, sum(if(a.hide_flag = 2, a.view_count, 0)) as infringingViews, sum(a.view_count) as reportedViews from matchedVideoViewCountCompletionAll as a, TitleBasedMeta as b, TitleBasedTrackingWebsite as c where a.trackingWebsite_id = c.trackingWebsite_id and a.trackingMeta_id = b.trackingMeta_id and c.websiteType = 'ugc' and a.report_at > "%(start_date)s" and a.report_at <= "%(end_date)s" group by 1, 2, 3, 4, 5) as a left join MetaTitleMapTitle as b on a.title = b.metaTitle left join SiteMap as c on a.trackingWebsite_id = c.trackingWebsite_id group by 1, 2, 3, 4, 5 """ % date_dict target_mysql = MySQLHelper(host=target_host, user=target_user, passwd=target_passwd, \ db_name=target_db, port=target_port, charset='utf8') data = target_mysql.queryCMD(get_data_sql) insert_sql = """insert into TitleBased1 (reportDate, trackingWebsite_id, websiteName, websiteType, title, infringingViews, reportedViews, ETLDate) values(%s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE infringingViews = values(infringingViews), reportedViews = values(reportedViews), ETLDate = values(ETLDate), websiteName = VALUES(websiteName) """ commitInTurn(commit_num=100000, data=data, executeFun=target_mysql.executeManyCMD, \ commitFun=target_mysql.commit, executeSQL=insert_sql) except Exception, e: logger.debug("aggregate data to TitleBased1 ERROR , %s" % e) sendToMe(subject="titleBased_infringAllViews ERROR", body=re.sub(r'\'|"|!', "", str(e))) sys.exit(0)
def dataToTarget(data, db, insert_sql): logger.info("load data target metaExtraInfo start") try: 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 = db, port = target_port, charset = 'utf8') commitInTurn(commit_num = 50000, data = data, executeFun = target_mysql.executeManyCMD, \ commitFun = target_mysql.commit, executeSQL =insert_sql) #target_mysql.executeManyCMD(insert_sql, data) #target_mysql.commit() except Exception, e: logger.debug("extral data from tracker2.metaExtraInfo, %s" %e) sendToMe(subject = "metaExtraInfo ERROR", body = re.sub(r'\'|"|!', "", str(e))) sys.exit(0)
def dataToTarget(data, db, insert_sql): logger.info("load data target start") try: 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 = db, port = target_port, charset = 'utf8') commitInTurn(commit_num = 100000, data = data, executeFun = target_mysql.executeManyCMD, \ commitFun = target_mysql.commit, executeSQL =insert_sql) #target_mysql.executeManyCMD(insert_sql, data) #target_mysql.commit() except Exception, e: logger.debug("extract data from tracker2, %s" %e) sendToMe(subject = "extract data from tracker2 ERROR", body = re.sub(r'\'|"|!', "", str(e))) sys.exit(0)
def loadDataTo123(vtweb_data): logger.info(":load data to TitleBasedMeta start") target_server_section = "target_server_staging" try: 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 = target_db, port = target_port, charset = 'utf8') insertUpdate_SQL = """ INSERT INTO TitleBasedMeta(trackingMeta_id, metaType, title, ETLDate) VALUES(%s, %s, %s, %s) on duplicate key update title = values(title), ETLDate = values(ETLDate), metaType = values(metaType) """ commitInTurn(commit_num = 50000, data = vtweb_data, executeFun = target_mysql.insertUpdateCMD, \ commitFun = target_mysql.commit, executeSQL = insertUpdate_SQL) #target_mysql.insertUpdateCMD(insertUpdate_SQL, result) #target_mysql.commit() except Exception, e: logger.debug(": load data to TitleBasedMeta, %s" %e) sendToMe(subject = "titleBased_meta ERROR", body = e) sys.exit(0)
def loadDataTo123(vtweb_data): logger.info(":load data to TitleBasedTrackingWebsite start") target_server_section = "target_server_staging" try: 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 = target_db, port = target_port, charset = 'utf8') insertUpdate_SQL = """ INSERT INTO TitleBasedTrackingWebsite (trackingWebsite_id, websiteType, websiteName, websiteDomain, country_id, ETLDate) VALUES(%s, %s, %s, %s, %s, %s) on duplicate key update websiteDomain = values(websiteDomain), websiteType = values(websiteType), ETLDate = values(ETLDate), websiteName = values(websiteName), country_id = values(country_id) """ result1 = [(item[0], item[1], item[2], re.sub(r"_ugc|_hybrid|_Hybrid|_cyberlocker|_UGC|_Cy|_cy", '', item[3]), item[4], item[5]) for item in vtweb_data] commitInTurn(commit_num = 50000, data = result1, executeFun = target_mysql.insertUpdateCMD, \ commitFun = target_mysql.commit, executeSQL = insertUpdate_SQL) except Exception, e: logger.debug(": load data to TitleBasedTrackingWebsite, %s" %e) sendToMe(subject = "TitleBasedTrackingWebsite ERROR", body = re.sub(r'\'|"|!', "", str(e))) sys.exit(0)
def loadDataToTitleBasedRemoveNum(vtweb_data): 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.queryNoData("delete from TitleBasedRemoveNum") commitInTurn(commit_num = 50000, data = vtweb_data, executeFun = target_mysql.executeManyCMD, \ commitFun = target_mysql.commit, executeSQL = insert_SQL) #target_mysql.executeManyCMD(insert_SQL, result) #target_mysql.commit() except Exception, e: logger.debug(": load data to TitleBasedRemoveNum, %s" %e) sendToMe(subject = "TitleBasedRemove ERROR", body = e) sys.exit(0)
def dataToTarget(data, db): logger.info("load data target metaExtraInfo start") try: 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 = db, port = target_port, charset = 'utf8') insert_sql = """ insert into metaExtraInfo (meta_id, company_id, display_name, priority_type, created_at, updated_at) values(%s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE display_name = values(display_name), priority_type = values(priority_type), created_at = values(created_at), updated_at = values(updated_at) """ commitInTurn(commit_num = 50000, data = data, executeFun = target_mysql.executeManyCMD, \ commitFun = target_mysql.commit, executeSQL =insert_sql) #target_mysql.executeManyCMD(insert_sql, data) #target_mysql.commit() except Exception, e: logger.debug("extral data from tracker2.metaExtraInfo, %s" %e) sendToMe(subject = "metaExtraInfo ERROR", body = re.sub(r'\'|"|!', "", str(e))) sys.exit(0)
def aggregateDataToTitleBased1(): logger.info(" aggregate data from TitleBased to TitleBased1 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') aggregate_TitleBased1_SQL = """ select a.reportDate, a.trackingWebsite_id, ifnull(c.displayName, a.websiteName) as websiteName, a.websiteType, ifnull(b.mapTitle, a.title) title, min(a.tier) as tier, sum(matchedNum) as matchedNum, sum(matchedNumDurationNoZero) as matchedNumDurationNoZero, sum(infringingNum) as infringingNum, sum(infringingNumDurationNoZero) as infringingNumDurationNoZero, sum(clipDurationSum) as clipDurationSum, sum(clipDurationInfringingSum) as clipDurationInfringingSum, current_timestamp as ETLDate from (select a.reportDate, a.trackingWebsite_id, c.websiteName, c.websiteType, b.title, min(a.tier) tier, sum(matchedNum) as matchedNum, sum(matchedNumDurationNoZero) as matchedNumDurationNoZero, sum(infringingNum) as infringingNum, sum(infringingNumDurationNoZero) as infringingNumDurationNoZero, sum(clipDurationSum) as clipDurationSum, sum(clipDurationInfringingSum) as clipDurationInfringingSum, current_timestamp as ETLDate from TitleBasedTmp as a, TitleBasedMeta as b, TitleBasedTrackingWebsite as c where a.trackingWebsite_id = c.trackingWebsite_id and a.trackingMeta_id = b.trackingMeta_id group by 1, 2, 3, 4, 5) as a left join MetaTitleMapTitle as b on a.title = b.metaTitle left join SiteMap as c on a.trackingWebsite_id = c.trackingWebsite_id group by 1, 2, 3, 4, 5 """ #%date_para_TitleBased1_dict TitleBased1_result = target_mysql.queryCMD(aggregate_TitleBased1_SQL) target_mysql.queryNoData("delete from TitleBased1;") insert_TitleBased1_SQL = """ INSERT INTO TitleBased1 (reportDate, trackingWebsite_id, websiteName, websiteType, title, tier, matchedNum, matchedNumDurationNoZero, infringingNum, infringingNumDurationNoZero, clipDurationSum, clipDurationInfringingSum, ETLDate) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE matchedNum = VALUES(matchedNum), clipDurationSum = VALUES(clipDurationSum), infringingNum = VALUES(infringingNum), ETLDate = VALUES(ETLDate), matchedNumDurationNoZero = VALUES(matchedNumDurationNoZero), matchedNumDurationNoZero = VALUES(matchedNumDurationNoZero), infringingNumDurationNoZero = VALUES(infringingNumDurationNoZero), clipDurationInfringingSum = VALUES(clipDurationInfringingSum), websiteName = VALUES(websiteName), tier = VALUES(tier) """ commitInTurn(commit_num = 50000, data = TitleBased1_result, executeFun = target_mysql.insertUpdateCMD, \ commitFun = target_mysql.commit, executeSQL = insert_TitleBased1_SQL) #target_mysql.insertUpdateCMD(insert_TitleBased1_SQL, TitleBased1_result) #target_mysql.commit() except Exception, e: logger.debug(": load data to TitleBased1, %s" %e) sendToMe(subject = "titleBased_titleBased ERROR", body = re.sub(r'\'|"|!', "", str(e))) sys.exit(0)
def aggregateDataToTitleBased1(): logger.info(" aggregate data from TitleBased to TitleBased1 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') aggregate_TitleBased1_SQL = """ select a.reportDate, a.trackingWebsite_id, a.websiteName, a.websiteType, ifnull(b.mapTitle, a.title) title, min(a.tier) as tier, sum(matchedNum) as matchedNum, sum(matchedNumDurationNoZero) as matchedNumDurationNoZero, sum(infringingNum) as infringingNum, sum(infringingNumDurationNoZero) as infringingNumDurationNoZero, sum(clipDurationSum) as clipDurationSum, sum(clipDurationInfringingSum) as clipDurationInfringingSum, current_timestamp as ETLDate from (select a.reportDate, a.trackingWebsite_id, c.websiteName, c.websiteType, b.title, min(a.tier) tier, sum(matchedNum) as matchedNum, sum(matchedNumDurationNoZero) as matchedNumDurationNoZero, sum(infringingNum) as infringingNum, sum(infringingNumDurationNoZero) as infringingNumDurationNoZero, sum(clipDurationSum) as clipDurationSum, sum(clipDurationInfringingSum) as clipDurationInfringingSum, current_timestamp as ETLDate from TitleBasedTmp as a, TitleBasedMeta as b, TitleBasedTrackingWebsite as c where a.trackingWebsite_id = c.trackingWebsite_id and a.trackingMeta_id = b.trackingMeta_id group by 1, 2, 3, 4, 5) as a left join MetaTitleMapTitle as b on a. title = b.metaTitle group by 1, 2, 3, 4, 5 """ #%date_para_TitleBased1_dict TitleBased1_result = target_mysql.queryCMD(aggregate_TitleBased1_SQL) insert_TitleBased1_SQL = """ INSERT INTO TitleBased1 (reportDate, trackingWebsite_id, websiteName, websiteType, title, tier, matchedNum, matchedNumDurationNoZero, infringingNum, infringingNumDurationNoZero, clipDurationSum, clipDurationInfringingSum, ETLDate) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE matchedNum = VALUES(matchedNum), clipDurationSum = VALUES(clipDurationSum), infringingNum = VALUES(infringingNum), ETLDate = VALUES(ETLDate), matchedNumDurationNoZero = VALUES(matchedNumDurationNoZero), matchedNumDurationNoZero = VALUES(matchedNumDurationNoZero), infringingNumDurationNoZero = VALUES(infringingNumDurationNoZero), clipDurationInfringingSum = VALUES(clipDurationInfringingSum) """ commitInTurn(commit_num = 50000, data = TitleBased1_result, executeFun = target_mysql.insertUpdateCMD, \ commitFun = target_mysql.commit, executeSQL = insert_TitleBased1_SQL) #target_mysql.insertUpdateCMD(insert_TitleBased1_SQL, TitleBased1_result) #target_mysql.commit() except Exception, e: logger.debug(": load data to TitleBased1, %s" %e) sendToMe(subject = "titleBased_titleBased ERROR", body = re.sub(r'\'|"|!', "", str(e))) sys.exit(0)