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