def dataTo_matchedVideo():
logger.info("load data target matchedVideo start")
sendToMe(subject = "matchedVideo start", body = "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 = "tracker2", port = target_port, charset = 'utf8')
sql = """
insert into tracker2.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)
select
a.id, a.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
from tracker2.matchedVideoTmp as a, tracker2.matchedVideoSequence as b
where a.id = b.id and a.company_id = b.company_id
"""
target_mysql.queryNoData("delete from tracker2.matchedVideo")
target_mysql.queryNoData(sql)
target_mysql.commit()
except Exception, e:
logger.debug("extract data from 192.168.111.235 tracker2 error, %s" %e)
sendToMe(subject = "extract data from 192.168.111.235 tracker2", body = re.sub(r'\'|"|!', "", str(e)))
sys.exit(0)
def main():
cfg_file = "/Job/FOX/Dashboard/TitleBased/conf/viacom_dashboard.cfg"
if not os.path.exists(cfg_file):
logging.debug(": config file not exists; file_name %s" %cfg_file)
sendToMe(subject = "SiteBased_alexa ERROR", body = "config file not exists")
sys.exit(0)
logger.info(": extract data from siteBased start")
socket.setdefaulttimeout(10.0)
sendToMe(subject = "SiteBased_alexa start", body = "extract data from siteBased start")
target_server_section = "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')
if True:
f = open("id_dis_domain", "r")
for line in f.readlines():
line = line.strip("\n")
base_url = "http://www.alexa.com/siteinfo/"
id, display_name, domain = line.split(",")[1], line.split(",")[2], line.split(",")[3]
url = base_url + domain
run_time = 0
alexaGlobalRank, alexaTopCountry = getAlexaInfo(url)
while True:
run_time += 1
if alexaGlobalRank == 0 or alexaTopCountry == "unknown":
alexaGlobalRank, alexaTopCountry = getAlexaInfo(url)
else:
break
if run_time == 3:
break
time.sleep(random.randint(5, 8))
time.sleep(random.randint(5, 8))
alexa_info_tuple = [("2016-05-31", id, display_name, alexaGlobalRank,alexaTopCountry,1)]
if not (alexaGlobalRank == 0 and alexaTopCountry == "unknown"):
insert_SiteBasedAlexa_SQL = """
insert into Website_Alexa_Info
(CreateDate, WebsiteId, DisplayName, Rank, TopOneCountry, IsEndOfMonth)
values (%s, %s, %s, %s, %s, %s)
on duplicate key update
Rank = values(Rank),
TopOneCountry = values(TopOneCountry)
"""
try:
target_mysql.insertUpdateCMD(insert_SiteBasedAlexa_SQL, alexa_info_tuple)
print alexa_info_tuple
target_mysql.commit()
except MySQLdb.Error, e:
logger.debug(e)
sendToMe(subject = "update SiteBasedAlexa Error", body = re.sub(r'\'|"|!', "", str(e)))
continue
else:
logger.info("has no data %s" %alexa_date_max)
except Exception, e:
logger.debug(": load data to SiteBasedAlexa %s" %e)
sendToMe(subject = "SiteBasedAlexa ERROR", body = re.sub(r'\'|"|!', "", str(e)))
sys.exit(0)
def updateSiteBasedAlexa():
sendToMe(subject="update alexa data start", body="update alexa data start")
logger.info(" updateSiteBasedAlexa start")
update_SQL1 = """
update SiteBased as a, SiteBasedAlexa as b
set a.alexaGlobalRank = b.alexaGlobalRank, a.alexaTopCountry = b.alexaTopCountry
where a.trackingWebsite_id = b.trackingWebsite_id
and b.reportDate = (select max(reportDate) from SiteBasedAlexa)
and a.reportDate > (select max(reportDate) from SiteBasedAlexa);
"""
update_SQL2 = """
update SiteBased as a, SiteBasedAlexa as b
set a.alexaGlobalRank = b.alexaGlobalRank, a.alexaTopCountry = b.alexaTopCountry
where a.trackingWebsite_id = b.trackingWebsite_id
and b.reportDate = (select max(reportDate) from SiteBasedAlexa)
and a.alexaTopCountry = "unknown"
and a.alexaGlobalRank = 0;
"""
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=target_db,
port=target_port,
charset='utf8')
target_mysql.queryCMD(update_SQL1)
target_mysql.commit()
target_mysql.queryCMD(update_SQL2)
target_mysql.commit()
except Exception, e:
sendToMe(subject="update alexa data ERROR", body=e)
logger.DEBUG("update alexa data %s" % e)
def getInsightViews(start_date, end_date):
logger.info(" extral data from Dashboard.matchedVideoViewCountCompletion start")
date_dict ={"start_date": start_date, "end_date": end_date}
try:
get_data_sql = """
select matchedVideo_id, trackingWebsite_id, trackingMeta_id,
company_id, report_at, view_count, current_timestamp as ETLDate
from matchedVideoViewCountCompletion
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 matchedVideoViewCountCompletion
(matchedVideo_id, trackingWebsite_id, trackingMeta_id, company_id, report_at, view_count, ETLDate)
values(%s, %s, %s, %s, %s, %s, %s)
"""
target_mysql.executeManyCMD(insert_sql, data)
target_mysql.commit()
except Exception, e:
logger.debug("extral data from Dashboard.matchedVideoViewCountCompletion, %s" %e)
sendToMe(subject = "titleBased_infringAllViews ERROR", body = str(e).replace("\"", "").replace("'", "").replace("!", ""))
sys.exit(0)
def dataTo_matchedVideo():
logger.info("load data target matchedVideo start")
sendToMe(subject = "matchedVideo start", body = "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 = "FOX_DASHBOARD", port = target_port, charset = 'utf8')
sql = """
insert into FOX_DASHBOARD.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)
select
a.id, a.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
from FOX_DASHBOARD.matchedVideoTmp as a, FOX_DASHBOARD.matchedVideoSequence as b
where a.id = b.id and a.company_id = b.company_id
"""
target_mysql.queryNoData("delete from FOX_DASHBOARD.matchedVideo")
target_mysql.queryNoData(sql)
target_mysql.commit()
except Exception, e:
logger.debug("extract data from 192.168.111.235 tracker2 error, %s" %e)
sendToMe(subject = "extract data from 192.168.111.235 tracker2", body = re.sub(r'\'|"|!', "", str(e)))
sys.exit(0)
def updateSiteBasedAlexa():
sendToMe(subject = "update alexa data start", body = "update alexa data start")
logger.info(" updateSiteBasedAlexa start")
update_SQL1 = """
update SiteBased as a, SiteBasedAlexa as b
set a.alexaGlobalRank = b.alexaGlobalRank, a.alexaTopCountry = b.alexaTopCountry
where a.trackingWebsite_id = b.trackingWebsite_id
and b.reportDate = (select max(reportDate) from SiteBasedAlexa)
and a.reportDate > (select max(reportDate) from SiteBasedAlexa);
"""
update_SQL2 = """
update SiteBased as a, SiteBasedAlexa as b
set a.alexaGlobalRank = b.alexaGlobalRank, a.alexaTopCountry = b.alexaTopCountry
where a.trackingWebsite_id = b.trackingWebsite_id
and b.reportDate = (select max(reportDate) from SiteBasedAlexa)
and a.alexaTopCountry = "unknown"
and a.alexaGlobalRank = 0;
"""
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 = target_db, port = target_port, charset = 'utf8')
target_mysql.queryCMD(update_SQL1)
target_mysql.commit()
target_mysql.queryCMD(update_SQL2)
target_mysql.commit()
except Exception, e:
sendToMe(subject = "update alexa data ERROR", body = e)
logger.DEBUG("update alexa data %s" %e)
def getInsightViews(start_date, end_date):
logger.info(" extral data from Dashboard.matchedVideoViewCountCompletion start")
date_dict ={"start_date": start_date, "end_date": end_date}
try:
get_data_sql = """
select matchedVideo_id, trackingWebsite_id, trackingMeta_id,
company_id, report_at, view_count, current_timestamp as ETLDate
from matchedVideoViewCountCompletion
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 matchedVideoViewCountCompletion
(matchedVideo_id, trackingWebsite_id, trackingMeta_id, company_id, report_at, view_count, ETLDate)
values(%s, %s, %s, %s, %s, %s, %s)
"""
target_mysql.executeManyCMD(insert_sql, data)
target_mysql.commit()
except Exception, e:
logger.debug("extral data from Dashboard.matchedVideoViewCountCompletion, %s" %e)
sendToMe(subject = "titleBased_matchedVideoViewCountCompletio ERROR", body = re.sub(r'\'|"|!', "", str(e)))
sys.exit(0)
def updateViews(start_date, end_date):
logger.info(" aggregate data from matchedVideoViewCountCompletion start")
date_dict = {"start_date": start_date, "end_date": end_date}
try:
get_data_sql = """
select
a.reportDate,
a.trackingWebsite_id,
a.websiteName,
a.websiteType,
ifnull(b.mapTitle, a.title) title,
sum(a.infringingViews) as infringingViews,
current_timestamp as ETLDate
from
(select
a.report_at as reportDate,
a.trackingWebsite_id,
c.websiteName,
c.websiteType,
b.title,
sum(a.view_count) as infringingViews
from matchedVideoViewCountCompletion 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
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, ETLDate)
values(%s, %s, %s, %s, %s, %s, %s)
ON DUPLICATE KEY UPDATE
infringingViews = values(infringingViews), ETLDate = values(ETLDate)
"""
target_mysql.executeManyCMD(insert_sql, data)
target_mysql.commit()
except Exception, e:
logger.debug("aggregate data to TitleBased1 ERROR , %s" % e)
sendToMe(subject="titleBased_infringViews ERROR",
body=re.sub(r'\'|"|!', "", str(e)))
sys.exit(0)
def addAlexa(d):
sendToMe(subject = "add alexa data start", body = "add alexa data start")
logger.info(" addSiteBasedAlexa start")
get_alexa_sql = "select '%s', trackingWebsite_id, websiteDomain, alexaGlobalRank, alexaTopCountry, current_timestamp() from SiteBasedAlexa where reportDate = (select max(reportDate) from SiteBasedAlexa);" %d
insert_alexa_sql = "insert into SiteBasedAlexa(reportDate,trackingWebsite_id, websiteDomain, alexaGlobalRank, alexaTopCountry,ETLDate) VALUES(%s,%s,%s,%s,%s,%s);"
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 = target_db, port = target_port, charset = 'utf8')
alexa_result = target_mysql.queryCMD(get_alexa_sql)
target_mysql.insertUpdateCMD(insert_alexa_sql, alexa_result)
target_mysql.commit()
except Exception, e:
sendToMe(subject = "add alexa data ERROR", body = e)
logger.DEBUG("add alexa data %s" %e)
def updateCountry():
sendToMe(subject = "update Country start", body = "update Country table: SiteBased")
logger.info("update Country for table SiteBased start")
update_SiteBased_SQL = """
update SiteBased as a, TitleBasedCountry as b
set a.hostCountry = b.countryName
where a.country_id = b.country_id
"""
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 = target_db, port = target_port, charset = 'utf8')
target_mysql.queryCMD(update_SiteBased_SQL)
target_mysql.commit()
except Exception, e:
sendToMe(subject = "update TrackingWebsite ERROR", body = e)
logger.DEBUG("update TrackingWebsite data %s" %e)
def addAlexa(d):
sendToMe(subject="add alexa data start", body="add alexa data start")
logger.info(" addSiteBasedAlexa start")
get_alexa_sql = "select '%s', trackingWebsite_id, websiteDomain, alexaGlobalRank, alexaTopCountry, current_timestamp() from SiteBasedAlexa where reportDate = (select max(reportDate) from SiteBasedAlexa);" % d
insert_alexa_sql = "insert into SiteBasedAlexa(reportDate,trackingWebsite_id, websiteDomain, alexaGlobalRank, alexaTopCountry,ETLDate) VALUES(%s,%s,%s,%s,%s,%s);"
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=target_db,
port=target_port,
charset='utf8')
alexa_result = target_mysql.queryCMD(get_alexa_sql)
target_mysql.insertUpdateCMD(insert_alexa_sql, alexa_result)
target_mysql.commit()
except Exception, e:
sendToMe(subject="add alexa data ERROR", body=e)
logger.DEBUG("add alexa data %s" % e)
def updateTrackingWebsite():
sendToMe(subject = "update TrackingWebsite start", body = "update TrackingWebsite table: SiteBased TitleBased1 TitleBasedRemoveNum1")
logger.info("update TrackingWebsite start")
update_SiteBased_SQL = """
update SiteBased as a, TitleBasedTrackingWebsite as b
set a.websiteName = b.websiteName, a.websiteDomain = b.websiteDomain, a.websiteType = b.websiteType
where a.trackingWebsite_id = b.trackingWebsite_id
"""
update_TitleBased1_SQL = """
update TitleBased1 as a, TitleBasedTrackingWebsite as b
set a.websiteName = b.websiteName, a.websiteType = b.websiteType
where a.trackingWebsite_id = b.trackingWebsite_id
"""
update_TitleBasedRemoveNum1_SQL = """
update TitleBasedRemoveNum1 as a, TitleBasedTrackingWebsite as b
set a.websiteName = b.websiteName, a.websiteType = b.websiteType
where a.trackingWebsite_id = b.trackingWebsite_id
"""
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 = target_db, port = target_port, charset = 'utf8')
target_mysql.queryCMD(update_SiteBased_SQL)
target_mysql.commit()
target_mysql.queryCMD(update_TitleBased1_SQL)
target_mysql.commit()
target_mysql.queryCMD(update_TitleBasedRemoveNum1_SQL)
target_mysql.commit()
except Exception, e:
sendToMe(subject = "update TrackingWebsite ERROR", body = e)
logger.DEBUG("update TrackingWebsite data %s" %e)
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 getMatchedVideo(start_date, end_date):
logger.info(" extral data from tracker2.matchedVideo start")
date_dict ={"start_date": start_date, "end_date": end_date}
try:
get_data_sql = """
select
a.id as matchedVideo_id, trackingMeta_id, trackingWebsite_id,
date_format(first_send_notice_date, "%%Y-%%m-%%d") as firstSendNoticeDate,
date_format(a.created_at, "%%Y-%%m-%%d") as created_at,
CURRENT_TIMESTAMP as ETLDate
from matchedVideo as a, tracker2.metaExtraInfo as b , mddb.trackingWebsite as c
where a.company_id =14
and b.company_id = 14
and c.website_type = "ugc"
and a.trackingMeta_id = b.meta_id
and a.trackingWebsite_id = c.id
and a.hide_flag = 2
and a.created_at >= "2015-03-01"
and count_send_notice > 0
and date_format(a.first_send_notice_date, "%%Y-%%m-%%d") > "%(start_date)s"
and date_format(a.first_send_notice_date, "%%Y-%%m-%%d") <= "%(end_date)s"
""" %date_dict
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 = '-8:00'")
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)
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 updateCountry():
sendToMe(subject="update Country start",
body="update Country table: SiteBased")
logger.info("update Country for table SiteBased start")
update_SiteBased_SQL = """
update SiteBased as a, TitleBasedCountry as b
set a.hostCountry = b.countryName
where a.country_id = b.country_id
"""
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=target_db,
port=target_port,
charset='utf8')
target_mysql.queryCMD(update_SiteBased_SQL)
target_mysql.commit()
except Exception, e:
sendToMe(subject="update TrackingWebsite ERROR", body=e)
logger.DEBUG("update TrackingWebsite data %s" % e)
def updateTrackingWebsite():
sendToMe(
subject="update TrackingWebsite start",
body=
"update TrackingWebsite table: SiteBased TitleBased1 TitleBasedRemoveNum1"
)
logger.info("update TrackingWebsite start")
update_SiteBased_SQL = """
update SiteBased as a, TitleBasedTrackingWebsite as b
set a.websiteName = b.websiteName, a.websiteDomain = b.websiteDomain, a.websiteType = b.websiteType
where a.trackingWebsite_id = b.trackingWebsite_id
"""
update_TitleBased1_SQL = """
update TitleBased1 as a, TitleBasedTrackingWebsite as b
set a.websiteName = b.websiteName, a.websiteType = b.websiteType
where a.trackingWebsite_id = b.trackingWebsite_id
"""
update_TitleBasedRemoveNum1_SQL = """
update TitleBasedRemoveNum1 as a, TitleBasedTrackingWebsite as b
set a.websiteName = b.websiteName, a.websiteType = b.websiteType
where a.trackingWebsite_id = b.trackingWebsite_id
"""
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=target_db,
port=target_port,
charset='utf8')
target_mysql.queryCMD(update_SiteBased_SQL)
target_mysql.commit()
target_mysql.queryCMD(update_TitleBased1_SQL)
target_mysql.commit()
target_mysql.queryCMD(update_TitleBasedRemoveNum1_SQL)
target_mysql.commit()
except Exception, e:
sendToMe(subject="update TrackingWebsite ERROR", body=e)
logger.DEBUG("update TrackingWebsite data %s" % e)
def getMatchedVideo(min_reportDate, max_reportDate, min_updateDate):
logger.info(" extral data from tracker2.matchedVideo start")
date_dict ={"min_reportDate": min_reportDate, "max_reportDate": max_reportDate, "min_updateDate": min_updateDate}
try:
get_data_sql = """
select
a.id as matchedVideo_id,
a.trackingMeta_id,
a.trackingWebsite_id,
date_format(a.first_send_notice_date, "%%Y-%%m-%%d") as firstSendNoticeDate,
a.hide_flag as hideFlag,
count_send_notice as countSendNotice,
date_format(a.created_at, "%%Y-%%m-%%d") as reportDate,
date_format(a.updated_at, "%%Y-%%m-%%d") as updateDate,
CURRENT_TIMESTAMP as ETLDate
from matchedVideo as a, mddb.trackingWebsite as b
where date_format(a.created_at, "%%Y-%%m-%%d") > "%(min_reportDate)s"
and date_format(a.created_at, "%%Y-%%m-%%d") <= "%(max_reportDate)s"
and date_format(a.updated_at, "%%Y-%%m-%%d") <= "%(max_reportDate)s"
and a.company_id =14
and b.website_type = "ugc"
and a.created_at >= "2015-03-01"
union all
select
a.id as matchedVideo_id,
a.trackingMeta_id,
a.trackingWebsite_id,
date_format(a.first_send_notice_date, "%%Y-%%m-%%d") as firstSendNoticeDate,
a.hide_flag as hideFlag,
count_send_notice as countSendNotice,
date_format(a.created_at, "%%Y-%%m-%%d") as reportDate,
date_format(a.updated_at, "%%Y-%%m-%%d") as updateDate,
CURRENT_TIMESTAMP as ETLDate
from matchedVideo as a, mddb.trackingWebsite as b
where date_format(a.created_at, "%%Y-%%m-%%d") <= "%(min_reportDate)s"
and date_format(a.updated_at, "%%Y-%%m-%%d") > "%(min_updateDate)s"
and date_format(a.updated_at, "%%Y-%%m-%%d") <= "%(max_reportDate)s"
and a.company_id =14
and b.website_type = "ugc"
and a.created_at >= "2015-03-01"
""" %date_dict
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 = '-8:00'")
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, hideFlag,
countSendNotice, reportDate, updateDate, ETLDate)
values(%s, %s, %s, %s, %s, %s, %s, %s, %s)
ON DUPLICATE KEY UPDATE firstSendNoticeDate = values(firstSendNoticeDate), hideFlag = values(hideFlag),
countSendNotice = values(countSendNotice), reportDate = values(reportDate),
updateDate = values(updateDate), ETLDate =values(ETLDate)
"""
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 = re.sub(r'\'|"|!', "", str(e)))
sys.exit(0)
def main():
cfg_file = "/Job/FOX/Dashboard/TitleBased/conf/viacom_dashboard.cfg"
if not os.path.exists(cfg_file):
logging.debug(": config file not exists; file_name %s" % cfg_file)
sendToMe(subject="SiteBased_alexa ERROR",
body="config file not exists")
sys.exit(0)
logger.info(": extract data from siteBased start")
socket.setdefaulttimeout(10.0)
sendToMe(subject="SiteBased_alexa start",
body="extract data from siteBased start")
target_server_section = "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')
if True:
f = open("id_dis_domain", "r")
for line in f.readlines():
line = line.strip("\n")
base_url = "http://www.alexa.com/siteinfo/"
id, display_name, domain = line.split(",")[1], line.split(
",")[2], line.split(",")[3]
url = base_url + domain
run_time = 0
alexaGlobalRank, alexaTopCountry = getAlexaInfo(url)
while True:
run_time += 1
if alexaGlobalRank == 0 or alexaTopCountry == "unknown":
alexaGlobalRank, alexaTopCountry = getAlexaInfo(url)
else:
break
if run_time == 3:
break
time.sleep(random.randint(5, 8))
time.sleep(random.randint(5, 8))
alexa_info_tuple = [("2016-05-31", id, display_name,
alexaGlobalRank, alexaTopCountry, 1)]
if not (alexaGlobalRank == 0 and alexaTopCountry == "unknown"):
insert_SiteBasedAlexa_SQL = """
insert into Website_Alexa_Info
(CreateDate, WebsiteId, DisplayName, Rank, TopOneCountry, IsEndOfMonth)
values (%s, %s, %s, %s, %s, %s)
on duplicate key update
Rank = values(Rank),
TopOneCountry = values(TopOneCountry)
"""
try:
target_mysql.insertUpdateCMD(insert_SiteBasedAlexa_SQL,
alexa_info_tuple)
print alexa_info_tuple
target_mysql.commit()
except MySQLdb.Error, e:
logger.debug(e)
sendToMe(subject="update SiteBasedAlexa Error",
body=re.sub(r'\'|"|!', "", str(e)))
continue
else:
logger.info("has no data %s" % alexa_date_max)
except Exception, e:
logger.debug(": load data to SiteBasedAlexa %s" % e)
sendToMe(subject="SiteBasedAlexa ERROR",
body=re.sub(r'\'|"|!', "", str(e)))
sys.exit(0)
def main():
cfg_file = "/Job/VIACOM/Dashboard/TitleBased/conf/viacom_dashboard.cfg"
if not os.path.exists(cfg_file):
logging.debug(": config file not exists; file_name %s" %cfg_file)
sendToMe(subject = "SiteBased_alexa ERROR", body = "config file not exists")
sys.exit(0)
logger.info(": extract data from siteBased start")
socket.setdefaulttimeout(10.0)
sendToMe(subject = "SiteBased_alexa start", body = "extract data from siteBased 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')
alexa_date_min = getMinDatePara("SiteBasedAlexa", "reportDate")
alexa_date_min = '2016-04-12'
if alexa_date_min == None:
alexa_date_min = time.strftime("%Y-%m-%d", time.localtime(time.time() - 10 * 24 * 60 * 60))
alexa_date_max = time.strftime("%Y-%m-%d", time.localtime(time.time() - 1 * 24 * 60 * 60))
delt = time.mktime(time.strptime(str(alexa_date_max), "%Y-%m-%d")) - time.mktime(time.strptime(str(alexa_date_min), "%Y-%m-%d"))
if delt >= 10*24*60*60:
alexa_date_dict = {"alexa_date_min": alexa_date_min, "alexa_date_max": alexa_date_max}
site_SQL = """
select distinct trackingWebsite_id, websiteDomain from SiteBased
where reportDate <= "%(alexa_date_max)s"
and reportDate > "%(alexa_date_min)s"
and alexaGlobalRank = 0
and alexaTopCountry = "unknown"
""" %alexa_date_dict
site_info = target_mysql.queryCMD(site_SQL)
if site_info:
base_url = "http://www.alexa.com/siteinfo/"
for site in site_info:
domain = site[1]
url = base_url + domain
run_time = 0
alexaGlobalRank, alexaTopCountry = getAlexaInfo(url)
while True:
run_time += 1
if alexaGlobalRank == 0 or alexaTopCountry == "unknown":
alexaGlobalRank, alexaTopCountry = getAlexaInfo(url)
else:
break
if run_time == 3:
break
time.sleep(random.randint(5, 8))
time.sleep(random.randint(5, 8))
alexa_info_tuple = [(alexa_date_max, site[0], site[1], alexaGlobalRank, alexaTopCountry, time.strftime("%Y-%m-%d %H:%M:%S"))]
if not (alexaGlobalRank == 0 and alexaTopCountry == "unknown"):
insert_SiteBasedAlexa_SQL = """
insert into SiteBasedAlexa
(reportDate, trackingWebsite_id, websiteDomain, alexaGlobalRank, alexaTopCountry, ETLDate)
values (%s, %s, %s, %s, %s, %s)
on duplicate key update
alexaGlobalRank = values(alexaGlobalRank),
alexaTopCountry = values(alexaTopCountry), ETLDate = values(ETLDate)
"""
try:
target_mysql.insertUpdateCMD(insert_SiteBasedAlexa_SQL, alexa_info_tuple)
print alexa_info_tuple
target_mysql.commit()
except MySQLdb.Error, e:
logger.debug(e)
sendToMe(subject = "update SiteBasedAlexa Error", body = re.sub(r'\'|"|!', "", str(e)))
continue
else:
logger.info("has no data %s" %alexa_date_max)
except Exception, e:
logger.debug(": load data to SiteBasedAlexa %s" %e)
sendToMe(subject = "SiteBasedAlexa ERROR", body = re.sub(r'\'|"|!', "", str(e)))
sys.exit(0)
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()
except Exception, e:
logger.debug(": load data to TitleBasedRemoveNum, %s" %e)
sys.exit(0)
finally:
target_mysql.closeCur()
target_mysql.closeConn()
logger.info(":load data to TitleBasedRemoveNum end")
#################################################################################################################################
logger.info(":extract data from TitleBasedRemoveNum start")
target_server_section = "target_server_staging"
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,
def main():
cfg_file = "/Job/FOX/Dashboard/TitleBased/conf/viacom_dashboard.cfg"
if not os.path.exists(cfg_file):
logging.debug(": config file not exists; file_name %s" % cfg_file)
sendToMe(subject="SiteBased_alexa ERROR",
body="config file not exists")
sys.exit(0)
logger.info(": extract data from siteBased start")
socket.setdefaulttimeout(10.0)
sendToMe(subject="SiteBased_alexa start",
body="extract data from siteBased 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')
alexa_date_min = getMinDatePara("SiteBasedAlexa", "reportDate")
alexa_date_min = '2016-04-12'
if alexa_date_min == None:
alexa_date_min = time.strftime(
"%Y-%m-%d", time.localtime(time.time() - 10 * 24 * 60 * 60))
alexa_date_max = time.strftime(
"%Y-%m-%d", time.localtime(time.time() - 1 * 24 * 60 * 60))
delt = time.mktime(time.strptime(
str(alexa_date_max), "%Y-%m-%d")) - time.mktime(
time.strptime(str(alexa_date_min), "%Y-%m-%d"))
if delt >= 10 * 24 * 60 * 60:
alexa_date_dict = {
"alexa_date_min": alexa_date_min,
"alexa_date_max": alexa_date_max
}
site_SQL = """
select distinct trackingWebsite_id, websiteDomain from SiteBased
where reportDate <= "%(alexa_date_max)s"
and reportDate > "%(alexa_date_min)s"
and alexaGlobalRank = 0
and alexaTopCountry = "unknown"
""" % alexa_date_dict
site_info = target_mysql.queryCMD(site_SQL)
if site_info:
base_url = "http://www.alexa.com/siteinfo/"
for site in site_info:
domain = site[1]
url = base_url + domain
run_time = 0
alexaGlobalRank, alexaTopCountry = getAlexaInfo(url)
while True:
run_time += 1
if alexaGlobalRank == 0 or alexaTopCountry == "unknown":
alexaGlobalRank, alexaTopCountry = getAlexaInfo(
url)
else:
break
if run_time == 3:
break
time.sleep(random.randint(5, 8))
time.sleep(random.randint(5, 8))
alexa_info_tuple = [(alexa_date_max, site[0], site[1],
alexaGlobalRank, alexaTopCountry,
time.strftime("%Y-%m-%d %H:%M:%S"))]
if not (alexaGlobalRank == 0
and alexaTopCountry == "unknown"):
insert_SiteBasedAlexa_SQL = """
insert into SiteBasedAlexa
(reportDate, trackingWebsite_id, websiteDomain, alexaGlobalRank, alexaTopCountry, ETLDate)
values (%s, %s, %s, %s, %s, %s)
on duplicate key update
alexaGlobalRank = values(alexaGlobalRank),
alexaTopCountry = values(alexaTopCountry), ETLDate = values(ETLDate)
"""
try:
target_mysql.insertUpdateCMD(
insert_SiteBasedAlexa_SQL, alexa_info_tuple)
print alexa_info_tuple
target_mysql.commit()
except MySQLdb.Error, e:
logger.debug(e)
sendToMe(subject="update SiteBasedAlexa Error",
body=re.sub(r'\'|"|!', "", str(e)))
continue
else:
logger.info("has no data %s" % alexa_date_max)
except Exception, e:
logger.debug(": load data to SiteBasedAlexa %s" % e)
sendToMe(subject="SiteBasedAlexa ERROR",
body=re.sub(r'\'|"|!', "", str(e)))
sys.exit(0)
def getMatchedVideo(min_reportDate, max_reportDate, min_updateDate):
logger.info(" extral data from tracker2.matchedVideo start")
date_dict = {
"min_reportDate": min_reportDate,
"max_reportDate": max_reportDate,
"min_updateDate": min_updateDate
}
try:
get_data_sql = """
select
a.id as matchedVideo_id,
a.trackingMeta_id,
a.trackingWebsite_id,
date_format(a.first_send_notice_date, "%%Y-%%m-%%d") as firstSendNoticeDate,
a.hide_flag as hideFlag,
count_send_notice as countSendNotice,
date_format(a.created_at, "%%Y-%%m-%%d") as reportDate,
date_format(a.updated_at, "%%Y-%%m-%%d") as updateDate,
CURRENT_TIMESTAMP as ETLDate
from matchedVideo as a, mddb.trackingWebsite as b
where date_format(a.created_at, "%%Y-%%m-%%d") > "%(min_reportDate)s"
and date_format(a.created_at, "%%Y-%%m-%%d") <= "%(max_reportDate)s"
and date_format(a.updated_at, "%%Y-%%m-%%d") <= "%(max_reportDate)s"
and a.company_id =14
and b.website_type = "ugc"
and a.created_at >= "2015-03-01"
and a.trackingWebsite_id = b.id
union all
select
a.id as matchedVideo_id,
a.trackingMeta_id,
a.trackingWebsite_id,
date_format(a.first_send_notice_date, "%%Y-%%m-%%d") as firstSendNoticeDate,
a.hide_flag as hideFlag,
count_send_notice as countSendNotice,
date_format(a.created_at, "%%Y-%%m-%%d") as reportDate,
date_format(a.updated_at, "%%Y-%%m-%%d") as updateDate,
CURRENT_TIMESTAMP as ETLDate
from matchedVideo as a, mddb.trackingWebsite as b
where date_format(a.created_at, "%%Y-%%m-%%d") <= "%(min_reportDate)s"
and date_format(a.updated_at, "%%Y-%%m-%%d") > "%(min_updateDate)s"
and date_format(a.updated_at, "%%Y-%%m-%%d") <= "%(max_reportDate)s"
and a.company_id =14
and b.website_type = "ugc"
and a.created_at >= "2015-03-01"
and a.trackingWebsite_id = b.id
""" % date_dict
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 = '-8:00'")
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, hideFlag,
countSendNotice, reportDate, updateDate, ETLDate)
values(%s, %s, %s, %s, %s, %s, %s, %s, %s)
ON DUPLICATE KEY UPDATE firstSendNoticeDate = values(firstSendNoticeDate), hideFlag = values(hideFlag),
countSendNotice = values(countSendNotice), reportDate = values(reportDate),
updateDate = values(updateDate), ETLDate =values(ETLDate)
"""
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=re.sub(r'\'|"|!', "", str(e)))
sys.exit(0)
