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