def insert_new_recipe( rec_title, rec_instructions, rec_url, rec_img, calories, carbs, fibre, sugar, protein, fats, sat_fats, serving_size, ): session = Session() new_recipe = Recipe( rec_title, rec_instructions, rec_url, rec_img, calories, carbs, fibre, sugar, protein, fats, sat_fats, serving_size, ) session.add(new_recipe) session.commit() session.close()
def connect_shoplist_with_ingredients_and_user(ingredients, user_id, shoplist_name): session = Session() now = datetime.datetime.now() date = now.strftime("%Y-%m-%d %H:%M:%S") newshopList = ShopList(shoplist_name, str(date)) new_user = session.query(User).filter(User.id == user_id).first() new_ingredients = (session.query(Ingredient).filter( Ingredient.ingredient.in_(ingredients)).all()) old_ings = [] for ing in new_ingredients: old_ings.append(ing.ingredient) for new_ing in ingredients: if new_ing not in old_ings: insert_new_ingredient(new_ing) new_ingredients = (session.query(Ingredient).filter( Ingredient.ingredient.in_(ingredients)).all()) newshopList.users.append(new_user) newshopList.ingredients.extend(new_ingredients) session.add(newshopList) session.commit() session.close()
def connect_recipe_with_ingredients_and_user( rec_title, rec_instructions, rec_ingredients, scrape_url, rec_image, rec_calories, rec_carbs, rec_fibre, rec_sugar, rec_protein, rec_fats, rec_sat_fats, rec_serving, user_id, ): session = Session() newRecipe = Recipe( rec_title, rec_instructions, scrape_url, rec_image, rec_calories, rec_carbs, rec_fibre, rec_sugar, rec_protein, rec_fats, rec_sat_fats, rec_serving, ) new_user = session.query(User).filter(User.id == user_id).first() new_ingredients = (session.query(Ingredient).filter( Ingredient.ingredient.in_(rec_ingredients)).all()) old_ings = [] for ing in new_ingredients: old_ings.append(ing.ingredient) for new_ing in rec_ingredients: if new_ing not in old_ings: insert_new_ingredient(new_ing) new_ingredients = (session.query(Ingredient).filter( Ingredient.ingredient.in_(rec_ingredients)).all()) newRecipe.users.append(new_user) newRecipe.ingredients.extend(new_ingredients) session.add(newRecipe) session.commit() session.close()
def connect_recipe_with_ingredients(rec_id, ing_ids): session = Session() recipe = session.query(Recipe).filter(Recipe.id == rec_id).first() # get ingredients for recipe ingredients = session.query(Ingredient).filter( Ingredient.id.in_(ing_ids)).all() recipe.ingredients = ingredients session.add(recipe) session.commit() session.close()
def post(self, ticker): DBSession = sessionmaker(bind=self.engine) session = DBSession() try: ticker_instance = models.Ticker(symbol=ticker['symbol'], ) session.add(ticker_instance) session.commit() except: session.rollback() finally: sesssion.close()
def connect_user_to_shoplist(user_id): session = Session() user = session.query(User).filter(User.id == user_id).first() ingredients_list = [] for recipe in user.recipes: for ingredient in recipe.ingredients: ingredients_list.append(ingredient) user.ingredients = ingredients_list session.add(user) session.commit() session.close()
def connect_user_with_recipe(user_id, rec_id): session = Session() user = session.query(User).filter(User.id == user_id).first() new_recipe = session.query(Recipe).filter(Recipe.id == rec_id).first() # get all current recipes all_recipes = user.recipes # append new recipe and add as new user.recipes all_recipes.append(new_recipe) user.recipes = all_recipes session.add(user) session.commit() session.close()
def post_many(self, tickers): DBSession = sessionmaker(bind=self.engine) session = DBSession() try: with session.no_autoflush: for ticker in tickers: if ticker.get('ticker', None): ticker_instance = models.Ticker( symbol=ticker['ticker'], ) session.add(ticker_instance) session.commit() print("Post successful.") except Exception as e: print("Post many failed. Roll Back ! ", e) session.rollback() finally: session.close()
def disconnect_shoplist_from_user(rec_id, user_id): session = Session() user = session.query(User).filter(User.id == user_id).first() shoplist_todelete = session.query(ShopList).filter( ShopList.id == rec_id).first() new_shoplists = [] for shoplist in user.shoplists: if shoplist != shoplist_todelete: new_shoplists.append(shoplist) user.shoplists = new_shoplists if len(shoplist_todelete.users) == 0: for ingredient in shoplist_todelete.ingredients: if len(ingredient.recipes) == 0 and len(ingredient.shoplists) == 1: session.delete(ingredient) session.delete(shoplist_todelete) session.add(user) session.commit() session.close()
def disconnect_recipe_from_user(rec_title, user_id): session = Session() user = session.query(User).filter(User.id == user_id).first() recipe_todelete = session.query(Recipe).filter( Recipe.title == rec_title).first() new_recipes = [] for recipe in user.recipes: if recipe != recipe_todelete: new_recipes.append(recipe) user.recipes = new_recipes if len(recipe_todelete.users) == 0: for ingredient in recipe_todelete.ingredients: if len(ingredient.recipes) == 1: session.delete(ingredient) session.delete(recipe_todelete) session.add(user) session.commit() session.close()
# !!! Load !!! # session API makes loading straightfoward # Create a new seesion object, add rows to it, then merges and commits them to the database Session = sessionmaker(bind=engine) #generate a new session class session = Session() for user in users: #loop to iterate through the users list previewsly created(the already clean one) row = Users(**user) #** unpacks what is in the dictionary from {'a':1,'b':2} to (a=1,b=2) session.add(row) # adding the object to the current session for upload in uploads: row = Uploads(**upload) session.add(row) session.commit() # commit the transation to the database # !!! Aggregating !!! # creating an aggregated table showing how many articles each user has posted, and the time they were last active class UploadCounts(Base): #creating the new table __tablename__ = 'upload_counts' UserId = Column(Integer, primary_key=True) LastActive = Column(DateTime) PostCount = Column(Integer) UploadCounts.__table__.create(bind = engine, checkfirst = True) # table with 3 columns, for each usedId, it will store the timestamp of when they were last active and a count of how #many posts they have uploaded # In SQL: INSERT INTO upload_counts # SELECT UserId, MAX (Timestamp) AS LastActive, COUNT (UploadId) As PostCount
def insert_new_ingredient(new_ing): session = Session() new_ingredient = Ingredient(new_ing, "measure", "Other info") session.add(new_ingredient) session.commit() session.close()
def delete(cls, sesson, qry): session.delete(qry) session.commit()
def update(cls, session, qry, **kw): for k in kw: setattr(qry[0], k, kw[k]) session.commit()
def create(cls, session, **kw): obj = cls(**kw) session.add(obj) session.commit() return obj.id