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)