def connect(self, db_name="foodlik"): """Connect to the database.""" database.connect(db_name) database.execute("SELECT COUNT(title) FROM CATEGORY;") self.len_categories = database.get_row()[0] database.execute("SELECT COUNT(*) FROM SUBSTITUTE") self.len_substitutes = database.get_row()[0]
def load_substitute_page(self): """Load the substitute page.""" database.execute("SELECT prd.title, prd.description, prd.stores, " "prd.site_url, prd.score FROM product AS prd " f"WHERE prd.title = '{self.chosen_product}'") substitut = database.get_row() database.execute("SELECT product_title FROM product_per_substitute " f"WHERE substitute_title = '{self.chosen_product}'") products = database.get_row(True) return substitut, products
def load_second_substitute(self, title, score): """Find a better product in the smallest product category.""" query = SUBSTITUTE3.replace("*title*", title) database.execute(query) result = database.get_row(True) if result[0][0] == self.chosen_category: return "Désolé, nous n'avons pas trouvé une catégorie plus ciblée." query = SUBSTITUTE4.replace("*ncategory*", result[0][0]) query = query.replace("*score*", str(score)) database.execute(query) return database.get_row(True)
def _load_first_substitute(self, score, title): """Find a better product in the same category.""" query = SUBSTITUTE1.replace("*category*", self.chosen_category) query = query.replace("*score*", str(score)) database.execute(query) result = database.get_row(True) if result: return result query = SUBSTITUTE2.replace("*category*", self.chosen_category) query = query.replace("*score*", str(score)) query = query.replace("*title*", title) database.execute(query) return database.get_row(True)
def load_products(self, page): """Load the products of a category.""" database.execute("SELECT product_title " "FROM CATEGORY_PER_PRODUCT " f"WHERE category_title = '{self.chosen_category}' " f"LIMIT 15 OFFSET {page * 15}") return [prod[0] for prod in database.get_row(True)]
def get_len_products(self): """Return the products len in the current category. Call this method when you initialize a new Category class. """ database.execute("SELECT COUNT(*) " "FROM CATEGORY_PER_PRODUCT " f"WHERE category_title = '{self.chosen_category}'") return database.get_row()[0]
def _fill_in_products_number(datas_path): """Insert the products number of each category. Remove lines that do not contain products. """ database.execute("SELECT title FROM CATEGORY") result = [wrd[0] for wrd in database.get_row(True) if wrd[0]] for category in result: database.execute("SELECT COUNT(*) " "FROM CATEGORY_PER_PRODUCT AS CPP " f"WHERE CPP.category_title='{category}'") database.execute("UPDATE CATEGORY " f"SET product_number = {database.get_row()[0]} " f"WHERE CATEGORY.title = '{category}'") database.execute("DELETE FROM CATEGORY " "WHERE product_number = 0")
def load_product(self): """Load a product.""" database.execute("SELECT * FROM PRODUCT " f"WHERE title = '{self.chosen_product}'") return database.get_row()
def load_categories(self, page): """Load the categories.""" database.execute("SELECT * FROM CATEGORY " f"LIMIT 15 OFFSET {page * 15}") return [wrd[0:2] for wrd in database.get_row(True)]
def load_substitutes_page(self, page): """Load the substitutes.""" database.execute("SELECT * FROM substitute " f"LIMIT 15 OFFSET {page * 15}") return [wrd[0] for wrd in database.get_row(True)]