Python Custom_MySQL.commit方法代码示例

# 需要导入模块: from custom.db.mysql import Custom_MySQL [as 别名]
# 或者: from custom.db.mysql.Custom_MySQL import commit [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 commit [as 别名]
def main():
    tables = excel_table_byindex()
    
    db = Custom_MySQL(using='center_app')
    
    for row in tables:
        print row                  
        if row['pub']!="":
            param = {}
            param['pwd'] = row['pwd']
            db.update('assets','public_ip="%s"'%row['pub'],**param)
            db.commit()

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

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

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

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

# 需要导入模块: from custom.db.mysql import Custom_MySQL [as 别名]
# 或者: from custom.db.mysql.Custom_MySQL import commit [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 commit [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 commit [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
        ##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)
        
#.........这里部分代码省略.........

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