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