tables = RunQuery(cnx, TableNames)

RunQuery(cnx, ForeignKeysOff)

for x in tables:

RunQuery(cnx, TruncateTable % x)

RunQuery(cnx, ForeignKeysOn)

tables = RunQuery(cnx, TableNames)

RunQuery(cnx, ForeignKeysOff)

for x in tables:

RunQuery(cnx, DropTable % x)

RunQuery(cnx, ForeignKeysOn)

page = requests.get(

'http://startcooking.com/measurement-and-conversion-charts')

tree = html.fromstring(page.content)

ing = re.sub("'", '', ing)

ing = re.sub(", or to taste", '', ing)

try:

page = requests.get('http://www.google.com/search?q=%s+nutrition' % ing)

tree = html.fromstring(page.content)

ingType = tree.xpath('//div[@class="_zdb _Pxg"]/text()')

if ingType == []:

ingType = ['-']

ingDescription = [' '.join((tree.xpath('//div[@class="_tXc"]//span/text()')[0]).split('\n'))]

ingAmount = tree.xpath('//div[@class="_f6d"]/text()')

ingCalories = [tree.xpath('//table[@class="_Y5d"]//tr//td/text()')[0]]

ingFat = [tree.xpath('//table[@class="_Y5d"]//tr//td[@class="_b6d"]//span/text()')[1]]

ingCholesterol = [tree.xpath('//table[@class="_Y5d"]//tr//td[@class="_b6d"]//span/text()')[3]]

ingSodium = [tree.xpath('//table[@class="_Y5d"]//tr//td[@class="_b6d"]//span/text()')[5]]

ingPotassium = [tree.xpath('//table[@class="_Y5d"]//tr//td[@class="_b6d"]//span/text()')[7]]

ingCarbohydrate = [tree.xpath('//table[@class="_Y5d"]//tr//td[@class="_b6d"]//span/text()')[9]]

ingProtein = [tree.xpath('//table[@class="_Y5d"]//tr//td[@class="_b6d"]//span/text()')[1]]

ingVitamins = tree.xpath('//table[@class="_Y5d"]//tr//td/text()')[20:]

i = 0

while i < len(ingVitamins) - 1:

if ingVitamins[i + 1] == '0%':

del ingVitamins[i]

del ingVitamins[i]

i += 1

ingVitamins = [x for x in ingVitamins if x.find('%') == -1]

ingDescription[0] = ingDescription[0].replace('\'', '')

results = [ing, ingType, ingDescription, ingAmount, ingCalories, ingFat, ingCholesterol, ingSodium, ingPotassium, ingCarbohydrate, ingProtein, ingVitamins]

# print(results)

return results

except:

source = addr[:[m.start() for m in re.finditer(r"/", addr)][2]]

if source == 'http://allrecipes.com':

page = requests.get(addr)

tree = html.fromstring(page.content)

# print(page.content)

sourceName = 'Allrecipes'

sourceType = "url"

author = tree.xpath('//span[@class="submitter__name"]/text()')

ingredients = tree.xpath('//span[@class="recipe-ingred_txt added"]/text()')

directions = tree.xpath('//span[@class="recipe-directions__list--item"]/text()')

serveSize = [''.join([x for x in tree.xpath('//p[@class="subtext"]/text()')[0] if x.isdigit()])]

recipeName = tree.xpath('//h1[@class="recipe-summary__h1"]/text()')

cookTime = tree.xpath('//span[@class="ready-in-time"]/text()')

calorieCount = tree.xpath('//span[@class="calorie-count"]//span/text()')

recipeType = [''.join(tree.xpath('//ul[@class="breadcrumbs breadcrumbs"]//li//a//span[@class="toggle-similar__title"]/text()')[-1].split())]

recipeDescription = str(tree.xpath('//div[@class="submitter__description"]/text()')[0])

recipeDescription = [recipeDescription[recipeDescription.index('"') + 1:recipeDescription.rindex('"')]]

ingredientCount = []

ingredientUnit = []

ingredientItem = []

rescrapePageder = 0

for ing in ingredients:

ing = ''.join(filter(lambda x: x in string.printable, ing))

ingredientCount.append(ing[:[m.start() for m in re.finditer(r" ", ing)][0]])

remainder = [m.start() for m in re.finditer(r" ", ing)][0]

step = len(ingredientUnit)

for u in units:

if ing.find(u) >= 0:

ingredientUnit.append(u)

remainder = ing.index(u) + len(u)

break

if len(ingredientUnit) == step:

ingredientUnit.append("-")

ingredientItem.append(ing[remainder + 1:])

print(ing)

print(ingredientCount)

# print(source)

#print('%s' % recipeName[0])

#print('%s' % author[0])

#print('%s' % serveSize[0])

