def update(cls, user_id, attrs):
if 'hashed_password' in attrs:
engine.execute("UPDATE users SET first_name=%s, last_name=%s, hashed_password=%s WHERE id=%s",
(attrs['first_name'], attrs['last_name'], attrs['hashed_password'], user_id))
else:
engine.execute("UPDATE users SET first_name=%s, last_name=%s WHERE id=%s",
(attrs['first_name'], attrs['last_name'], user_id))
def insert_step(cls,rid, n, text):
data = {'rid':rid,
'n':n,
'text':text
}
engine.execute("""INSERT INTO steps (recipe_id, number, instructions)
VALUES (%(rid)s, %(n)s, %(text)s)""", data)
def create_comment(cls, user, recipe_id, text):
user_id = long(user.id)
recipe_id = long(recipe_id)
created_at = datetime.datetime.now()
updated_at = created_at
engine.execute("INSERT INTO comments (user_id, recipe_id, text, created_at, updated_at) VALUES (%s,%s,%s,%s,%s)", (user_id, recipe_id, text, BaseModel.timestamp_to_db(created_at), BaseModel.timestamp_to_db(updated_at)))
def mark_as_favorite(cls, current_user, recipe_id):
test = BaseModel.timestamp_to_db(datetime.datetime.now())
engine.execute("INSERT INTO saved (user_id, recipe_id, saved_at) VALUES (%s,%s,%s)", (
current_user.id,
long(recipe_id),
BaseModel.timestamp_to_db(datetime.datetime.now())
))
def insert_ingredient_recipe(cls, ing, q, u, d, rid):
data = {'ingredient':ing,
'quantity':q,
'unit':u,
'comment':d,
'recipe_id':rid
}
engine.execute("""INSERT INTO ingredients_recipes (ingredient_name, recipe_id, quantity, unit, comment)
VALUES (%(ingredient)s, %(recipe_id)s, %(quantity)s, %(unit)s, %(comment)s)""", data)
def rate(cls, user, recipe_id, rating):
user_id = long(user.id)
recipe_id = long(recipe_id)
results = engine.execute("SELECT COUNT(*) FROM ratings WHERE user_id=%s AND recipe_id=%s", (user_id,recipe_id))
exists = False
for result in results:
if result[0] > 0:
exists = True
if exists:
engine.execute("UPDATE ratings SET rating=%s WHERE user_id=%s AND recipe_id=%s", (int(rating), user_id, recipe_id))
else:
engine.execute("INSERT INTO ratings (user_id, recipe_id, rating) VALUES (%s,%s,%s)", (user_id, recipe_id, int(rating)))
def avg_rating_by_recipe(cls, recipe_ids):
recipe_ids = [adapt(str(rid)).getquoted() for rid in recipe_ids]
result = engine.execute("SELECT recipe_id, AVG(rating) FROM ratings WHERE recipe_id IN (%s) GROUP BY recipe_id" % (",".join(recipe_ids)))
ratings = defaultdict(int)
for result_tuple in result:
ratings[int(result_tuple[0])] = float(result_tuple[1])
return ratings
def load_categories(cls, recipe_id):
attributes = ['name', 'description']
results = engine.execute("SELECT %s FROM categories_recipes JOIN categories ON categories_recipes.category_name=categories.name WHERE recipe_id=%i ORDER BY name ASC" % (",".join(attributes), long(recipe_id)))
categories = []
for result in results:
category = Category()
category.name = result[0]
category.description = result[1]
categories.append(category)
return categories
def load_steps(cls, recipe_id):
attributes = ['number', 'instructions']
results = engine.execute("SELECT %s FROM steps WHERE recipe_id=%i ORDER BY number ASC" % (",".join(attributes), long(recipe_id)))
steps = []
for result in results:
step = Step()
step.number = int(result[0])
step.instructions = result[1]
steps.append(step)
return steps
def load_ingredients(cls, recipe_id):
attributes = ['ingredient_name', 'quantity', 'unit', 'comment', 'name']
results = engine.execute("SELECT %s FROM ingredients_recipes a JOIN ingredients b ON a.ingredient_name=b.name WHERE recipe_id=%i" % (",".join(attributes), long(recipe_id)))
ingredients = []
for result in results:
ingredient = cls()
ingredient.name = result[0]
ingredient.quantity = result[1]
ingredient.unit = result[2]
ingredient.comment = result[3]
ingredients.append(ingredient)
return ingredients
def load_user_by_id(cls, id):
attributes = ['id','email','first_name','last_name','icon_code','created_at','last_login_at','hashed_password']
result = engine.execute("SELECT %s FROM users WHERE id=%s;" % (",".join(attributes), '%s'), id)
attrs = None
for values in result:
attrs = {attributes[i]:value for i, value in enumerate(values) }
if not attrs:
return None
user = cls.create_from_dict(attrs)
return user
def load_last_comment(cls, recipe_id):
attributes = ['id', 'user_id', 'text', 'created_at', 'updated_at']
results = engine.execute("SELECT %s FROM comments WHERE recipe_id=%s ORDER BY created_at DESC LIMIT 1" % (",".join(attributes), '%s'), long(recipe_id))
comment = None
for result in results:
comment = Comment()
comment.id = long(result[0])
comment.user_id = long(result[1])
comment.text = result[2]
comment.created_at = result[3]
comment.updated_at = result[4]
comment.creator = models.user.User.load_user_by_id(comment.user_id)
return comment
def delete_comment(cls, comment_id, user_id):
engine.execute("DELETE FROM comments WHERE id=%s AND user_id=%s", (
comment_id,
user_id
))
def load_recipe(cls, recipe_id, current_user):
attributes = [
"id",
"name",
"servings",
"preparation_time",
"photo_file",
"created_at",
"creator_id",
"nutritional_info",
"avg_ratings.avg_rating",
"my_ratings.rating",
"saved_counts.saved_count",
"saved.saved_at",
"avg_ratings.rating_count",
]
join_list = []
join_list.append(
"LEFT OUTER JOIN (SELECT recipe_id, AVG(rating) AS avg_rating, COUNT(rating) AS rating_count FROM ratings GROUP BY recipe_id) AS avg_ratings ON recipes.id=avg_ratings.recipe_id"
)
join_list.append(
"LEFT OUTER JOIN ratings AS my_ratings ON recipes.id=my_ratings.recipe_id AND my_ratings.user_id=%s"
% adapt(str(current_user.id)).getquoted()
)
join_list.append(
"LEFT OUTER JOIN (SELECT recipe_id, COUNT(user_id) AS saved_count FROM saved GROUP BY recipe_id) AS saved_counts ON recipes.id=saved_counts.recipe_id"
)
join_list.append(
"LEFT OUTER JOIN saved ON recipes.id=saved.recipe_id AND saved.user_id=%s"
% adapt(str(current_user.id)).getquoted()
)
join_sql = " ".join(join_list)
where_sql = "WHERE recipes.id=%s" % adapt(str(recipe_id)).getquoted()
results = engine.execute("SELECT %s FROM recipes %s %s" % (",".join(attributes), join_sql, where_sql))
recipe = None
for result in results:
recipe = Recipe()
recipe.id = result[0]
recipe.name = result[1]
recipe.servings = result[2]
recipe.preparation_time = result[3]
recipe.photo_file = result[4]
recipe.created_at = result[5]
recipe.creator_id = result[6]
recipe.nutritional_info = result[7]
recipe.avg_rating = result[8]
recipe.rating = result[9]
recipe.favorite_count = result[10]
recipe.is_favorite = result[11] != None
recipe.rating_count = result[12]
if not recipe.favorite_count:
recipe.favorite_count = 0
if not recipe.rating_count:
recipe.rating_count = 0
if not recipe.avg_rating:
recipe.avg_rating = 0.0
else:
recipe.avg_rating = round(recipe.avg_rating, 1)
user = models.user.User.load_user_by_id(recipe.creator_id)
recipe.creator = user
recipe.steps = Step.load_steps(recipe.id)
recipe.ingredients_recipes = IngredientRecipe.load_ingredients(recipe.id)
recipe.categories = Category.load_categories(recipe.id)
recipe.category_count = len(recipe.categories)
recipe.comments = Comment.load_comments(recipe.id)
return recipe
def unmark_as_favorite(cls, current_user, recipe_id):
engine.execute("DELETE FROM saved WHERE user_id=%s AND recipe_id=%s", (
current_user.id,
long(recipe_id)
))
def favorites(cls, recipe_ids, user_id):
recipe_ids = [adapt(str(rid)).getquoted() for rid in recipe_ids]
result = engine.execute("SELECT recipe_id FROM saved WHERE recipe_id IN (%s) AND user_id=%s" % (",".join(recipe_ids), adapt(str(user_id)).getquoted()))
favorite_ids = set([ int(rid_tuple[0]) for rid_tuple in result ])
return favorite_ids
def load_recipes(cls, current_user, limit=8, page=1, order_by="created_at DESC", join=[], where=[]):
attributes = ["id", "name", "servings", "preparation_time", "photo_file", "created_at", "creator_id"]
if page == 1:
offset = ""
else:
offset = "OFFSET %s" % adapt(str(limit * (page - 1))).getquoted()[1:-1]
join_sql = ""
if len(join) > 0:
join_list = []
for join_desc in join:
if join_desc == "avg_ratings":
join_list.append(
"LEFT OUTER JOIN (SELECT recipe_id, AVG(rating) AS avg_rating FROM ratings GROUP BY recipe_id) AS avg_ratings ON recipes.id=avg_ratings.recipe_id"
)
if join_desc == "saved":
join_list.append(
"INNER JOIN saved ON recipes.id=saved.recipe_id AND saved.user_id=%s"
% adapt(str(current_user.id)).getquoted()
)
join_sql = " ".join(join_list)
where_array = where
where_sql = ""
if len(where_array) > 0:
where_sql += "WHERE " + (" AND ".join(where_array))
result = engine.execute(
"SELECT %s FROM recipes %s %s ORDER BY %s LIMIT %s %s"
% (",".join(attributes), join_sql, where_sql, order_by, adapt(str(limit)).getquoted(), offset)
)
dicts = []
for values in result:
attrs = {attributes[i]: value for i, value in enumerate(values)}
dicts.append(attrs)
recipes = cls.create_from_dicts(dicts)
page_count = 1
recipe_count = 0
if len(recipes) > 0:
# get total recipe count
result = engine.execute("SELECT COUNT(*) FROM recipes %s %s" % (join_sql, where_sql))
recipe_count = None
for result_tuple in result:
recipe_count = result_tuple[0]
page_count = int(math.ceil(float(recipe_count) / limit))
if page_count == 0:
page_count = 1
# get avg ratings
recipe_ids = [str(recipe.id) for recipe in recipes]
ratings = Rating.avg_rating_by_recipe(recipe_ids)
# get favorited
favorites = Saved.favorites(recipe_ids, current_user.id)
# inject things on recipes
for recipe in recipes:
recipe.avg_rating = ratings[recipe.id]
recipe.round_rating = round(recipe.avg_rating)
recipe.is_user_favorite = recipe.id in favorites
return recipes, page_count, recipe_count
def insert_ingredient(cls, name):
d = {'name':name}
engine.execute("INSERT INTO ingredients (name) VALUES %(name)s", d)
def insert_category_recipe(catname, recipeid):
d = {'recipe_id':recipeid, 'category_name':catname}
engine.execute("""INSERT INTO categories_recipes (recipe_id, category_name)
VALUES (%(recipe_id)s, %(category_name)s)""", d)
def load_unique_ingredients(cls):
results = engine.execute("SELECT name FROM ingredients;")
ingredients = []
for result in results:
ingredients.append(Ingredient(name=result[0]))
return ingredients
def db_seed3():
random.seed(1)
conn = db_connect()
cur = conn.cursor()
# create the main user
user = {
'email' : "*****@*****.**",
'first_name': "Anthony",
'last_name': "Bourdain",
'hashed_password':User.hash_password("qwerty"),
'icon_code':1,
'created_at' : BaseModel.timestamp_to_db(datetime.now()),
'last_login_at' : BaseModel.timestamp_to_db(datetime.now())
}
cur.execute("""INSERT INTO users (email, first_name, last_name,
hashed_password, icon_code, created_at, last_login_at)
VALUES (%(email)s, %(first_name)s, %(last_name)s, %(hashed_password)s, %(icon_code)s,
%(created_at)s, %(last_login_at)s)""", user)
cur.execute("SELECT id FROM users WHERE email=%(email)s", {'email':user['email']})
user_id = cur.fetchone()
# Create other users
engine.execute("""
INSERT INTO
users (email, first_name, last_name, hashed_password, icon_code, created_at, last_login_at)
VALUES
('*****@*****.**', 'Abc', 'Xyz', 'GrYOEQ1BqarF4w0IbEkIGb/jRhs4x2uWAv6WhqoKo9KMY8lqEBnjeIxAoU9CkuUP', 0, '2015-10-08 12:00:00', '2015-10-08 12:00:00'),
('*****@*****.**', 'Franklin', 'Roosevelt', '36f104ac393b8431b57e78670c350359059f5bac353ef3ce620ee7c8ccf38928', 1, '2015-10-09 12:00:00', '2015-10-09 12:00:00'),
('*****@*****.**', 'George', 'Washington', '1bd918318467b5edf3243b90633427d2facaf630747d2d33bce137638a8719d4', 2, '2015-10-10 12:00:00', '2015-10-10 12:00:00'),
('*****@*****.**', 'George', 'Bush', '1bd918318467b5edf3243b90633427d2facaf630747d2d33bce137638a8719d4', 0, '2015-10-08 12:00:00', '2015-10-08 12:00:00'),
('*****@*****.**', 'Bill', 'Clinton', '237cef09c18de58503d79d9dd966c73c9736a8a9b8def484ba08f4d97bd2d3aa', 1, '2015-10-08 12:00:00', '2015-10-08 12:00:00'),
('*****@*****.**', 'Theodore', 'Roosevelt', 'a8979eec0be4e79b40e969c701e012c56dc3dbec3ba63611e597f605fe26eac8', 2, '2015-10-08 12:00:00', '2015-10-08 12:00:00'),
('*****@*****.**', 'Richard', 'Nixon', '5f872912d9b2b6f312711902991ac83fd854c746a8922e36715ff3aff18574b1', 3, '2015-10-08 12:00:00', '2015-10-08 12:00:00'),
('*****@*****.**', 'Thomas', 'Jefferson', '62660e10f69dcf92334c3bcae6330673947c2863982a9e8af92f141ad9587ce2', 0, '2015-10-08 12:00:00', '2015-10-08 12:00:00'),
('*****@*****.**', 'John', 'Kennedy', '9c4b7a6b4af91b44be8d9bb66d41e82589f01974702d3bf1d9b4407a55593c3c', 1, '2015-10-08 12:00:00', '2015-10-08 12:00:00'),
('*****@*****.**', 'Harry', 'Truman', '79ff9c4d2fe456cc3015d157cf941fa51a4b2c51629d73b057629cdbb9801416', 2, '2015-10-08 12:00:00', '2015-10-08 12:00:00');
""")
results = engine.execute("SELECT id from users;")
all_user_ids = [tup[0] for tup in results]
print "CHEF ID: ", user_id
with open('data/recipe_data.json' ,'r') as f:
data = json.loads(f.read())
# load all of the ingredients
print "INSERTING ALL INGREDIENTS"
unique_ingredients = list(set([first_lower(i['name']) for d in data for i in d['ingredients']]))
ingredients = [{'name':i} for i in unique_ingredients]
cur.executemany("""INSERT INTO ingredients (name) VALUES (%(name)s)""", ingredients)
# load all of the categories
print "INSERTING ALL CATEGORIES"
unique_categories = list(set([ t['name'] for d in data for t in d['tags']]))
categories = [{'name':i} for i in unique_categories]
cur.executemany("""INSERT INTO categories (name) VALUES (%(name)s)""", categories)
# for each recipe, load it, get its id, then load its steps, ingredients, and categories
recipe_ids = []
recipe_count = len(data)
for j,r in enumerate(data):
recipe = {
'name': r['name'],
'servings': r['yield'],
'preparation_time': r['preparation_time'],
'photo_file': Photo.download_photo(r['photo_url']),
'nutritional_info': r['description'],#get_random_nutritional_info(),
'creator_id': user_id,
'created_at': BaseModel.timestamp_to_db(datetime.now() - timedelta(minutes=(recipe_count - j)))
}
cur.execute("""INSERT INTO recipes (name, servings, preparation_time, photo_file, nutritional_info, creator_id, created_at)
VALUES (%(name)s, %(servings)s, %(preparation_time)s, %(photo_file)s,
%(nutritional_info)s, %(creator_id)s, %(created_at)s)""", recipe)
cur.execute("SELECT id FROM recipes ORDER BY id DESC LIMIT 1;")
recipe_id = cur.fetchone()
recipe_ids.append(recipe_id[0])
print "RECIPE NUM: ", recipe_id[0]
categories = [{'recipe_id':recipe_id, 'category_name':t['name']} for t in r['tags']]
cur.executemany("""INSERT INTO categories_recipes (recipe_id, category_name)
VALUES (%(recipe_id)s, %(category_name)s)""", categories)
steps = [ {'id':recipe_id, 'n':s['number'], 'instructions':s['instructions']} for s in r['steps'] ]
cur.executemany("""INSERT INTO steps (recipe_id, number, instructions)
VALUES (%(id)s, %(n)s, %(instructions)s)""", steps)
ingredients = [{'name':first_lower(i['name']), 'id':recipe_id, 'q':i['quantity'], 'u':i['unit'],\
'comment':i['comment']} for i in r['ingredients'] ]
cur.executemany("""INSERT INTO ingredients_recipes (ingredient_name, recipe_id, quantity, unit, comment)
VALUES (%(name)s, %(id)s, %(q)s, %(u)s, %(comment)s)""", ingredients)
conn.commit()
conn.close()
print "INSERTING RATINGS AND COMMENTS"
for recipe_id in recipe_ids:
# for each recipe, use a Bernoulli do define if the recipe should be rated/favorited or not
if random.uniform(0,10) >= 2:
# randomly select target expected rating
target_rating = random.randint(1,5)
offset = target_rating - 3
# select how many users are going to rate thsi recipe
rating_count = random.randint(3,len(all_user_ids))
# select which users are going to rate this recipe
rating_user_ids = random.sample(all_user_ids, rating_count)
# for each user, randomly select and create rating
for ruid in rating_user_ids:
rating = random.randint(1,5) + offset
if rating < 1:
rating = 1
elif rating > 5:
rating = 5
engine.execute("INSERT INTO ratings (user_id, recipe_id, rating) VALUES (%s,%s,%s)", (ruid, recipe_id, int(rating)))
# each user that rated has 1/3 chance of favoriting the recipe
if random.randint(1,3) == 1:
engine.execute("INSERT INTO saved (user_id, recipe_id, saved_at) VALUES (%s,%s,%s)", (
ruid,
recipe_id,
BaseModel.timestamp_to_db(datetime.now())
))
# select how many users are going to comment (max is rating count)
comment_count = random.randint(1,rating_count)
comments = get_comments(comment_count)
# select which users are going to comment in this recipe
comment_user_ids = random.sample(rating_user_ids, comment_count)
# for each user, randomly select and create comment
for i, cuid in enumerate(comment_user_ids):
engine.execute("INSERT INTO comments (user_id, recipe_id, text, created_at, updated_at) VALUES (%s,%s,%s,%s,%s)",
(cuid, recipe_id, comments[i],
BaseModel.timestamp_to_db(datetime.now()-timedelta(minutes=(comment_count - i))),
BaseModel.timestamp_to_db(datetime.now()-timedelta(minutes=(comment_count - i)))
))
print "DONE"
def check_if_authentic(cls, email, password):
result = engine.execute("SELECT email FROM users WHERE email=%s \
AND hashed_password=%s;", (email, cls.hash_password(password)))
return ([email[0] for email in result] + [None])[0]
def load_unique_categories(cls):
results = engine.execute("SELECT name, description FROM categories;")
categories = []
for result in results:
categories.append(Category(name=result[0], description=result[1]))
return categories
def insert_category(cls, name):
d = {'name':name}
engine.execute("INSERT INTO categories (name) VALUES %(name)s", d)