Python Custom_MySQL.begin方法代码示例

# 需要导入模块: from custom.db.mysql import Custom_MySQL [as 别名]
# 或者: from custom.db.mysql.Custom_MySQL import begin [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 begin [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 begin [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 begin [as 别名]
#当前时间
now_time = datetime.datetime.now()
task_date = now_time.strftime('%Y%m%d')
log_time = now_time.strftime('%H%M')

if log_time < '0200':
    task_date = (now_time-datetime.timedelta(days=1)).strftime('%Y%m%d')

log_time_before_30min = (now_time-datetime.timedelta(seconds=1800)).strftime('%H%M')
log_time_before_1hour = (now_time-datetime.timedelta(seconds=3600)).strftime('%H%M')

now = now_time.strftime('%Y-%m-%d %H:%M:%S')

try:
    mysql.begin()

    #重置etl_status
    etl_result = mysql.update_by_sql('update etl_data_log set etl_status=0,etl_exec_num=0,in_etl_queue=0 '
                                     'where etl_status=-1 and task_date="%s"' % task_date)
    if etl_result['status'] != 0:
        print(now+" restart etl task error")

    etl_result_1 = mysql.update_by_sql('update etl_data_log set etl_status=0,etl_exec_num=0,in_etl_queue=0 '
                                       'where etl_status!=6 and etl_status!=-2 and in_etl_queue=1 and log_time <="%s" and task_date="%s"'
                                       '' % (log_time_before_30min, task_date))
    if etl_result_1['status'] != 0:
        print(now+" restart etl_1 task error")

    #重置download_status
    download_result = mysql.update_by_sql('update etl_data_log set download_status=0,download_exec_num=0,in_download_queue=0 '