#print('%s' % recipeType[0])

#print('%s' % recipeDescription[0])

#print('CookTime: %s' % cookTime[0])

#print('CalorieCount: %s' % calorieCount[0])

# print('\nIngredients:')

ingredientDetials = []

for i in range(len(ingredients)):

#print(''.join(filter(lambda x: x in string.printable, ingredients[i])))

# print(ingredientCount[i], ingredientUnit[i], ingredientItem[i])

ingredientDetials.append(scrapeGoogleNutrition(ingredientItem[i]))

# print()

# print('\nDirections:')

# for i in range(len(directions)):

# print('%s:' % str(i + 1), directions[i])

directions = ('\n'.join(['%d: %s' % (i + 1, directions[i]) for i in range(len(directions))]))

# print(directions)

pageInfo = []

recipeName[0] = recipeName[0].replace('\'', '')

recipeDescription[0] = recipeDescription[0].replace('\'', '')

recipeInfo = [recipeName[0], recipeDescription[0], serveSize[0], int(calorieCount[0]) * int(serveSize[0]), recipeType[0]]

pageInfo.append(recipeInfo)

author[0] = author[0].replace('\'', '')

sourceInfo = [sourceName, source, sourceType, author[0]]

pageInfo.append(sourceInfo)

amountInfo = [ingredientCount, ingredientUnit]

pageInfo.append(amountInfo)

ingInfo = [ingredientDetials]

pageInfo.append(ingInfo)

if cookTime == []:

dircInfo = [directions, [0]]

else:

dircInfo = [directions, cookTime]

pageInfo.append(dircInfo)

return pageInfo

else:

recipeNum = RunQuery(cnx, NextRecipeNum)[0][0] + 1

sourceNum = RunQuery(cnx, NextSourceNum)[0][0] + 1

addRecipe = "INSERT INTO RECIPE (Recipe_No, Name, Description, Quantity, Calories, Type, Direction_No, Source_No) VALUES (%d, '%s', '%s', %s, %s, '%s', %d, %d)"

addSource = "INSERT INTO SOURCE (Source_No, Name, Reference, Type, Author) VALUES (%d, '%s', '%s', '%s', '%s')"

addIngredient = "INSERT INTO INGREDIENT (Ingredient_No, Name, Type, Description, Contains_Dairy, Contains_Glutten, Nutrition_No) VALUES (%s, '%s', '%s', '%s', %s, %s, %s)"

addAmount = "INSERT INTO AMOUNT_REQUIRED (Recipe_No, Ingredient_No, Amount, Unit) VALUES (%s, %s, %s, '%s')"

addNutrition = "INSERT INTO NUTRITIONAL_FACTS (Nutrition_No, Units, Calories, Protien, Sugar, Sodium, Fat) VALUES (%s, '%s', '%s', '%s', '%s', '%s', '%s')"

addDirection = "INSERT INTO INSTRUCTION_LIST (Direction_No, Directions, Prep_Time, Difficulty) VALUES (%s, '%s', '%s', %s)"

addVitamin = "INSERT INTO VITAMIN (Nutrition_No, Vitamin) VALUES (%s, '%s')"

addConversion = "INSERT INTO MEASUREMENT_CONVERSION (Unit, Standard_Unit, Unit_to_Standard_Value) SELECT * FROM (SELECT '{0}', '{1}', '{2}') AS tmp WHERE NOT EXISTS (SELECT * FROM MEASUREMENT_CONVERSION WHERE Unit = '{0}' AND Standard_Unit = '{1}' AND Unit_to_Standard_Value = '{2}') LIMIT 1;"

recipeInfo = pageInfo[0]

sourceInfo = pageInfo[1]

amountInfo = pageInfo[2]

ingredInfo = pageInfo[3][0]

directInfo = pageInfo[4]

# Source Entry

source = (sourceNum, sourceInfo[0], sourceInfo[1], sourceInfo[2], sourceInfo[3])

RunQuery(cnx, addSource % source)

# Ingredient and Amount Entries

for i in range(len(amountInfo[0])):

ingredientNum = RunQuery(cnx, NextIngredientNum)[0][0] + 1

amounts = (recipeNum, ingredientNum, amountInfo[0][i], amountInfo[1][i])

RunQuery(cnx, addAmount % amounts)

nutritionNum = RunQuery(cnx, NextNutritionNum)[0][0] + 1

if len(ingredInfo[i]) > 1:

ingredient = (ingredientNum, ingredInfo[i][0], ingredInfo[i][1][0], ingredInfo[i][2][0], 0, 1 if ingredInfo[i][9] != 0 else 0, nutritionNum)

nutrition = (nutritionNum, re.findall(r"[\w']+", ingredInfo[i][3][0])[1], ingredInfo[i][4][0], ingredInfo[i][10][0], ingredInfo[i][9][0], ingredInfo[i][7][0], ingredInfo[i][5][0])

