Python MySQLHelper.closeCur方法代码示例

# 需要导入模块: from mysqlHelp import MySQLHelper [as 别名]
# 或者: from mysqlHelp.MySQLHelper import closeCur [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 closeCur [as 别名]
	  and date_format(a.takeoff_time, "%%Y-%%m-%%d") < "%(date_para_TitleBasedRemoveNum_max)s"
	group by 1, 2, 3, 4
""" %date_para_TitleBasedRemoveNum_dict

#vtweb_tracker2_section = "vtweb_tracker2"
vtweb_tracker2_section = "vtweb_staging"
try:
	vt_host, vt_user, vt_passwd, vt_port, vt_db = getConfMysqlInfo(vtweb_tracker2_section)
	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 = '-5:00'")
	result = vtweb_mysql.queryCMD(vt_TitleBasedRemoveNum_SQL)
except Exception, e:
	logger.debug(": extract data from vt for TitleBasedRemoveNum, %s" %e)
	sys.exit(0)
finally:
	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)

# 需要导入模块: from mysqlHelp import MySQLHelper [as 别名]
# 或者: from mysqlHelp.MySQLHelper import closeCur [as 别名]
		  title,
		  sum(ReportedViews) as reportedViews,
		  current_timestamp as ETLDate
		from DM_VIACOM.SelfService_Aggregate_ByNoticedDate as a, TitleBasedTrackingWebsite as b
		where  a.trackingWebsite_id = b.trackingWebsite_id
		  and a.WebsiteType = 'ugc'
		  and b.WebsiteType = 'ugc'
		  and a.Date_ID >= '%(date_para_reportedViews_min)s'
		  and a.Date_ID < '%(date_para_reportedViews_max)s'
		group by 1, 2, 3, 4, 5
	""" %date_para_reportedViews_dict

	reportedViews_result = target_mysql.queryCMD(aggregate_reportedViews_SQL)

	insert_reportedViews_SQL = """
		INSERT INTO TitleBased1 
		(reportDate, trackingWebsite_id, websiteName, websiteType, title, reportedViews, ETLDate) 
  		VALUES (%s, %s, %s, %s, %s, %s, %s) 
  		ON DUPLICATE KEY UPDATE reportedViews = VALUES(reportedViews), ETLDate = VALUES(ETLDate)
	"""
	target_mysql.insertUpdateCMD(insert_reportedViews_SQL, reportedViews_result)
	target_mysql.commit()
except Exception, e:
	logger.debug(": load data to TitleBased1, %s" %e)
	sys.exit(0)
finally:
	target_mysql.closeCur()
	target_mysql.closeConn()
logger.info(" aggregate data from DM_VIACOM.SelfService_Aggregate_ByNoticedDate to  TitleBased1 end")

# 需要导入模块: from mysqlHelp import MySQLHelper [as 别名]
# 或者: from mysqlHelp.MySQLHelper import closeCur [as 别名]
		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)
	finally:
		vtweb_mysql.closeConn()
		vtweb_mysql.closeCur()
		target_mysql.closeCur()
		target_mysql.closeConn()
	logger.info(" extral data from tracker2.matchedVideo end")	

def main():
	sendToMe(subject = "titleBased_infringAllViews start", body = "titleBased_infringAllViews start")

	matchedVideo_start_date = getMinDatePara("matchedVideo", "firstSendNoticeDate")
	if not matchedVideo_start_date:
		matchedVideo_start_date = "2015-02-28"

	while True:
		if str(matchedVideo_start_date) >= str(time.strftime('%Y-%m-%d',time.localtime(time.time() - 24 * 60 * 60 * 365))):
			break
		matchedVideo_end_date = time.strftime("%Y-%m-%d", time.localtime(time.mktime(time.strptime(str(matchedVideo_start_date), "%Y-%m-%d")) + 1 * 24 * 60 * 60))