def delete_record(table_name, key_columns, engine_source, engine_local):
source_ut = sync_helper.query_local_table_latest_time(
table_name, engine_source)
local_ut = sync_helper.query_local_table_latest_time(
table_name, engine_local)
compared_keys_generator = sync_helper.compare_main_keys_generator(
table_name,
key_columns,
engine_source,
engine_local,
latest=max([source_ut, local_ut]))
delete_jobs = Queue()
delete_results = Queue()
delete_status = Queue()
threading_delete = DeleteByKey(delete_jobs, delete_results, delete_status,
table_name, key_columns, engine_local)
log_start = "Start deleting!"
sync_helper.save_log(table_name, 1, log_start, 'DELETE-1', engine_local)
threading_delete.create_threads()
threading_delete.add_jobs(compared_keys_generator)
deleted_num, error_status = threading_delete.process()
return deleted_num, error_status, local_ut
def check_keys_sync(table_name, key_columns, engine_source, engine_local):
"""
检查主键，同步并删除数据
:param table_name:
:param key_columns:
:param engine_source:
:param engine_local:
:return:
"""
source_ut = sync_helper.query_local_table_latest_time(
table_name, engine_source)
local_ut = sync_helper.query_local_table_latest_time(
table_name, engine_local)
compared_keys_generator = sync_helper.compare_main_keys_generator(
table_name,
key_columns,
engine_source,
engine_local,
latest=max([source_ut, local_ut]))
check_jobs = Queue()
check_results = Queue()
check_status = Queue()
threading_check = CompareByKey(check_jobs, check_results, check_status,
table_name, key_columns, engine_source,
engine_local)
log_start = "Start checking!"
sync_helper.save_log(table_name, 1, log_start, 'CHECK-1', engine_local)
threading_check.create_threads()
threading_check.add_jobs(compared_keys_generator)
updated_num, deleted_num, error_status = threading_check.process()
return updated_num, deleted_num, error_status, local_ut
def update_table_by_latest_time(table_name, key_columns, engine_source,
engine_local):
local_ut = sync_helper.query_local_table_latest_time(
table_name, engine_local)
new_record_num, source_ut = sync_helper.query_new_record_amount(
table_name, engine_source, local_ut)
key_values_generator = sync_helper.main_key_values_generator(
table_name, key_columns, engine_source, {
'min': local_ut,
'max': source_ut
}, new_record_num)
update_jobs = Queue()
update_results = Queue()
update_status = Queue()
threading_update = UpdateByKey(update_jobs, update_results, update_status,
table_name, key_columns, engine_source,
engine_local)
log_start = "{} new records start updating!".format(new_record_num)
sync_helper.save_log(table_name, 1, log_start, 'UPDATE-1', engine_local)
threading_update.create_threads()
threading_update.add_jobs(key_values_generator)
updated_num, error_status = threading_update.process()
return local_ut, updated_num, error_status
def remove_deleted_records(param):
schema = param.get('schema')
table_name = param.get('table')
key_columns = param.get('key_columns')
_source_engine = create_db_connection(settings.SOURCE_DB_PARAMS, schema)
_local_engine = create_db_connection(settings.LOCAL_DB_PARAMS,
settings.SCHEMA_DICT.get(schema))
status = {'table_name': table_name, 'start_time': now(), 'status': 'Done'}
if not check_local_table(table_name, _source_engine, _local_engine):
structure_changed_error(table_name, status, _local_engine)
return
deleted_num, delete_error, latest_update_time = delete_record(
table_name, key_columns, _source_engine, _local_engine)
status.update({
'end_time': now(),
'deleted': deleted_num,
'error_info': delete_error,
'latest_update_time': latest_update_time
})
if delete_error:
status.update({'status': 'Break'})
sync_helper.save_log(
table_name, 0,
"Break while deleting with error {}".format(delete_error),
'DELETE-1', _local_engine)
sync_helper.save_log(table_name, 1,
"ALL {} deleted records removed".format(deleted_num),
'DELETE', _local_engine)
save_sync_status(status, _local_engine)
def task(self, key_values):
try:
return update_by_keys(self.table, self.key_columns, key_values,
self.source_engine, self.local_engine)
except Exception as e:
error = str(e)
save_log(self.table, 0,
'Error occur while updating: {}'.format(error),
'UPDATE-0', self.local_engine)
self.status.put(error)
return 0
def task(self, key_values):
try:
return delete_by_keys(self.table, self.key_columns, key_values,
self.local_engine)
except Exception as e:
error = str(e)
save_log(
self.table, 0,
'Error occur while remove deleted records: {}'.format(error),
'DELETE-0', self.local_engine)
self.status.put(error)
return 0
def structure_changed_error(table_name, status, engine):
log = "Table structure has changed, or Table not exists."
sync_helper.save_log(table_name, 0, log, 'TABLE_STRUCTURE', engine)
status.update({
'updated': 0,
'deleted': 0,
'end_time': now(),
'error_info': log,
'status': 'Break'
})
save_sync_status(status, engine)
def task(self, key_values):
try:
deleted = delete_by_keys(self.table, self.key_columns,
key_values[0], self.local_engine)
updated = update_by_keys(self.table, self.key_columns,
key_values[1], self.source_engine,
self.local_engine)
return {"updated": updated, 'deleted': deleted}
except Exception as e:
error = str(e)
save_log(self.table, 0,
'Error occur while check: {}'.format(error), 'CHECK-0',
self.local_engine)
self.status.put(error)
return {"updated": 0, 'deleted': 0}
def check_table_structure(param):
schema = param.get('schema')
table_name = param.get('table')
_source_engine = create_db_connection(settings.SOURCE_DB_PARAMS, schema)
_local_engine = create_db_connection(settings.LOCAL_DB_PARAMS,
settings.SCHEMA_DICT.get(schema))
status = {'table_name': table_name, 'start_time': now(), 'status': 'Done'}
if not check_local_table(table_name, _source_engine, _local_engine):
structure_changed_error(table_name, status, _local_engine)
return
else:
sync_helper.save_log(table_name, 1, "Structure Check passed",
'TABLE_STRUCTURE', _local_engine)
status.update({'updated': 0, 'deleted': 0, 'end_time': now()})
save_sync_status(status, _local_engine)
def update_table(param):
schema = param.get('schema')
table_name = param.get('table')
key_columns = param.get('key_columns')
_source_engine = create_db_connection(settings.SOURCE_DB_PARAMS, schema)
_local_engine = create_db_connection(settings.LOCAL_DB_PARAMS,
settings.SCHEMA_DICT.get(schema))
status = {'table_name': table_name, 'start_time': now(), 'status': 'Done'}
if not check_local_table(table_name, _source_engine, _local_engine):
structure_changed_error(table_name, status, _local_engine)
return
latest_update_time, updated_num, update_error = update_table_by_latest_time(
table_name, key_columns, _source_engine, _local_engine)
status.update({
'latest_update_time': latest_update_time,
'updated': updated_num,
'error_info': update_error,
'end_time': now()
})
if update_error:
status.update({'status': 'Break', 'updated': 0})
sync_helper.save_log(
table_name, 0,
"Break with updating with error {}".format(update_error),
'UPDATE-1', _local_engine)
sql = "DELETE FROM {} WHERE update_time > '{}'".format(
table_name, latest_update_time)
_local_engine.execute(sql)
sync_helper.save_log(table_name, 0,
"Rollback with error {}".format(update_error),
'UPDATE-1', _local_engine)
sync_helper.save_log(table_name, 1, "ALL records updated", 'UPDATE-1',
_local_engine)
save_sync_status(status, _local_engine)