0

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 (?)

Owenn
  • 638
  • 1
  • 6
  • 23
  • you need to read your file line by line: https://stackoverflow.com/questions/22169325/reading-excel-file-using-python-how-do-i-get-the-values-of-a-specific-column-wi , parse each variable from your file, and then `delete all Leaderboard objects`, and then `create new Leaderbord objects` with data u've read from your excel file – oruchkin Jun 05 '22 at 08:45
  • @oruchkin how about setting up a loop for each .csv item and use `get_or_create` manager method and update/create accordingly? – JSRB Jun 05 '22 at 12:17
  • @JSRB for loop is a good idea, but he says he wants to `overwrite old objects` so there is no point in `get_or_create`, all ranks in leaderboard would be different each time – oruchkin Jun 05 '22 at 12:20

1 Answers1

1

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

Chris Ward
  • 136
  • 1
  • 8