Python MySQLHelper.commit方法代码示例

# 需要导入模块: from mysqlHelp import MySQLHelper [as 别名]
# 或者: from mysqlHelp.MySQLHelper import commit [as 别名]
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)

# 需要导入模块: from mysqlHelp import MySQLHelper [as 别名]
# 或者: from mysqlHelp.MySQLHelper import commit [as 别名]
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)

# 需要导入模块: from mysqlHelp import MySQLHelper [as 别名]
# 或者: from mysqlHelp.MySQLHelper import commit [as 别名]
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)

# 需要导入模块: from mysqlHelp import MySQLHelper [as 别名]
# 或者: from mysqlHelp.MySQLHelper import commit [as 别名]
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)

# 需要导入模块: from mysqlHelp import MySQLHelper [as 别名]
# 或者: from mysqlHelp.MySQLHelper import commit [as 别名]
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)

# 需要导入模块: from mysqlHelp import MySQLHelper [as 别名]
# 或者: from mysqlHelp.MySQLHelper import commit [as 别名]
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)

# 需要导入模块: from mysqlHelp import MySQLHelper [as 别名]
# 或者: from mysqlHelp.MySQLHelper import commit [as 别名]
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)

# 需要导入模块: from mysqlHelp import MySQLHelper [as 别名]
# 或者: from mysqlHelp.MySQLHelper import commit [as 别名]
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)

# 需要导入模块: from mysqlHelp import MySQLHelper [as 别名]
# 或者: from mysqlHelp.MySQLHelper import commit [as 别名]
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")

# 需要导入模块: from mysqlHelp import MySQLHelper [as 别名]
# 或者: from mysqlHelp.MySQLHelper import commit [as 别名]
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)

# 需要导入模块: from mysqlHelp import MySQLHelper [as 别名]
# 或者: from mysqlHelp.MySQLHelper import commit [as 别名]
	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,

# 需要导入模块: from mysqlHelp import MySQLHelper [as 别名]
# 或者: from mysqlHelp.MySQLHelper import commit [as 别名]
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)