renew_table_safely.py

schema = 'important_schema'
tbl_name = f'important_table'
tmp_tbl = f'{tbl_name}_TMP'
old_tbl = f'{tbl_name}_OLD'
try:
    engine.execute(f'TRUNCATE TABLE {tmp_tbl}') # Make sure tmp table is empty 
    pandas_dataframe.to_sql(tmp_tbl, engine, schema, if_exists='append', index=False)
    engine.execute(f'RENAME TABLE {tbl_name} TO {old_tbl}, {tmp_tbl} TO {tbl_name}, {old_tbl} TO {tmp_tbl}')
    engine.execute(f'TRUNCATE TABLE {tmp_tbl}')
except Exception as e:
    logging.error(e)
    exit(1)

logging.debug(f'Finished inserting dataframe to {tbl_name}')