def db_check():
#this fonction checks if the database pur beurre exists if it does returns 1 else 0
dbcursor.execute(
"SELECT schema_name FROM information_schema.schemata "
"WHERE schema_name = 'pur_beurre';"
)
db_exists = dbcursor.fetchall()
return len(db_exists)
def save(self):
# Saves the id (id_product) attribute of the favorite
# in the Favorites table of the database
dbcursor.execute("USE pur_beurre;")
dbcursor.execute("INSERT IGNORE into Favorites(id_product) "
"VALUES('%s');" % (self.id))
db_pur_beurre.commit()
def save(self):
# Inserts category data in database
categories = self._get_names()
dbcursor.execute("USE pur_beurre;")
command = "INSERT IGNORE INTO Categories (name) VALUES (%(name)s);"
for name in categories:
dbcursor.execute(command, {"name": name})
db_pur_beurre.commit()
def delete_doubles(self):
# This method deletes the doubles which
# can occur in the product_categories table
dbcursor.execute("USE pur_beurre;")
dbcursor.execute("DELETE product_categories FROM product_categories "
"LEFT OUTER JOIN ( "
"SELECT MIN(id) as id, idproduct, idcategory "
"FROM product_categories "
"GROUP BY idproduct, idcategory "
") as t1 "
"ON product_categories.id = t1.id "
"WHERE t1.id IS NULL ")
def save(self):
# Inserts products in database
dbcursor.execute("USE pur_beurre;")
for product in self.product_list:
dbcursor.execute("INSERT IGNORE into Products"
"(id, name, nutriscore, ingredients, url) "
"VALUES('%s','%s','%s','%s','%s');" % (
product.id,
product.name.replace("'", " "),
product.nutriscore,
product.ingredients.replace("'", " "),
product.url,
))
db_pur_beurre.commit()
def fetcher(self):
# This method gets all the favorites
# saved by the user in the db
# it retreives them by searching the
# id_product column saved in the Favorites tab
# to the corresponding codes in the
# id column in the products tab
dbcursor.execute("USE pur_beurre;")
dbcursor.execute(
"SELECT * FROM Products "
"INNER JOIN Favorites ON Products.id = Favorites.id_product "
"WHERE Products.id = Favorites.id_product;")
stored_favorites = dbcursor.fetchall()
# print(self.stored_favorites)
return stored_favorites
def get_product_nutriscore(self):
# Gets the product nutriscore
dbcursor.execute("USE pur_beurre")
dbcursor.execute(
"SELECT products.nutriscore "
"FROM products "
"WHERE products.id = %(prod)s",
{
"prod":
str(self.result[int(constants.product_input) - 1]).replace(
"(", "").replace(")", "").replace(",", "").replace(
"'", "")
},
)
nutriscore = dbcursor.fetchall()
return nutriscore
def get_product_substitutes_2(self):
# Gets the substitutes of a product 2/2
substitutes_final = []
for substitute, categories in self.substitutes.items():
shared_categories = 0
for category in categories:
if category in self.get_product_categories():
shared_categories += 1
self.substitutes.update([(substitute, shared_categories)])
sorted_substitutes = sorted(self.substitutes.items(),
key=lambda x: x[1],
reverse=True)
# sorts the substitutes by most
# shared categories with the original product
for key, shared_cat in sorted_substitutes:
if shared_cat > 1:
# verifies if they have more than 1 category
# in common the fetches the subtitutes
dbcursor.execute("USE pur_beurre")
dbcursor.execute(
"SELECT products.name, "
"products.id, "
"products.ingredients, "
"products.nutriscore, "
"products.url "
"FROM products "
"WHERE products.id = %(name)s ",
{
"name":
str(key[0]).replace("(", "").replace(")", "").replace(
",", "").replace("'", "").strip()
},
)
substitutes_fetcher = dbcursor.fetchall()
substitutes_final.append(substitutes_fetcher)
return substitutes_final
def products_category_fetcher(self):
# Gets products from the selected category
dbcursor.execute("USE pur_beurre")
dbcursor.execute(
"SELECT products.id, products.name "
"FROM products "
"INNER JOIN product_categories ON "
"product_categories.idproduct = products.id "
"INNER JOIN categories ON "
"product_categories.idcategory = categories.id "
"WHERE categories.name = %(category)s",
{
"category":
constants.categories_menu_list[int(constants.categories_choice)
- 1]
},
)
self.result = dbcursor.fetchall()
return self.result
def get_product_categories(self):
# Gets the categories associated with a product
dbcursor.execute("USE pur_beurre")
dbcursor.execute(
"SELECT categories.name "
"FROM categories "
"INNER JOIN product_categories ON "
"product_categories.idcategory = categories.id "
"INNER JOIN products ON "
"product_categories.idproduct = products.id "
"WHERE products.id = %(prod)s",
{
"prod":
str(self.result[int(constants.product_input) - 1]).replace(
"(", "").replace(")", "").replace(",", "").replace(
"'", "")
},
)
product_cats = dbcursor.fetchall()
return product_cats
def __init__(self, products):
self.products = products
dbcursor.execute(
"CREATE DATABASE IF NOT EXISTS pur_beurre CHARACTER SET 'utf8';")
def create_table(self):
# creates a table takes the name and attributes as arguments
dbcursor.execute("USE pur_beurre")
dbcursor.execute("CREATE TABLE IF NOT EXISTS {}({})".format(
self.name, self.attrs))
def get_product_substitutes(self):
# Gets the substitutes of a product 1/2
self.substitutes_categories = []
self.substitutes = {}
dbcursor.execute("USE pur_beurre")
dbcursor.execute(
"SELECT products.id "
"FROM products "
"INNER JOIN product_categories ON "
"product_categories.idproduct = products.id "
"INNER JOIN categories ON "
"product_categories.idcategory = categories.id "
"WHERE categories.name IN (SELECT categories.name "
"FROM categories "
"INNER JOIN product_categories ON "
"product_categories.idcategory = categories.id "
"INNER JOIN products ON "
"product_categories.idproduct = products.id "
"WHERE products.id = %(product)s)",
{
"product":
str(self.result[int(constants.product_input) - 1]).replace(
"(", "").replace(")", "").replace(",", "").replace(
"'", "")
},
)
# 1. gets all products(possible substitutes)
# with at least one common category
substitutes_ids = dbcursor.fetchall()
for substitute in substitutes_ids:
dbcursor.execute("USE pur_beurre")
dbcursor.execute(
"SELECT categories.name "
"FROM categories "
"INNER JOIN product_categories ON "
"product_categories.idcategory = categories.id "
"INNER JOIN products ON "
"product_categories.idproduct = products.id "
"WHERE products.id = %(substitute)s",
{
"substitute":
str(substitute).replace("(", "").replace(")", "").replace(
",", "").replace("'", "")
},
)
# 2. gets all the categories names attached
# to the possible substitutes found in step 1
fetcher = dbcursor.fetchall()
self.substitutes_categories.append(fetcher)
for categories in self.substitutes_categories:
self.substitutes.update([(substitute, categories)])
# zips the possible substitutes and their categories in a dict
return self.substitutes
