def insert_products(self, data):
"""Insert data in all tables : Product, Category,
Product_category, Store, Product_store"""
for product in tqdm(data,
desc="Inserting products in database",
total=len(data)):
db.query(
"""INSERT INTO Product(barcode, product_name,
nutriscore, url)
VALUES (:barcode, :product_name,:nutriscore, :url)
ON DUPLICATE KEY UPDATE
barcode=:barcode,product_name=:product_name,
nutriscore=:nutriscore, url=:url;
""", **product)
for store_name in product["store"]:
StoreManager.insert_into_store(store_name)
barcode, store_id = self.last_insert_id(product)
StoreManager.insert_into_product_store(barcode, store_id)
CategoryManager.insert_into_category(product)
barcode, category_id = self.last_insert_id(product)
CategoryManager.insert_into_product_category(barcode, category_id)
def wipe_out(self):
"""Simple query erasing every tables"""
print("Ditching old database...")
db.query(""" DROP TABLE IF EXISTS
Product, Category, Store,
Favorite, Product_category,
Product_Store;
""")
print(cf.green("Database is now clean !"))
def insert_into_store(store_name):
"""Insert the data into the Store table"""
db.query(
"""INSERT INTO Store(id, store_name)
VALUES(null, :store_name)
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id),
store_name=store_name;""",
store_name=store_name.strip(),
)
def insert_into_category(product):
"""Insert data into the Category table"""
category = product["category"]
db.query(
"""INSERT INTO Category(id, category_name)
VALUES(null, :category)
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id),
category_name=:category;""",
category=category,
)
def insert_into_product_store(barcode, store_id):
"""Insert data into the association table"""
db.query(
"""INSERT INTO Product_store(product_barcode, store_id)
values (:barcode, :store_id)
;
""",
barcode=barcode,
store_id=store_id,
)
def insert_into_product_category(barcode, category_id):
"""Insert data into the association table"""
db.query(
"""
INSERT INTO Product_category(product_barcode, category_id)
values (:barcode, :category_id)
;
""",
barcode=barcode,
category_id=category_id,
)
def save_healthy_product_to_favorite(self, event, uh_barcode, sub_product):
"""Insert into the Favorite table a couple of barcode"""
product_barcode = uh_barcode[event]
substitute_barcode = sub_product["barcode"]
db.query("""INSERT INTO Favorite(product_barcode, substitute_barcode)
VALUES(:product_barcode, :substitute_barcode)
ON DUPLICATE KEY UPDATE
product_barcode=:product_barcode,
substitute_barcode=:substitute_barcode;""",
product_barcode=product_barcode,
substitute_barcode=substitute_barcode)
def create_tables(self):
"""Create the Favorite Table"""
db.query(""" CREATE TABLE IF NOT EXISTS Favorite (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
product_barcode BIGINT UNSIGNED NOT NULL,
substitute_barcode BIGINT UNSIGNED NOT NULL,
CONSTRAINT fk_favorite_substitute
FOREIGN KEY (substitute_barcode)
REFERENCES Product(barcode),
CONSTRAINT fk_favorite_product
FOREIGN KEY (product_barcode)
REFERENCES Product(barcode));
""")
def last_insert_id(self, product):
""" This return the last inserted id and the barcode associated"""
id = None
for row in db.query("""SELECT LAST_INSERT_ID() as id"""):
id = row["id"]
barcode = product["barcode"]
return barcode, id
def get_cat():
"""Return the category name"""
category_names = {}
i = 1
for row in db.query("""SELECT Category.category_name
FROM Category
ORDER BY RAND() LIMIT 5;"""):
category_names[i] = row["category_name"]
i += 1
return category_names
def create_tables(self):
"""This function create the Product table
and the association tables related"""
db.query(""" CREATE TABLE IF NOT EXISTS Product (
barcode BIGINT UNSIGNED UNIQUE PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
nutriscore CHAR(1),
url VARCHAR(255));
""")
db.query(""" CREATE TABLE IF NOT EXISTS Product_category (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
product_barcode BIGINT UNSIGNED,
category_id INT UNSIGNED,
CONSTRAINT fk_productbarcode_barcode
FOREIGN KEY (product_barcode)
REFERENCES Product(barcode),
CONSTRAINT fk_categoryid_id
FOREIGN KEY (category_id)
REFERENCES Category(id));
""")
db.query(""" CREATE TABLE IF NOT EXISTS Product_store(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
product_barcode BIGINT UNSIGNED,
store_id INT UNSIGNED,
UNIQUE(product_barcode, store_id),
CONSTRAINT fk_productbarcodestore_barcode
FOREIGN KEY (product_barcode)
REFERENCES Product(barcode),
CONSTRAINT fk_storeid_id
FOREIGN KEY (store_id)
REFERENCES Store(id));
""")
def get_healthier_product_by_category(self, category):
"""Get a A or B rated randomized product to substitute to the unhealthy
one selected"""
input_category = category
healthy_prod_by_cat = {}
for row in db.query("""SELECT Product.product_name, Product.barcode
FROM Product
INNER JOIN Product_category AS pc
ON Product.barcode = pc.product_barcode
INNER JOIN Category ON pc.category_id = Category.id
WHERE Category.category_name = :input_category AND
(Product.nutriscore = 'a' OR Product.nutriscore= 'b')
ORDER BY RAND() LIMIT 5
;""",
input_category=input_category):
healthy_prod_by_cat[row["barcode"]] = row["product_name"]
return healthy_prod_by_cat
def get_product_by_barcode(self, barcode):
"""Return a dictionary with full description
of a product given a barcode"""
pbarcode = barcode
prod_by_barcode = {}
store_list = []
for row in db.query("""SELECT Product.product_name, Product.nutriscore,
Product.url, Product.barcode, Store.store_name
FROM Product
INNER JOIN Product_store AS ps
ON ps.product_barcode=Product.barcode
INNER JOIN Store ON Store.id=ps.store_id
WHERE Product.barcode = :pbarcode;
""",
pbarcode=pbarcode):
store_list.append(row["store_name"])
prod_by_barcode.update(row)
prod_by_barcode["store_name"] = store_list
return prod_by_barcode
def get_all_favorite(self):
""" retrieve all substitute saved by the user"""
fav_name = {}
fav_barcode = {}
uh_barcode = {}
i = 1
for row in db.query("""SELECT Product.product_name, Product.barcode,
Favorite.product_barcode, Favorite.substitute_barcode
FROM Product
INNER JOIN Favorite ON
Favorite.substitute_barcode=Product.barcode
"""):
uh_name = product_manager.get_product_by_barcode(
row["product_barcode"])
fav_name[i] = row["product_name"], row["substitute_barcode"], row[
"product_barcode"], uh_name['product_name']
fav_barcode[i] = row["barcode"], row["product_barcode"]
i += 1
return fav_name, fav_barcode
def get_unhealthy_prod_by_category(self, category):
""" Retrieve bad rated products by user's selected category"""
input_category = category
unhealthy_prod_by_cat = {}
# i = 1
for row in db.query("""SELECT Product.product_name, Product.barcode
FROM Product
INNER JOIN Product_category AS pc
ON Product.barcode = pc.product_barcode
INNER JOIN Category ON pc.category_id = Category.id
WHERE Category.category_name = :input_category AND
(Product.nutriscore = 'e' OR Product.nutriscore= 'd')
ORDER BY RAND() LIMIT 5
;""",
input_category=input_category):
unhealthy_prod_by_cat[row["barcode"]] = row["product_name"]
# unhealthy_prod_by_cat[i] = row["product_name"]
# i += 1
return unhealthy_prod_by_cat
def create_tables(self):
"""Create the Store table"""
db.query(""" CREATE TABLE IF NOT EXISTS Store (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
store_name VARCHAR(255) UNIQUE);
""")
def create_tables(self):
"""Create the Category table"""
db.query(""" CREATE TABLE IF NOT EXISTS Category (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
category_name VARCHAR(255) UNIQUE);
""")
