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]