Python Custom_MySQL.update方法代码示例

# 需要导入模块: from custom.db.mysql import Custom_MySQL [as 别名]
# 或者: from custom.db.mysql.Custom_MySQL import update [as 别名]
def run_task(self, task_param):



    mysql = Custom_MySQL(using='etl_manage')
    mysql.begin()

    try:

        '''
        业务代码块放下方
        '''
        dir_param ={'game':task_param['game'],
                    'platform':task_param['platform'],
                    'log_date':task_param['log_date'],
                    'log_name':task_param['log_name']}

        filename_dict = {'log_name':task_param['log_name'],'log_time':task_param['log_time']}
        
        '''
        游戏＼平台＼日期＼业务日志名＼日志或者md5文件
        '''
        log_dir =  "/%(game)s/%(platform)s/%(log_date)s/%(log_name)s/" % dir_param

        lzo_file_name = "%(log_name)s_%(log_time)s.txt"% filename_dict

        local_log_dir = '/tmp'+log_dir


        dump_sql = task_param['dump_sql']
        dump_sql = dump_sql.replace('{table_name}',task_param['table_name'])
        dump_sql = dump_sql.replace('{partition_name}',task_param['partition_name'])
        dump_sql = dump_sql.replace('{db_name}',task_param['db_name'])
        print(dump_sql)

        result = mysql.dump(sql,local_log_dir+lzo_file_name)
        #print(result)



        '''
        将任务标识为加载文件完成：2
        '''
        datas = {'load_status':2}
        where = {}
        where['id'] = int(task_param['id'])

        mysql.update('etl_data_log',
                          ' id = %(id)d' % where,
                            **datas)
        mysql.commit()
        mysql.close()
        return True

    except Exception as exc:
        print (exc)
        mysql.rollback()
        raise self.retry(exc=exc, countdown=60)

# 需要导入模块: from custom.db.mysql import Custom_MySQL [as 别名]
# 或者: from custom.db.mysql.Custom_MySQL import update [as 别名]
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)

# 需要导入模块: from custom.db.mysql import Custom_MySQL [as 别名]
# 或者: from custom.db.mysql.Custom_MySQL import update [as 别名]
    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)  

# 需要导入模块: from custom.db.mysql import Custom_MySQL [as 别名]
# 或者: from custom.db.mysql.Custom_MySQL import update [as 别名]
 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) 

# 需要导入模块: from custom.db.mysql import Custom_MySQL [as 别名]
# 或者: from custom.db.mysql.Custom_MySQL import update [as 别名]
    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()

# 需要导入模块: from custom.db.mysql import Custom_MySQL [as 别名]
# 或者: from custom.db.mysql.Custom_MySQL import update [as 别名]
 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)

# 需要导入模块: from custom.db.mysql import Custom_MySQL [as 别名]
# 或者: from custom.db.mysql.Custom_MySQL import update [as 别名]
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' 

# 需要导入模块: from custom.db.mysql import Custom_MySQL [as 别名]
# 或者: from custom.db.mysql.Custom_MySQL import update [as 别名]
        if result is not None:
            etl_exec_num = int(result['etl_exec_num'])
            in_etl_queue = int(result['in_etl_queue'])
            etl_retry_num = int(result['etl_retry_num'])

            if etl_retry_num < 5:
                if in_etl_queue == 0:
                    if etl_exec_num < 3:
                        if etl_exec_num == 0:
                            etl_retry_num += 1

                        etl_exec_num += 1
                        in_etl_queue = 1

                        datas = {'etl_exec_num': etl_exec_num, 'in_etl_queue': in_etl_queue, 'etl_retry_num': etl_retry_num}
                        update_result = mysql.update('etl_data_log', ' id = %(id)d' % where, **datas)
                        if update_result == 1:
                            run_task.apply_async((result,), queue='etl_data')
                        else:
                            print "update data{'etl_exec_num', 'in_etl_queue', 'etl_retry_num'} error"
                    else:
                        #任务在该次自动重试中执行失败
                        datas = {'etl_status': -1}
                        mysql.update('etl_data_log', ' id = %(id)d' % where, **datas)
            else:
                #任务执行彻底失败，不再自动重试
                datas = {'etl_status': -2}
                mysql.update('etl_data_log', ' id = %(id)d' % where, **datas)

        mysql.commit()

# 需要导入模块: from custom.db.mysql import Custom_MySQL [as 别名]
# 或者: from custom.db.mysql.Custom_MySQL import update [as 别名]
        '''
        照理说for里面不会有人抢快照数据，以防万一起动排他锁(使用主键启动行锁)，兄弟们最好别瞎用
        '''
        #result = mysql.get('select f.*,s.db_name,s.table_name from file2dw_log as f left join structure as s '
        result = mysql.get('select f.*,s.db_name,s.table_name,s.flag from file2dw_log as f left join structure as s '
                           'on f.target_id=s.id where f.id = %(id)s ' % where)
        if result is not None:
            exec_num = int(result['exec_num'])
            in_queue = int(result['in_queue'])
            if in_queue == 0:
                if exec_num < 3:
                    exec_num += 1
                    in_queue = 1

                    datas = {'exec_num': exec_num, 'in_queue': in_queue}
                    update_result = mysql.update('file2dw_log', ' id = %(id)d' % where, **datas)
                    # 为防止修改数据库时出现异常，调整为：确认修改字段状态成功后，再加入队列
                    if update_result == 1:
                        run_task.apply_async((result,), queue='mergefile2dw')
                    else:
                        print "update data{'exec_num', 'in_queue'} error"
                else:
                    datas = {'load_status': -1}
                    mysql.update('file2dw_log', ' id = %(id)d' % where, **datas)

        mysql.commit()

    except Exception as exc:
        print(exc)
        mysql.rollback()
        print "db rollback success"

# 需要导入模块: from custom.db.mysql import Custom_MySQL [as 别名]
# 或者: from custom.db.mysql.Custom_MySQL import update [as 别名]
                        "" % (pid, log_date, game, platform)
                gpresult = mysql.query(gpsql)
                if not gpresult:
                    formal_result = mysql.get(formal_sql)

            # 如果为空，则全部完成
            if formal_result is not None and formal_result != "":
                exec_num = int(formal_result['exec_num'])
                in_queue = int(formal_result['in_queue'])
                if in_queue == 0:
                    if exec_num < 3:
                        exec_num += 1
                        in_queue = 1

                        datas = {'exec_num': exec_num, 'in_queue': in_queue}
                        update_result = mysql.update('dw2dm_log', ' id = %s' % pid, **datas)
                        if update_result == 1:
                            run_task.apply_async((formal_result,), queue='dw2dm')
                        else:
                            print "update data{'exec_num', 'in_queue'} error"
                    else:
                        datas = {'status': -1}
                        mysql.update('dw2dm_log', ' id = %s' % pid, **datas)
        mysql.commit()

    except Exception as exc:
        print(exc)
        mysql.rollback()
        print "db rollback success"
        mysql.close()

# 需要导入模块: from custom.db.mysql import Custom_MySQL [as 别名]
# 或者: from custom.db.mysql.Custom_MySQL import update [as 别名]
 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)

# 需要导入模块: from custom.db.mysql import Custom_MySQL [as 别名]
# 或者: from custom.db.mysql.Custom_MySQL import update [as 别名]
 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()})