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
