Python mysql.Custom_MySQL類代碼示例

 def get_monitor():
     n = Custom_MySQL(using='center_app')
     
     return n.query('select public_ip as ip,22 as port,"playcrab" as  user,pwd  \
                     from assets \
                     where \
                     public_ip ="115.29.10.48" \
                    ')

 def get_assets():
     n = Custom_MySQL(using='center_app')
     
     return n.query('select public_ip as ip,22 as port,"playcrab" as  user,pwd  \
                     from assets \
                     where \
                     public_ip !="" and public_ip !="NULL" \
                     and public_ip not in("115.29.12.230","115.29.12.219","49.213.111.2","49.213.111.3","49.213.111.4","49.213.111.5","49.213.111.6") \
                     order by id')

def main():
   tables = excel_table_byindex()
   
   db = Custom_MySQL(using='center_app')
   
   for row in tables:
       if row['pub'] != "":
           param ={}
           param['pwd'] = row['pwd']
           db.update('assets','public_ip="%s"'%row['pub'],**param)

 def get_result(self):
     '''
     獲取子進程執行結果
     '''
     db = Custom_MySQL(using='log')
     result = db.query('select ip,result as data,IF(flag=2,1,0) as flag \
                        from batch_detail \
                        where batch_id ="%s"'% self.batch_id )
     db.close()
     print '===========', len(result),'================'
     self.result = result

 def assets2appinfo(self):
     
     db = Custom_MySQL(using='center_app')
     #將assets中有的，但是在app_info沒有的，插入到app_info中
     sql ='''
         INSERT into app_info (assets_id,name,public_ip,inner_ip,main_category_id)
         SELECT id,hostname as name,public_ip,inner_ip,main_category_id
         from 
         assets
         where public_ip 
         not in (select public_ip from app_info )
         '''
     db.execute(sql) 
     db.commit()

 def call_back(self,data=[]):
     
     
     db = Custom_MySQL(using='log')
     
     sql = "insert into last_ip (id,ip,login_ip) 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

    def call_back(self,data=[]):
        
        db = Custom_MySQL(using='center_app')

        for result in data:

            if result !=[] and result['flag'] == '1':
                
                param = {}
                param['is_manage'] = 1
                db.update('assets', 'public_ip="%s"' %  result['ip'], **param)
                db.commit()
                
            else:
                print result
                param = {}
                param['is_manage'] = 0
                db.update('assets', 'public_ip="%s"' %  result['ip'], **param)
                db.commit()

 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)

