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)]