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 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 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 save(self, existing_categories, existing_ingredients):
connection = engine.connect()
transaction = connection.begin()
try:
# upload photo
if not self.upload_file.filename:
self.photo_file = Photo.DEFAULT_IMAGE
else:
self.photo_file = Photo.upload_photo(self.upload_file)
connection.execute(
"""
INSERT INTO recipes (name, servings, preparation_time, nutritional_info, photo_file, creator_id, created_at)
VALUES (%s,%s,%s,%s,%s,%s,%s)""",
(
self.name,
self.servings,
self.preparation_time,
self.nutritional_info,
self.photo_file,
self.creator_id,
BaseModel.timestamp_to_db(datetime.datetime.now()),
),
)
# get recipe_id
results = connection.execute("SELECT id FROM recipes ORDER BY id DESC LIMIT 1;")
recipe_id = None
for result in results:
recipe_id = result[0]
# create categories
for category_name in self.category_names:
if category_name not in existing_categories:
connection.execute("INSERT INTO categories (name) VALUES (%s)", category_name)
connection.execute(
"INSERT INTO categories_recipes (recipe_id, category_name) VALUES (%s,%s)",
(recipe_id, category_name),
)
# create ingredients
for ingredient in self.ingredients:
if ingredient.name not in existing_ingredients:
connection.execute("INSERT INTO ingredients (name) VALUES (%s)", ingredient.name)
connection.execute(
"""
INSERT INTO ingredients_recipes (ingredient_name, recipe_id, quantity, unit, comment)
VALUES (%s,%s,%s,%s,%s)""",
(ingredient.name, recipe_id, ingredient.quantity, ingredient.unit, ingredient.comment),
)
# create steps
for step in self.steps:
connection.execute(
"""
INSERT INTO steps (recipe_id, number, instructions)
VALUES (%s,%s,%s)""",
(recipe_id, step.number, step.instructions),
)
transaction.commit()
except Exception as e:
transaction.rollback()
self.error_message = e.message
return False
return True
