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