def suggest_substitute(self, product_origin, category_selected):
"""
suggest a substitute with a better nutriscore than the product
selected by the user.
"""
cursor = db.cursor()
query = (f"""SELECT p.*,
GROUP_CONCAT(DISTINCT b.name SEPARATOR ", ") as Brands,
GROUP_CONCAT(DISTINCT s.name SEPARATOR ", ") as Stores,
GROUP_CONCAT(DISTINCT c.name SEPARATOR ", ") as Categories
FROM {self.table_name} p
join productbrand prodbd
on p.idProduct = prodbd.Product_idProduct
join brand b
on b.idBrand = prodbd.Brand_idBrand
join productstore ps
on ps.Product_idProduct = p.idProduct
join store s
on s.idStore = ps.Store_idStore
join productcategory pc
on pc.Product_idProduct = p.idProduct
join category c
on c.idCategory = pc.Category_idCategory
WHERE
(p.nutriscore <= %s AND c.name like
'{category_selected}%')
GROUP BY p.idProduct""")
cursor.execute(query, (product_origin.nutriscore, ))
result = cursor.fetchall()
cursor.close()
return [Product(*data) for data in result]
def is_database_empty(self):
"""return if product table is empty."""
cursor = db.cursor()
query = (f"""SELECT 1 FROM {self.table_name} LIMIT 1""")
cursor.execute(query)
return cursor.fetchall()
def __init__(self):
self.table_name = "`purebeurredbv5`.`ProductCategory`"
self.model = "ProductCategory"
cursor = db.cursor()
cursor.execute(f"""
CREATE TABLE IF NOT EXISTS {self.table_name} (
`Category_idCategory` INT UNSIGNED NOT NULL,
`Product_idProduct` INT UNSIGNED NOT NULL,
PRIMARY KEY (`Category_idCategory`,
`Product_idProduct`),
INDEX `fk_Category_has_Product_Product1_idx`
(`Product_idProduct` ASC),
INDEX `fk_Category_has_Product_Category1_idx`
(`Category_idCategory` ASC),
CONSTRAINT `fk_Category_has_Product_Category1`
FOREIGN KEY (`Category_idCategory`)
REFERENCES `purebeurredbv5`.`Category` (`idCategory`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Category_has_Product_Product1`
FOREIGN KEY (`Product_idProduct`)
REFERENCES `purebeurredbv5`.`Product` (`idProduct`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
""")
cursor.close()
def get_by_id(self, idprod):
""" return a product by id"""
cursor = db.cursor()
query = (f"""SELECT p.*,
GROUP_CONCAT(DISTINCT b.name SEPARATOR ", ") as Brands,
GROUP_CONCAT(DISTINCT s.name SEPARATOR ", ") as Stores
FROM {self.table_name} p
join productbrand prodbd
on p.idProduct = prodbd.Product_idProduct
join brand b
on b.idBrand = prodbd.Brand_idBrand
join productstore ps
on ps.Product_idProduct = p.idProduct
join store s
on s.idStore = ps.Store_idStore
join productcategory pc
on pc.Product_idProduct = p.idProduct
join category c
on c.idCategory = pc.Category_idCategory
WHERE p.idProduct = %s""")
cursor.execute(query, (idprod, ))
product_tuple = cursor.fetchone()
cursor.close()
return Product(*product_tuple)
def get_all(self):
""" return all the store from table database """
cursor = db.cursor()
cursor.execute(f"""
SELECT idStore, name FROM {self.table_name}
""")
collection_tuple = cursor.fetchall()
cursor.close()
return [Store(*data) for data in collection_tuple]
def get_all(self):
""" return all the brand information from database"""
cursor = db.cursor()
cursor.execute(f"""
SELECT idBrand, name FROM {self.table_name}
""")
collection_tuple = cursor.fetchall()
cursor.close()
return [Brand(*data) for data in collection_tuple]
def get_all_from_database(self):
""" return all the category information from database"""
cursor = db.cursor()
cursor.execute(f"""
SELECT idCategory, name FROM {self.table_name}
""")
collection_tuple = cursor.fetchall()
cursor.close()
return [Category(*data) for data in collection_tuple]
def get_all(self):
""" return all product from database"""
cursor = db.cursor()
cursor.execute(f"""
SELECT idProduct, name, description, nutriscore, url
FROM {self.table_name}
""")
collection_tuple = cursor.fetchall()
cursor.close()
return [Product(*data) for data in collection_tuple]
def __init__(self):
self.table_name = "`purebeurredbv5`.`Store`"
self.model = "Store"
self.store_list = []
cursor = db.cursor()
cursor.execute(f"""CREATE TABLE IF NOT EXISTS {self.table_name} (
`idStore` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idStore`))
ENGINE = InnoDB;
""")
cursor.close()
def get_by_id(self, idbra):
""" return a list of brand retrieved by id"""
cursor = db.cursor()
cursor.execute(
f"""
SELECT idBrand, name FROM {self.table_name}
WHERE idBrand = %(brand_id)
""", {"brand_id": idbra})
brand_tuple = cursor.fetchone()
cursor.close()
return Brand(*brand_tuple)
def get_by_id(self, idcat):
""" return a list of category by id"""
cursor = db.cursor()
cursor.execute(
f"""
SELECT idCategory, name FROM {self.table_name}
WHERE idCategory = %(category_id)
""", {"category_id": idcat})
category_tuple = cursor.fetchone()
cursor.close()
return Category(*category_tuple)
def get_by_id(self, idstor):
""" return a store by id provided """
cursor = db.cursor()
cursor.execute(
f"""
SELECT idStore, name FROM {self.table_name}
WHERE idStore = %(store_id)
""", {"idStore": idstor})
store_tuple = cursor.fetchone()
cursor.close()
return Store(*store_tuple)
def __init__(self):
self.table_name = "`purebeurredbv5`.`Product`"
self.model = "Product"
cursor = db.cursor()
cursor.execute(f"""
CREATE TABLE IF NOT EXISTS {self.table_name} (
`idProduct` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(200) NOT NULL,
`description` VARCHAR(400) NOT NULL,
`nutriscore` CHAR(1) NOT NULL,
`url` VARCHAR(200) NULL,
PRIMARY KEY (`idProduct`))
ENGINE = InnoDB;
""")
cursor.close()
def insert_category(self, category_list):
"""insert data in table category"""
cursor = db.cursor()
try:
for i, _value in enumerate(category_list):
query = (f"""INSERT INTO {self.table_name} (name)
VALUES ("{category_list[i].name}") """)
data = category_list[i]
cursor.execute(query, data)
db.commit()
cursor.close()
print(f'Data successfully inserted in table: {self.table_name}')
except Error as err:
print(f'Failed to insert data in MySQL table: {err}')
def insert_productbrand(self, prodstore_list):
""" insert data in association table productbrand"""
cursor = db.cursor()
try:
for i, _value in enumerate(prodstore_list):
query = (f"""INSERT INTO {self.table_name}
(product_idProduct, brand_idBrand)
VALUES (%s, %s)""")
data = prodstore_list[i]
cursor.execute(query, data)
db.commit()
cursor.close()
print(f'Data successfully inserted in table: {self.table_name}')
except Error as err:
print(f'Failed to insert data in MySQL table: {err}')
def save_substitute(self, product_origin, product_substitute):
""" save product origin and product substitute in database """
date_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
cursor = db.cursor()
try:
query = (f"""INSERT INTO {self.table_name}
(idProductOrigin, idProductSubstitute, requestDate)
VALUES (%s, %s, %s)""")
data = (product_origin.idProduct, product_substitute.idProduct,
date_time)
cursor.execute(query, data)
db.commit()
cursor.close()
except Error as err:
print(f'Failed to insert data in MySQL table: {err}')
def get_all(self):
""" get all favorites from database"""
cursor = db.cursor()
cursor.execute(f"""SELECT
idProductOrigin, idProductSubstitute, requestDate
FROM {self.table_name}""")
collection_tuple = cursor.fetchall()
cursor.close()
favorites = []
for data in collection_tuple:
idProductOrigin, idProductSubstitute, requestDate = data
productf, substitutef = ProductManager(), ProductManager()
product = productf.get_by_id(idProductOrigin)
substitute = substitutef.get_by_id(idProductSubstitute)
favorites.append((product, substitute, requestDate))
return favorites
def insert_product(self, product_list):
""" insert product in product table"""
cursor = db.cursor()
try:
for i, _value in enumerate(product_list):
query = (f"""
INSERT INTO {self.table_name}
(name, description, nutriscore, url)
VALUES ("{product_list[i].name}",
"{product_list[i].description}",
"{product_list[i].nutriscore}",
"{product_list[i].url}")
""")
data = product_list[i]
cursor.execute(query, data)
db.commit()
cursor.close()
print(f'Data successfully inserted in table: {self.table_name}')
except Error as err:
print(f'Failed to insert data in MySQL table: {err}')
def __init__(self):
self.table_name = "`purebeurredbv5`.`Favorite`"
self.model = "Favorite"
cursor = db.cursor()
cursor.execute(f"""CREATE TABLE IF NOT EXISTS {self.table_name} (
`idProductOrigin` INT UNSIGNED NOT NULL,
`idProductSubstitute` INT UNSIGNED NOT NULL,
`requestDate` DATETIME NOT NULL,
INDEX `fk_Favorite_Product1_idx` (`idProductOrigin` ASC),
INDEX `fk_Favorite_Product2_idx` (`idProductSubstitute` ASC),
CONSTRAINT `fk_Favorite_Product1`
FOREIGN KEY (`idProductOrigin`)
REFERENCES `purebeurredbv5`.`Product` (`idProduct`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Favorite_Product2`
FOREIGN KEY (`idProductSubstitute`)
REFERENCES `purebeurredbv5`.`Product` (`idProduct`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
""")
cursor.close()
def get_by_category(self, category_selected):
""" return all product by category selected"""
cursor = db.cursor()
query = (f"""SELECT p.*,
GROUP_CONCAT(DISTINCT b.name SEPARATOR ", ") as Brands,
GROUP_CONCAT(DISTINCT s.name SEPARATOR ", ") as Stores
FROM {self.table_name} p
join productbrand prodbd
on p.idProduct = prodbd.Product_idProduct
join brand b on b.idBrand = prodbd.Brand_idBrand
join productstore ps
on ps.Product_idProduct = p.idProduct
join store s
on s.idStore = ps.Store_idStore
join productcategory pc
on pc.Product_idProduct = p.idProduct
join category c
on c.idCategory = pc.Category_idCategory
WHERE c.name like %s GROUP BY p.idProduct""")
cursor.execute(query, (category_selected, ))
collection_tuple = cursor.fetchall()
cursor.close()
return [Product(*data) for data in collection_tuple]
