Python Custom_MySQL.query方法代码示例

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

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

# 需要导入模块: from custom.db.mysql import Custom_MySQL [as 别名]
# 或者: from custom.db.mysql.Custom_MySQL import query [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 query [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 query [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 query [as 别名]
 def get_idcs(idc_name):
     db = Custom_MySQL(using='center_app')
     sql ="select id as idc_id,prefix  from idc where name like  %s and is_del = 0" 
     p=(idc_name+'%',)
     return db.query(sql,*p)

# 需要导入模块: from custom.db.mysql import Custom_MySQL [as 别名]
# 或者: from custom.db.mysql.Custom_MySQL import query [as 别名]
class CenterApp():
    def __init__(self):
        self.center_mysql = Custom_MySQL(using='center_app')
        
#     def get_server_list(self):
#         '''
#             获取执行任务
#         '''
#         try:
#             ip_list = []
#             if param['flag'] == 'basic':
#                 ip_list.append('120.26.1.250')
#                 
#             if param['flag'] == 'most':
#                 ip_list.append('120.26.13.150')
#             
#             if param['flag'] == 'log':
#                 s_sql = "select t1.*  from \
#                             (select a.public_ip as source_ip,a.platform_id from assets a inner join main_category m \
#                             on a.main_category_id = m.id  \
#                             where a.is_del = 0 and (a.hostname like '%%%%web_balance%%%%' or a.hostname like '%%%%web_admin%%%%') ) t1 \
#                             left join platform t2 on t1.platform_id = t2.id \
#                             where t1.source_ip is not null group by source_ip"
#                             
#                 if param['game'] == 'kof':
#                     s_sql = "select t1.*  from \
#                             (select a.public_ip as source_ip,a.platform_id from assets a inner join main_category m \
#                             on a.main_category_id = m.id  \
#                             where a.is_del = 0 and a.hostname like '%%%%gameserver%%%%' ) t1 \
#                             left join platform t2 on t1.platform_id = t2.id \
#                             where t1.source_ip is not null group by source_ip" 
#                  
#                 ip_list = self.exec_sql(s_sql)
#   
#             if param['flag'] == 'snap':
#                 s_sql = "select s.prefix as source_ip from sub_category s inner join main_category m \
#                         on s.main_category_id = m.id \
#                         where s.platform = '%s' and m.prefix = '%s'" % (param['platform'],param['game'])
#                 ip_list = self.exec_sql(s_sql)
#             
#             return ip_list
#         except Exception as exc:
#             print exc
#             #异常回滚
#             self.center_mysql.rollback()
    
    
    def get_log_ip(self):
        '''
            获取所有log的IP
        '''
        
        ip_list = {}
        sql = "select t1.*,t2.prefix as platform  from \
                            (select m.prefix as gamename,a.public_ip as source_ip,a.platform_id from assets a inner join main_category m \
                            on a.main_category_id = m.id  \
                            where a.is_del = 0 and (a.hostname like '%%%%web_balance%%%%' or a.hostname like '%%%%web_admin%%%%') ) t1 \
                            left join platform t2 on t1.platform_id = t2.id \
                            where t1.source_ip is not null group by source_ip\
                union all \
                select t1.*,t2.prefix as platform from \
                            (select m.prefix as gamename,a.public_ip as source_ip,a.platform_id from assets a inner join main_category m \
                            on a.main_category_id = m.id  \
                            where a.is_del = 0 and a.hostname like '%%%%gameserver%%%%' ) t1 \
                            left join platform t2 on t1.platform_id = t2.id \
                            where t1.source_ip is not null group by source_ip"
        
        result = self.center_mysql.query(sql)
  
        for info in result:
            if info['gamename'] is None or info['platform'] is None:
                continue
            
            if info['gamename'].encode('utf8') not in ip_list:
                ip_list[info['gamename'].encode('utf8')] = {}
            
            if info['platform'].encode('utf8') not in ip_list[info['gamename'].encode('utf8')]:
                ip_list[info['gamename'].encode('utf8')][info['platform'].encode('utf8')] = []
                
            ip_list[info['gamename'].encode('utf8')][info['platform'].encode('utf8')].append(info['source_ip'].encode('utf8'))

        return ip_list
    
    
    def get_snap_ip(self):
        '''
            获取所有快照的IP
        '''
        ip_list = {}
        s_sql = "select f.prefix as platform,s.prefix as source_ip,m.prefix as gamename from sub_category s inner join main_category m \
                        on s.main_category_id = m.id \
                left join platform f on f.id = s.platform_id"
        
        result = self.center_mysql.query(s_sql)
        
        for info in result:
            if info['gamename'] is None or info['platform'] is None:
                continue
            
            if info['gamename'].encode('utf8') not in ip_list:
