Django - Update database entry if it exists or insert new entry combining two tables together (models)
I have an app that merges two excel files into one (data from Students and data from their subjects' grades). Therefore, one table for students and one for the grades. Here is what my models.py looks like:
models.py
class Students(models.Model):
study_program = models.CharField(max_length=40, blank=True, null=True)
registration_number = models.IntegerField(blank=True, null=True)
id_number_1 = models.FloatField(blank=True, null=True)
name = models.CharField(max_length=100, null=True)
surname = models.CharField(max_length=100)
nationality = models.CharField(max_length=10)
.
.
(many more columns here)
.
def __str__(self):
return self.name + ' ' + self.surname
class StudentGrades(models.Model):
student = models.ForeignKey(Students, default=None, on_delete=models.CASCADE)
subject = models.CharField(max_length=40) # Μάθημα (Subject)
subject_code = models.CharField(max_length=20, ) # Κωδ. μαθ. (Subject Code)
student_class = models.CharField(max_length=40, ) # Τμήμα Τάξης (Class)
.
.
(many more columns here)
.
def __str__(self):
return self.subject + ' ' + self.student.name + ' ' + self.student.surname
The primary key that connects these two is the registration number of the student. Each student has a unique registration number. On the views.py I've created two functions that (one for each table) that saves the excel files' content to the database. Here is the views.py:
views.py
This function saves the students records to the database. If the student's row has a registration number that is a match, it updates the record otherwise it inserts it as new.
def update_or_create_student(df):
try:
df = df[0]
except:
return redirect('/dashboard/upload/students')
for i in range(len(df)):
AM = df.iloc[i]['ΑΜ']
if Students.objects.filter(registration_number=AM).exists():
print('updated..')
Students.objects.filter(registration_number=AM).update(
study_program=df.iloc[i]['ΠΣ'],
registration_number=df.iloc[i]['ΑΜ'],
id_number_1=df.iloc[i]['Ακ. Ταυτότητα'],
name=df.iloc[i]['Όνομα'],
surname=df.iloc[i]['Επώνυμο'],
.
.
.
)
else:
print('created..')
Students(
study_program=df.iloc[i]['ΠΣ'],
registration_number=df.iloc[i]['ΑΜ'],
.
.
.
).save()
This function now does the same thing for the student's grades.
def updated_or_create_grades(df):
try:
df = df[0]
except:
return redirect('/dashboard/upload/grades')
student_id = None
for i in range(len(df)):
reg_no = df.iloc[i]["AM"]
if Students.objects.filter(registration_number=reg_no).exists():
student_id = Students.objects.get(registration_number=reg_no)
if StudentGrades.objects.filter(registration_number=df.iloc[i]["AM"]).exists():
print('updated..')
StudentGrades.objects.filter(student=student_id).update(
subject_code=df.iloc[i]['Κωδ. μαθ.'],
subject_of_academic_year=df.iloc[i]['Μάθημα Ακ. Έτους'],
student_class=df.iloc[i]['Τμήμα Τάξης'],
.
.
.
else:
print('created..')
StudentGrades(
student=student_id,
subject_code=df.iloc[i]['Κωδ. μαθ.'],
subject_of_academic_year=df.iloc[i]['Μάθημα Ακ. Έτους'],
student_class=df.iloc[i]['Τμήμα Τάξης'],
.
.
.
).save()
Here is the problem. This works only if the relationship is 1:1 (one student has one subject/grade). And this was the original functionality of the app. However, I want to expand this and include many subjects/grades (therefore 1:N, each student has attended multiple subjects). There is no need to create a third model/table, because each new grade excel file has the exact same columns formatting.
I want to modify the code as to include records from all the grades of all subjects a student has attended. Each subject has a code, so i thought to filter both by registration number and by subject code but i wasn't able to implement it in my function. I don't think there is any need to do any modification in the first function, only in the updated_or_create_grades function. If it is any help, this is the column in the Table for the subject code:
subject_code = models.CharField(max_length=20, )
If i try to insert it as it is, it will only keep the latest record of the student, since it checks for his registration number and it updates it, therefore losing the record from a previous subject/grade. For example, if i upload 10 different excel files (10 different grades), and the student has attended all 10, the database will only keep the latest subject record and discard the rest.
I think your code is limiting df for only first element. input df might have all grades information but you are slicing and fetching only first element?
df = df[0]