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 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 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 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)
