How to overwrite all objects in Django database after uploading an excel file?-CodePudding

So I have a Leaderboard page and a Django model such as:

models.py

class Leaderboard(models.Model):
    rank = models.IntegerField()
    team_name = models.CharField(max_length=100)
    score = models.IntegerField()

The thing I'm confused is how can I write the views.py function so that whenever I make a request and upload a new excel file to the views.py it would overwrite the old objects inside of the database and fill the database with the new data. I'm not sure if this is the correct way of creating a manual leaderboard but it's the only way I could think of currently.

What I could think of so far:

views.py

def update_leaderboard(request):
    new_excel = request.body
    data = pd.read_excel(new_excel)
    # Overwrite the database here
    ...

ADDITIONAL INFORMATION

I'm using Django Rest Framework as well in this case, because I'm using React for my frontend. So the views might come out a little different (?)

CodePudding user response：

You could try something like this:

form . models import Leaderboard

def update_leaderboard(request):
    new_excel = request.body
    data = pd.read_excel(new_excel)
    # Overwrite the database here
    ...
    
    # Create a list of dictionaries from a DF
    records = data.to_dict('records')
    
    # Iterate over the list and update each teams records i.e. rank & score
    for r in records:
        team_name = r['team_name']
        rank = r['rank']
        score = r['score']

        Leaderboard.objects.get(team_name=team_name).update(rank=rank, score=score)

        **OR**

        # Using bulk_create and 
        # Deleting all existing records and creatinge new ones from the spreadsheet data.
        Leaderboard.objects.all().delete()

        leaderboard_objs= [ 
             Leaderboard(
                 team_name=r['team_name'],
                 rank=r['rank'],
                 score=r['score'],
                 )
                 for r in records
        ]

    Leaderboard.objects.bulk_create(leaderboard_objs)

Note:

*For the first method(Using update) - If a new team is added to your dataset this will break when you call 'Leaderboard.objects.get' because it won't find the team you are trying to update if it is new. You could either use a try except or if else statement to get around this otherwise just use the second method of deleting the existing records and creating new ones.

*If you are updating/creating a large data set you would want to use the bulk_update() or bulk_create() methods instead of using create() or update(). https://docs.djangoproject.com/en/4.0/ref/models/querysets/#django.db.models.query.QuerySet.bulk_update