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 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 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 delete_by_keys(table, key_columns, key_values, engine):
nums = len(key_values)
if nums:
print("{}: {} -- {} records deleting!\n".format(
now(), table, nums))
sql = "DELETE FROM {} WHERE {}".format(table, _format_sql(key_columns, key_values))
engine.execute(sql)
_deleted = pd.DataFrame(key_values, columns=key_columns)
_deleted['table_name'] = table
to_sql('sync_deleted_keys', engine, _deleted)
log = "{} records deleted!".format(nums)
save_log(table, 1, log, 'DELETE-0', engine)
return nums
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)
def update_by_keys(table_name, key_columns, key_values, source_engine, local_engine):
if not len(key_values):
return 0
sql = "SELECT * FROM {tb} WHERE {mkv}".format(tb=table_name, mkv=_format_sql(key_columns, key_values))
insert_records = pd.read_sql(sql, source_engine)
nums = len(insert_records)
if nums:
print("{}: {} -- {} new records updating!\n".format(now(), table_name, nums))
# noinspection PyBroadException
try:
to_sql(table_name, local_engine, insert_records)
except Exception:
to_sql(table_name, local_engine, insert_records, chunksize=10)
log = '{} records updated!'.format(nums)
save_log(table_name, 1, log, 'UPDATE-0', local_engine)
return nums
def save_log(table_name, status, log, level, engine):
print("{}: {} -- {}\n".format(now(), table_name, log))
log_record = pd.DataFrame([[table_name, status, log, level]], columns=['table_name', 'status', 'log_info', 'level'])
to_sql('sync_log', engine, log_record)