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]