def find_substitute(self): """Method used to find the healthiest substitute according to the targeted category.""" products_list = None while not products_list: self.get_targeted_category() db.connect() db.execute( """ SELECT product_id, nutriscore_id FROM Product_per_category INNER JOIN Product ON Product.id = product_id WHERE category_id = %s AND nutriscore_id < %s ORDER BY nutriscore_id """, ( self.category_id, self.nutriscore, )) products_list = db.fetch(True) db.disconnect() self.category_concordance += 1 return products_list[0][0]
def get_targeted_category(self): """Method getting the most targeted category of a product.""" db.execute( """ SELECT Category.id, ( SELECT COUNT(Product_per_category.product_id) FROM Category AS category_duplicate INNER JOIN Product_per_category ON Product_per_category.category_id = category_duplicate.id WHERE Category.id = category_duplicate.id ) AS products_count FROM Product INNER JOIN Product_per_category ON Product.id = Product_per_category.product_id INNER JOIN Category ON Category.id = Product_per_category.category_id WHERE Product.id = %s ORDER BY products_count """, (self.id, )) try: self.category_id = db.fetch(True)[self.category_concordance][0] except IndexError: return
def get_products_id(self): """Method returning all IDs matching 'category_id' from 'Product_per_category' table.""" db.execute("SELECT * FROM Product_per_category WHERE category_id = %s", (self.category_id,)) products_list = db.fetch(True) self.products_id_list = [product_id for (pk, category_id, product_id) in products_list]
def recreate_db(): """Method used to reinitialize database.""" choice = input("Confirmez-vous la réinitialisation de la base de données? (Y/N): ").lower() if choice == "y": db.execute("DROP DATABASE IF EXISTS purbeurre") create_db()
def select(self, product_id): """Method used to assign selected product and substitute to model.""" db.execute("SELECT * FROM Substitute WHERE product_id = %s", (product_id)) product = db.fetch() self.product = product[1] self.substitute = product[2]
def get_max_pages(self): """Method returning count of all categories from 'Category' table.""" db.execute("SELECT COUNT(*) FROM Category") max_categories = db.fetch()[0] if max_categories % self.page_size == 0: self.max_pages = max_categories // self.page_size else: self.max_pages = (max_categories // self.page_size) + 1
def is_product_saved(self): """Method that checks if product is already in database.""" db.execute("SELECT product_id FROM Substitute WHERE product_id = %s", (self.product.id, )) product = db.fetch() if product: return True else: return False
def get_page(self): """Method adding categories current page to list.""" self.categories_list = [] db.execute("SELECT * FROM Category LIMIT %s,%s", (self.limit, self.page_size)) categories_list = db.fetch(True) for category in categories_list: self.categories_list.append(category)
def save(self): """Method used to save the found substitute in database.""" db.execute( "INSERT IGNORE INTO Substitute(product_id, substitute_id) VALUES(%s, %s)", ( self.product.id, self.substitute.id, )) db.commit() print("Substitut sauvegardé dans la base de données.")
def create_db(): """Method used to create database.""" print("Creating database... This might take a few minutes.") try: db.execute("CREATE DATABASE purbeurre") print("Database has been created.") create_tables() except: print("Database already exists.")
def get_max_pages(self): """Method returning the count of all categories matching 'category_id' from 'Product_per_category' table.""" db.execute("SELECT COUNT(*) FROM Product_per_category WHERE category_id = %s", (self.category_id,)) max_products = db.fetch()[0] if max_products % self.page_size == 0: self.max_pages = max_products // self.page_size else: self.max_pages = (max_products // self.page_size) + 1
def get_page(self): """Method adding categories current page to list.""" self.products_list = [] db.execute("SELECT product_id FROM Product_per_category WHERE category_id = %s LIMIT %s,%s", (self.category_id, self.limit, self.page_size,)) products_list = db.fetch(True) for product in products_list: product = ProductDetails(product[0]) self.products_list.append(product)
def get_product_details(self): """Method getting all product details.""" db.execute("SELECT * FROM Product WHERE id = %s", (self.id, )) product = db.fetch() self.name = product[1] self.brand = product[2] self.nutriscore_id = product[3] self.store = product[4] self.description = product[5] self.url = product[6]
def create_tables(): """Method used to create tables in database.""" with open("app/database/tables.sql", mode="r", encoding="utf-8") as file: sql = file.read() requests = sql.split(";") for line in requests: db.execute(line) print("Tables have been added to database.") data.get_data()
def create_product(product): """Method used to insert product in 'Product' table.""" # Get product name name = product.get("product_name", "").replace("'", " ") if not name: return # Get product brand brand = product.get("brands", "").replace("'", " ") if not brand: return # Get product nutriscore nutriscore = product.get("nutriscore_grade") if not nutriscore: return db.execute("SELECT id FROM Nutriscore WHERE score = %s", (nutriscore, )) nutriscore_id = db.fetch()[0] # Get product store store = product.get("stores", "").replace("'", " ") # Get product description description = product.get("generic_name", "").replace("'", " ") # Get product URL url = product.get("url", "") if not url: return # Get product categories categories = product.get("categories").replace("'", " ").replace("en:", "") if not categories or product.get("categories_lc") != "fr": return # Insert product in database db.execute( """ INSERT IGNORE INTO Product( name, brand, nutriscore_id, store, description, url ) VALUES(%s, %s, %s, %s, %s, %s)""", ( name, brand, nutriscore_id, store, description, url, )) db.commit() # Get product ID and create category db.execute("SELECT LAST_INSERT_ID()") product_id = db.fetch()[0] for category in categories.split(","): create_category(product_id, category)
def create_category(product_id, category): """Method used to insert category in 'Category' table.""" # Insert category in database db.execute("INSERT IGNORE INTO Category(name) VALUES(%s)", (category, )) db.commit() # Get category ID and create product per category db.execute("SELECT id FROM Category WHERE name = %s", (category, )) category_id = db.fetch()[0] db.execute( """ INSERT IGNORE INTO Product_per_category(category_id, product_id) VALUES(%s, %s)""", ( category_id, product_id, )) db.commit()
def get_saved_substitutes(self): """Method getting saved substitutes/products from database.""" self.products_list = [] db.execute("SELECT * FROM Substitute") products_list = db.fetch(True) self.products_id_list = [ product_id for (pk, product_id, substitute_id) in products_list ] for product in products_list: product_id = product[1] substitute_id = product[2] db.execute("SELECT * FROM Product WHERE id = %s", (product_id, )) product_info = db.fetch() db.execute("SELECT * FROM Product WHERE id = %s", (substitute_id, )) substitute_info = db.fetch() self.products_list.append((product_info, substitute_info))
def get_nutriscore(self): """Method getting product's nutriscore ID.""" db.execute("SELECT nutriscore_id FROM Product WHERE id = %s", (self.id, )) self.nutriscore = db.fetch()[0]
def get_categories_id(self): """Method returning all IDs from 'Category' table.""" db.execute("SELECT * FROM Category") categories_list = db.fetch(True) self.categories_id_list = [pk for (pk, name) in categories_list]