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 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 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 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 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 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()