Python Custom_MySQL.close方法代码示例

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

# 需要导入模块: from custom.db.mysql import Custom_MySQL [as 别名]
# 或者: from custom.db.mysql.Custom_MySQL import close [as 别名]
def query_mysql(sql):

    mysql = Custom_MySQL(using='etl_manage')

    try:
        mysql.begin()
        result = mysql.query(sql)

        mysql.commit()
        mysql.close()

        return result

    except Exception as exc:
        #回滚
        mysql.rollback()
        print(exc)
        mysql.close()

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

# 需要导入模块: from custom.db.mysql import Custom_MySQL [as 别名]
# 或者: from custom.db.mysql.Custom_MySQL import close [as 别名]
        if file2dw_result['status'] != 0:
            print(now+" restart file2dw task error")

        file2dw_result_1 = mysql.update_by_sql('update file2dw_log set load_status=0,exec_num=0,in_queue=0 '
                                               'where load_status!=5 and in_queue=1 and log_time <="%s" and task_date="%s"'
                                               '' % (log_time_before_1hour, task_date))
        if file2dw_result_1['status'] != 0:
            print(now+" restart file2dw_1 task error")

    if '0500' < log_time < '1100':
        #重置dm2report_status
        dm2report_result = mysql.update_by_sql('update dm2report_log set status=0,exec_num=0,in_queue=0 '
                                               'where status=-1 and task_date="%s"' % task_date)
        if dm2report_result['status'] != 0:
            print(now+" restart dm2report task error")

        dm2report_result_1 = mysql.update_by_sql('update dm2report_log set status=0,exec_num=0,in_queue=0 '
                                                 'where status!=4 and in_queue=1 and log_time <="%s" and task_date="%s"'
                                                 '' % (log_time_before_1hour, task_date))
        if dm2report_result_1['status'] != 0:
            print(now+" restart dm2report_1 task error")

    mysql.commit()
    mysql.close()

except Exception as exc:
    #回滚
    mysql.rollback()
    print(exc)
    mysql.close()

# 需要导入模块: from custom.db.mysql import Custom_MySQL [as 别名]
# 或者: from custom.db.mysql.Custom_MySQL import close [as 别名]

#.........这里部分代码省略.........
                ##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)

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