How to avoid duplication of records in the database?

Question

There are following models:

class Parameter (models.Model):
    id_parameter = models.IntegerField(primary_key=True)
    par_rollennr = models.IntegerField(default=0)
    par_definition_id = models.IntegerField(default=0) #not FK
    par_name = models.CharField(max_length=200)


class Measurements (models.Model):
    id_measurement = models.AutoField(primary_key=True)
    par_value = models.IntegerField(default=0)
    line = models.ForeignKey(Line, on_delete=models.CASCADE, null=True)
    order = models.ForeignKey(Order, on_delete=models.CASCADE, null=True)
    recipe = models.ForeignKey(Recipe, on_delete=models.CASCADE, null=True)
    parameter = models.ForeignKey(Parameter, on_delete=models.CASCADE, null=True)

I write down as follows: def handle_parameters_upload(request, file):

wb = openpyxl.load_workbook(file, read_only=True)
first_sheet = wb.get_sheet_names()[0]
ws = wb.get_sheet_by_name(first_sheet)

recipe, created = Recipe.objects.get_or_create(par_recipe=ws["B2"].value)

line, created = Line.objects.get_or_create(par_machine=ws["C2"].value)

order, created = Order.objects.get_or_create(par_fa=ws["D2"].value)

data = []
data_par = []
_id = 1
for row in ws.iter_rows(row_offset=1):
    parameter = Parameter()
    parameter.id_parameter = _id
    _id += 1
    parameter.par_rollennr = row[5].value
    parameter.par_definition_id = row[6].value
    parameter.par_name = row[7].value
    data_par.append(parameter)

    measurements = Measurements()
    measurements.par_value = row[8].value
    measurements.line = line
    measurements.order = order
    measurements.parameter = parameter
    measurements.recipe = recipe

    data.append(measurements)
# Bulk create data
Measurements.objects.all().delete()
Parameter.objects.all().delete()
Parameter.objects.bulk_create(data_par)
Measurements.objects.bulk_create(data)
return True

How to avoid duplication of records in the Parameter table and not to lose dependencies by Id. The parameter is 3 fields in the file, each next line has its own, but there are no more than 1052 in total and they are repeated, respectively, every 1052 entries. It looks like this:

rollennr | definitionid | name | value

Where rollennr, definitionid, name are the model parameter fields. In the future, I want to restore this information by approximately the following query:

select * from Measurements as m
join Parameter as p on m.parameter_id = p.id

A temporary solution to the problem, this method beats the data for a very long time:

def handle_parameters_upload_v2(request, file):

    wb = openpyxl.load_workbook(file, read_only=True)
    first_sheet = wb.get_sheet_names()[0]
    ws = wb.get_sheet_by_name(first_sheet)

    recipe, created = Recipe.objects.get_or_create(par_recipe=ws["B2"].value)

    line, created = Line.objects.get_or_create(par_machine=ws["C2"].value)

    order, created = Order.objects.get_or_create(par_fa=ws["D2"].value)

    Measurements.objects.all().delete()
    Parameter.objects.all().delete()

    data = []
    data_par = []

    _id = 0
    for row in ws.iter_rows(row_offset=1):
        _id += 1
        parameter = dict(par_rollennr=row[5].value, par_definition_id=row[6].value, par_name=row[7].value)
        if parameter not in data_par:
            # data_par_all.append(parameter)
            data_par.append(parameter)
        measurements = dict(par_value=row[8].value, line=line, order=order, parameter=parameter, recipe=recipe)
        data.append(measurements)
    else:
        write_param(data_par, data)
    return True


def write_param(data_par, data):
    _id = 0
    for i in data_par:
        i['id_parameter'] = _id
        _id += 1
    Parameter.objects.all().delete()
    parameter = [Parameter(**i) for i in data_par]
    created_param = Parameter.objects.bulk_create(parameter)
    # data_par_obj_all = data_par_obj_all + created_param
    for obj in data:
        obj['parameter'] = find_parameter(obj, created_param)
    Measurements.objects.all().delete()
    measurements = [Measurements(**i) for i in data]
    i = 0
    while i < measurements.__len__():
        tmp = measurements[i: i + 100000]
        Measurements.objects.bulk_create(tmp)
        i += 100000
    # return data_par_obj_all


def find_parameter(obj, data_par_obj_all):
    for data in data_par_obj_all:
        if data.par_rollennr != obj['parameter']['par_rollennr']:
            continue
        if data.par_definition_id != obj['parameter']['par_definition_id']:
            continue
        if data.par_name != obj['parameter']['par_name']:
            continue
        return data

Answer 1

You need to use a better data structure than a list to prevent duplication.

from itertools import zip_longest

def handle_parameters_upload(request, file):
    wb = openpyxl.load_workbook(file, read_only=True)
    first_sheet = wb.get_sheet_names()[0]
    ws = wb.get_sheet_by_name(first_sheet)

    recipe, _ = Recipe.objects.get_or_create(par_recipe=ws["B2"].value)

    line, _ = Line.objects.get_or_create(par_machine=ws["C2"].value)

    order, _ = Order.objects.get_or_create(par_fa=ws["D2"].value)

    # Clear existing data
    Measurements.objects.all().delete()
    Parameter.objects.all().delete()

    parameter_data = set()
    duplicate_measurement_parameter = {}
    measurement_data = []

    for row in ws.iter_rows(row_offset=1):
        parameter = (row[5].value, row[6].value, row[7].value,)

        if parameter in parameter_data:
            duplicate_measurement_parameter[row[8].value] = {
               'par_rollennr': parameter[0],
               'par_definition_id': parameter[1],
               'par_name': parameter[2],
            }
        # Add the parameter
        parameter_data.add(parameter)
        # par_rollennr, par_definition_id, par_name, measurement: par_value   
        measurement_data.append(row[8].value)

    # Bulk create data
    parameters = Parameter.objects.bulk_create([
        Parameter(
            id_parameter=index, 
            par_rollennr=p_data[0], 
            par_definition_id=p_data[1],
            par_name=p_data[2],
       ) for index, p_data in enumerate(parameter_data) 
    ])
    Measurements.objects.bulk_create([
        Measurements(
            line=line,
            order=order,
            recipe=recipe,
            par_value=m_data,
            parameter=parameter or Parameter.objects.filter(**duplicate_measurement_parameter.get(m_data, {})).first(),
        ) 
        for parameter, m_data in zip_longest(parameters, measurement_data)
    ])
    return True