def produit_generator():
"""
Generate products
"""
header = ["Id_prod", "Type"]
# Id Prod is FK from Livres UNION Périodiques
ids_prod_livre = get_column("Id", "CSV/livre.csv")
ids_prod_periodique = get_column("Id", "CSV/numero_periodique.csv")
with open("CSV/produit.csv", "w", encoding="UTF8") as f:
writer = csv.writer(f)
writer.writerow(header)
last_id_livre = 0
for id_prod in ids_prod_livre:
row = [id_prod, "livre"]
writer.writerow(row)
last_id_livre = id_prod
for id_prod in ids_prod_periodique:
id_periodique = int(id_prod) + int(last_id_livre)
row = [str(id_periodique), "periodique"]
writer.writerow(row)
def produit_commande():
"""
Generate contenu_commandes.csv
"""
# get_column is used to respect FK constraint
ids_prod = get_column("Id_prod", "CSV/produit.csv")
ids_commande = get_column("Id_commande", "CSV/commande.csv")
with open("CSV/produit_commande.csv", "w", encoding="UTF8") as f:
writer = csv.writer(f)
# loop over ids_commande to respect FK constraint
for id_commande in ids_commande:
# choose k <= 10 products
k = random.randint(1, 10)
ids_prod_chosen = random.choices(ids_prod, k=k)
# 1 or 2 products for small commandes but 1 for big ones
if (k <= 2):
quantite = random.randint(1, 2)
else:
quantite = 1
for id_prod in ids_prod_chosen:
row = [id_commande, id_prod, quantite]
writer.writerow(row)
def historique_prix_generator():
"""
Generate historique_prix.csv
"""
header = ["Id_prod",
"Date", "Prix"]
# Id Prod is FK from Livres UNION Périodiques
ids_prod = get_column("ID", "CSV/livres.csv")
#ids_prod += get_column("SICI", "CSV/periodiques.csv")
# Limited set of dates
dates = ["17/05/2021", "18/05/2021",
"19/05/2021", "20/05/2021", "21/05/2021"]
with open("CSV/historique_prix.csv", "w", encoding="UTF8") as f:
writer = csv.writer(f)
writer.writerow(header)
# 5 entries by product
for i in range(5):
for id_prod in ids_prod:
# random date
date = dates[i]
row = [id_prod, date, rand_price()]
writer.writerow(row)
def get_average_by_manufacturer(table, manufacturer):
"""
Question: What is the average amount of games in stock of a given manufacturer?
Args:
table (list): data table to work on
manufacturer (str): Name of manufacturer
Returns:
number
"""
manufacturer_found = False
manufacturer_column = 2
in_stock_column = 4
manufacturer_table = []
for line in table:
if manufacturer in line[manufacturer_column]:
manufacturer_table.append(line)
manufacturer_found = True
if manufacturer_found is False:
raise KeyError('Manufacturer not found')
manufacturer_in_stock = common.get_column(manufacturer_table,
in_stock_column)
for item_index, item in enumerate(manufacturer_in_stock):
manufacturer_in_stock[item_index] = int(item)
return (common.my_sum_(manufacturer_in_stock) / len(manufacturer_in_stock))
def print_table(table, title_list):
"""
Prints table with data.
Example:
/-----------------------------------\
| id | title | type |
|--------|----------------|---------|
| 0 | Counter strike | fps |
|--------|----------------|---------|
| 1 | fo | fps |
\-----------------------------------/
Args:
table (list): list of lists - table to display
title_list (list): list containing table headers
Returns:
None: This function doesn't return anything it only prints to console.
"""
columns_length = []
table.insert(0, title_list)
for column_index, column in enumerate(table[0]):
current_max_item_length = 0
current_column = common.get_column(table, column_index)
for item in current_column:
if current_max_item_length < len(item):
current_max_item_length = len(item)
columns_length.append(current_max_item_length)
print_table = '/'
whitespace_border = 1
for columns_index, columns in enumerate(columns_length):
print_table += (columns + whitespace_border * 2) * '-'
print_table += (len(columns_length) - 1) * '-' + '\\\n'
for line_index, line in enumerate(table):
for item_index, item in enumerate(line):
before_spaceing = ' ' * \
((columns_length[item_index] - len(item)) // 2)
after_spaceing = ' ' * \
((columns_length[item_index] - len(item)) // 2)
if (columns_length[item_index] - len(item)) % 2 == 1:
before_spaceing += ' '
print_table += '|' + whitespace_border * ' ' + before_spaceing + \
item + after_spaceing + whitespace_border * ' '
print_table += '|\n'
if line_index != len(table) - 1:
for columns in columns_length:
print_table += '|' + (columns + whitespace_border * 2) * '-'
print_table += '|\n'
print_table += '\\'
for columns_index, columns in enumerate(columns_length):
print_table += (columns + whitespace_border * 2) * '-'
print_table += (len(columns_length) - 1) * '-' + '/'
print(print_table)
def commande_generator():
"""
Generate commande.csv
"""
header = [
"id_commande", "date_commande", "id_panier", "id_client",
"adresse_livraison", "prix", "mode_payement", "effectivement_paye"
]
ids_panier = get_column("id_panier", "CSV/panier.csv")
ids_clients = get_column("id_client", "CSV/panier.csv")
ids_commande = ids_panier[:int(len(ids_panier) / 2)]
with open("CSV/commande.csv", "w", encoding="UTF8") as f:
writer = csv.writer(f)
writer.writerow(header)
# 10000 entries in commande.csv
for i, id_commande in enumerate(ids_commande):
id_panier = id_commande
date_commande = rand_date()
id_client = ids_clients[i]
adresse_livraison = "adresse du client"
prix = rand_price()
if (prix > 2):
mode_payement = "CB"
elif (prix <= 2):
mode_payement = "chèque"
else:
mode_payement = "espèces"
effectivement_paye = "Oui"
row = [
id_commande, date_commande, id_panier, id_client,
adresse_livraison, prix, mode_payement, effectivement_paye
]
writer.writerow(row)
def produit_dans_panier_generator():
"""
Generate produit_dans_panier.csv
"""
header = ["id_produit", "id_panier", "quantite"]
ids_produit = get_column("Id_produit", "CSV/produit_commande.csv")
ids_panier = get_column("Id_commande", "CSV/produit_commande.csv")
quantites = get_column("Quantite", "CSV/produit_commande.csv")
with open("CSV/produit_dans_panier.csv", "w", encoding="UTF8") as f:
writer = csv.writer(f)
writer.writerow(header)
# 10 000 entries in commandes_vals.csv
for id_produit, id_panier, quantite in zip(ids_produit, ids_panier,
quantites):
row = [id_produit, id_panier, quantite]
writer.writerow(row)
def get_counts_by_manufacturers(table):
"""
Question: How many different kinds of game are available of each manufacturer?
Args:
table (list): data table to work on
Returns:
dict: A dictionary with this structure: { [manufacturer] : [count] }
"""
games_by_manufacturer = {}
manufacturers = common.get_column(table, 2)
for manufacturer in manufacturers:
if manufacturer in games_by_manufacturer:
games_by_manufacturer[manufacturer] += 1
else:
games_by_manufacturer[manufacturer] = 1
return games_by_manufacturer
def panier_generator():
"""
Generate panier.csv
"""
header = ["id_panier", "id_client"]
ids_client = get_column("id_client", "CSV/client.csv")
n_client = len(ids_client)
with open("CSV/panier.csv", "w", encoding="UTF8") as f:
writer = csv.writer(f)
writer.writerow(header)
# 10 000 entries in panier.csv
for id_panier in range(1, 10000):
# 10 paniers pour chaque client
id_client = ids_client[id_panier % n_client]
row = [id_panier, id_client]
writer.writerow(row)
def get_longest_name_id(table):
"""
Question: What is the id of the customer with the longest name?
Args:
table (list): data table to work on
Returns:
string: id of the longest name (if there are more than one, return
the last by alphabetical order of the names)
"""
names = common.get_column(table, 1)
ordered_names = common.my_sort_(names)
longest_name_len = 0
longest_name = ""
for name in ordered_names:
if longest_name_len <= len(name):
longest_name_len = len(name)
longest_name = name
id_index = 0
for user in table:
if longest_name in user:
return user[id_index]