def save_hosts(self):
if self.data == None:
return False
db = Custom_MySQL(using='center_app')
for param in self.data:
sql ='select count(*) as count from assets where wxsn= %s'
p =(param['wxsn'],)
count = db.count(sql,*p)
if count['count'] == 0:
db.insert('assets',**param)
else:
db.update('assets','wxsn="%s"'%param['wxsn'],**param)
def save_idcs(self):
if self.data == None:
return False
db = Custom_MySQL(using='center_app')
for param in self.data:
sql = 'select count(*) as count from idc where prefix = %s'
p = (param['prefix'],)
count = db.count(sql, *p)
# 检查是否存在
if count['count'] == 0:
db.insert('idc', **param)
else:
db.update('idc', 'prefix="%s"' % param['prefix'], **param)
def run(self):
'''
业务入口
'''
api_data = self.get_api_data()
if api_data == None:return
db = Custom_MySQL(using='center_app')
#更新大类中的应用类型
app_type ={}
app_type ={'app_type':','.join(api_data['type'])}
db.update('main_category',' prefix="%s" ' % self.game_code,**app_type)
game = db.get('select id from main_category where prefix="%s"' % self.game_code)
main_category_id = game['id']
#获取区组信息
for dist in api_data['dists']:
print '========'+dist['name']+'+'+dist['code']
sql ='select count(id) as count from sub_category where main_category_id ='+str(main_category_id)+ ' and name="'+dist['name']+'"'
count = db.count(sql)
if count==None:
print 'SQL Error:%s'% sql
return False
#区组更新内容
dist_data ={}
dist_data ={'prefix':dist['code'],
'main_category_id':main_category_id,
'name':dist['name'],
'platform':self.platform}
#如果没有区组信息则保存
if count['count'] == 0:
db.insert('sub_category',**dist_data)
else:
db.update('sub_category',' main_category_id ='+str(main_category_id)+ ' and name="'+dist['name']+'"',**dist_data)
def main():
tables = excel_table_byindex()
db = Custom_MySQL(using='center_app')
for row in tables:
print row
game = row['name'].split('_')[0]
sql ='select id from main_category where prefix= %s'
p =(game,)
result= db.get(sql,*p)
if result:
print result['id']
param={}
param['main_category_id']= result['id']
if row['inner'] !='' and row['pub']!="":
sql='select count(*) as count from assets where inner_ip="%s" or public_ip ="%s"'%(row['inner'],row['pub'])
count = db.count(sql)['count']
if count == 0:
param['inner_ip'] = row['inner']
param['public_ip'] = row['pub']
param['hostname'] = row['name']
param['wxsn'] = row['name']
db.insert('assets',**param)
else:
param['hostname'] = row['name']
db.update('assets','inner_ip="%s"'%row['inner'],**param)
elif row['inner'] !='':
sql='select count(*) as count from assets where inner_ip="%s"'%row['inner']
count = db.count(sql)['count']
if count == 0:
param['inner_ip'] = row['inner']
param['hostname'] = row['name']
param['wxsn'] = row['name']
db.insert('assets',**param)
else:
param['hostname'] = row['name']
db.update('assets','inner_ip="%s"'%row['inner'],**param)
elif row['pub']!="":
sql='select count(*) as count from assets where public_ip="%s"'%row['pub']
count = db.count(sql)['count']
if count ==0:
param['public_ip'] = row['pub']
param['hostname'] = row['name']
param['wxsn'] = row['name']
db.insert('assets',**param)
else:
param['hostname'] = row['name']
db.update('assets','public_ip="%s"'%row['pub'],**param)
else:
print 'pub and inner are both empty'
def call_back(self,data=[]):
db = Custom_MySQL(using='log')
last_insert_id = db.insert('df_batch',**{})
db.commit()
sql = "insert into df_detail (id,ip,df_used) values(null,%s,%s)"
for result in data:
if int(result['flag']) == 1:
ips = json.loads(result['data'])
host = result['ip']
set_param =[]
for ip in ips:
set_param.append(tuple([host,ip]))
db.executemany(sql,set_param)
db.commit()
else:
print result
class CopyConfig():
def __init__(self):
self.mysql = Custom_MySQL(using='etl_manage')
self.source_game = 'ares'
def get_all_task(self, task_name):
condition = 'game = "%s" ' % self.source_game
if task_name is not None:
condition += 'and task_name="%s"' % task_name
##appstoremix is_delete = 0 and
task_list = self.mysql.query(
"select * from dw2dm where platform='all' and %s" % (condition))
return task_list
def get_structure(self, id, game, plat_form):
'''
获取当前游戏的，参数structure.如不存在则会添加
:param id:
:param game:
:param plat_form:
:return:
'''
structure = self.mysql.get(
"select * from structure where is_delete=0 and id=%s", id)
if structure != None:
t_structure = [
structure['type'],
structure['flag'],
structure['db_type'],
game,
plat_form,
#structure['platform'],
#'db_name':structure['db_name'],
structure['table_name'],
structure['column_name'],
##structure['partition_name'],
##structure['partition_rule'],
##structure['index_name'],
structure['create_table_sql'],
structure['user_id'],
0,
datetime.datetime.today().strftime("%Y-%m-%d")
]
game_db = None
if structure['type'] != None and str(
structure['type']).__eq__('dw'):
game_db = '%s_dw' % game
t_structure.append(game_db)
elif structure['type'] != None and str(
structure['type']).__eq__('dm'):
game_db = '%s_dm' % game
t_structure.append(game_db)
elif structure['type'] != None and str(
structure['type']).__eq__('report'):
game_db = 'report_%s' % game
t_structure.append(game_db)
exis_row = self.mysql.query(
"select id from structure where platform='%s' and is_delete=0 and db_name='%s' and platform='all' and table_name='%s' and db_type='%s'"
% (plat_form, game_db, str(
structure['table_name']), str(structure['db_type'])))
if len(exis_row) > 0:
return int(exis_row[0]['id'])
else:
return self.save_newstructure(t_structure)
def save_new_task(self, task):
self.mysql.insert("dw2dm", **task)
self.mysql.commit()
def save_newstructure(self, structure):
query = 'INSERT INTO structure(type,flag,db_type,game,platform,table_name,column_name,create_table_sql,user_id,is_delete,create_date,db_name) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
rowNum = self.mysql.execute(query, *tuple(structure))
self.mysql.commit()
return rowNum
def run(self, game, task_name=None, plat_form="all"):
print "start copy"
task_list = self.get_all_task(task_name)
for task in task_list:
form_ids = ""
for form_id_str in task['from_id'].split(","):
if len(str(form_ids)) > 0:
form_ids = form_ids + "," + str(
self.get_structure(int(form_id_str), game, plat_form))
else:
form_ids = str(
self.get_structure(int(form_id_str), game, plat_form))
target_id = self.get_structure(int(task['target_id']), game,
plat_form)
t_task = {
'game':
game,
##'platform':task['platform'],
'platform':
plat_form,
'log_name':
task['log_name'],
'do_rate':
task['do_rate'],
'priority':
task['priority'],
'prefix_sql':
task['prefix_sql'],
'exec_sql':
task['exec_sql'].replace("%s_dw" % self.source_game,
"%s_dw" % game).replace(
"%s_dm" % self.source_game,
"%s_dm" % game),
'post_sql':
task['post_sql'],
'from_id':
form_ids,
'target_id':
target_id,
'create_date':
datetime.datetime.today().strftime("%Y-%m-%d"),
'comment':
task['comment'],
'grouped':
task['grouped'],
'is_delete':
task['is_delete'],
'user_id':
task['user_id']
}
self.save_new_task(t_task)
self.mysql.close()
print "over"
def add_structure(self, game, plat_form):
platforms_str = plat_form.split(",")
structures = self.mysql.query(
"select * from structure where platform='all' and is_delete=0 and flag='log' and game='ares' and type in ('report','dm')"
)
for structure in structures:
for platform in platforms_str:
t_structure = [
structure['type'],
structure['flag'],
structure['db_type'],
game,
platform,
#structure['platform'],
#'db_name':structure['db_name'],
structure['table_name'],
structure['column_name'],
##structure['partition_name'],
##structure['partition_rule'],
##structure['index_name'],
structure['create_table_sql'],
structure['user_id'],
0,
datetime.datetime.today().strftime("%Y-%m-%d")
]
game_db = None
if structure['type'] != None and str(
structure['type']).__eq__('dw'):
game_db = '%s_dw' % game
elif structure['type'] != None and str(
structure['type']).__eq__('dm'):
game_db = '%s_dm' % game
elif structure['type'] != None and str(
structure['type']).__eq__('report'):
game_db = 'report_%s' % game
t_structure.append(game_db)
self.save_newstructure(t_structure)
class CopyConfig():
def __init__(self):
self.mysql = Custom_MySQL(using='etl_manage')
self.source_game = 'ares'
def get_all_task(self,task_name):
condition = 'game = "%s" ' % self.source_game
if task_name is not None:
condition += 'and task_name="%s"' % task_name
##appstoremix is_delete = 0 and
task_list = self.mysql.query("select * from dw2dm where platform='all' and %s" % (condition))
return task_list
def get_structure(self,id,game,plat_form):
'''
获取当前游戏的，参数structure.如不存在则会添加
:param id:
:param game:
:param plat_form:
:return:
'''
structure=self.mysql.get("select * from structure where is_delete=0 and id=%s",id)
if structure!=None:
t_structure=[
structure['type'],
structure['flag'],
structure['db_type'],
game,
plat_form,
#structure['platform'],
#'db_name':structure['db_name'],
structure['table_name'],
structure['column_name'],
##structure['partition_name'],
##structure['partition_rule'],
##structure['index_name'],
structure['create_table_sql'],
structure['user_id'],
0,
datetime.datetime.today().strftime("%Y-%m-%d")
]
game_db=None
if structure['type']!=None and str(structure['type']).__eq__('dw'):
game_db='%s_dw' % game
t_structure.append(game_db)
elif structure['type']!=None and str(structure['type']).__eq__('dm'):
game_db='%s_dm' % game
t_structure.append(game_db)
elif structure['type']!=None and str(structure['type']).__eq__('report'):
game_db='report_%s' % game
t_structure.append(game_db)
exis_row=self.mysql.query("select id from structure where platform='%s' and is_delete=0 and db_name='%s' and platform='all' and table_name='%s' and db_type='%s'"%(plat_form,game_db,str(structure['table_name']),str(structure['db_type'])))
if len(exis_row)>0:
return int(exis_row[0]['id'])
else:
return self.save_newstructure(t_structure)
def save_new_task(self,task):
self.mysql.insert("dw2dm",**task)
self.mysql.commit()
def save_newstructure(self,structure):
query='INSERT INTO structure(type,flag,db_type,game,platform,table_name,column_name,create_table_sql,user_id,is_delete,create_date,db_name) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
rowNum=self.mysql.execute(query,*tuple(structure))
self.mysql.commit()
return rowNum
def run(self,game,task_name=None,plat_form="all"):
print "start copy"
task_list = self.get_all_task(task_name)
for task in task_list:
form_ids=""
for form_id_str in task['from_id'].split(","):
if len(str(form_ids))>0:
form_ids=form_ids+","+str(self.get_structure(int(form_id_str),game,plat_form))
else:
form_ids=str(self.get_structure(int(form_id_str),game,plat_form))
target_id=self.get_structure(int(task['target_id']),game,plat_form)
t_task = {
'game':game,
##'platform':task['platform'],
'platform':plat_form,
'log_name':task['log_name'],
'do_rate':task['do_rate'],
'priority':task['priority'],
'prefix_sql':task['prefix_sql'],
'exec_sql':task['exec_sql'].replace("%s_dw" % self.source_game,"%s_dw" % game).replace("%s_dm" % self.source_game,"%s_dm" % game),
'post_sql':task['post_sql'],
'from_id':form_ids,
'target_id':target_id,
'create_date':datetime.datetime.today().strftime("%Y-%m-%d"),
'comment':task['comment'],
'grouped':task['grouped'],
'is_delete':task['is_delete'],
'user_id':task['user_id']
}
self.save_new_task(t_task)
self.mysql.close()
print "over"
def add_structure(self,game,plat_form):
platforms_str=plat_form.split(",")
structures=self.mysql.query("select * from structure where platform='all' and is_delete=0 and flag='log' and game='ares' and type in ('report','dm')")
for structure in structures:
for platform in platforms_str:
t_structure=[
structure['type'],
structure['flag'],
structure['db_type'],
game,
platform,
#structure['platform'],
#'db_name':structure['db_name'],
structure['table_name'],
structure['column_name'],
##structure['partition_name'],
##structure['partition_rule'],
##structure['index_name'],
structure['create_table_sql'],
structure['user_id'],
0,
datetime.datetime.today().strftime("%Y-%m-%d")
]
game_db=None
if structure['type']!=None and str(structure['type']).__eq__('dw'):
game_db='%s_dw' % game
elif structure['type']!=None and str(structure['type']).__eq__('dm'):
game_db='%s_dm' % game
elif structure['type']!=None and str(structure['type']).__eq__('report'):
game_db='report_%s' % game
t_structure.append(game_db)
self.save_newstructure(t_structure)
def insert_db(self,main_category_id,sub_category_id,platform,app):
temp_app ={}
temp_app['name']=app['type']+'['+app['memo']+']'
temp_app['platform'] = platform
temp_app['type']=app['type']
temp_app['port']=app['port']
temp_app['main_category_id'] = main_category_id
temp_app['sub_category_id'] = sub_category_id
if app.get('db_type',False):
temp_app['db_type'] = app['db_type']
sql ='select count(*) as count from app_info where '
#同一游戏同一区组
where ='type="%s" and port="%s" and main_category_id="%s" \
and sub_category_id="%s"'% (app['type'],app['port'],main_category_id,sub_category_id)
db = Custom_MySQL(using='center_app')
#处理内网
if app['ip'].split('.')[0] in ['10','172']:
inner_ip ='and inner_ip="%s"'%(app['ip'])
count = db.count(sql+where+inner_ip)
if count==None:
print 'SQL Error:%s'% sql+where+inner_ip
return False
count = count['count']
try:
temp_app['public_ip'] = db.get('select public_ip from assets where inner_ip="%s"'% app['ip'])['public_ip']
except:
pass
if count==0:
temp_app['inner_ip'] = app['ip']
db.insert('app_info',**temp_app)
else:
db.update('app_info',where+inner_ip,**temp_app)
else:
import re
if app['type']=='web':
app['ip'] = app['ip'].replace('http://','').split('/')[0]
p=r'(?<![\.\d])(?:\d{1,3}\.){3}\d{1,3}(?![\.\d])'
mo = re.search(p ,app['ip'])
if not mo:
domain = app['ip'].replace('http://','').split('/')[0]
app['ip'] = db.get('select ip from domain where domain="%s"'% domain)['ip']
temp_app['domain'] = domain
public_ip ='and (public_ip="%s") '%(app['ip'])
count = db.count(sql+where+public_ip)
if count==None:
print 'SQL Error:%s'% sql+where+public_ip
return False
count = count['count']
if count==0:
temp_app['public_ip'] = app['ip']
db.insert('app_info',**temp_app)
else:
db.update('app_info',where+public_ip,**temp_app)
class CopyConfig():
def __init__(self):
self.mysql = Custom_MySQL(using='etl_manage')
self.source_game = 'ares'
def get_all_task(self,task_name):
condition = 'game = "%s" ' % self.source_game
if task_name is not None:
condition += 'and task_name="%s"' % task_name
task_list = self.mysql.query("select * from dm2report where is_delete = 0 and %s" % condition)
return task_list
def get_structure(self,id,game):
structure=self.mysql.get("select * from structure where is_delete=0 and id=%s",id)
if structure!=None:
t_structure=[
structure['type'],
structure['flag'],
structure['db_type'],
game,
structure['platform'],
#'db_name':structure['db_name'],
structure['table_name'],
structure['column_name'],
##structure['partition_name'],
##structure['partition_rule'],
##structure['index_name'],
structure['create_table_sql'],
structure['user_id'],
0,
datetime.datetime.today().strftime("%Y-%m-%d")
]
game_db=None
if structure['db_type']!=None and str(structure['db_type']).__eq__('hive'):
game_db='%s_dw' % game
t_structure.append(game_db)
elif structure['db_type']!=None and str(structure['db_type']).__eq__('mysql'):
game_db='report_%s' % game
t_structure.append(game_db)
exis_row=self.mysql.query("select id from structure where platform='all' and user_id='wxx' and is_delete=0 and db_name='%s' and table_name='%s' and db_type='%s'"%(game_db,str(structure['table_name']),str(structure['db_type'])))
if len(exis_row)>0:
return int(exis_row[0]['id'])
else:
return self.save_newstructure(t_structure)
def save_new_task(self,task):
self.mysql.insert("dm2report",**task)
self.mysql.commit()
def save_newstructure(self,structure):
query='INSERT INTO structure(type,flag,db_type,game,platform,table_name,column_name,create_table_sql,user_id,is_delete,create_date,db_name) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
rowNum=self.mysql.execute(query,*tuple(structure))
self.mysql.commit()
return rowNum
def run(self,game,task_name=None):
print "start copy"
task_list = self.get_all_task(task_name)
for task in task_list:
form_id=self.get_structure(int(task['from_id']),game)
target_id=self.get_structure(int(task['target_id']),game)
t_task = {
'game':game,
'platform':task['platform'],
'task_name':task['task_name'],
'date_cycle':task['date_cycle'],
'do_rate':task['do_rate'],
'group':task['group'],
'priority':task['priority'],
'prefix_sql':task['prefix_sql'],
'exec_sql':task['exec_sql'].replace("%s_dw" % self.source_game,"%s_dw" % game),
'post_sql':task['post_sql'],
'from_id':form_id,
'target_id':target_id,
'create_date':datetime.datetime.today().strftime("%Y-%m-%d"),
'comment':task['comment']
}
self.save_new_task(t_task)
self.mysql.close()
print "over"
class CopyConfig():
def __init__(self):
self.mysql = Custom_MySQL(using='etl_manage')
self.source_game = 'ares'
def get_all_task(self, task_name):
condition = 'game = "%s" ' % self.source_game
if task_name is not None:
condition += 'and task_name="%s"' % task_name
task_list = self.mysql.query(
"select * from dm2report where is_delete = 0 and %s" % condition)
return task_list
def get_structure(self, id, game):
structure = self.mysql.get(
"select * from structure where is_delete=0 and id=%s", id)
if structure != None:
t_structure = [
structure['type'],
structure['flag'],
structure['db_type'],
game,
structure['platform'],
#'db_name':structure['db_name'],
structure['table_name'],
structure['column_name'],
##structure['partition_name'],
##structure['partition_rule'],
##structure['index_name'],
structure['create_table_sql'],
structure['user_id'],
0,
datetime.datetime.today().strftime("%Y-%m-%d")
]
game_db = None
if structure['db_type'] != None and str(
structure['db_type']).__eq__('hive'):
game_db = '%s_dw' % game
t_structure.append(game_db)
elif structure['db_type'] != None and str(
structure['db_type']).__eq__('mysql'):
game_db = 'report_%s' % game
t_structure.append(game_db)
exis_row = self.mysql.query(
"select id from structure where platform='all' and user_id='wxx' and is_delete=0 and db_name='%s' and table_name='%s' and db_type='%s'"
% (game_db, str(
structure['table_name']), str(structure['db_type'])))
if len(exis_row) > 0:
return int(exis_row[0]['id'])
else:
return self.save_newstructure(t_structure)
def save_new_task(self, task):
self.mysql.insert("dm2report", **task)
self.mysql.commit()
def save_newstructure(self, structure):
query = 'INSERT INTO structure(type,flag,db_type,game,platform,table_name,column_name,create_table_sql,user_id,is_delete,create_date,db_name) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
rowNum = self.mysql.execute(query, *tuple(structure))
self.mysql.commit()
return rowNum
def run(self, game, task_name=None):
print "start copy"
task_list = self.get_all_task(task_name)
for task in task_list:
form_id = self.get_structure(int(task['from_id']), game)
target_id = self.get_structure(int(task['target_id']), game)
t_task = {
'game':
game,
'platform':
task['platform'],
'task_name':
task['task_name'],
'date_cycle':
task['date_cycle'],
'do_rate':
task['do_rate'],
'group':
task['group'],
'priority':
task['priority'],
'prefix_sql':
task['prefix_sql'],
'exec_sql':
task['exec_sql'].replace("%s_dw" % self.source_game,
"%s_dw" % game),
'post_sql':
task['post_sql'],
'from_id':
form_id,
'target_id':
target_id,
'create_date':
datetime.datetime.today().strftime("%Y-%m-%d"),
'comment':
task['comment']
}
self.save_new_task(t_task)
self.mysql.close()
print "over"