How do I export my django model into custom csv?

Question

I have a Django app in which I maintain products for a webshop. Since many products have multiple variations and working in Excel is not optimal, I thought Python could help me and make the work easier. However, for the import into my store system I need a CSV file and here comes my problem.

I have a model with five fields where two are multiple fields (django-multiselectfield).

SIZES = (('16', 'Size 16'),
              ('17', 'Size 17'),
              ('18', 'Size 18'),
              ('19', 'Size 19'),
              ('20', 'Size 20'))

COLORS = (('white', 'White'),
              ('black', 'Black'),
              ('blue', 'Blue'),
              ('red', 'Red'),
              ('yellow', 'Yellow'))

class Items(models.Model):
    name = models.CharField(_('Name'), max_length=254, null=True, blank=True, default='',)
    sku = models.CharField(_('SKU'), max_length=254, null=True, blank=True, default='',)
    size = MultiSelectField(_('Size'), choices=SIZES)
    color = MultiSelectField(_('Color'), choices=COLORS)
    price = models.DecimalField(_('Price'), max_digits=7, decimal_places=2)

I created two products to demonstrate my needs In my CSV file, for example, I need a new line for each size and color so that the variation is created correctly in the webshop software.

So my CSV would have to look like this:

"type","sku","attribute 1 name","attribute 1 value","attribute 2 name","attribute 2 value"
"variable","abc01921","size","16","color","white,black,blue"
"variation","abc01921_white_16","size","16","color","white"
"variation","abc01921_black_16","size","16","color","black"
"variation","abc01921_blue_16","size","16","color","blue"
"variable","abc01831","size","16,17","color","black,blue"
"variation","abc01831_black_16","size","16","color","black"
"variation","abc01831_blue_16","size","16","color","blue"
"variation","abc01831_black_17","size","17","color","black"
"variation","abc01831_blue_17","size","17","color","blue"

How do I best implement this successfully? How can I check if it is a variable or a variation and use this information to write the CSV line into the file accordingly?

Answer 1

As I understand correctly you have to generate CSV file from Django database values?

To be able reproduce code without real Django model I created Item that has same fields as your model. CSV generation code should work for Django real model objects as is (mostly... I'm not sure how model return MultiSelectField values).

Create list of example item objects:

class Item:
    def __init__(self, **kw):
        self.name = kw['name']
        self.sku = kw['sku']
        self.size = kw['size']
        self.color = kw['color']
        self.price = kw['price']


items = [
    Item(
        **{
            'name': 'product1',
            'sku': 'abc123',
            'size': ('16', '17'),
            'color': ('green', 'orange'),
            'price': 123
        }
    ),
    Item(
        **{
            'name': 'product2',
            'sku': 'abc321',
            'size': ('61', '71'),
            'color': ('yellow', 'red'),
            'price': 321
        }
    ),
]

Write csv file with all items:

header = ["type", "sku", "attribute 1 name", "attribute 1 value", "attribute 2 name", "attribute 2 value"]
csv_lines = [header]

for item in items:
    csv_lines.append(['variable', item.sku, 'size', ','.join(item.size), 'color', ','.join(item.color)])
    for size_variation in item.size:
        for color_variation in item.color:
            csv_lines.append(['variation', f'{item.sku}_{color_variation}_{size_variation}', 'size', size_variation, 'color', color_variation])


import csv


with open('./csv_file_to_import.csv', 'w', newline='') as csv_file:
    write = csv.writer(csv_file, quoting=csv.QUOTE_ALL)
    write.writerows(csv_lines)

Check csv resulted file:

"type","sku","attribute 1 name","attribute 1 value","attribute 2 name","attribute 2 value"
"variable","abc123","size","16,17","color","green,orange"
"variation","abc123_green_16","size","16","color","green"
"variation","abc123_orange_16","size","16","color","orange"
"variation","abc123_green_17","size","17","color","green"
"variation","abc123_orange_17","size","17","color","orange"
"variable","abc321","size","61,71","color","yellow,red"
"variation","abc321_yellow_61","size","61","color","yellow"
"variation","abc321_red_61","size","61","color","red"
"variation","abc321_yellow_71","size","71","color","yellow"
"variation","abc321_red_71","size","71","color","red"

If users of you Django app don't have access to Django server storage you should make CSV file downloadable via browser instead of save it to disk. @DarkDrifterX99 already answered how to implement CSV response

Answer 2

I have a model with five fields where two are multiple fields (django-multiselectfield)

I need a new line for each size and color so that the variation is created correctly in the webshop software.

I would assume each Product DB entry would be a "variable" and then the "variations" would be manually created when writing to the CSV for each count of the two multiselect fields.