else:

ingredient = (ingredientNum, ingredInfo[i][0], '-', '-', 0, 0, ingredientNum)

nutrition = (nutritionNum, '-', 0, 0, 0, 0, 0)

RunQuery(cnx, ''.join(filter(lambda x: x in string.printable, addNutrition % nutrition)))

RunQuery(cnx, ''.join(filter(lambda x: x in string.printable, addIngredient % ingredient)))

if len(ingredInfo[i]) > 1:

# Vitamins

for x in ingredInfo[i][-1]:

vitamin = (nutritionNum, x)

RunQuery(cnx, addVitamin % vitamin)

#convValue = scrapeGoogleConverter(ingredInfo[i][3][0], amountInfo[1][i])

#print(addConversion.format(re.sub(r'\([^)]*\)', '', ingredInfo[i][3][0]), amountInfo[1][i], convValue))

#RunQuery(cnx, addConversion.format(re.sub(r'\([^)]*\)', '', ingredInfo[i][3][0]), amountInfo[1][i], convValue))

direction = (recipeNum, directInfo[0], directInfo[1][0], 0)

RunQuery(cnx, addDirection % direction)

recipe = (recipeNum, recipeInfo[0], recipeInfo[1], recipeInfo[2], recipeInfo[3], recipeInfo[4], recipeNum, sourceNum)

print('\nRun: %s' % query)

cursor = cnx.cursor()

cursor.execute(query)

result = [x for x in cursor]

# if result == []:

# cnx.commit()

cursor.close()

cursor = cnx.cursor()

f = open(filename, 'r')

sqlFile = f.read()

f.close()

for query in [x for x in sqlFile.split(';') if x != '\n']:

try:

cursor.execute(query)

except:

print("\nCommand skipped: %s" % query, '\n')

print("Error: ", sys.exc_info())

cnx.commit()

#try:

pageInfo = scrapeRecipe(addr)

insertInfo(cnx, pageInfo)

cnx.commit()

#except:

# print("Unexpected error:", sys.exc_info())

# print(addr, 'had unknown problems... Rollingback')

resetDatabase(cnx)

if RunQuery(cnx, NumTables)[0][0] == 0:

print("\nRun: Create tables from SchemaSetup...")

RunSQLFile(cnx, 'SchemaSetup.sql')

else:

print('Tables already built.')

resetTables(cnx)

print()

# scrapeTableConverter(cnx)

cnx = mysql.connector.connect()

try:

cnx = mysql.connector.connect(

user='root', password='<replace>', host='localhost', database='Recipes')

except:

print('Can\'t connect to MySQL server...\n')

if reset == 1:

ResetDB(cnx)

testing = 0

if testing == 1:

InsertIntoDB(cnx, 'http://allrecipes.com/recipe/56673/eileens-spicy-gingerbread-men/')

else:

ExampleData = [ 'http://allrecipes.com/recipe/8691/chicken-enchiladas-i/',

'http://allrecipes.com/recipe/213742/meatball-nirvana/',

'http://allrecipes.com/recipe/219046/rich-and-creamy-beef-stroganoff/',

'http://allrecipes.com/recipe/14773/tangy-bbq-ribs/',

'http://allrecipes.com/recipe/9023/baked-teriyaki-chicken/',

'http://allrecipes.com/recipe/56673/eileens-spicy-gingerbread-men/',

#'http://allrecipes.com/recipe/219472/french-onion-green-bean-casserole/',

'http://allrecipes.com/recipe/15650/slow-cooker-adobo-chicken/',

'http://allrecipes.com/recipe/141678/slow-cooker-pulled-pork/',

'http://allrecipes.com/recipe/14985/its-chili-by-george/',

'http://allrecipes.com/recipe/172050/quinoa-vegetable-salad/',

'http://allrecipes.com/recipe/52047/adriennes-cucumber-salad/',

#'http://allrecipes.com/recipe/235229/persimmon-and-pomegranate-salad/',

#'http://allrecipes.com/recipe/236003/tomatoes-and-bacon-egg-muffins/',

'http://allrecipes.com/recipe/21014/good-old-fashioned-pancakes/',

'http://allrecipes.com/recipe/20934/baked-french-toast/',

#'http://allrecipes.com/recipe/9011/simple-lemon-herb-chicken/',

'http://allrecipes.com/recipe/85452/homemade-black-bean-veggie-burgers/',

'http://allrecipes.com/recipe/16723/pesto-chicken-florentine/',

'http://allrecipes.com/recipe/16259/ds-famous-salsa/'

]

for addr in ExampleData:

InsertIntoDB(cnx, addr)

try:

cnx.close()

except: