def get_substitutes(product):
cursor = connexion.cursor(pymysql.cursors.DictCursor)
s_products = []
if product.nutrition_grade == "d" or product.nutrition_grade == "e":
cursor.execute(
"""SELECT * FROM product WHERE category LIKE %s AND nutrition_grade="a"
OR category LIKE %s AND nutrition_grade="b"
OR category LIKE %s AND nutrition_grade="c" """,
(product.category, product.category, product.category))
result = cursor.fetchall()
for element in result:
s_products.append(
cl.Product(element['name'], element['store'],
element['nutrition_grade'], element['url'],
element['category']))
return s_products
else:
cursor.execute(
"""SELECT * FROM product WHERE category LIKE %s AND nutrition_grade="a" """,
(product.category))
result = cursor.fetchall()
for element in result:
s_products.append(
cl.Product(element['name'], element['store'],
element['nutrition_grade'], element['url'],
element['category']))
return s_products
def fill_product():
page = 1
cursor = connexion.cursor(pymysql.cursors.DictCursor)
for page in range(1, 30):
result = requests.get(
'https://fr.openfoodfacts.org/cgi/search.pl?page_size=1000&page={}&action=process&json=1'
.format(page)).json()
for element in result['products']:
try:
product_info = (element["product_name"], element["stores"],
element["nutrition_grade_fr"], element["url"],
element['categories_tags'][0])
products_list.append(
cl.Product(element["product_name"], element["stores"],
element["nutrition_grade_fr"], element["url"],
element['categories_tags'][0]))
cursor.execute("INSERT INTO product" "(name, store, nutrition_grade, url, category)"\
"VALUES (%s, %s, %s, %s, %s)", product_info)
connexion.commit()
print(len(products_list), " products")
except KeyError: #Don't take lignes without 'product_name'
pass
except connexion.OperationalError: #Don't take the products with encoding error
pass
except connexion.DataError: #Pass when product name is too long
pass
def get_products_from_favorite():
cursor = connexion.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT * FROM favorite")
result = cursor.fetchall()
cursor.close()
favorite_db_products = []
for element in result:
favorite_db_products.append(
cl.Product(element['name'], element['store'],
element['nutrition_grade'], element['url'],
element['category']))
return favorite_db_products
def select_products_from_category(category):
cursor = connexion.cursor(pymysql.cursors.DictCursor)
cursor.execute("""SELECT * FROM product WHERE category LIKE %s """,
(category))
result = cursor.fetchall()
category_products = []
for element in result:
category_products.append(
cl.Product(element['name'], element['store'],
element['nutrition_grade'], element['url'],
element['category']))
return category_products
def get_products_from_db():
"""Get a list of products from the database"""
cursor = connexion.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT * FROM product")
result = cursor.fetchall()
cursor.close()
db_products = []
for element in result:
db_products.append(
cl.Product(element['name'], element['store'],
element['nutrition_grade'], element['url'],
element['category']))
return db_products