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 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 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)