Python Custom_MySQL.get方法代碼示例

# 需要導入模塊: from custom.db.mysql import Custom_MySQL [as 別名]
# 或者: from custom.db.mysql.Custom_MySQL import get [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 get [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 get [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 get [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 get [as 別名]
        else:
            sql = ""

        #result = mysql.query(sql)
        result = mysql.query_by_sql(sql)

        # 如果為空，則全部完成
        if result['output'] is None or result['output'] == '':

            # 查找該條任務
            formal_sql = "select d.*,s.db_name,s.table_name from dw2dm_log d, structure s " \
                         "where d.id = %s and d.target_id = s.id" % pid
            formal_result = ""

            if grouped == 1 and priority == 1:
                formal_result = mysql.get(formal_sql)
            elif grouped == 1 and priority != 1:
                # 查找組別為1且優先級小於該條任務優先級的其他dw2dm任務是否完成
                gpsql = "select d.* from dw2dm_log d,(select priority from dw2dm_log where id = %s) a " \
                        "where d.log_date = '%s' and d.game = '%s' and d.platform = '%s' and d.grouped = 1 " \
                        "and d.priority<a.priority and d.status != 3" % (pid, log_date, game, platform)
                gpresult = mysql.query(gpsql)
                if not gpresult:
                    formal_result = mysql.get(formal_sql)
            elif grouped != 1 and priority == 1:
                # 查找組別小於該條任務組別的其他dw2dm任務是否完成
                gpsql = "select d.* from dw2dm_log d,(select grouped from dw2dm_log where id = %s) a " \
                        "where d.log_date = '%s' and d.game = '%s' and d.platform = '%s' and d.grouped<a.grouped " \
                        "and d.status != 3" % (pid, log_date, game, platform)
                gpresult = mysql.query(gpsql)
                if not gpresult:

# 需要導入模塊: from custom.db.mysql import Custom_MySQL [as 別名]
# 或者: from custom.db.mysql.Custom_MySQL import get [as 別名]
          '' % (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)

        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

# 需要導入模塊: from custom.db.mysql import Custom_MySQL [as 別名]
# 或者: from custom.db.mysql.Custom_MySQL import get [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 get [as 別名]
          'and in_queue =0 and exec_num < 4 and log_time <="%s"' \
          '' % (game, platform, do_rate, log_date, log_time)

tasks = mysql.query(sql)

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

        '''
        照理說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"

# 需要導入模塊: from custom.db.mysql import Custom_MySQL [as 別名]
# 或者: from custom.db.mysql.Custom_MySQL import get [as 別名]
          'and in_download_queue=0 and download_exec_num<4 and download_retry_num<6 and log_time<="%s")' \
          '' % (game, platform, do_rate, log_date, log_time, game, platform, do_rate, 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 = 6  and id = %(id)s for update' % where)
        result = mysql.get('select * from etl_data_log where etl_status = 6  and id = %(id)s ' % where)
        if result is not None:
            download_exec_num = int(result['download_exec_num'])
            in_download_queue = int(result['in_download_queue'])
            download_retry_num = int(result['download_retry_num'])

            if download_retry_num < 5:
                if in_download_queue == 0:
                    if download_exec_num < 3:
                        if download_retry_num == 0:
                            download_retry_num += 1

                        download_exec_num += 1
                        in_download_queue = 1

                        datas = {'download_exec_num': download_exec_num, 'in_download_queue': in_download_queue,

# 需要導入模塊: from custom.db.mysql import Custom_MySQL [as 別名]
# 或者: from custom.db.mysql.Custom_MySQL import get [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"