def main():
tracker2 = MySQLHelper(tracker2_host, tracker2_user, tracker2_pass,
tracker2_db)
countid = 0
countid1 = 1000000
while countid < 123000000:
select = """SELECT b.id, c.key_id FROM tracker2.matchedVideoP2PItem AS b, tracker2.matchedVideo AS c WHERE b.company_id = 14 AND c.company_id = 14 AND b.matchedVideo_id = c.id and b.id > %s and b.id <= %s""" % (
countid, countid1)
countid += 1000000
countid1 += 1000000
print select
select_result = tracker2.query_sql_cmd(select)
print "fetched!"
retrytracker2 = 0
while retrytracker2 < 10:
try:
print "retrytracker2:"
print retrytracker2
target = MySQLHelper(t_host, t_user, t_pass, t_db)
print "start insert!"
print datetime.datetime.now()
break
except Exception, e:
print e
retrytracker2 += 1
#target = MySQLHelper(t_host,t_user,t_pass,t_db)
insert_result = "insert into infback_Tmp " + " values " + str(
select_result)[1:-1].replace("L", "").replace("u", "")
target.insert_sql_cmd(insert_result)
print "finished!"
print datetime.datetime.now()
retryTime_ware = 0
while retryTime_ware < 10:
try:
print "retryTime_ware:"
print retryTime_ware
p2pwarehouse = MySQLHelper(p2pwarehouse_host, p2pwarehouse_user, p2pwarehouse_pass, p2pwarehouse_db)
break
except Exception,e:
print e
retryTime_ware += 1
#sql_set_time_zone = """set time_zone = '-8:00';"""
#db1.query_sql_cmd(sql_set_time_zone)
#print sql_set_time_zone
allP2P_Tmp_select = """SELECT a.trackingMeta_id AS meta_id, a.isp_id, a.protocol_hash, count(a.peer_ip_address) AS allIPs FROM infringmentSummary20150304 AS a WHERE a.created_at >= DATE_SUB('%s 08:00:00', INTERVAL +1 DAY) AND a.created_at < DATE_SUB('%s 08:00:00', INTERVAL 0 DAY) GROUP BY a.trackingMeta_id, a.isp_id, a.protocol_hash;""" %(ETL_DTE,ETL_DTE)
print allP2P_Tmp_select
allP2P_result = p2pwarehouse.query_sql_cmd(allP2P_Tmp_select)
print "all fetched"
#print len(Res)
allP2P_Tmp_insert = "insert into allP2P_Tmp_150304 " + " values " + str(allP2P_result)[1:-1].replace("L", "").replace("u","")
retryVIADASHBOARD = 0
while retryVIADASHBOARD < 10:
try:
print "retryVIADASHBOARD:"
print retryVIADASHBOARD
VIACOM_DASHBOARD = MySQLHelper(DASHBOARD_host, DASHBOARD_user, DASHBOARD_pass, DASHBOARD_db)
break
except Exception,e:
print e
retryVIADASHBOARD += 1
try:
print "retryTime_ware:"
print retryTime_ware
p2pwarehouse = MySQLHelper(p2pwarehouse_host, p2pwarehouse_user,
p2pwarehouse_pass, p2pwarehouse_db)
break
except Exception, e:
print e
retryTime_ware += 1
# sql_set_time_zone = """set time_zone = '-8:00';"""
# db1.query_sql_cmd(sql_set_time_zone)
# print sql_set_time_zone
allP2P_Tmp_select = """SELECT a.trackingMeta_id AS meta_id, a.isp_id, a.protocol_hash, count(a.peer_ip_address) AS allIPs FROM p2pArchViacom.infringmentSummary20150304 AS a WHERE a.created_at >= DATE_SUB('%s', INTERVAL +1 DAY) AND a.created_at < DATE_SUB('%s', INTERVAL 0 DAY) GROUP BY a.trackingMeta_id, a.isp_id, a.protocol_hash;""" % (
ETL_DTE, ETL_DTE)
print allP2P_Tmp_select
allP2P_result = p2pwarehouse.query_sql_cmd(allP2P_Tmp_select)
print "all fetched"
#print len(Res)
allP2P_Tmp_insert = "insert into allP2P_Tmp " + " values " + str(
allP2P_result)[1:-1].replace("L", "").replace("u", "")
VIACOM_DASHBOARD.insert_sql_cmd(allP2P_Tmp_insert)
print datetime.datetime.now()
retryTime_afterdashboard = 0
while retryTime_afterdashboard < 10:
try:
print "retryTime_afterdashboard:"
print retryTime_afterdashboard
after_DASHBOARD = MySQLdb.connect(DASHBOARD_host, DASHBOARD_user,
DASHBOARD_pass, DASHBOARD_db)
cursor = after_DASHBOARD.cursor()
while retrytracker2 < 10:
try:
print "retrytracker2:"
print retrytracker2
tracker2 = MySQLHelper(tracker2_host, tracker2_user, tracker2_pass,
tracker2_db)
break
except Exception, e:
print e
retrytracker2 += 1
#tracker2 = MySQLHelper(tracker2_host,tracker2_user,tracker2_pass,tracker2_db)
print datetime.datetime.now()
infP2P_Tmp_select = """SELECT a.meta_id, a.isp_id, c.key_id AS protocol_hash, count(a.IP) AS infringingIPs FROM tracker2.takedownNoticeItemP2PDetail AS a, tracker2.matchedVideoP2PItem AS b, tracker2.matchedVideo AS c WHERE a.company_id = 14 AND b.company_id = 14 AND c.company_id = 14 AND a.matchedVideoP2PItem_id = b.id AND b.matchedVideo_id = c.id AND a.first_notice_send_time >= DATE_SUB('%s 08:00:00', INTERVAL +1 DAY) AND a.first_notice_send_time < DATE_SUB('%s 08:00:00', INTERVAL 0 DAY) GROUP BY a.meta_id, a.isp_id, c.key_id""" % (
ETL_DTE, ETL_DTE)
print infP2P_Tmp_select
infP2P_result = tracker2.query_sql_cmd(infP2P_Tmp_select)
print "inf fetched"
retryVIADASHBOARD = 0
while retryVIADASHBOARD < 10:
try:
print "retryVIADASHBOARD:"
print retryVIADASHBOARD
VIACOM_DASHBOARD = MySQLHelper(DASHBOARD_host, DASHBOARD_user,
DASHBOARD_pass, DASHBOARD_db)
break
except Exception, e:
print e
retryVIADASHBOARD += 1
infP2P_Tmp_insert = "insert into infringingP2P_Tmp " + " values " + str(
infP2P_result)[1:-1].replace("L", "").replace("u", "")
VIACOM_DASHBOARD.insert_sql_cmd(infP2P_Tmp_insert)
print "retrytracker2:"
print retrytracker2
tracker2 = MySQLHelper(tracker2_host, tracker2_user, tracker2_pass,
tracker2_db)
break
except Exception, e:
print e
retrytracker2 += 1
#tracker2 = MySQLHelper(tracker2_host,tracker2_user,tracker2_pass,tracker2_db)
print datetime.datetime.now()
infP2P_Tmp_select = """SELECT a.meta_id, a.isp_id, c.key_id AS protocol_hash, count(a.IP) AS infringingIPs FROM tracker2.takedownNoticeItemP2PDetail AS a, tracker2.matchedVideoP2PItem AS b, tracker2.matchedVideo AS c WHERE a.company_id = 14 AND b.company_id = 14 AND c.company_id = 14 AND a.matchedVideoP2PItem_id = b.id AND b.matchedVideo_id = c.id AND a.first_notice_send_time >= DATE_SUB('%s 08:00:00', INTERVAL +1 DAY) AND a.first_notice_send_time < DATE_SUB('%s 08:00:00', INTERVAL 0 DAY) GROUP BY a.meta_id, a.isp_id, c.key_id""" % (
ETL_DTE, ETL_DTE)
print infP2P_Tmp_select
set_NamesUTF8 = """SET NAMES UTF8"""
print set_NamesUTF8
infP2P_result = tracker2.query_sql_cmd(infP2P_Tmp_select)
print "inf fetched"
retryVIADASHBOARD = 0
while retryVIADASHBOARD < 10:
try:
print "retryVIADASHBOARD:"
print retryVIADASHBOARD
VIACOM_DASHBOARD = MySQLHelper(DASHBOARD_host, DASHBOARD_user,
DASHBOARD_pass, DASHBOARD_db)
break
except Exception, e:
print e
retryVIADASHBOARD += 1
infP2P_Tmp_insert = "insert into infringingP2P_Tmp " + " values " + str(
infP2P_result)[1:-1].replace("L", "").replace("u", "")
VIACOM_DASHBOARD.insert_sql_cmd(infP2P_Tmp_insert)
while retrytracker2 < 10:
try:
print "retrytracker2:"
print retrytracker2
tracker2 = MySQLHelper(tracker2_host,tracker2_user,tracker2_pass,tracker2_db)
break
except Exception,e:
print e
retrytracker2 += 1
#tracker2 = MySQLHelper(tracker2_host,tracker2_user,tracker2_pass,tracker2_db)
print datetime.datetime.now()
infP2P_Tmp_select = """SELECT a.meta_id, a.isp_id, c.key_id AS protocol_hash, count(a.IP) AS infringingIPs FROM tracker2.takedownNoticeItemP2PDetail AS a, tracker2.matchedVideoP2PItem AS b, tracker2.matchedVideo AS c WHERE a.company_id = 14 AND b.company_id = 14 AND c.company_id = 14 AND a.matchedVideoP2PItem_id = b.id AND b.matchedVideo_id = c.id AND a.first_notice_send_time >= DATE_SUB('%s 08:00:00', INTERVAL +1 DAY) AND a.first_notice_send_time < DATE_SUB('%s 08:00:00', INTERVAL 0 DAY) GROUP BY a.meta_id, a.isp_id, c.key_id""" %(ETL_DTE,ETL_DTE)
print infP2P_Tmp_select
set_NamesUTF8 = """SET NAMES UTF8"""
print set_NamesUTF8
infP2P_result = tracker2.query_sql_cmd(infP2P_Tmp_select)
print "inf fetched"
retryVIADASHBOARD = 0
while retryVIADASHBOARD < 10:
try:
print "retryVIADASHBOARD:"
print retryVIADASHBOARD
VIACOM_DASHBOARD = MySQLHelper(DASHBOARD_host, DASHBOARD_user, DASHBOARD_pass, DASHBOARD_db)
break
except Exception,e:
print e
retryVIADASHBOARD += 1
infP2P_Tmp_insert = "insert into infringingP2P_Tmp " + " values " + str(infP2P_result)[1:-1].replace("L", "").replace("u","")
VIACOM_DASHBOARD.insert_sql_cmd(infP2P_Tmp_insert)
print datetime.datetime.now()
#fetch the VTWeb infringing IPs and hashes end
retrytracker2 = 0
while retrytracker2 < 10:
try:
print "retrytracker2:"
print retrytracker2
tracker2 = MySQLHelper(tracker2_host,tracker2_user,tracker2_pass,tracker2_db)
break
except Exception,e:
print e
retrytracker2 += 1
#tracker2 = MySQLHelper(tracker2_host,tracker2_user,tracker2_pass,tracker2_db)
print datetime.datetime.now()
infP2P_Tmp_select = """SELECT a.meta_id, a.isp_id, c.key_id AS protocol_hash, count(a.IP) AS infringingIPs FROM tracker2.takedownNoticeItemP2PDetail AS a, tracker2.matchedVideoP2PItem AS b, tracker2.matchedVideo AS c WHERE a.company_id = 14 AND b.company_id = 14 AND c.company_id = 14 AND a.matchedVideoP2PItem_id = b.id AND b.matchedVideo_id = c.id AND a.first_notice_send_time >= DATE_SUB('%s 08:00:00', INTERVAL +1 DAY) AND a.first_notice_send_time < DATE_SUB('%s 08:00:00', INTERVAL 0 DAY) GROUP BY a.meta_id, a.isp_id, c.key_id""" %(ETL_DTE,ETL_DTE)
print infP2P_Tmp_select
infP2P_result = tracker2.query_sql_cmd(infP2P_Tmp_select)
print "inf fetched"
retryVIADASHBOARD = 0
while retryVIADASHBOARD < 10:
try:
print "retryVIADASHBOARD:"
print retryVIADASHBOARD
VIACOM_DASHBOARD = MySQLHelper(DASHBOARD_host, DASHBOARD_user, DASHBOARD_pass, DASHBOARD_db)
break
except Exception,e:
print e
retryVIADASHBOARD += 1
infP2P_Tmp_insert = "insert into infringingP2P_Tmp " + " values " + str(infP2P_result)[1:-1].replace("L", "").replace("u","")
VIACOM_DASHBOARD.insert_sql_cmd(infP2P_Tmp_insert)
print datetime.datetime.now()
