def get_favourites():
cursor_wrapper = CursorWrapper()
sql = "SELECT * FROM PurBeurre.favourites INNER JOIN PurBeurre.foods ON PurBeurre.favourites.food_id = PurBeurre.foods.food_id "
with cursor_wrapper as cursor:
cursor.execute(sql, commit=False)
favourites_list = cursor.fetchall()
favourites = []
for item in favourites_list:
food_dict = {
'food_id': item[3],
'category_id': item[4],
'name': item[5],
'ingredients': item[6],
'additives': item[7],
'nutrients': item[9],
'nutriscore': item[8],
'labels': item[10],
'stores': item[11],
'barcode': item[12],
'url': item[13],
'favourite': True
}
favourite = Favourite(food_dict, item[2])
favourites.append(favourite)
return favourites
def get_substitutes(cat_id, nutriscore):
cursor_wrapper = CursorWrapper()
sql = f"SELECT * FROM PurBeurre.foods WHERE category_id={cat_id} AND nutriscore < '{nutriscore}' ORDER BY nutriscore;"
with cursor_wrapper as cursor:
cursor.execute(sql, commit=False)
item_list = cursor.fetchall()
items = []
for item in item_list:
item_dict = {
'food_id': item[0],
'category_id': item[1],
'name': item[2],
'ingredients': item[3],
'additives': item[4],
'nutrients': item[6],
'nutriscore': item[5],
'labels': item[7],
'stores': item[8],
'barcode': item[9],
'url': item[10],
'favourite': is_favourite_in_db(item[0])
}
food = Item(item_dict)
items.append(food)
return items
def get_foods_as_list_of_objects(category_id):
cursor_wrapper = CursorWrapper()
sql = "SELECT * FROM foods WHERE category_id = {}".format(category_id)
with cursor_wrapper as cursor:
cursor.execute(sql, commit=False)
foods_tuple = cursor.fetchall()
foods = []
for item in foods_tuple:
food_dict = {
'food_id': item[0],
'category_id': item[1],
'name': item[2],
'ingredients': item[3],
'additives': item[4],
'nutrients': item[6],
'nutriscore': item[5],
'labels': item[7],
'stores': item[8],
'barcode': item[9],
'url': item[10],
'favourite': is_favourite_in_db(item[0])
}
food = Item(food_dict)
foods.append(food)
return foods
def is_favourite_in_db(food_id):
cursor_wrapper = CursorWrapper()
sql = "SELECT * FROM PurBeurre.favourites WHERE food_id = {}".format(
food_id)
with cursor_wrapper as cursor:
cursor.execute(sql, commit=False)
item_list = cursor.fetchall()
return bool(item_list)
def get_categories():
cursor_wrapper = CursorWrapper()
sql = "SELECT category_id, category_name from categories"
with cursor_wrapper as cursor:
cursor.execute(sql, commit=False)
categories_tuple = cursor.fetchall()
categories = []
for cat in categories_tuple:
categorie = {'id': cat[0], 'name': cat[1]}
categories.append(categorie)
return categories
def get_foods(category_id):
cursor_wrapper = CursorWrapper()
sql = "SELECT food_id, food_name FROM foods WHERE category_id = {}".format(
category_id)
with cursor_wrapper as cursor:
cursor.execute(sql, commit=False)
# categories_tuple = cursor.fetchall()
categories = Category.from_rows(cursor.fetchall())
# categories = []
# for cat in categories_tuple:
# categorie = {'id': cat[0], 'name': cat[1]}
# categories.append(categorie)
return categories
def get_item(food_id):
cursor_wrapper = CursorWrapper()
sql = "SELECT * FROM PurBeurre.foods WHERE food_id = {}".format(food_id)
with cursor_wrapper as cursor:
cursor.execute(sql, commit=False)
item_list = cursor.fetchall()
item_dict = {
'food_id': item_list[0][0],
'category_id': item_list[0][1],
'name': item_list[0][2],
'ingredients': item_list[0][3],
'additives': item_list[0][4],
'nutriscore': item_list[0][5],
'nutrients': item_list[0][6],
'labels': item_list[0][7],
'stores': item_list[0][8],
'barcode': item_list[0][9],
'url': item_list[0][10],
}
return item_dict
def reset_favourite(food_id):
cursor_wrapper = CursorWrapper()
sql = "DELETE FROM PurBeurre.favourites WHERE food_id ={}".format(food_id)
with cursor_wrapper as cursor:
cursor.execute(sql, commit=True)
def set_favourite(food_id):
cursor_wrapper = CursorWrapper()
sql = "INSERT INTO PurBeurre.favourites (food_id) VALUES ({})".format(
food_id)
with cursor_wrapper as cursor:
cursor.execute(sql, commit=True)
def process_update(notifier=print):
wrapper = CursorWrapper()
empty_db(wrapper)
wrapper.reload()
get_categories(wrapper, notifier=notifier)
wrapper.close()