def __init__(self, list, name):
        threading.Thread.__init__(self)
        self.owner_list = list
        self.nams = name
        self.oracle = Oracle( user = "******", passwd = "hl_q" , host = "172.16.6.19:1521/HLSALES" )
        #self.oracle = Oracle( user = "******", passwd = "WZCXK20AKGD0P0OL" , host = "172.16.6.114:1521/PROCINST" )
        #self.mysql = Mysql( user = "******", passwd = "5664922ed6404022b4b192499dc56399" , host = "10.10.6.21", port = 6623, db = "biz_huadong", charset = 'utf-8' )
        self.mysql = Mysql( user = "******", passwd = "123456" , host = "172.30.11.240", port = 3306, db = "biz_huadong", charset = 'utf8' )

#        self.mysql = Mysql( user = "******", passwd = "5664922ed6404022b4b192499dc56399" , host = "10.10.6.21", port = 6623, db = "biz_huadong", charset = 'utf8' )
        # new_table_field_name,  old_field_name, transfer by function or null skip
        self.tup_list = [( "standard_house_id", "HSTANDARD_ID"), \
                     ( "name", "OWNER_NAME"), \
                     ( "phone", "MOBILE_PHONE"), \
                     ( "home_phone", "HOME_PHONE"), \
                     ( "office_phone", "OFFICE_PHONE" ), \
                     ( "owner_type", "OWNER_TYPE",  "transfer_owner_type"  ), \
                     ( "qq", "QQ" ), \
                     ( "STATUS", "IS_VALID"), \
                     ( "create_time", "CREATED_TIME", "date_time_transfer"), \
                     ( "creator_id", "CREATED_BY",  "gen_create_id"  ) \
                    ]

        self.track_tup_list = [( "owner_id", "OWNERINFO_MYSQL_PKID"), \
                     ( "phone", "", "select_phone"), \
                     ( "description", "TRACK_DESCRIPTION"), \
                     ( "creator_id", "creator_id", "gen_create_id"), \
                     ( "creator_name", "DESCRIPTION"  ), \
                     ( "create_time", "CREATED_TIME", "timestamp_convert"), \
                     ( "org_code", "ORG_CODE"), \
                     ( "org_name", "ORG_SHORT_NAME"), \
                    ]
class Owner_import( threading.Thread ):
    
    def __init__(self, list, name):
        threading.Thread.__init__(self)
        self.owner_list = list
        self.nams = name
        self.oracle = Oracle( user = "******", passwd = "hl_q" , host = "172.16.6.19:1521/HLSALES" )
        #self.oracle = Oracle( user = "******", passwd = "WZCXK20AKGD0P0OL" , host = "172.16.6.114:1521/PROCINST" )
        #self.mysql = Mysql( user = "******", passwd = "5664922ed6404022b4b192499dc56399" , host = "10.10.6.21", port = 6623, db = "biz_huadong", charset = 'utf-8' )
        self.mysql = Mysql( user = "******", passwd = "123456" , host = "172.30.11.240", port = 3306, db = "biz_huadong", charset = 'utf8' )

#        self.mysql = Mysql( user = "******", passwd = "5664922ed6404022b4b192499dc56399" , host = "10.10.6.21", port = 6623, db = "biz_huadong", charset = 'utf8' )
        # new_table_field_name,  old_field_name, transfer by function or null skip
        self.tup_list = [( "standard_house_id", "HSTANDARD_ID"), \
                     ( "name", "OWNER_NAME"), \
                     ( "phone", "MOBILE_PHONE"), \
                     ( "home_phone", "HOME_PHONE"), \
                     ( "office_phone", "OFFICE_PHONE" ), \
                     ( "owner_type", "OWNER_TYPE",  "transfer_owner_type"  ), \
                     ( "qq", "QQ" ), \
                     ( "STATUS", "IS_VALID"), \
                     ( "create_time", "CREATED_TIME", "date_time_transfer"), \
                     ( "creator_id", "CREATED_BY",  "gen_create_id"  ) \
                    ]

        self.track_tup_list = [( "owner_id", "OWNERINFO_MYSQL_PKID"), \
                     ( "phone", "", "select_phone"), \
                     ( "description", "TRACK_DESCRIPTION"), \
                     ( "creator_id", "creator_id", "gen_create_id"), \
                     ( "creator_name", "DESCRIPTION"  ), \
                     ( "create_time", "CREATED_TIME", "timestamp_convert"), \
                     ( "org_code", "ORG_CODE"), \
                     ( "org_name", "ORG_SHORT_NAME"), \
                    ]

    def select_phone(self, owner):
        if owner['MOBILE_PHONE'] is not None:
            return owner['MOBILE_PHONE']
        elif owner['HOME_PHONE'] is not None:
            return owner['HOME_PHONE']
        elif owner['OFFICE_PHONE'] is not None:
            return owner['OFFICE_PHONE']
        else:
            return None

    def timestamp_convert(self, owner):
        if owner['CREATED_TIME'] is not None:
            #return time.strftime("%Y-%m-%d %H:%M:%S", time.strptime(owner['CREATED_TIME'], "%Y%m%d%H%M%S"))
            #print(time.strptime(owner['CREATED_TIME'], "%Y%m%d%H%M%S"))
            #print(time.strftime("%Y-%m-%d %H:%M:%S", time.strptime(owner['CREATED_TIME'], "%Y%m%d%H%M%S")))
            return time.strftime("%Y-%m-%d %H:%M:%S", time.strptime(owner['CREATED_TIME'], "%Y%m%d%H%M%S"))
            #return owner['CREATED_TIME'].strftime("%Y%m%d%H%M%S")
        return None

    def date_time_transfer(self, owner):
        if owner["CREATED_TIME"] is not None:
            return  owner["CREATED_TIME"].strftime("%Y-%m-%d %H:%M:%S")
        return None

    def transfer_owner_type(self, owner):
        if owner["OWNER_TYPE"] == 0:
            return 0
        else:
           return 3

    def gen_create_id(self, owner):
        user_code = owner["USER_CODE"]
        if user_code == None or not user_code.isdigit():
            return None
        
        return str( 1000000000000000 + int(user_code) )

    def gen_sql_dict(self, owner, dict_mapping):
        sql_dict = {}
        for field_tup in dict_mapping:
            value = None
            if len(field_tup) <= 2:
                value = owner[  field_tup[1]  ]
            elif len( field_tup ) > 2:
                fun = field_tup[2]
                value = getattr(self, fun)( owner )
            if value is None:
                continue
            sql_dict[ field_tup[0] ] = value

        return sql_dict

    def check_standard_id( self, standard_id, oracle_pkid ):
        #check mysql exist
        sql = "select * from owner_info where standard_house_id = %d" %standard_id
        res = self.mysql.query_sql( sql ) 
        if len(res) != 0:
            if len(res) > 1 :
                print('unexpected two tuples containing same standard_house_id' + standard_id)
            ORACLE_MYSQL_DICT[oracle_pkid]=res[0]['id']
            #print(ORACLE_MYSQL_DICT[oracle_pkid])
            return False
 
        #check is valud in hdic
        return True
    def check_ownerinfo_freshness(self, oracle_ownerinfo_pkid, logging) :
        logging.info("checking ownerinfo pkid, oracle pkid %s" % oracle_ownerinfo_pkid)
        sql = "select * from dm_temp where oracle_ownerinfo_pkid = %s" % oracle_ownerinfo_pkid
        res = self.mysql.query_sql(sql)
        if len(res) != 0:
            return False
        return True


    def run(self):
        self.batch( self.owner_list, self.name )

    def batch_track(self, owner_track_list, i, logging) :
        error_fp = open("error/error.log." + str(i), "w")
        normal_fp = open("error/normal.log." + str(i), "w")
        for track in owner_track_list :
            dict = {}
            try:
                #pdb.set_trace()
                logging.info("begin to migration track, info %s" % track)
                #0. check if has migrated
                sql = "select * from dm_temp where oracle_track_pkid = %d" % track['TRACK_PKID']
                res = self.mysql.query_sql( sql )

                if len(res) != 0 :
                    logging.warning("track pkid %s has been handled, skip." % track['TRACK_PKID'])
                    continue

                #1. get oracle owner info pkid to new mysql owner info pkid
                sql = "select * from dm_temp where oracle_ownerinfo_pkid = %s and oracle_track_pkid is null" % track['OWNER_PKID']
                res = self.mysql.query_sql( sql ) 

                if len(res) == 0:
                    logging.warning("HEAVY_ERROR. no mysql ownerinfo mapping relation find, ownerinfo oracle id is %s" % track['OWNER_PKID'])
                    continue

                logging.info("begin to insert into mysql follow up track, info %s" % track)
                track['OWNERINFO_MYSQL_PKID']=res[0]['mysql_ownerinfo_pkid']#mysql_ownerinfo_pkid#ownerinfo_mysql_pkid
                dict = self.gen_sql_dict(track, self.track_tup_list)
                dict['status']='1'
                logging.info(dict)
                self.mysql.insert_dict(table = "owner_follow_up", argv=dict)
                logging.info("SUCCESS for new mysql follow up insert. oracle track pkid %s, data %s" % (track['TRACK_PKID'], dict))

                #get mysql new pkid
                sql = "select max(id) as id from owner_follow_up where owner_follow_up.owner_id = %s" % track['OWNERINFO_MYSQL_PKID']
                res = self.mysql.query_sql(sql)

                mysql_track_pkid = res[0]['id']
                logging.info(res)
                #store relation in temp table
                temp_dict = {}
                temp_dict['oracle_ownerinfo_pkid']=track['OWNER_PKID']
                temp_dict['oracle_track_pkid']=track['TRACK_PKID']
                temp_dict['mysql_ownerinfo_pkid']=track['OWNERINFO_MYSQL_PKID']
                temp_dict['mysql_followup_sh_id']=track['STANDARD_ID']
                temp_dict['mysql_followup_pkid']=mysql_track_pkid
                #pdb.set_trace()
                self.mysql.insert_dict(table = "dm_temp", argv = temp_dict)

                logging.info("SUCCESS for ownerinfo follow up migration, oracle pkid %s, mysql pkid %s" % (track['TRACK_PKID'], mysql_track_pkid))

            except Exception, e:
                pdb.set_trace()
                logging.warning("exception happens for ownerinfo follow up migration. oracle pkid %s. ERROR %s" % (track['TRACK_PKID'], e))
Пример #3
0
class Owner_import( threading.Thread ):
    
    def __init__(self, list, name):
        threading.Thread.__init__(self)
        self.owner_list = list
        self.nams = name
        self.oracle = Oracle( user = "******", passwd = "hl_q" , host = "172.16.6.19:1521/HLSALES" )
        #self.oracle = Oracle( user = "******", passwd = "WZCXK20AKGD0P0OL" , host = "172.16.6.114:1521/PROCINST" )
        #self.mysql = Mysql( user = "******", passwd = "5664922ed6404022b4b192499dc56399" , host = "10.10.6.21", port = 6623, db = "biz_huadong", charset = 'utf-8' )
        self.mysql = Mysql( user = "******", passwd = "123456" , host = "172.30.11.240", port = 3306, db = "biz_huadong", charset = 'utf8' )

#        self.mysql = Mysql( user = "******", passwd = "5664922ed6404022b4b192499dc56399" , host = "10.10.6.21", port = 6623, db = "biz_huadong", charset = 'utf8' )
        # new_table_field_name,  old_field_name, transfer by function or null skip
        self.tup_list = [( "standard_house_id", "HSTANDARD_ID"), \
                     ( "name", "OWNER_NAME"), \
                     ( "phone", "MOBILE_PHONE"), \
                     ( "home_phone", "HOME_PHONE"), \
                     ( "office_phone", "OFFICE_PHONE" ), \
                     ( "owner_type", "OWNER_TYPE",  "transfer_owner_type"  ), \
                     ( "qq", "QQ" ), \
                     ( "STATUS", "IS_VALID"), \
                     ( "create_time", "CREATED_TIME", "date_time_transfer"), \
                     ( "creator_id", "CREATED_BY",  "gen_create_id"  ) \
                    ]
    def date_time_transfer(self, owner):
        if owner["CREATED_TIME"] is not None:
            return  owner["CREATED_TIME"].strftime("%Y-%m-%d %H:%M:%S")
        return None

    def transfer_owner_type(self, owner):
        if owner["OWNER_TYPE"] == 0:
            return 0
        else:
           return 3

    def gen_create_id(self, owner):
        user_code = owner["USER_CODE"]
        if user_code == None or not user_code.isdigit():
            return None
        
        return str( 1000000000000000 + int(user_code) )

    def gen_sql_dict(self, owner):
        sql_dict = {}
        for field_tup in self.tup_list:
            value = owner[  field_tup[1]  ]
            if len( field_tup ) > 2:
                fun = field_tup[2]
                value = getattr(self, fun)( owner )
            if value is None:
                continue
            sql_dict[ field_tup[0] ] = value

        return sql_dict

    def check_standard_id( self, standard_id, oracle_pkid ):
        global ORACLE_MYSQL_DICT
        #check mysql exist
        sql = "select * from owner_info where standard_house_id = %d" %standard_id
        res = self.mysql.query_sql( sql ) 
        if len(res) != 0:
            if len(res) > 1 :
                print('unexpected two tuples containing same standard_house_id' + standard_id)
            ORACLE_MYSQL_DICT[oracle_pkid]=res[0]['id']
            return False
 
        #check is valud in hdic
        
        return True


    def run(self):
        self.batch( self.owner_list, self.name )

    def batch( self, owner_list, i):
        error_fp = open( "error/error.log." + str(i), "w" )
        normal_fp = open( "error/normal.log." + str(i), "w" )
        for owner in owner_list:
            dict = {}
            try:
                standard_id = owner["HSTANDARD_ID"]
                if not self.check_standard_id( standard_id, owner['PKID'] ):
                   continue
                dict = self.gen_sql_dict( owner )
                self.mysql.insert_dict( table = "owner_info", argv = dict )
                normal_fp.write( str(dict["standard_house_id"]) + "\n" )

                #check again to get newly inserted tuple id
                self.check_standard_id( standard_id, owner['PKID'] )
            except Exception,e:
                error_fp.write( str( standard_id  ) + "\n" )
        error_fp.close()
        normal_fp.close()