def selectGroupOfRecipes(recipeIdList):
print(recipeIdList)
if len(recipeIdList) != 0:
if len(recipeIdList) > 1:
try:
mycursor = mydb.cursor()
sql = 'SELECT r.recipe_id,r.recipe_name,r.recipe_type FROM recipe r WHERE r.recipe_id IN %s ORDER BY r.recipe_id' % str(
tuple(recipeIdList))
mycursor.execute(sql)
myresult = mycursor.fetchall()
mycursor.close()
return myresult
except:
print("error")
else:
try:
mycursor = mydb.cursor()
sql = 'SELECT r.recipe_id,r.recipe_name,r.recipe_type FROM recipe r WHERE r.recipe_id = %s'
val = (recipeIdList[0], )
mycursor.execute(sql, val)
myresult = mycursor.fetchall()
mycursor.close()
return myresult
except:
print("error")
def getAllProducts():
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM product")
productDetails = mycursor.fetchall()
mycursor.close()
return productDetails
def allNutritionValue():
mycursor = mydb.cursor()
sql1 = "SELECT * FROM nutrition"
mycursor.execute(sql1)
myresult = mycursor.fetchall()
mycursor.close()
return myresult
def do_admin_login():
if (request.form['username']== 'admin'):
if request.form['password'] == 'password' and request.form['username'] == 'admin':
session['logged_in'] = True
session['username'] = request.form['username']
else:
flash('wrong password!')
return home()
else:
username = request.form['username']
teleno = request.form['password']
mycursor = mydb.cursor()
sql=("SELECT customer_telephoneNo FROM customers WHERE customer_username =%s")
val = (username,)
mycursor.execute(sql,val)
customer_telephoneNo = mycursor.fetchall()
for tele in customer_telephoneNo:
if teleno == tele[0] and request.form['username'] == username :
session['logged_in'] = True
session['username'] = request.form['username']
else:
flash('wrong password!')
return home()
def customerDetails(customerId):
mycursor = mydb.cursor()
sql1 = "SELECT * FROM customers C JOIN diseases D ON C.disease_id=D.disease_id WHERE C.customer_id=%s"
val = (customerId, )
mycursor.execute(sql1, val)
myresult = mycursor.fetchall()
mycursor.close()
return myresult
def getNutritionId(name):
mycursor = mydb.cursor()
sql = "SELECT nutrition_id FROM nutrition WHERE name = %s"
val = (name, )
mycursor.execute(sql, val)
myResult = mycursor.fetchall()
for nutrition_id in myResult:
return nutrition_id[0]
def getDetailId(nutrition_id):
mycursor = mydb.cursor()
sql = "SELECT details_id FROM details WHERE nutrition_id = %s"
val = (nutrition_id, )
mycursor.execute(sql, val)
myResult = mycursor.fetchall()
for detail_id in myResult:
return detail_id[0]
def selectedProctDetails(productName):
mycursor = mydb.cursor()
sql = "SELECT * FROM nutrition N JOIN (SELECT D.*,P.* FROM product P JOIN details D ON P.detail_id=D.details_id WHERE P.product_name = %s) X ON N.nutrition_id=X.nutrition_id "
val = (productName, )
mycursor.execute(sql, val)
productDetails = mycursor.fetchall()
mycursor.close()
return productDetails
def insertProduct(productData, nutrition_id):
detail_id = getDetailId(nutrition_id)
name = productData['name']
mycursor = mydb.cursor()
sql = "INSERT INTO product(product_name,detail_id,start_date) VALUES(%s, %s, %s)"
val = (name, detail_id, today)
mycursor.execute(sql, val)
mydb.commit()
def getCustomerDisese(customerId):
mycursor = mydb.cursor()
sql = (
"SELECT disease_name FROM customers C JOIN diseases D ON C.disease_id = D.disease_id WHERE customer_id = %s"
)
val = (customerId, )
mycursor.execute(sql, val)
customerDisease = mycursor.fetchall()
for disease in customerDisease:
return disease[0]
def insertRecipe1():
sheet = wb1.sheet_by_index(0)
for i in range(sheet.nrows):
mycursor = mydb.cursor()
sql = "INSERT INTO recipe (recipe_id, recipe_name, recipe_type, start_date) VALUES (%s, %s, %s, %s)"
val = (sheet.cell_value(i, 0), sheet.cell_value(i, 1), sheet.cell_value(i, 3), today)
mycursor.execute(sql, val)
mydb.commit()
def getCustomerDisease(username):
mycursor = mydb.cursor()
sql = (
"SELECT D.disease_name FROM customers C JOIN diseases D ON C.disease_id=D.disease_id WHERE C.customer_username =%s"
)
val = (username, )
mycursor.execute(sql, val)
disease = mycursor.fetchall()
mycursor.close()
for customer_disease in disease:
return customer_disease[0]
def insertRecipeIngredient1():
sheet = wb1.sheet_by_index(0)
for i in range(sheet.nrows):
data = sheet.cell_value(i, 2).split("**")
for index in range(len(data)):
print(data[index])
mycursor = mydb.cursor()
sql = "INSERT INTO recipe_data (recipe_id, recipe_ingredient,start_date) VALUES (%s, %s, %s)"
val = (sheet.cell_value(i, 0), data[index], today)
mycursor.execute(sql, val)
mydb.commit()
def personRegister(personalData):
height = (int(personalData["height"]) / 100)
BMI = int(personalData["weight"]) / (height * height)
mycursor = mydb.cursor()
sql = "INSERT INTO customers (customer_username, customer_telephoneNo,customer_age,customer_height,customer_weight,customer_BMI) " \
"VALUES (%s, %s, %s, %s, %s, %s)"
val = (personalData['username'], personalData['telephoneNo'],
personalData['age'], personalData['height'], personalData['weight'],
BMI)
mycursor.execute(sql, val)
mydb.commit()
def insertProductDetails(productData):
name = productData['name']
price = productData['price']
discount = productData['discount']
netWeight = productData['netWeight']
nutrition_id = getNutritionId(name)
mycursor = mydb.cursor()
sql = "INSERT INTO details(price,discount,net_weight,nutrition_id) VALUES(%s, %s, %s, %s)"
val = (price, discount, netWeight, nutrition_id)
mycursor.execute(sql, val)
mydb.commit()
insertProduct(productData, nutrition_id)
def insertProductValue(productData):
name = productData['name']
servingSize = productData['servingSize']
energy = productData['energy']
carbohydrate = productData['carbohydrate']
protein = productData['protein']
totalFat = productData['totalFat']
sugar = productData['sugar']
cholesterol = productData['cholesterol']
fiber = productData['fiber']
mycursor = mydb.cursor()
sql = "INSERT INTO nutrition(name,serving_size,calories,total_fat,cholesterol,protein,carbohydrate,fiber,sugar) VALUES(%s, %s, %s, %s,%s, %s, %s, %s, %s)"
val = (name, servingSize, energy, totalFat, cholesterol, protein,
carbohydrate, fiber, sugar)
mycursor.execute(sql, val)
mydb.commit()
insertProductDetails(productData)
def getIdFromRecipe():
mycursor = mydb.cursor()
mycursor.execute("SELECT DISTINCT(recipe_id) FROM recipe_data")
recipe_id = mycursor.fetchall()
return recipe_id
from DB_config import mydb
import numpy as np
import xlsxwriter
import xlrd
import re
import builtins
import pymysql
from DictList import DictList
mycursor = mydb.cursor()
mycursor.execute(
"SELECT r.recipe_id, rd.recipe_ingredient,r.recipe_name,r.recipe_type FROM recipe_data rd JOIN recipe r ON rd.recipe_id=r.recipe_id"
)
myresult = mycursor.fetchall()
def getIdFromRecipe():
mycursor = mydb.cursor()
mycursor.execute("SELECT DISTINCT(recipe_id) FROM recipe_data")
recipe_id = mycursor.fetchall()
return recipe_id
def getCustomerDisease(username):
mycursor = mydb.cursor()
sql = (
"SELECT D.disease_name FROM customers C JOIN diseases D ON C.disease_id=D.disease_id WHERE C.customer_username =%s"
)
val = (username, )
mycursor.execute(sql, val)
disease = mycursor.fetchall()
def nutritionDownload():
chrome_path = "E:/All in/Final Year Project/Akki/Programmes/chromedriver_win32/chromedriver.exe"
driver = webdriver.Chrome(chrome_path)
count = 0
types = [
"https://www.nutrition-and-you.com/fruit-nutrition.html",
"https://www.nutrition-and-you.com/vegetable-nutrition.html",
"https://www.nutrition-and-you.com/nuts_nutrition.html",
"https://www.nutrition-and-you.com/dairy-products.html"
]
typen = []
name = []
workbook = xlsxwriter.Workbook('nutrition.xlsx')
worksheet = workbook.add_worksheet()
# nuritionTypes = driver.find_elements_by_xpath("//*[@id='mySidebar']//a[@href]")
# for nuritionType in nuritionTypes:
# types.append(nuritionType.get_attribute("href"))
#
# types.pop(0)
for type in types:
# print (type)
driver.get(type)
typename = driver.find_elements_by_xpath(
"/html/body/div[3]/div[2]/div/div/table//a[@href]")
for Typex in typename:
typen.append(Typex.get_attribute("href"))
# print(len(typen))
# print(Typex)
for i in range(len(typen)):
driver.get(typen[i])
print(typen[i])
try:
downloadNames = driver.find_element_by_xpath(
"/html/body/div[3]/div[1]/div[1]/h1").text
name = re.sub("nutrition facts", "", downloadNames)
except Exception:
print("No name")
res = requests.get(typen[i])
soup = BeautifulSoup(res.text, "lxml")
try:
# print(name)
for tr in soup.find(id="tab").find_all("tr"):
data = [
item.get_text(strip=True)
for item in tr.find_all(["th", "td"])
]
# print(data)
worksheet.write(count, 0, name)
if (data[0] == "Energy"):
worksheet.write(count, 1, data[1])
energy = data[1]
if (data[0] == "Carbohydrates"):
worksheet.write(count, 2, data[1])
carbohydrates = data[1]
if (data[0] == "Protein"):
worksheet.write(count, 3, data[1])
protein = data[1]
if (data[0] == "Total Fat"):
worksheet.write(count, 4, data[1])
totalFat = data[1]
if (data[0] == "Cholesterol"):
worksheet.write(count, 5, data[1])
cholesterol = data[1]
if (data[0] == "Dietary Fiber"):
worksheet.write(count, 6, data[1])
fiber = data[1]
count = count + 1
mycursor = mydb.cursor()
sql = "INSERT INTO nutrition(name,calories,total_fat,cholesterol,protein,carbohydrate,fiber) VALUES (%s, %s, %s, %s,%s, %s, %s)"
val = (name, energy, totalFat, cholesterol, protein, carbohydrates,
fiber)
mycursor.execute(sql, val)
mydb.commit()
except Exception:
continue
print("Nutrition table not exist ")
worksheet.write(count, 0, "null")
count = count + 1
print(
"---------------------------------------------------------------------------------"
)
driver.close()
workbook.close()