class Extract:
""" Extract data from OpenFoodFacts. """
def __init__(self):
self.URL = "https://fr.openfoodfacts.org/cgi/search.pl"
self.HEADERS = {"User-Agent": "OC-P5 - GNU/Windows - Version 0.1"}
self.PARAMS = {
"search_simple": 1,
"action": "process",
"json": 1,
"tagtype_0": "countries",
"tag_contains_0": "contains",
"tag_0": "france",
"page_size": 40,
"page": 1,
"sort_by": "unique_scans_n",
}
self.views = Views()
def extract(self):
""" I extract product from OpenFoodFacts """
try:
request = requests.get(
url=self.URL, params=self.PARAMS, headers=self.HEADERS
)
products = request.json()
with open("thesubstitute/off_data_extract.json", "w") as f:
json.dump(products, f)
self.views.display_text(
f"""
REUSSITE de l'Extraction :
{len(products['products'])} produits ont été téléchargés dans le fichier off_data_extract.json."""
)
except Exception as error:
self.view.display_text_error("ECHEC : les produits ne sont pas téléchargés.", f"Type de l'erreur : {error}")
class Database:
""" I'm the database. """
def __init__(self):
""" """
self.connection = Connection()
self.db_name = self.connection.db_name
self.tables = Tables()
self.view = Views()
def db_create(self):
""" I create the database. """
try:
self.connection.execute(
"CREATE DATABASE IF NOT EXISTS {} DEFAULT CHARACTER SET 'utf8'"
.format(self.db_name))
self.view.display_text("REUSSITE : la base est crée.")
except Exception as error:
self.view.display_text_error("ECHEC : la base n'est pas crée.",
f"Type de l'erreur : {error}")
def tables_create(self):
""" I create tables in the database. """
for table_name in self.tables.TABLES:
table_description = self.tables.TABLES[table_name]
try:
self.connection.execute(table_description)
self.view.display_text(
f"REUSSITE : la table {table_name.upper()} est active.")
except Exception as error:
self.view.display_text_error(
"ECHEC : les tables ne sont pas crées.",
f"Type de l'erreur : {error}")
def tables_delete(self):
""" I delete all the tables in the database. """
# self.connection.close()
# self.connection = Connection()
query = "SET FOREIGN_KEY_CHECKS = 0;"
self.connection.execute(query)
for n in range(len(self.tables.tab_names)):
try:
query = f"TRUNCATE TABLE {self.tables.tab_names[n]};"
self.connection.execute(query)
self.view.display_text(
f"REUSSITE : la table {self.tables.tab_names[n].upper()} est remise à zéro."
)
time.sleep(1)
except Exception as error:
self.view.display_text_error(
"ECHEC : problème lors du delete des tables",
f"Type de l'erreur : {error}")
class Load:
def __init__(self):
""" """
self.views = Views()
self.connection = Connection()
self.open_json()
def open_json(self):
""" I open the json. """
with open(
"thesubstitute/off_data_transform.json", encoding="utf-8"
) as json_file:
self.my_products = json.load(json_file)
def load_nutriscore(self):
""" I load the nutriscore and their id into the table. """
try:
query = "INSERT INTO nutriscore (nut_id, nut_type) VALUES (1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'E')"
self.connection.execute(query)
self.connection.commit()
self.views.display_text(
"""
REUSSITE :
Les différents Nutriscore ont été chargés dans la base."""
)
except:
self.views.display_text_error("REUSSITE : Les Nutriscore étaient dans la base.")
def load_data(self):
""" I load all the data from transform.json to their table. """
# Loading the nutriscore
self.load_nutriscore()
for prod_key in list(self.my_products.keys()):
prod_to_load = self.my_products[prod_key]
# Insert Products
if self.read_produits(prod_key) is False:
nut_id = self.read_nutriscore(prod_to_load["nutriscore_grade"][0])
add_product = ("INSERT INTO produits SET prod_id=%s, prod_nom=%s, prod_url=%s, nut_id=%s")
data_product = (prod_key, prod_to_load['product_name_fr'], prod_to_load['url'], nut_id)
self.connection.execute(add_product, data_product)
self.connection.commit()
else:
pass
# Insert Categories
for n in range(len(prod_to_load["categories"])):
# In categories table
if self.read_categorie(prod_to_load["categories"][n]) is False:
add_categorie = ("INSERT INTO categories SET cat_nom=%s")
self.connection.execute(add_categorie, (prod_to_load['categories'][n],))
self.connection.commit()
# In prodcat table
cat_id = self.read_categorie(prod_to_load["categories"][n])
check = self.search_id(
f"SELECT * FROM prodcat WHERE cat_id='{cat_id}' AND prod_id='{prod_key}' "
)
if not (check):
add_prodcat = ("INSERT INTO prodcat SET cat_id=%s, prod_id=%s")
self.connection.execute(add_prodcat, (cat_id, prod_key))
self.connection.commit()
# Insert Marques
for n in range(len(prod_to_load["brands"])):
# In marques table
if self.read_marque(prod_to_load["brands"][n]) is False:
add_marque = ("INSERT INTO marques SET marq_nom=%s")
self.connection.execute(add_marque, (prod_to_load['brands'][n],))
self.connection.commit()
# In prodmarq table
marq_id = self.read_marque(prod_to_load["brands"][n])
check = self.search_id(
f"SELECT * FROM prodmarq WHERE marq_id='{marq_id}' AND prod_id='{prod_key}' "
)
if not (check):
add_prodmarq = ("INSERT INTO prodmarq SET marq_id=%s, prod_id=%s")
self.connection.execute(add_prodmarq, (marq_id, prod_key))
self.connection.commit()
# Insert Shops
for n in range(len(prod_to_load["stores"])):
# In shops table
if self.read_shop(prod_to_load["stores"][n]) is False:
add_shop = ("INSERT INTO shops SET shop_nom=%s")
self.connection.execute(add_shop, (prod_to_load['stores'][n],))
self.connection.commit()
# In prodshop table
shop_id = self.read_shop(prod_to_load["stores"][n])
check = self.search_id(
f"SELECT * FROM prodshop WHERE shop_id='{shop_id}' AND prod_id='{prod_key}' "
)
if not (check):
add_prodshop = ("INSERT INTO prodshop SET shop_id=%s, prod_id=%s")
self.connection.execute(add_prodshop, (shop_id, prod_key))
self.connection.commit()
self.views.display_text(
f"""
REUSSITE du chargement des produits :
{len(self.my_products.keys())} produits sont entrés en base."""
)
def read_categorie(self, value):
""" I search if the category is already in the table. """
query = ("SELECT cat_id FROM categories WHERE cat_nom LIKE %s")
self.connection.execute(query, (value,))
result = self.connection.fetchall()
if len(result) < 1:
return False
else:
return int(result[0][0])
def read_marque(self, value):
""" I search if the brand is already in the table. """
query = ("SELECT marq_id FROM marques WHERE marq_nom LIKE %s")
self.connection.execute(query, (value,))
result = self.connection.fetchall()
if len(result) < 1:
return False
else:
return int(result[0][0])
def read_shop(self, value):
""" I search if the shop is already in the table. """
query = ("SELECT shop_id FROM shops WHERE shop_nom LIKE %s")
self.connection.execute(query, (value,))
result = self.connection.fetchall()
if len(result) < 1:
return False
else:
return int(result[0][0])
def read_produits(self, value):
""" I search if the product is already in the table. """
query = ("SELECT prod_id FROM produits WHERE prod_id LIKE '%s'")
self.connection.execute(query, (int(value),))
result = self.connection.fetchall()
if len(result) < 1:
return False
else:
return int(result[0][0])
def read_nutriscore(self, value):
""" I search if the nutriscore is already in the table. """
query = ("SELECT nut_id FROM nutriscore WHERE nut_type LIKE %s")
self.connection.execute(query, (value,))
result = self.connection.fetchall()
if len(result) < 1:
return False
else:
return int(result[0][0])
def search_id(self, query):
""" I search an ID. """
self.connection.execute(query)
rows = self.connection.fetchall()
return rows
class Save:
""" """
def __init__(self):
self.connection = Connection()
self.model = Model()
self.view = Views()
def option_save(self):
""" I display the saving option. """
option = input("\n Sauvegarder ce substitut (o/n) ? : ")
if option == "O" or option == "o":
return True
elif option == "N" or option == "n":
return False
else:
print("Vous devez entrer un o ou un n")
def saving(self, prod_id):
""" I save the substitut into the sauvegardes table. """
check = self._check(prod_id)
if check is False:
try:
query = f"INSERT INTO sauvegardes SET save_time='{datetime.datetime.now()}', prod_id='{prod_id}'"
self.connection.execute(query)
self.connection.commit()
except:
self.view.display_text("La sauvegarde n'a pas pu se faire.")
else:
self.view.display_text(check)
return False
def save_listing(self):
""" I display the list of all the save. """
self.view.display_text("""
Choisissez la sauvegarde
que vous souhaitez visualiser : \n""")
self.view.display_text("0 - Retour à la page précédente")
query = f"SELECT * FROM sauvegardes"
self.connection.execute(query)
result = self.connection.fetchall()
for n in range(len(result)):
save_id, prod_id, datetime = result[n]
infos = self.model.product_infos(prod_id)
self.view.display_text(
f"{n+1} - Substitut : {infos['prod_name']}, sauvegardé le {datetime}"
)
choice = input("""
votre choix : """)
if not choice.isdigit():
print("""
Vous devez entrer un chiffre
Merci de réessayer.""")
time.sleep(2)
return self.save_listing()
elif choice == "0":
return "0"
elif int(choice) >= 1 and int(choice) <= (len(result)):
save_id = result[int(choice) - 1][0]
return save_id
else:
print("""
Ce numéro de sauvegarde n'existe pas
Merci de réessayer.""")
time.sleep(2)
return self.save_listing()
def save_display(self, save_id):
""" I display the informations of a substitute saved. """
query = f"SELECT * FROM sauvegardes WHERE save_id='{save_id}'"
self.connection.execute(query)
result = self.connection.fetchall()
save_id, prod_id, datetime = result[0]
infos = self.model.product_infos(prod_id)
self.model.sub_prod_infos(infos)
def option_delete(self):
""" I display the delete save option. """
option = input("\n Effacer cette sauvegarde (o/n) ? : ")
if option == "O" or option == "o":
return True
elif option == "N" or option == "n":
return False
else:
print("Vous devez entrer un o ou un n")
def save_delete(self, save_id):
""" I delete a save. """
try:
query = f"DELETE FROM sauvegardes WHERE save_id='{save_id}'"
self.connection.execute(query)
self.connection.commit()
self.view.display_text("La sauvegarde a été effacée.")
except:
self.view.display_text(
"L'effacement de la sauvegade n'a pas pu se faire.")
def _check(self, prod_id):
""" I Check if a value is in a table, if yes I return its id """
query = f"SELECT save_id FROM sauvegardes WHERE prod_id LIKE '{prod_id}'"
self.connection.execute(query)
result = self.connection.fetchall()
if len(result) == 0:
return False
else:
return "Vous avez déjà sauvegardé ce substitut."
class Transform:
def __init__(self):
""" """
self.fields = (
"product_name_fr",
"code",
"categories",
"nutriscore_grade",
"url",
"brands",
"stores",
)
self.data_clean = {}
self.views = Views()
self.open_json()
def open_json(self):
""" I open the extract json. """
with open("thesubstitute/off_data_extract.json",
encoding="utf-8") as json_file:
self.data_extract = json.load(json_file)
def transform_basic(self):
""" I take the fields I want from the extract json. """
for n in range(len(self.data_extract["products"])):
self.data_clean[self.data_extract["products"][n][
self.fields[1]].lower()] = {}
for field in self.fields:
if field != self.fields[1]:
self.data_clean[self.data_extract["products"][n][
self.fields[1]].lower()][field] = self.data_extract[
"products"][n][field].lower()
self.transform_field(self.data_clean)
def transform_field(self, data_clean):
""" I try to clean the data. """
for code in data_clean:
# Categories
list_values = data_clean[code]["categories"].split(",")
list_values = [value.strip(" ") for value in list_values]
data_clean[code]["categories"] = list_values
# Brands
list_values = data_clean[code]["brands"].split(",")
list_values = [value.strip(" ") for value in list_values]
data_clean[code]["brands"] = list_values
# Stores
list_values = data_clean[code]["stores"].split(",")
list_values = [value.strip(" ") for value in list_values]
data_clean[code]["stores"] = list_values
self.create_json(data_clean)
def create_json(self, data_clean):
""" I create the transform json. """
with open("thesubstitute/off_data_transform.json", "w") as fp:
json.dump(data_clean, fp)
self.views.display_text(f"""
REUSSITE de la Transformation :
Les produits ont été téléchargés dans le fichier off_data_transform.json."""
)
class Main:
""" """
def __init__(self):
""" """
self.views = Views()
self.db = Database()
self.model = Model()
self.save = Save()
def main_menu(self):
""" I display the main menu. """
self.clear()
self.views.header()
choice = self.views.main_choice().lower()
if choice == "b":
self.back_menu()
elif choice == "f":
self.front_menu()
elif choice == "q":
sys.exit
else:
self.views.display_text("""
Vous devez taper B, F ou Q
Merci de réessayer.""")
time.sleep(2)
self.main_menu()
def back_menu(self):
""" I display the back-office menu. """
self.clear()
self.views.header_admin()
option = self.views.admin_choice()
if option == "0":
self.main_menu()
elif option == "1":
self.clear()
self.views.header_admin()
self.db.db_create()
self.views.pause()
self.back_menu()
elif option == "2":
self.clear()
self.views.header_admin()
self.db.tables_create()
self.views.pause()
self.back_menu()
elif option == "3":
self.clear()
self.views.header_admin()
self.db.tables_delete()
self.views.pause()
self.back_menu()
elif option == "4":
self.clear()
self.views.header_admin()
extraction = Extract()
extraction.extract()
self.views.pause()
self.back_menu()
elif option == "5":
self.clear()
self.views.header_admin()
transform = Transform()
transform.transform_basic()
self.views.pause()
self.back_menu()
elif option == "6":
self.clear()
self.views.header_admin()
load = Load()
load.load_data()
self.views.pause()
self.back_menu()
elif option == "7":
self.clear()
self.views.header_admin()
extraction = Extract()
transform = Transform()
load = Load()
extraction.extract()
transform.transform_basic()
load.load_data()
self.views.pause()
self.back_menu()
elif option == "Q" or option == "q":
sys.exit
else:
print("""
Vous devez taper A ou Q
Merci de réessayer.""")
time.sleep(2)
self.back_menu()
def front_menu(self):
""" I display the front-office menu. """
self.clear()
self.views.header_front()
option = self.views.app_choice()
if option == "0":
self.main_menu()
elif option == "1":
cat = self.fos_s1()
cat_id, prod = self.fos_s2(cat)
prod_id = self.fos_s3(cat_id, prod)
self.fos_s4(prod_id)
elif option == "2":
self.clear()
self.views.header_front()
save_id = self.save.save_listing()
if save_id == "0":
self.front_menu()
self.clear()
self.views.header_front()
self.save.save_display(save_id)
if self.save.option_delete() is True:
self.save.save_delete(save_id)
self.views.pause()
self.front_menu()
elif option == "Q" or option == "q":
sys.exit
else:
print("""
Vous devez taper A ou Q
Merci de réessayer.""")
time.sleep(2)
self.front_menu()
def clear(self):
""" I clear the terminal. """
if name == "nt":
_ = system("cls")
else:
_ = system("clear")
def fos_s1(self):
""" Front-Office Substitute step 1 : categorie choice. """
self.clear()
self.views.header_front()
cat = self.model.cat_options(self.model.cat_popular())
return cat
def fos_s2(self, cat):
""" Front-Office Substitute step 2 : product to substitute choice. """
self.clear()
self.views.header_front()
prod_list, cat_id = self.model.product_list(cat)
cat_id, prod = self.model.prod_options(prod_list, cat_id)
return cat_id, prod
def fos_s3(self, cat_id, prod):
""" Front-Office Substitute step 3 : substitute choice. """
self.clear()
self.views.header_front()
sub_list = self.model.sub_list(cat_id, prod)
prod_id = self.model.sub_options(sub_list)
if prod_id is None:
self.front_menu()
return prod_id
def fos_s4(self, prod_id):
""" Front-Office Substitute step 4 : product infos. """
self.clear()
self.views.header_front()
prod_infos = self.model.product_infos(prod_id)
self.model.sub_prod_infos(prod_infos)
if self.save.option_save() is True:
if self.save.saving(prod_id) is not False:
self.views.display_text("Ce substitut a été sauvegardé.")
self.views.pause()
self.front_menu()
class Model:
""" """
def __init__(self):
""" """
self.view = Views()
self.connection = Connection()
# =====================================
# ===== Categories list of choice =====
# =====================================
def cat_popular(self):
""" I return the list of the 10 most popular categories. """
list_of_tuple = []
query = f"SELECT cat_id FROM prodcat GROUP BY cat_id ORDER BY COUNT(*) DESC LIMIT 10;"
result = self.to_list_of_integer(self.query(query))
for n in range(len(result)):
query = (
f"SELECT cat_id, cat_nom FROM categories WHERE cat_id = '{result[n]}'"
)
cat_popular = self.to_list_of_tuple(list_of_tuple, query, n)
return cat_popular
def cat_options(self, cat_popular):
""" I display the categories options. """
print(
"""
Choisissez la catégorie dans laquelle se trouve le produit
pour lequel vous cherchez un substitut : \n"""
)
for n in range(len(cat_popular)):
self.view.display_text(f" {cat_popular[n][2]+1} - {cat_popular[n][1]}")
choice = input(
"""
votre choix : """
)
if not choice.isdigit():
print(
"""
Vous devez entrer un chiffre entre 1 et 10
Merci de réessayer."""
)
time.sleep(2)
return self.cat_options(cat_popular)
elif int(choice) >= 1 and int(choice) <= 10:
cat = cat_popular[int(choice) - 1]
return cat
else:
print(
"""
Vous devez entrer un chiffre entre 1 et 10
Merci de réessayer."""
)
time.sleep(2)
return self.cat_options(cat_popular)
# ===================================
# ===== Products list of choice =====
# ===================================
def product_list(self, category):
""" I return the list of product for un categorie. """
list_of_tuple = []
cat_id, cat_nom, n = category
query = f"SELECT prod_id FROM prodcat WHERE cat_id = '{cat_id}';"
result = self.to_list_of_integer(self.query(query))
for n in range(len(result)):
query = (
f"SELECT prod_id, prod_nom FROM produits WHERE prod_id = '{result[n]}'"
)
prod_list = self.to_list_of_tuple(list_of_tuple, query, n)
return prod_list, cat_id
def prod_options(self, prod_list, cat_id):
""" I display the products for category. """
print(
"""
Choisissez le produit pour lequel
vous cherchez un substitut : \n"""
)
for n in range(len(prod_list)):
self.view.display_text(f" {prod_list[n][2]+1} - {prod_list[n][1]}")
choice = input(
"""
votre choix : """
)
if not choice.isdigit():
print(
"""
Vous devez entrer un chiffre
Merci de réessayer."""
)
time.sleep(2)
return self.prod_options(prod_list)
elif int(choice) >= 1 and int(choice) <= len(prod_list):
prod = prod_list[int(choice) - 1]
return cat_id, prod
else:
print(
"""
Vous devez entrer un chiffre de la liste
Merci de réessayer."""
)
time.sleep(2)
return self.prod_options(prod_list)
# ======================================
# ===== Substitutes list of choice =====
# ======================================
def sub_list(self, cat_id, prod):
""" I create a list of substitute for a product """
list_of_tuple = []
prod_id, prod_nom, n = prod
query_sub = f"SELECT nut_id FROM produits WHERE prod_id ='{prod_id}';"
prod_nut = self.to_list_of_integer(self.query(query_sub))
prod_nut = prod_nut[0]
query_sub = f"SELECT p.prod_id, p.prod_nom, p.nut_id FROM produits p INNER JOIN prodcat pc WHERE pc.cat_id ='{cat_id}' AND p.prod_id = pc.prod_id AND p.nut_id < '{prod_nut}' ORDER BY p.nut_id, p.prod_nom ASC LIMIT 5;"
result = self.query(query_sub)
return result
def sub_options(self, sub_list):
""" I display the substitutes options for a product. """
if sub_list == []:
self.view.display_text("Il n'y a pas mieux dans notre base, désolé.")
time.sleep(2)
return None
print(
"""
Choisissez votre substitut : \n"""
)
for n in range(len(sub_list)):
prod_id, prod_name, prod_nut = sub_list[n]
self.view.display_text(f" {n+1} - {prod_name}")
choice = input(
"""
votre choix : """
)
if not choice.isdigit():
print(
"""
Vous devez entrer un chiffre
Merci de réessayer."""
)
time.sleep(2)
return self.sub_options(sub_list)
elif int(choice) >= 1 and int(choice) <= len(sub_list):
prod_id, prod_nom, nut_id = sub_list[int(choice) - 1]
return prod_id
else:
print(
"""
Vous devez entrer un chiffre de la liste
Merci de réessayer."""
)
time.sleep(2)
return self.prod_options(sub_list)
# =============================================
# ===== Substitution product informations =====
# =============================================
def product_infos(self, prod_id):
""" I return all the infos for a product. """
# Select in produits table.
query_prod = f"SELECT prod_id, prod_nom, prod_url, nut_id FROM produits WHERE prod_id ='{prod_id}';"
result = self.query(query_prod)
prod_id, nom, url, nutriscore = result[0]
# Select in shops table.
query_shop = f"SELECT s.shop_nom FROM shops s INNER JOIN prodshop ps ON ps.prod_id = '{prod_id}' AND ps.shop_id = s.shop_id;"
result = self.query(query_shop)
shop_name = [result[n][0] for n in range(len(result))]
# Select in marques table.
query_marque = f"SELECT m.marq_nom FROM marques m INNER JOIN prodmarq pm ON pm.prod_id = '{prod_id}' AND pm.marq_id = m.marq_id;"
result = self.query(query_marque)
marque_name = [result[n][0] for n in range(len(result))]
# Select the nutriscore
query_nutri = f"SELECT nut_type FROM nutriscore WHERE nut_id = '{nutriscore}';"
result = self.query(query_nutri)
nut_type = result[0][0]
prod_infos = {
"prod_id": prod_id,
"prod_name": nom,
"prod_url": url,
"prod_nut": nut_type,
"prod_shop": shop_name,
"prod_marq": marque_name,
}
return prod_infos
def sub_prod_infos(self, prod_infos):
""" I display the infos of a product. """
print(
f"""
Nom du produit : {prod_infos['prod_name']}
Nutriscore : {prod_infos['prod_nut']}
Code du produit : {prod_infos['prod_id']}
Lien vers sa page sur OpenFoodFacts :
{prod_infos['prod_url']}
"""
)
self.view.display_text("====================")
self.view.display_text("C'est un produit :")
for marq in prod_infos["prod_marq"]:
self.view.display_text(f"- {marq}")
self.view.display_text("")
self.view.display_text("Que vous pouvez trouver chez :")
for shop in prod_infos["prod_shop"]:
self.view.display_text(f"- {shop}")
self.view.display_text("====================")
# ========== Methods ==========
def query(self, query):
""" I execute a request and return a result. """
self.connection.execute(query)
return self.connection.fetchall()
def to_list_of_integer(self, l_o_t):
""" Transform a list of tuple (l_o_t) to a list of integer (l_o_i) """
l_o_i = [[str(x) for x in tup] for tup in l_o_t]
l_o_i = ["".join(i) for i in l_o_i]
l_o_i = [int(i) for i in l_o_i]
return l_o_i
def to_list_of_tuple(self, list_of_tuple, query, n):
""" Create the list of tuple and add the ranking """
value = self.query(query)
value = value[0]
rank = list(value)
rank.append(n)
value = tuple(rank)
list_of_tuple.append(value)
return list_of_tuple
