Python MySQLHelper.queryNoData方法代码示例

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

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

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

# 需要导入模块: from mysqlHelp import MySQLHelper [as 别名]
# 或者: from mysqlHelp.MySQLHelper import queryNoData [as 别名]
        vtweb_mysql.closeConn()
        logger.info(": extract data from tracker2 reportDate  end") 

    # put data to TitleBased
    logger.info(":load data to TitleBasedTmp reportDate 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 = target_db, port = target_port, charset = 'utf8')

        target_TitleBased_SQL = """
         insert into TitleBasedTmp (reportDate, trackingWebsite_id, trackingMeta_id, tier, matchedNum, matchedNumDurationNoZero,
            infringingNum, infringingNumDurationNoZero, clipDurationSum, clipDurationInfringingSum, ETLDate)
         values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        target_mysql.queryNoData("delete from TitleBasedTmp")
        commitInTurn(commit_num = 50000, data = result_report, executeFun = target_mysql.executeManyCMD, \
          commitFun = target_mysql.commit, executeSQL  =target_TitleBased_SQL)
    except Exception, e:
        logger.debug(": mysql data to TitleBasedTmp,  %s" %e)
        sendToMe(subject = "TitleBased ERROR", body = re.sub(r'\'|"|!', "", str(e)))
        sys.exit(0)
    finally:
        target_mysql.closeCur()
        target_mysql.closeConn()
        logger.info(": data to TitleBasedTmp reportDate end")
    sendToMe(subject = "TitleBasedTmp end", body = "data to TitleBasedTmp end")
#################################################################################################################################

# 需要导入模块: from mysqlHelp import MySQLHelper [as 别名]
# 或者: from mysqlHelp.MySQLHelper import queryNoData [as 别名]
		        sum(clipDurationInfringingSum) as clipDurationInfringingSum,
		        sum(reportedViews) as reportedViews,
		        sum(infringingViews) as infringingViews,
		        sum(reportedViewsCMS) as reportedViewsCMS,
		        CURRENT_TIMESTAMP as ETLDate
		    from TitleBased1 as a, TitleBasedTrackingWebsite as b, TitleBasedCountry as c
    		    where a.trackingWebsite_id = b.trackingWebsite_id
      		      and b.country_id = c.country_id
      		      and(a.websiteType = "ugc" or a.websiteType = 'cyberlocker' or a.websiteType = 'hybrid')
		      group by 1, 2, 3, 4, 5, 6, 7, 8) as a
		      left join SiteMap as b on a.trackingWebsite_id = b.trackingWebsite_id
		    group by 1, 2, 3, 4, 5, 6, 7, 8
	"""
	aggregate_TitleBased1_result = target_mysql.queryCMD(aggregate_TitleBased1_SQL)
	
	target_mysql.queryNoData("delete from SiteBased")
	
	insert_SiteBased_SQL = """
		INSERT INTO SiteBased
			(reportDate, trackingWebsite_id, websiteName, websiteType, 
			websiteDomain, country_id, hostCountry, title, matchedNum, 
			matchedNumDurationNoZero, infringingNum, infringingNumDurationNoZero,
			infringingNumCMS, clipDurationSum, clipDurationInfringingSum,
			reportedViews, infringingViews, reportedViewsCMS, ETLDate) 
  		VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) 
  		ON DUPLICATE KEY UPDATE 
  			websiteName = VALUES(websiteName), websiteDomain = VALUES(websiteDomain), 
  			country_id = VALUES(country_id), hostCountry = VALUES(hostCountry), 
  			matchedNum = VALUES(matchedNum), matchedNumDurationNoZero = values(matchedNumDurationNoZero), 
  			infringingNum = VALUES(infringingNum), infringingNumDurationNoZero = values(infringingNumDurationNoZero),
  			infringingNumCMS = VALUES(infringingNumCMS),