def new_to_db(self):
"""Write a new recipe entry to the DB. Return id assigned by the DB."""
table_main = to_db_obj_name(self.table_main)
# Inserting values to the main table
recipe = (self.name, self.instructions)
self.db.c.execute(
f'INSERT INTO "{table_main}" (name, instructions) VALUES (?, ?)',
recipe)
# Remembering id assigned by the DB
new_row_id = (self.db.c.lastrowid, )
self.db.c.execute(f'SELECT id FROM "{table_main}" WHERE rowid = ?',
new_row_id)
row = self.db.c.fetchone()
id = row["id"]
# inserting contents to the associative table
contents = {(id, c.ingredient.id, c.amount, c.units)
for c in self.contents}
self.db.c.executemany(
'INSERT INTO recipe_contents (recipe_id, ingredient_id, amount, units) '
' VALUES (?, ?, ?, ?)', contents)
return id
def get_summary(cls, db, name_sort=False):
"""Return summary table for all Allergy objects in DB as dictionary list.
Table contains columns:
id: allergy db_id.
name: allergy name.
dependents: number of other class entries referencing this id as a foreign key.
param name_sort: A boolean. If True, summary will be recursively sorted by
object name ascending.
"""
table_main = to_db_obj_name(cls.table_main)
# ingredient_allergies to be renamed to ingredient_allergies
db.c.execute(
'SELECT id, name, (COUNT(ingredient_id) + COUNT(user_id)) as dependents '
'FROM allergies '
'LEFT JOIN ingredient_allergies ON allergies.id = ingredient_allergies.allergy_id '
'LEFT JOIN user_allergies ON allergies.id = user_allergies.allergy_id '
'GROUP BY allergies.id '
'ORDER BY allergies.id ASC')
rows = db.c.fetchall()
summary = [{x: y for x, y in zip(row.keys(), row)} for row in rows]
if name_sort:
summary.sort(key=lambda x: x["name"].lower())
return summary
def edit_in_db(self):
"""Edit existing DB ingredient to match current object state. Return number of
affected rows.
"""
table_main = to_db_obj_name(self.table_main)
rows_affected = 0
# Updating values to the main table
ingredient = (self.name, self.category.id, self.id)
self.db.c.execute(f'UPDATE "{table_main}" SET name = ?, category_id = ? WHERE id = ?',
ingredient)
rows_affected += self.db.c.rowcount
# Constructing sets of the ingredient's old and new allergies' ids
new_allergy_ids = {a.id for a in self.allergies}
needle = (self.id,)
old_allergies = self.db.c.execute('SELECT allergy_id as id FROM ingredient_allergies '
' WHERE ingredient_id = ?', needle).fetchall()
old_allergy_ids = {a["id"] for a in old_allergies}
# Removing allergies missing in the new set
to_remove = {(self.id, a_id) for a_id in old_allergy_ids - new_allergy_ids}
self.db.c.executemany('DELETE FROM ingredient_allergies WHERE ingredient_id = ? AND allergy_id = ?',
to_remove)
rows_affected += self.db.c.rowcount
# Adding allergies missing in the old set
to_add = {(self.id, a_id) for a_id in new_allergy_ids - old_allergy_ids}
self.db.c.executemany('INSERT INTO ingredient_allergies (ingredient_id, allergy_id) VALUES (?, ?)',
to_add)
rows_affected += self.db.c.rowcount
return rows_affected
def new_to_db(self):
"""Write a new user to the DB. Return id assigned by the DB."""
table_main = to_db_obj_name(self.table_main)
# Inserting values to the main table
user = (self.name, self.password_hash, self.is_admin)
self.db.c.execute(
f'INSERT INTO "{table_main}" (name, password_hash, is_admin) '
'VALUES (?, ?, ?)', user)
# Remembering id assigned by the DB
new_row_id = (self.db.c.lastrowid, )
self.db.c.execute(f'SELECT id FROM "{table_main}" WHERE rowid = ?',
new_row_id)
row = self.db.c.fetchone()
id = row["id"]
# inserting allergies to the associative table
allergies = {(id, a.id) for a in self.allergies}
self.db.c.executemany(
'INSERT INTO user_allergies (user_id, allergy_id) VALUES (?, ?)',
allergies)
# inserting meals to the associative table
meals = {(id, m.id) for m in self.meals}
self.db.c.executemany(
'INSERT INTO user_meals (user_id, recipe_id) VALUES (?, ?)', meals)
return id
def edit_in_db(self):
"""Edit existing DB recipe to match current object state. Return number of
affected rows.
"""
table_main = to_db_obj_name(self.table_main)
rows_affected = 0
# Updating values to the main table
recipe = (self.name, self.instructions, self.id)
self.db.c.execute(
f'UPDATE "{table_main}" SET name = ?, instructions = ? WHERE id = ?',
recipe)
rows_affected += self.db.c.rowcount
# Constructing sets of the recipe's old and new contents' ingredient ids
new_ingredient_ids = {c.ingredient.id for c in self.contents}
needle = (self.id, )
old_contents = self.db.c.execute(
'SELECT ingredient_id as id FROM recipe_contents WHERE '
'recipe_id = ?', needle).fetchall()
old_ingredient_ids = {c["id"] for c in old_contents}
# Removing contents missing in the new set
to_remove = {(self.id, i_id)
for i_id in old_ingredient_ids - new_ingredient_ids}
self.db.c.executemany(
'DELETE FROM recipe_contents WHERE recipe_id = ? AND ingredient_id = ?',
to_remove)
rows_affected += self.db.c.rowcount
# Adding contents missing in the old set
new_contents = {
c
for c in self.contents
if c.ingredient.id in new_ingredient_ids - old_ingredient_ids
}
to_add = {(self.id, c.ingredient.id, c.amount, c.units)
for c in new_contents}
self.db.c.executemany(
'INSERT INTO recipe_contents (recipe_id, ingredient_id, amount, units) '
' VALUES (?, ?, ?, ?)', to_add)
rows_affected += self.db.c.rowcount
# Updating contents present in both the old and the new sets
updated_contents = self.contents - new_contents
to_update = {(c.amount, c.units, self.id, c.ingredient.id)
for c in updated_contents}
self.db.c.executemany(
'UPDATE recipe_contents SET amount = ?, units = ? '
' WHERE recipe_id = ? AND ingredient_id = ?', to_update)
return rows_affected
def new_to_db(self):
"""Write a new ingredient to the DB. Return id assigned by the DB."""
table_main = to_db_obj_name(self.table_main)
# Inserting values to the main table
ingredient = (self.name, self.category.id )
self.db.c.execute(f'INSERT INTO "{table_main}" (name, category_id) VALUES (?, ?)',
ingredient)
# Remembering id assigned by the DB
new_row_id = (self.db.c.lastrowid,)
self.db.c.execute(f'SELECT id FROM "{table_main}" WHERE rowid = ?', new_row_id)
row = self.db.c.fetchone()
id = row["id"]
# inserting allergies to the associative table
allergies = {(id, a.id) for a in self.allergies}
self.db.c.executemany('INSERT INTO ingredient_allergies (ingredient_id, allergy_id) VALUES (?, ?)',
allergies)
return id
def edit_in_db(self):
"""Edit existing DB user to match current object state. Return number of affected rows."""
table_main = to_db_obj_name(self.table_main)
rows_affected = 0
# Updating values to the main table
user = (self.name, self.password_hash, self.is_admin, self.id)
self.db.c.execute(
f'UPDATE "{table_main}" SET name = ?, password_hash = ?, is_admin = ? '
'WHERE id = ?', user)
rows_affected += self.db.c.rowcount
# Allergies block
# Constructing sets of the users's old and new allergies' ids
new_allergy_ids = {a.id for a in self.allergies}
needle = (self.id, )
old_allergies = self.db.c.execute(
'SELECT allergy_id as id FROM user_allergies WHERE '
'user_id = ?', needle).fetchall()
old_allergy_ids = {a["id"] for a in old_allergies}
# Removing allergies missing in the new set
to_remove = {(self.id, a_id)
for a_id in old_allergy_ids - new_allergy_ids}
self.db.c.executemany(
'DELETE FROM user_allergies WHERE user_id = ? AND allergy_id = ?',
to_remove)
rows_affected += self.db.c.rowcount
# Adding allergies missing in the old set
to_add = {(self.id, a_id)
for a_id in new_allergy_ids - old_allergy_ids}
self.db.c.executemany(
'INSERT INTO user_allergies (user_id, allergy_id) VALUES (?, ?)',
to_add)
rows_affected += self.db.c.rowcount
# Meals block
# Constructing sets of the users's old and new recipes' ids
new_recipe_ids = {r.id for r in self.meals}
needle = (self.id, )
old_recipes = self.db.c.execute(
'SELECT recipe_id as id FROM user_meals WHERE '
'user_id = ?', needle).fetchall()
old_recipe_ids = {r["id"] for r in old_recipes}
# Removing recipes missing in the new set
to_remove = {(self.id, r_id)
for r_id in old_recipe_ids - new_recipe_ids}
self.db.c.executemany(
'DELETE FROM user_meals WHERE user_id = ? AND recipe_id = ?',
to_remove)
rows_affected += self.db.c.rowcount
# Adding recipes missing in the old set
to_add = {(self.id, r_id) for r_id in new_recipe_ids - old_recipe_ids}
self.db.c.executemany(
'INSERT INTO user_meals (user_id, recipe_id) VALUES (?, ?)',
to_add)
rows_affected += self.db.c.rowcount
return rows_affected