class AddPartition():
    def __init__(self):
        #self.mysql= {'report_ares': Custom_MySQL(using='report_ares'),'report_hebe': Custom_MySQL(using='report_hebe'),'report_crius': Custom_MySQL(using='report_crius')}
         self.mysql=Custom_MySQL(using='hadoops2') 
    def execPartitons(self,games,tables,start_day,end_day):
        ##conv db
        for game in games:
            db="report_"+game
            for table in tables:
                self.mysql.begin();
                do_date=start_day
                i=0;
                exec_partions_sql="ALTER TABLE "+db+"."+table+" ADD PARTITION ("
                patition_sql="";
                while do_date <= end_day:
                    i = i + 1
                    partition_name="p"+str(do_date).replace('-','');
                    is_exist=self.find_partition(db,table,partition_name)
                    if not is_exist:
                        patition_sql=patition_sql+"PARTITION %s VALUES LESS THAN (to_days('%s')),"%(partition_name,do_date)
                        #print patition_sql
                    do_date = start_day + datetime.timedelta(days = i)
                if len(patition_sql)>0:
                    replace_reg = re.compile(r',$')
                    print "add partition db:%s table:%s ,start_day:%s,end_day:%s"%(db,table,start_day,end_day)
                    sql=exec_partions_sql+replace_reg.sub('', patition_sql)+");"
                    print sql
                    self.mysql.execute(sql)
                    self.mysql.commit();

    def add_months(self,sourcedate,months):
        month = sourcedate.month - 1 + months
        year = int(sourcedate.year + month / 12 )
        month = month % 12 + 1
        day = min(sourcedate.day,calendar.monthrange(year,month)[1])
        return datetime.date(year,month,day)
    def find_partition(self,db,table_name,partition_name):
       # exis_row=self.mysql.query("select partition_name,partition_expression, partition_description,table_rows  from information_schema.partitions"\
        #                          " where table_schema = schema() and table_schema='%s' and table_name='%s' and partition_name='%s';"%(db,table_name,partition_name))
        exis_row=self.mysql.query("select partition_name,partition_expression, partition_description,table_rows  from information_schema.partitions"\
                                  " where table_schema='%s' and table_name='%s' and partition_name='%s';"%(db,table_name,partition_name))
        if len(exis_row)>0:
            print "exis partitons db:%s,table:%s,p_name:%s"%(db,table_name,partition_name)
            return True
        return False

 def call_back(self,data=[]):
         
     db = Custom_MySQL(using='log')   
     last_insert_id =  db.insert('cpu_batch',**{})
     db.commit()
     
     sql = "insert into cpu_detail (id,ip,cpu_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

    def run(self):
        '''
        業務入口
        '''
        try:
            #資產就是資產吧 不做為一個應用出現了，運維管理的是資產服務器，也就是應用的公網
            #self.assets2appinfo()
            
            api_data = self.get_api_data()
            if api_data == None:return 
            
            db = Custom_MySQL(using='center_app')    
       
            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 id from  sub_category where main_category_id ='+str(main_category_id)+ ' and name="'+dist['name']+'"'
                sub_category_id = db.get(sql)['id']
                
                
                #將各區組不共用信息入數據庫
                for app in dist['ips']:
                    self.insert_db(main_category_id,sub_category_id,self.platform,app)
                            
                #處理共用信息     
                #for app in api_data['global']:
                    
                    
 
            #更新資產id
            db.execute('update app_info as a left join assets as b on a.public_ip = b.public_ip set a.assets_id = b.id where a.public_ip is not NULL')                              
            db.execute('update app_info as a left join assets as b on a.inner_ip = b.inner_ip set a.assets_id = b.id where a.inner_ip is not NULL')                              
        
        except Exception as e:
            print e

    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):
     db = Custom_MySQL(using='log')
     status = {'flag':1}
     db.update('batch_detail',
               'batch_id="%s" and ip ="%s"'  % (self.host['batch_id'],self.host['ip']),
               **status)
     db.commit()
     db.close()
     try:
          
         #建立連接
         self.ssh=paramiko.SSHClient()
         
         #如果沒有密碼就走public key
         if self.host.get('pwd',True) == True:
             privatekeyfile = os.path.expanduser('/root/.ssh/id_rsa')
             paramiko.RSAKey.from_private_key_file(privatekeyfile)
 
         #缺失host_knows時的處理方法
         known_host = "/root/.ssh/known_hosts"
         self.ssh.load_system_host_keys(known_host)
         self.ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
 
         #os.system('/opt/local/junos/junos')
         #連接遠程客戶機器
         self.ssh.connect(
                     hostname =self.host['ip'],
                     port     =int(self.host['port']),
                     username =self.host['user'],
                     password =self.host['pwd'],
                     compress =True,
                     timeout  =20
                     )
      
         #獲取遠程命令執行結果
         stdin, stdout, stderr = self.ssh.exec_command(self.host['cmd'],bufsize=65535, timeout=10)
         temp = stdout.readlines()
         
         db = Custom_MySQL(using='log')
         status = {'flag':2,'result':''.join(temp)}
         db.update('batch_detail',
                   'batch_id="%s" and ip ="%s"' % (self.host['batch_id'],self.host['ip']),**status)
         db.commit()
         db.close()
        
         
         if temp ==[]:
 
             self.grandchild.put({'flag':'1','ip':self.host['ip'],'data':temp})
         else:
             self.grandchild.put({'flag':'0','ip':self.host['ip'],'data':temp})
         #輸出執行結果
         self.ssh.close()
         
     except  :
         #print trace_back()
         #以防paramiko本身出問題，這裏再用shell運行一次，如果還出問題再確認為問題
         cmd ="ssh -p %s -o StrictHostKeyChecking=no %[email protected]%s  %s"%(self.host['port'],self.host['user'],self.host['ip'],self.host['cmd'])
         (status,output) = commands.getstatusoutput(cmd)
         if status == 0:
             db = Custom_MySQL(using='log')
             status = {'flag':2,'result':output}
             db.update('batch_detail',
                       'batch_id="%s" and ip ="%s"' % (self.host['batch_id'],self.host['ip']),
                       **status)
             db.commit()
             db.close()
         
             self.grandchild.put({'flag':'1','ip':self.host['ip'],'data':output})
         else:
             
             db = Custom_MySQL(using='log')
             status = {'flag':-1,'result':'faild'}
             db.update('batch_detail',
                       'batch_id="%s" and ip ="%s"' % (self.host['batch_id'],self.host['ip']),
                       **status)
             db.commit()
             db.close()
         
             self.grandchild.put({'flag':'0','ip':self.host['ip'],'data':trace_back()})

 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)