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