Python Custom_MySQL.execute方法代码示例

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

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