Python Custom_MySQL.insert方法代码示例

# 需要导入模块: from custom.db.mysql import Custom_MySQL [as 别名]
# 或者: from custom.db.mysql.Custom_MySQL import insert [as 别名]
    def save_hosts(self):
        
        if self.data == None:
            return False
                
        db = Custom_MySQL(using='center_app')

        for param in self.data:

            sql ='select count(*) as count from assets where wxsn= %s'
            p =(param['wxsn'],)
            count = db.count(sql,*p)

 
            if count['count'] == 0:
                db.insert('assets',**param)
            else:
                db.update('assets','wxsn="%s"'%param['wxsn'],**param)  

# 需要导入模块: from custom.db.mysql import Custom_MySQL [as 别名]
# 或者: from custom.db.mysql.Custom_MySQL import insert [as 别名]
 def save_idcs(self):
     
     if self.data == None:
         return False
     
     db = Custom_MySQL(using='center_app')
     
     
     for  param in  self.data:
         
         sql = 'select count(*) as count from idc where prefix = %s'
         p = (param['prefix'],)
         count = db.count(sql, *p)
         # 检查是否存在
         if count['count'] == 0:      
             db.insert('idc', **param)
         else:
             db.update('idc', 'prefix="%s"' % param['prefix'], **param) 

# 需要导入模块: from custom.db.mysql import Custom_MySQL [as 别名]
# 或者: from custom.db.mysql.Custom_MySQL import insert [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 insert [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 insert [as 别名]
 def call_back(self,data=[]):
     
     db = Custom_MySQL(using='log')   
     last_insert_id =  db.insert('df_batch',**{})
     db.commit()
     
     sql = "insert into df_detail (id,ip,df_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 insert [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 insert [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 insert [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"