def download(self, url):
login = {'host': '127.0.0.1',
'user': '******',
'db': 'yummly_database'}
db = MysqlWrapper(login)
recipe_id = url.split('/')[-1]
if len(db.read_query("SELECT page_id FROM recipes WHERE page_id = %s", (recipe_id,))) > 0:
return None
else:
url = 'http://www.yummly.com' + url + "?unitType=imperial&servings=8"
print url
try:
r = requests.get(url)
return r.text
except:
return None
def save(self):
login = {'host': '127.0.0.1',
'user': '******',
'db': 'yummly_database'}
db = MysqlWrapper(login)
try:
source_id = db.read_query("SELECT id FROM source WHERE UID = %s", (self.recipe.get('source_UID'),))
if len(source_id) == 0:
db.write_query("INSERT INTO source (name, UID) VALUES (%s, %s)", (self.recipe.get('source_name'), self.recipe.get('source_UID')))
source_id = db.read_query("SELECT id FROM source WHERE UID = %s", (self.recipe.get('source_UID'),))
except:
print 'cannot write source'
source_id = None
try:
user_id = db.read_query("SELECT id FROM users WHERE username = %s", (self.recipe.get('username'),))
if len(user_id) == 0:
db.write_query("INSERT INTO users (username, nickname) VALUES (%s, %s)", (self.recipe.get('username'), self.recipe.get('nickname')))
user_id = db.read_query("SELECT id FROM users WHERE username = %s", (self.recipe.get('username'),))
except:
print 'cannot write user'
user_id = None
try:
recipe_id = db.read_query("SELECT id FROM recipes WHERE page_id = %s", (self.recipe.get('recipe_id'),))
if len(recipe_id) == 0:
db.write_query("INSERT INTO recipes (page_id, name, image, source_id, user_id, yums, total_time, rating, calories, calories_from_fat) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", (self.recipe.get('recipe_id'), self.recipe.get('recipe_name'), self.recipe.get('image'), source_id, user_id, self.recipe.get('yums'), self.recipe.get('total_time'), self.recipe.get('rating'), self.recipe.get('calories'), self.recipe.get('calories_from_fat')))
recipe_id = db.read_query("SELECT id FROM recipes WHERE page_id = %s", (self.recipe.get('recipe_id'),))
try:
ingredients = self.recipe.get('ingredients')
for ingredient in ingredients:
ingredient_id = db.read_query("SELECT id FROM ingredients WHERE ingredient = %s", (ingredient[2],))
if len(ingredient_id) == 0:
db.write_query("INSERT INTO ingredients (ingredient) VALUES (%s)", (ingredient[2],))
ingredient_id = db.read_query("SELECT id FROM ingredients WHERE ingredient = %s", (ingredient[2],))
db.write_query("INSERT INTO recipe_ingredients (recipe_id, ingredient_id, amount, unit) VALUES (%s, %s, %s, %s)", (recipe_id, ingredient_id, ingredient[0], ingredient[1]))
except:
print 'cannot write ingredient'
pass
try:
tags = self.recipe.get('tags')
for tag in tags:
db.write_query("INSERT INTO recipe_tags (recipe_id, tag) VALUES (%s, %s)", (recipe_id, tag))
except:
print 'cannot write tag'
pass
try:
reviews = self.recipe.get('reviews')
for review in reviews:
user_id = db.read_query("SELECT id FROM users WHERE username = %s", (review[1],))
if len(user_id) == 0:
db.write_query("INSERT INTO users (username, nickname) VALUES (%s, %s)", (review[1], review[0]))
user_id = db.read_query("SELECT id FROM users WHERE username = %s", (review[1],))
db.write_query("INSERT INTO reviews (recipe_id, user_id, rating, content) VALUES (%s, %s, %s, %s)", (recipe_id, user_id, review[2], review[3]))
except:
print 'cannot write review'
pass
try:
db.write_query("UPDATE recipes SET total_fat = %s, total_fat_dv = %s WHERE id = %s", (self.recipe.get('nutrients')[0][1], self.recipe.get('nutrients')[0][2], recipe_id))
db.write_query("UPDATE recipes SET sat_fat = %s, sat_fat_dv = %s WHERE id = %s", (self.recipe.get('nutrients')[1][1], self.recipe.get('nutrients')[1][2], recipe_id))
db.write_query("UPDATE recipes SET trans_fat = %s, trans_fat_dv = %s WHERE id = %s", (self.recipe.get('nutrients')[2][1], self.recipe.get('nutrients')[2][2], recipe_id))
db.write_query("UPDATE recipes SET cholesterol = %s, cholesterol_dv = %s WHERE id = %s", (self.recipe.get('nutrients')[3][1], self.recipe.get('nutrients')[3][2], recipe_id))
db.write_query("UPDATE recipes SET sodium = %s, sodium_dv = %s WHERE id = %s", (self.recipe.get('nutrients')[4][1], self.recipe.get('nutrients')[4][2], recipe_id))
db.write_query("UPDATE recipes SET potassium = %s, potassium_dv = %s WHERE id = %s", (self.recipe.get('nutrients')[5][1], self.recipe.get('nutrients')[5][2], recipe_id))
db.write_query("UPDATE recipes SET carb = %s, carb_dv = %s WHERE id = %s", (self.recipe.get('nutrients')[6][1], self.recipe.get('nutrients')[6][2], recipe_id))
db.write_query("UPDATE recipes SET fiber = %s, fiber_dv = %s WHERE id = %s", (self.recipe.get('nutrients')[7][1], self.recipe.get('nutrients')[7][2], recipe_id))
db.write_query("UPDATE recipes SET sugar = %s, sugar_dv = %s WHERE id = %s", (self.recipe.get('nutrients')[8][1], self.recipe.get('nutrients')[8][2], recipe_id))
db.write_query("UPDATE recipes SET protein = %s, protein_dv = %s WHERE id = %s", (self.recipe.get('nutrients')[9][1], self.recipe.get('nutrients')[9][2], recipe_id))
db.write_query("UPDATE recipes SET VA_dv = %s, VC_dv = %s WHERE id = %s", (self.recipe.get('nutrients')[10][2], self.recipe.get('nutrients')[11][2], recipe_id))
db.write_query("UPDATE recipes SET calcium_dv = %s, iron_dv = %s WHERE id = %s", (self.recipe.get('nutrients')[12][2], self.recipe.get('nutrients')[13][2], recipe_id))
db.write_query("UPDATE recipes SET salty = %s, savory = %s, sour = %s, bitter = %s , sweet = %s, spicy = %s WHERE id = %s", (self.recipe.get('tastes')[0], self.recipe.get('tastes')[1], self.recipe.get('tastes')[2], self.recipe.get('tastes')[3], self.recipe.get('tastes')[4], self.recipe.get('tastes')[5], recipe_id))
except:
print 'cannot write calorie content'
pass
except:
print 'cannot write recipe'
print self.recipe.data
pass
WHERE ingredient = %s
AND unit IS NULL
"""
feature_id = db.read_query(sql, (ingredient,))
sql = """
INSERT INTO input (recipe_id, feature_id, amount)
VALUES (%s, %s, %s)
"""
db.write_query(sql, (recipe_id, feature_id, row[4]))
login = {'host': '127.0.0.1',
'user': '******',
'db': 'yummly_database'}
db = MysqlWrapper(login)
sql = """
SELECT MAX(recipe_id_old)
FROM output
"""
result = db.read_query(sql, "")
sql = """
SELECT
recipes1.recipe_id1,
recipes1.recipe_name1,
ingredients.id,
ingredients.ingredient,
recipes1.ingredient_amount1,
recipes1.ingredient_unit1,
