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))
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()