Python/Openpyxl repeating data

Question

I am creating a program to move all individual quotes into a single quote matrix, using python and openpyxl, but when trying to load the model information it seems to be repeating the models from the first quote. Each quote is a separate excel file, it is confusing me as I'm having no problem getting the quote information, i.e. name, dates, etc. but the list of models is the same for every quote instead of pulling the models from each quote.

Data Structures:

class Model:
    SKU = None
    cost = None
    qty = None
    qty_remaining = None

class Quote:
    name = None
    number = None
    start_date = None
    end_date = None

    models = []

Function to get data from each quote spreadsheet:

def load_quote(filename):
    quote_wb = load_workbook(filename=filename, data_only=True, read_only=True)
    quote_sheet = quote_wb.active


    quote = Quote()

    quote.number = quote_sheet['A2'].value
    quote.number = quote.number[9:len(quote.number)]

    quote.name = quote_sheet['A3'].value
    quote.name = quote.name[14:len(quote.name)]

    quote.start_date = quote_sheet['A6'].value
    quote.start_date = quote.start_date[14:len(quote.start_date)]

    quote.end_date = quote_sheet['A7'].value
    quote.end_date = quote.end_date[12:len(quote.end_date)]


    for row_vals in quote_sheet.iter_rows(min_row=12, max_row=250, min_col=1, max_col=9, values_only=True):
        model = Model()
        if row_vals[0] is not None:
            model.SKU = row_vals[0]
            model.cost = row_vals[4]
            model.qty = row_vals[6]
            model.qty_remaining = row_vals[8]
            quote.models.append(model)
        else:
            continue
        
    return quote

Each quote is formatted the same, with the quote number being in cell A2, name being in cell A3, start date in cell A4, and end date in cell 5.

From row 12 to 250 is each model in this format:

I do not need the Description or Quantity Used, so I ignore those. If the quote_sheet variable changes based on iterating through each file in a folder, and it is getting all the quote information from each file, why is it not getting the model information from each file?

Not sure if the function call is helpful but here that is as well:

quotes = []
for file in os.listdir(os.chdir(directory)):
    if file.endswith(".xlsx"):
        quote = load_quote(file)
        quotes.append(quote)

user_11161432 · Answer

All attributes of Quote and Model are defined on the class level - they are class attributes. Class attributes are shared across all instances of a class.

This is not a problem for immutable data like int or str, because by writing:

q1 = Quote()
q1.name = 'quote_name'

you essentially create a new instance attribute name bound to the instance q1 at runtime, which allows the instance to avoid looking up the Quote.name class attribute when you ask for its name.

However, since Quote.models is a mutable data (list), it is shared across all instances of Quote. If you append to that list, this in-place operation will be reflected across any instance of that class.

You can see this behavior with this short example:

class Quote:
    name = None
    number = None
    start_date = None
    end_date = None

    models = []

q1 = Quote()
q2 = Quote()
q1.models.append('x')
print(q2.models)  # output: ['x']

The easiest way to fix this is to turn Quote.models into a proper instance attribute, by defining it in the __init__ method. This way, each Quote instance is created with an unique, independent list attached to its models attribute:

class Quote:
    name = None
    number = None
    start_date = None
    end_date = None
    
    def __init__(self):
        self.models = []

This should be enough to fix your issue, although I'd really recommend turning all class attributes from Quote and Model into proper instance attributes - for the sake of clarity.

Python/Openpyxl repeating data

1 Answers