#.........这里部分代码省略.........

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

    redis = Custom_Redis(using='etl_manage')
    mysql = Custom_MySQL(using='hadoops2')
    mysql_etl = Custom_MySQL(using='etl_manage')
    mysql.begin()
    mysql_etl.begin()

    where = {'id': int(task_param['id'])}
    datas = {'load_status': 0}

    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'], 'source_ip': task_param['source_ip']}
        prefix_sql = task_param['prefix_sql']
        post_sql = task_param['post_sql']

        log_dir = "/%(game)s/%(platform)s/%(log_date)s/%(log_name)s/" % dir_param
        file_name = "%(log_name)s_%(source_ip)s_%(log_time)s.txt" % filename_dict

        #从redis中，获取当前数据对应存储到哪块磁盘
        if redis.get("disk_xml") is None:
            disk_tmp = open('/data/etl_manage/conf/disk_game.xml', 'r')
            redis.set("disk_xml", str(disk_tmp.read()))

        disk_list = str(redis.get("disk_xml"))
        root = ET.fromstring(disk_list)
        disk = ""
        for gameinfo in root.findall('game'):
            if gameinfo.get('name') == task_param['game']:
                disk = gameinfo.get('disk')
                continue

        #local_log_dir = '/disk1/tmp_data'+log_dir
        local_log_dir = '/'+disk+'/data'+log_dir

        #判断要录入的文件是否存在，如果存在则执行，否则不执行
        if os.path.exists('%s%s' % (local_log_dir, file_name)):
            '''
            将任务标识为开始执行：1
            '''
            datas['load_status'] = 1
            mysql_etl.update('file2mysql_log', ' id = %(id)d' % where, **datas)
            mysql_etl.commit()
            logger.info('start load data')
            #执行前置sql
            if prefix_sql is not None and prefix_sql != '':
                mysql.query(prefix_sql)
 
            '''
            执行load数据进mysql
            '''
            load_sql = task_param['load_sql']
            load_sql = load_sql.replace('{dir_path}', local_log_dir+file_name)
            load_sql = load_sql.replace('{table_name}', task_param['table_name'])
            load_sql = load_sql.replace('{db_name}', task_param['db_name'])
        
            result = mysql.load(load_sql)
            logger.info('load data to mysql: {0}'.format(result['output']))

            #判断录入mysql是否成功
            if result['status'] == 0:
                #执行后置sql
                if post_sql is not None and post_sql != '':
                    post_sql = post_sql.replace('{table_name}', task_param['table_name'])
                    post_sql = post_sql.replace('{db_name}', task_param['db_name'])
                    mysql.query(post_sql)

                '''
                将任务标识为录入mysql完成：3
                '''
                datas['load_status'] = 3

            else:

                logger.error('Error Code %s : %s  Cmd: %s' % (result['status'], result['output'], load_sql))
                '''
                录入mysql失败，将任务标示为未执行：0
                '''
                datas['load_status'] = 0

        '''
        将任务标示为：(模拟) 已从任务队列中移除
        '''
        datas['in_queue'] = 0
        update_result = mysql_etl.update('file2mysql_log', ' id = %(id)d' % where, **datas)
        # 如果更新失败，则再调用一次，如果还是失败，则等待自动修复机制，但这种概率很小了
        if update_result != 1:
            mysql_etl.update('file2mysql_log', ' id = %(id)d' % where, **datas)

        mysql_etl.commit()
        mysql.commit()
        mysql_etl.close()
        mysql.close()
        return True
