def truncate_table(session, argsql):
db = Database()
flag = True
count = 0
while flag and count <= 3 :
try:
for ite in argsql:
time.sleep(1)
db.execute_nonquery(session, ite)
session.commit()
flag = False
count +=1
logger = getLog('Sync_Data')
logger.info('Complete truncate table')
print "completed"
return 1
except Exception as e:
logerr = getLog('Error_Sync_Data')
logerr.error('Truncate table %s'%(e))
flag = True
count += 1
finally:
logger = getLog('Sync_Data')
logger.info('Final truncate table')
return 0
def main():
#time.sleep(1800)
sqlTruncate =[ "truncate workflow;", "alter table workflow auto_increment = 1;", "truncate node;", "alter table node auto_increment = 1;", "truncate node_detail;", "alter table node_detail auto_increment = 1;", "truncate job_param;", "alter table job_param auto_increment = 1;", "truncate job;", "alter table job auto_increment = 1;"]
db = Database()
session = db.connect()
truncate_table(session, sqlTruncate)
db.close_connect(session)
def __init__(self, cfghost=None):
self.cfghost = cfghost
#self.jobp_Q = Queue()
#self.logger = getLog('Sync job param %s' %(cfghost['host']))
self.logger = getLog('Job_Param')
self.logger.setLevel(logging.INFO)
self.logerr = getLog('Error_Job_Param')
self.db = Database(log = 'Job_Param', logerror = 'Error_Job_Param')
self.session = self.db.connect()
def get_list_node_id(host):
"""
return array node id
host: ip thomson
"""
args = []
db = Database()
sql = "select nid from node where host = '{0}'".format(host)
try:
res = db.execute_query(sql)
for item in res:
args.append(item[0])
except Exception as e:
logerr = getLog("Error_Sync_Data")
logerr.error("Get node id %s" % (e))
finally:
return args
def truncate_table(self, argsql):
db = Database()
flag = True
count = 0
while flag and count <= 3 :
try:
for ite in argsql:
time.sleep(1)
db.execute_nonquery(self.session, ite)
self.session.commit()
flag = False
count +=1
return 1
except Exception as e:
self.logerr.error('Truncate table %s'%(e))
flag = True
count += 1
finally:
self.logger.info('Final truncate table')
return 0
def insert_workflow(session = None, host=None):
logger = getLog('Sync_Data')
logger.setLevel(logging.INFO)
db = Database()
session = db.connect()
start = time.time()
try:
data = get_workflow(host)
logger.info('Get query insert Workflow %s Completed in %s and len %s.' %(host['host'], time.time() - start,len(data)))
#logger.info('Get query insert Workflow %s data is %s.' %(host['host'],data))
truncate_table(session, ["delete from workflow where host = '{0}';".format(host['host'])])
db = Database()
db.many_insert(session, 'workflow',data, 'wid', 'host', 'name')
except Exception as e:
logerr = getLog('Error_Sync_Data')
logerr.error('Get workflow error %s.'%(e))
raise
finally:
db.close_connect(session)
print(session)
return 0
def insert_job(session=None, host=None):
logger = getLog('Sync_Data')
logger.setLevel(logging.INFO)
db = Database()
session = db.connect()
start = time.time()
try:
dataJob = get_job(host)
logger.info('Get query insert Job %s completed %s and len %s'%(host['host'], time.time() - start, len(dataJob)))
truncate_table(session, ["delete from job where host = '{0}';".format(host['host'])])
# truncate_table(session, ["delete from job_param where host = '{0}';".format(host['host'])])
db = Database()
db.many_insert(session, 'job', dataJob, 'jid', 'host', 'state', 'status', 'prog', 'ver', 'startdate', 'enddate')
#db.many_insert(session, 'job_param', dataPara, 'jid', 'host', 'name', 'wid', 'backup')
except Exception as e:
logerr = getLog('Error_Sync_Data')
logerr.error('Get Log {0} and len jobs {1}'.format(e,len(dataJob)))
finally:
db.close_connect(session)
print(session)
return 0
def insert_node(session = None, host=None):
db = Database()
session = db.connect()
logger = getLog('Sync_Data')
logger.setLevel(logging.INFO)
start = time.time()
try:
data_node = get_node(host)
logger.info(' Get query insert Node %s Completed in %s.' %(host['host'] ,time.time() - start))
truncate_table(session,["delete from node where host = '{0}';".format(host['host'])])
db.many_insert(session, 'node', data_node, 'nid', 'host', 'cpu', 'alloccpu', 'mem', 'allocmem', 'status', 'state', 'uncreachable')
data_nodedetail = get_node_detail(host)
truncate_table(session,["delete from node_detail where host = '{0}';".format(host['host'])])
db.many_insert(session, 'node_detail', data_nodedetail, 'nid','host','jid')
except Exception as e:
logerr = getLog('Error_Sync_Data')
logerr.error('Get Node %s' %(e))
raise
finally:
db.close_connect(session)
print(session)
return 0
class syncJobparam():
"""docstring for syncJobparam"""
def __init__(self, cfghost=None):
self.cfghost = cfghost
#self.jobp_Q = Queue()
#self.logger = getLog('Sync job param %s' %(cfghost['host']))
self.logger = getLog('Job_Param')
self.logger.setLevel(logging.INFO)
self.logerr = getLog('Error_Job_Param')
self.db = Database(log = 'Job_Param', logerror = 'Error_Job_Param')
self.session = self.db.connect()
#insert param to database
def insert_job_param(self):
start = time.time()
try:
self.logger.info('Get job param %s' %(self.cfghost['host']))
dataJobParam = get_job_param(self.cfghost)
self.logger.info('%s completed in %s and len %s' %(self.cfghost['host'] ,time.time() - start, len(dataJobParam)))
self.truncate_table(["delete from job_param where host = '{0}';".format(self.cfghost['host'])])
self.db.many_insert(self.session, 'job_param', dataJobParam, 'jid', 'host', 'name', 'wid', 'backup')
except Exception as e:
self.logerr.error('Get job param error {0}'.format(e))
raise
finally:
self.db.close_connect(self.session)
#connect to mysqld
def command_sql(self, sql):
return """mysql -u%s -p'%s' %s -h %s -e "%s" """%(osDb.DATABASE_USER, osDb.DATABASE_PASSWORD, osDb.DATABASE_NAME, osDb.DATABASE_HOST, sql)
#get list jobid by host
def get_lstJob_id(self, host):
try:
response_xml = Job(host).get_job_xml()
return Job(host).count_job(response_xml)
except Exception as e:
self.logerr.error(e)
return 1
#thread add query
def thread_sql(self,jobDetail=None):
self.jobp_Q.put(jobDetail.parse_xml_2_query(jobDetail.get_param()))
# time.sleep(10)
self.jobp_Q.task_done()
def truncate_table(self):
#time.sleep(1800)
time.sleep(10)
strQuery = 'truncate job_param;alter table job_param auto_increment = 1;'
os.system(command_sql(strQuery))
print "truncate complie"
def truncate_table(self, argsql):
db = Database()
flag = True
count = 0
while flag and count <= 3 :
try:
for ite in argsql:
time.sleep(1)
db.execute_nonquery(self.session, ite)
self.session.commit()
flag = False
count +=1
return 1
except Exception as e:
self.logerr.error('Truncate table %s'%(e))
flag = True
count += 1
finally:
self.logger.info('Final truncate table')
return 0
# Cache data to redis
def set_cache(self, redis_key):
self.cache = Redit(key = redis_key)
try:
result = self.get_job_host()
if not result:
time.sleep(1)
result = self.get_job_host()
self.logger.info("Get job list OK -{0}".format(len(result)))
except Exception as e:
self.logerr.error("Get job list Error {0}".format(e))
try:
tmp = self.json_job_host(result)
except Exception as e:
self.logerr.error("Convert Json Error {0}".format(e))
try:
self.cache.set_data(name= osDb.REDIS_NAME[0], val = tmp)
self.logger.info("Set data cache OK")
except Exception as e:
self.logerr.error("Set data cache Error {0}".format(e))
def get_job_host(self):
host = self.cfghost['host']
sql =" SELECT j.jid, pw.name, pw.wname, j.state, j.status, j.startdate, j.enddate, pw.wid, j_a.auto, pw.backup\
FROM (SELECT w.name as wname, p.name, w.wid, p.jid, p.host, p.backup FROM job_param p, (Select wid, name, host from workflow where host = '{0}') w WHERE p.host = '{1}' and w.wid= p.wid) pw, job j LEFT JOIN job_auto j_a ON j.jid = j_a.jid AND j.host = j_a.host WHERE pw.jid = j.jid;".format(host,host)
#print host
return self.db.execute_query(sql)
def json_job_host(self, lstjob):
args = []
for item in lstjob:
JId,jobname,workflow_name,State,Status,StartDate,EndDate,workflowIdRef,backMain,isBackup = item[0], item[1], item[2], item[3], item[4], item[5], item[6], item[7], item[8], item[9]
args.append({'jname' : jobname,
'wid' : workflowIdRef,
'wname' : workflow_name,
'state' : State,
'status' : Status,
'jid' : JId,
# 'prog' : int(Prog),
'startdate' : StartDate \
if StartDate else None,
# 'ver' : int(Ver),
'enddate' : EndDate \
if EndDate else None,
'iauto' : backMain,
'iBackup' : isBackup
})
return json.dumps(args)