Python MySQLHelper.executeManyCMD方法代码示例

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

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