Access table in sqlite using django and query creation-CodePudding

I want to access data from table1, from my database and to make a query based on other table, using django. For example: access data from table1 (user, date, hour:minutes, city, tag) and table 2 (user, date: hour:minute, latitude, longitudinal) and if the hour:minutes are the same in both table, I need to update the field tag with some values (North, South, East, West). I tried to access the data from table1 with objects.filter on user, date, but I think that it's not a good method, because it doesn't give me the values from the row. I read the documentation from https://docs.djangoproject.com/en/4.0/topics/db/queries/#copying-model-instances but I can not manage to do this correctly and finish it. And for the second part, I was thinking to make something with an if, but still not sure. In the beginning I need to access data from the table1.

Edit: I want to make this changes in the file uploadCoord_File_view, when I upload the excel file. model.py

class Localization(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    date = models.CharField(max_length=10, blank=False, null= False)
    time = models.CharField(max_length=5, blank=False, null= False)
    city = models.CharField(max_length=10, blank=False,, null = True)
    tag = models.CharField(max_length=10, default=None, null = True)

    def __str__(self):
        return f"{self.user}"



class Coordinates(models.Model):
    user = models.ForeignKey(UserAccount, related_name='hr_user', on_delete=models.CASCADE)
    date = models.CharField(max_length=10, blank=False, null= False)
    time = models.CharField(max_length=5, blank=False, null= False)
    latitude = models.CharField(max_length=10, blank=False, null= False)
    longitudinal = models.CharField(max_length=10, blank=False, null= False)

    def __str__(self):
        return f"{self.user}"

view.py

@login_required(login_url='login') 
def uploadLocalization_file_view(request):
        form = CsvFileForm(request.POST, request.FILES)

        if form.is_valid():
            form.save()
            form = CsvFileForm()

            obj = CsvFileModel.objects.get(activated=False)
            with open(obj.file_name.path, 'r') as f:
                reader = csv.reader(f)

                for i, row in enumerate(reader):
                    if i==0: 
                        pass
                    else: 
                       
                        date = row[0]
                        user = User.objects.get(id = user_id)
                        Localization.objects.create(
                            date=date,
                            time = row[1],
                            city = row[2],
                        )
                        t= convert_time2sec(row[1])

                obj.activated=True
                obj.save()
                return redirect('../uploadCoord_file_view')
        return render(request, 'uploadFile.html', {
        'importFileForm': form
    })

@login_required(login_url='login') 
def uploadCoord_file_view(request):
        form = CsvFileForm(request.POST, request.FILES)

        if form.is_valid():
            form.save()
            form = CsvFileForm()

            obj = CsvFileModel.objects.get(activated=False)
            with open(obj.file_name.path, 'r') as f:
                reader = csv.reader(f)

                for i, row in enumerate(reader):
                    if i==0: 
                        pass
                    else: 
                       
                        date = row[0]
                        user = User.objects.get(id = user_id)
                        Coordinates.objects.create(
                            date=date,
                            time = row[1],
                            latitude = row[2],
                            longitudinal = row[3],
                        )
                        t = convert_time2sec(row[1])
                        ###### Here I need to access the table Localization and create the query
                obj.activated=True
                obj.save()
                return redirect('../results')
        return render(request, 'uploadCoordFile.html', {
        'importCoordFileForm': form
    })

CodePudding user response：

I can't figure out in the line user = User.objects.get(id = user_id) where user_id is coming from. Is it coming from reader or can you use user = request.user?

If my assumptions are correct, then I think you can first get the User, UserAcoount and Localization tables first before the for loop:

@login_required(login_url='login') 
def uploadCoord_file_view(request):
    
    # Get the user
    user = request.user

    # Access the Localization table of the specific user
    localization = Localization.objects.get(user=request.user)

    # Access the UserAccount (you did not provide that model, so I am guessing
    user_account = UserAccount.objects.get(user=request.user)

   ...

Then you can do the matching and updating like this:

###### Here I need to access the table Localization and create the query

# Filter all Coordinates that have the same date and user as the Localization table
coordinates = Coordinates.objects.filter(date=localization.date, user=user_account)

# Update the values of all the matches
coordinates.update(latitude='North', longitude='West')

It is best to put all get queries in a try/except statement, otherwise you'll get an error if the get returns no matches:

try:
    # Access the Localization table of the specific user
    localization = Localization.objects.get(user=request.user)
except Localization.DoesNotExist
    # Code to handle what should happen if no match exists