#.........这里部分代码省略.........

# 需要导入模块: from custom.db.mysql import Custom_MySQL [as 别名]
# 或者: from custom.db.mysql.Custom_MySQL import query [as 别名]
    is_rely = sys.argv[5]
except Exception as exc:
    is_rely = "onrely"

#数据日期，格式如：20151015
task_date = now_time.strftime('%Y%m%d')
#调用处理时差工具类，获取对应游戏有时差的区服信息
game_platform_jetlag = cmd.run('/usr/bin/python /data/etl_manage/handle_jetlag.py -g'+game+'')['output']

# 查找当天未开始的任务
sql = "select id,from_id,log_date,grouped,priority from dw2dm_log " \
      "where task_date='%s' and game='%s' and platform='%s' " \
      "and do_rate='%s' and status=0 and in_queue=0 and exec_num < 3" \
      "" % (task_date, game, platform, do_rate)

tasks = mysql.query(sql)

for task in tasks:
    try:
        mysql.begin()
        pid = task['id']
        from_id = task['from_id']
        log_date = task['log_date']
        grouped = task['grouped']
        priority = task['priority']

        sql = ""
        if is_rely == "onrely":
            if platform == 'all':
                # 查找该条任务所依赖的上层mergefile2dw 或是 本层dw2dm 任务是否完成
                sql = "(select distinct a.* from file2dw_log f,(select s.game gamee,s.platform platformm," \

# 需要导入模块: from custom.db.mysql import Custom_MySQL [as 别名]
# 或者: from custom.db.mysql.Custom_MySQL import query [as 别名]
      'where game="%s" and platform="%s" and do_rate="%s" and log_name in (%s) and etl_status=0 and task_date="%s" ' \
      'and in_etl_queue =0 and etl_exec_num < 4 and etl_retry_num < 6 and log_time <="%s"' \
      '' % (game, platform, do_rate, tables, log_date, log_time)

if log_time <= '0100':
    log_time_old = '2400'
    log_date_old = (now_time-datetime.timedelta(days=1)).strftime('%Y%m%d')
    sql = '(select * from etl_data_log ' \
          'where game="%s" and platform="%s" and do_rate="%s" and log_name in (%s) and etl_status=0 and task_date="%s" ' \
          'and in_etl_queue =0 and etl_exec_num < 4 and etl_retry_num < 6 and log_time <="%s") ' \
          'union (select * from etl_data_log ' \
          'where game="%s" and platform="%s" and do_rate="%s" and log_name in (%s) and etl_status=0 and task_date="%s" ' \
          'and in_etl_queue =0 and etl_exec_num < 4 and etl_retry_num < 6 and log_time <="%s")' \
          '' % (game, platform, do_rate, tables, log_date, log_time, game, platform, do_rate, tables, log_date_old, log_time_old)

tasks = mysql.query(sql)

for task in tasks:
    #print(task)
    try:
        mysql.begin()
        where = {'id': int(task['id'])}

        '''
        照理说for里面不会有人抢快照数据，以防万一起动排他锁(使用主键启动行锁)，兄弟们最好别瞎用
        '''
        #result = mysql.get('select * from etl_data_log where etl_status = 0 and id = %(id)s for update' % where)
        #result = mysql.get('select * from etl_data_log where etl_status = 0 and id = %(id)s ' % where)
        result = mysql.get('select f.*,s.flag from etl_data_log as f left join structure as s '
                           'on f.target_id=s.id where f.id = %(id)s ' % where)

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