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 _fill_in_categories(datas_path): """Fill in database of categories.""" cat_fr = str(datas_path / "categories_fr.json") with open(cat_fr, "r", encoding="utf8") as file: categories = json.load(file) for name in categories: database.execute(f"INSERT INTO category (title) VALUES ('{name}')") print("Categories done.")
def _create_foodlik(): """Create the foodlik database.""" database.connect("root") database.execute("DROP DATABASE IF EXISTS foodlik") database.execute("CREATE DATABASE foodlik") database.close() print("database created.")
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 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 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 _insert_product(product): """Insert a product into the database.""" name = product["name"] descr = product["description"] stores = product["stores"] url = product["site_url"] score = product["score"] database.execute("INSERT INTO product (title, description, " "stores, site_url, score) VALUES " f"('{name}', '{descr}', '{stores}'," f" '{url}', '{score}')")
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 save_substitute(self, substitute): """Save the substitute title in the database.""" try: database.execute("INSERT INTO substitute (product_title) " f"VALUES ('{substitute[0]}')") except Exception as error: write_error(error) # to be sure it's the good error. pass # substitute already in the databse. try: database.execute("INSERT INTO product_per_substitute " "(substitute_title, product_title) VALUES " f"('{substitute[0]}', '{self.chosen_product}')") except Exception as error: write_error(error) return ("Erreur: ce substitut est " "déjà dans la base, ou il n'existe pas.") else: return "Substitut sauvegardé."
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 _insert_categorie_per_product(ctg, name): """Insert all categories of a product.""" database.execute("INSERT INTO category_per_product " "(category_title, product_title) " f"VALUES ('{ctg}', '{name}')")
def _create_structure(): """Create the database structure.""" path = str(Path().resolve() / "core" / "back" / "database") database.execute(open(path + "/structure.sql", "r").read(), multi=True) print("structure created.")
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)]