I have two csv files, pricat.csv which contains objects I need to populate my DB with, and mapping.csv which specifies how the value in pricat.csv must be displayed in my DB, for ex: 'NW 17-18' in pricat.csv has to be 'Winter Collection 2017/2018' in my DB. Here the csvs, first row in both are the headers:
ean;supplier;brand;catalog_code;collection;season;article_structure_code;article_number;article_number_2;article_number_3;color_code;size_group_code;size_code;size_name;currency;price_buy_gross;price_buy_net;discount_rate;price_sell;material;target_area
8719245200978;Rupesco BV;Via Vai;;NW 17-18;winter;10;15189-02;15189-02 Aviation Nero;Aviation;1;EU;38;38;EUR;;58.5;;139.95;Aviation;Woman Shoes
8719245200985;Rupesco BV;Via Vai;;NW 17-18;winter;10;15189-02;15189-02 Aviation Nero;Aviation;1;EU;39;39;EUR;;58.5;;139.95;Aviation;Woman Shoes
source;destination;source_type;destination_type
winter;Winter;season;season
summer;Summer;season;season
NW 17-18;Winter Collection 2017/2018;collection;collection
EU;European sizes;size_group_code;size_group
EU|36;European size 36;size_group_code|size_code;size
EU|37;European size 37;size_group_code|size_code;size
EU|38;European size 38;size_group_code|size_code;size
EU|39;European size 39;size_group_code|size_code;size
EU|40;European size 40;size_group_code|size_code;size
EU|41;European size 41;size_group_code|size_code;size
EU|42;European size 42;size_group_code|size_code;size
4;Boot;article_structure_code;article_structure
5;Sneaker;article_structure_code;article_structure
6;Slipper;article_structure_code;article_structure
7;Loafer;article_structure_code;article_structure
8;Mocassin;article_structure_code;article_structure
9;Sandal;article_structure_code;article_structure
10;Pump;article_structure_code;article_structure
1;Nero;color_code;color
2;Marrone;color_code;color
3;Brandy Nero;color_code;color
4;Indaco Nero;color_code;color
5;Fucile;color_code;color
6;Bosco Nero;color_code;color
In my models.py in Django I have three models: Catalog --> Article --> Variation the attributes of my models are manually named as mapping.csv specifies, for ex: Variation will not have a color_code attribute but color.
To populate the DB I've created a custom Django command which reads the rows in pricat.csv and create istances like this:
x = Catalog.objects.get_or_create(brand=info[2], supplier=info[1], catalog_code=info[3],
collection=map_dict[info[4]],
season=map_dict[info[5]], size_group=map_dict[info[11]],
currency=info[14], target_area=info[20])
y = Article.objects.get_or_create(article_structure=map_dict[info[6]],
article_number=info[7], catalog=x[0])
z = Variation.objects.get_or_create(ean=info[0], article=y[0], size_code=info[12], color=map_col[info[10]],
material=info[19], price_buy_gross=info[15], price_buy_net=info[16],
discount_rate=info[17], price_sell=info[18], size=f'{map_dict[info[11]]} {info[12]}')
info is a list of all the value in a pricat.csv row and map_dict and map_col are two dictionaries I create with two func() from the mapping.csv:
def mapping(map_file):
with open(map_file, 'r') as f:
f = [l.strip('\n') for l in f]
map_dict = {}
for l in f[1:19]:
info = l.strip().split(';')
source = info[0]
destination = info[1]
source_type = info[2]
destination_type = info[3]
map_dict[source] = destination
map_dict[source_type] = destination_type
return map_dict
def mapping_color(map_file):
with open(map_file, 'r') as f:
f = [l.strip('\n') for l in f]
map_dict = {}
for l in f[19:]:
info = l.strip().split(';')
source = info[0]
destination = info[1]
source_type = info[2]
destination_type = info[3]
map_dict[source] = destination
map_dict[source_type] = destination_type
return map_dict
map_dict = mapping('mapping.csv')
map_col = mapping_color('mapping.csv')
I had to create two dict because a single one would have duplicate keys.
The code works fine and the DB is populated as intended, but I feel the way I did the mapping is bad practice, also both my command and funcs relies on indeces so the values in my csvs have to be in that specific order to work. I would greatly appreciate any suggestion on how to improve my code or accomplish this task, I hope my explanation is clear.
EDIT:
class Catalog(models.Model):
brand = models.CharField(max_length=255)
supplier = models.CharField(max_length=255)
catalog_code = models.CharField(max_length=255, default=1, blank=True)
collection = models.CharField(max_length=255)
season = models.CharField(max_length=255)
size_group = models.CharField(max_length=2)
currency = models.CharField(max_length=3)
target_area = models.CharField(max_length=255)
def __str__(self):
return self.brand
def get_articles(self):
return Article.objects.filter(catalog=self.pk)
class Article(models.Model):
article_structure = models.CharField(max_length=255)
article_number = models.CharField(max_length=255)
catalog = models.ForeignKey(Catalog, on_delete=models.CASCADE)
def __str__(self):
return f'{self.article_number} | {self.article_structure}'
class Variation(models.Model):
ean = models.CharField(max_length=255)
article = models.ForeignKey(Article, on_delete=models.CASCADE)
size_code = models.IntegerField()
size = models.CharField(max_length=255, default=0)
color = models.CharField(max_length=255)
material = models.CharField(max_length=255)
price_buy_gross = models.CharField(max_length=255)
price_buy_net = models.FloatField()
discount_rate = models.CharField(max_length=255, default=0)
price_sell = models.FloatField()
def __str__(self):
return f'Ean: {self.ean}, article: {self.article}'
I've created a new mapping()
def mapping(map_file):
with open(map_file, 'r') as f:
f = [l.strip('\n') for l in f]
map_dict = {}
for l in f[1:]:
info = l.strip().split(';')
source = info[0]
destination = info[1]
source_type = info[2]
child_dict = {source: destination}
map_dict[source_type] = map_dict.get(source_type, {source: destination})
map_dict[source_type].update(child_dict)
return map_dict
It returns a nested dict, I'm trying to finda solution using this single nested dict instead of 2 dicts like before.
