def checks_if_substituted(self, id_food):
"""checks if the product is already substituted"""
query = "SELECT * FROM favorites WHERE id_food = %s" % id_food
db = ConnectBdd()
food_search = db.execute_mysql_sel(query)
db.destroy_mysql()
return food_search
def select_product(self, id_category):
""" select product in bdd purbeurre """
query = "SELECT idfood, name, dsc, cat_s2, shop, nutriscore FROM pur_beurre.food " \
"WHERE cat_s2 IN (SELECT " \
"ids2_categorie FROM pur_beurre.s2_category " \
"WHERE s1_category_id IN (SELECT idsous_categorie FROM " \
"pur_beurre.s1_category WHERE s0_category_id=%s )) " \
"ORDER BY RAND () LIMIT 10" % id_category
db = ConnectBdd()
self.product = db.execute_mysql_sel(query)
db.destroy_mysql()
def select_all_category(self):
"""select category"""
query = "SELECT s2_category.name, s1_category.name, s0_category.name " \
"FROM pur_beurre.s2_category " \
"INNER JOIN " \
"pur_beurre.s1_category " \
"ON s1_category_id = idsous_categorie " \
"INNER JOIN pur_beurre.s0_category " \
"ON s0_category_id = idcategorie "
db = ConnectBdd()
self.all_category = db.execute_mysql_sel(query)
db.destroy_mysql()
def select_favorite(self):
"""select category"""
query = "SELECT food.name, food.nutriscore " \
"FROM pur_beurre.food WHERE idfood = (" \
"SELECT id_food FROM pur_beurre.favorites " \
"WHERE id_food = food.idfood " \
"UNION " \
"SELECT id_substitute FROM pur_beurre.favorites " \
"WHERE id_substitute = food.idfood)"
db = ConnectBdd()
self.favorites = db.execute_mysql_sel(query)
db.destroy_mysql()
def select_substitute(self, id_food):
""" select product in bdd purbeurre """
self.id_food = self.get_id_food(id_food)
self.nutriscore = self.get_nutriscore(id_food)
list_nutriscore = self.create_list_nutriscore(self.nutriscore)
list_nutriscore = tuple(list_nutriscore)
query = "SELECT idfood, name, dsc, cat_s2, shop, nutriscore FROM food " \
"WHERE cat_s2=(SELECT cat_s2 FROM " \
"pur_beurre.food WHERE idfood=%s) AND idfood != %s AND nutriscore IN %s" \
"ORDER BY nutriscore " \
"LIMIT 5" % (self.id_food, self.id_food, list_nutriscore)
db = ConnectBdd()
self.substitute = db.execute_mysql_sel(query)
db.destroy_mysql()
def insert_cat(self):
"""insert category in mysql"""
db = ConnectBdd()
for row in self.category:
obj = row[0]
query = "INSERT INTO s0_category (name) VALUES (\"%s\")" % obj
print(query)
db.execute_mysql_ins(query)
db.destroy_mysql()
def insert_s2_cat(self):
"""insert category in mysql"""
db = ConnectBdd()
for row in self.s2category:
name = row[0]
s1 = row[1]
query = "INSERT INTO s2_category (name, s1_category_id) VALUES (\"%s\", %s)" % (name, s1)
print(query)
db.execute_mysql_ins(query)
db.destroy_mysql()
def save_substitute(self, id_substitute):
"""save substitute"""
id_substitute = self.get_id_substitute(id_substitute)
if not self.checks_if_substituted(self.id_food):
query = "INSERT INTO favorites (id_food, id_substitute) " \
"VALUES (%s, %s)" % (self.id_food, id_substitute)
db = ConnectBdd()
db.execute_mysql_ins(query)
db.destroy_mysql()
return SUB_SAVED
else:
return SUB_EXIST
def insert_food(self):
""" insert food in mysql """
db = ConnectBdd()
count_food = 0
for food in self.foods:
if "" not in food.values():
query = (
"INSERT INTO food (name, dsc, cat_s2, shop, url_page, nutriscore)"
"VALUES (%(product_name_fr)s, %(generic_name_fr)s, %(id_s2_category)s, %(stores)s,"
" %(url)s, %(nutrition_grade_fr)s)")
db.execute_mysql_ins_data(query, food)
else:
count_food += 1
print(count_food)
db.destroy_mysql()
def select_category_s0(self):
"""select category"""
query = "SELECT * FROM s0_category"
db = ConnectBdd()
self.category = db.execute_mysql_sel(query)
db.destroy_mysql()