def query_category_mini_recipes(self, table, search_by, search_value,
column, category, order_by, direction):
""" GET ALL MINI RECIPES FILTERED BY COURSE AND CUISINE,
SORTED BY GIVEN USER SELECTION, FOR USER OR PUBLIC FEED """
join_table = []
if table == 'Cuisine':
join_table = 'Course'
else:
join_table = 'Cuisine'
try:
with Db() as cursor:
condition = """ WHERE %s = %s AND %s = '%s'
ORDER BY %s %s;""" % (search_by, search_value,
column, category,
order_by, direction)
new_query = QueryCategory(table, join_table)
recipes_query = new_query.category_selection() + condition
cursor.execute(recipes_query)
recipes = [row for row in cursor]
return recipes
except pymysql.err.OperationalError as e:
message = " FAILED: query_category_mini_recipes method in db_read.QueryReadRecipes."
log = WriteErrorToLog(str(e), message, log_file,
str(datetime.now()))
log.write_to_doc()
else:
print("query_category_mini_recipes completed")
def update_recipe(self):
recipe_values = (self.recipe_title, self.recipe_description,
self.cooking_time, self.make_public, self.recipe_id)
try:
with Db(commit=True) as cursor:
cursor.execute(UpdateQuery.recipe_query, recipe_values)
except pymysql.err.OperationalError as e:
message = " FAILED: update_recipe in db_read.QueryUpdateRecipes."
log = WriteErrorToLog(str(e), message, log_file, str(datetime.now()))
log.write_to_doc()
else:
print("update_recipe completed")
def delete_saved_recipe(self, user_id):
try:
with Db(commit=True) as cursor:
cursor.execute(""" DELETE FROM %s
WHERE UserId = %s
AND RecipeId = %s;""" % (self.table, user_id, self.recipe_id))
except pymysql.err.OperationalError as e:
message = " FAILED: delete_saved_recipe in db_read.QueryDeleteRecipes."
log = WriteErrorToLog(str(e), message, log_file, str(datetime.now()))
log.write_to_doc()
else:
print("delete_saved_recipe completed")
def query_username_or_id(self, select_column, search_column, value):
try:
with Db() as cursor:
get_id_query = """SELECT %s FROM User
WHERE %s = '%s';""" % (
select_column, search_column, value)
cursor.execute(get_id_query)
username = [row for row in cursor]
return (username)
except pymysql.err.OperationalError as e:
message = " FAILED: query_username_or_id method in db_read.QueryReadRecipes."
log = WriteErrorToLog(str(e), message, log_file,
str(datetime.now()))
log.write_to_doc()
else:
print("query_username_or_id completed")
def get_all_cuisines_and_courses(self):
try:
with Db() as cursor:
query = """SELECT CuisineName, CourseName
FROM Course
JOIN Cuisine on Course.RecipeId = Cuisine.RecipeId;"""
cursor.execute(query)
results = [row for row in cursor]
return results
except pymysql.err.OperationalError as e:
message = " FAILED: get_all_cuisines_and_courses method in db_read.TotalCuisinesCourses"
log = WriteErrorToLog(str(e), message, log_file,
str(datetime.now()))
log.write_to_doc()
else:
print("get_all_cuisines_and_course complete")
def query_user(self):
""" GET USER RECORD BASED ON USER INFO """
try:
with Db() as cursor:
query = """SELECT `Username`,`First`,`Last`,`Password`, `UserId` FROM User
WHERE Username = %s;"""
cursor.execute(query, (self.username))
from_db = [result for result in cursor]
return from_db
except pymysql.err.OperationalError as e:
message = "FAILED: query_user method in db_read.UserVerify."
log = WriteErrorToLog(str(e), message, log_file,
str(datetime.now()))
log.write_to_doc()
else:
print("query_user completed")
def get_total_public(self):
try:
with Db() as cursor:
query = """SELECT COUNT(%s) as Public%s
FROM %s WHERE MakePublic = 1; """ % (
self.column, self.table, self.table)
cursor.execute(query)
result = [row for row in cursor]
return result
except pymysql.err.OperationalError as e:
message = " FAILED: get_total_public method in db_read.QueryAllData"
log = WriteErrorToLog(str(e), message, log_file,
str(datetime.now()))
log.write_to_doc()
else:
print("get_total_public complete")
def query_rating_and_comments(self):
try:
with Db() as cursor:
rating_query = """ SELECT Rating, Comments, Username FROM Rating
JOIN User on Rating.UserId = User.UserId
WHERE Rating.RecipeId = %s; """
cursor.execute(rating_query, self.recipe_id)
rating = [row for row in cursor]
return rating
except pymysql.err.OperationalError as e:
message = "FAILED: query_rating_and_comments method in db_read.QueryRating."
log = WriteErrorToLog(str(e), message, log_file,
str(datetime.now()))
log.write_to_doc()
else:
print("query_rating_and_comments completed")
def get_total(self):
try:
with Db() as cursor:
rating_query = """ SELECT COUNT(%s) as Total%s
FROM %s; """ % (self.column, self.table,
self.table)
cursor.execute(rating_query)
rating = [row for row in cursor]
return rating
except pymysql.err.OperationalError as e:
message = " FAILED: get_total method in db_read.QueryAllData"
log = WriteErrorToLog(str(e), message, log_file,
str(datetime.now()))
log.write_to_doc()
else:
print("get_total complete")
def query_method_for_full_recipe(self, recipe_id):
""" GET FULL METHOD BASED ON GIVEN RECIPE ID """
try:
with Db() as cursor:
recipes_query = """SELECT StepNumber, StepDescription, MethodId FROM Method
WHERE Method.RecipeId = %s;""" % (
recipe_id)
cursor.execute(recipes_query)
recipes = [row for row in cursor]
return recipes
except pymysql.err.OperationalError as e:
message = "FAILED: query_method_for_full_recipe method in db_read.QueryReadRecipes."
log = WriteErrorToLog(str(e), message, log_file,
str(datetime.now()))
log.write_to_doc()
else:
print("query_method_for_full_recipe completed")
def query_ingredients_for_full_recipe(self, recipe_id):
""" GET INGREDIENTS BASED ON GIVEN RECIPE ID """
try:
with Db() as cursor:
recipes_query = """SELECT IngredientName, Quantity, IngredientId FROM Ingredient
WHERE Ingredient.RecipeId = %s;""" % (
recipe_id)
cursor.execute(recipes_query)
recipes = [row for row in cursor]
return recipes
except pymysql.err.OperationalError as e:
message = "FAILED: query_ingredients_for_full_recipe method in db_read.QueryReadRecipes."
log = WriteErrorToLog(str(e), message, log_file,
str(datetime.now()))
log.write_to_doc()
else:
print("query_ingredients_for_full_recipe completed")
def query_users_saved_recipes(self, user_id, order_by, direction):
""" QUERY USERS SAVED RECIPES BASED ON USER ID, GET MINI RECIPES INFO BACK """
try:
with Db() as cursor:
recipes_query = QuerySelections().saved_recipes() + """
WHERE SavedRecipes.UserId = %s
ORDER BY %s %s;""" % (user_id, order_by,
direction)
cursor.execute(recipes_query)
recipes = [row for row in cursor]
return recipes
except pymysql.err.OperationalError as e:
message = "FAILED: query_users_saved_recipes method in db_read.QueryReadRecipes."
log = WriteErrorToLog(str(e), message, log_file,
str(datetime.now()))
log.write_to_doc()
else:
print("query_users_saved_recipes completed")
def query_is_recipe_saved(self, user_id, recipe_id):
""" QUERY IF A USER HAS SAVED A RECIPE """
try:
with Db() as cursor:
recipes_query = """ SELECT * FROM SavedRecipes
WHERE UserId = %s
AND RecipeId = %s """ % (user_id,
recipe_id)
cursor.execute(recipes_query)
recipes = [row for row in cursor]
return recipes
except pymysql.err.OperationalError as e:
message = "FAILED: query_is_recipe_saved method in db_read.QueryReadRecipes."
log = WriteErrorToLog(str(e), message, log_file,
str(datetime.now()))
log.write_to_doc()
else:
print("query_is_recipe_saved completed")
def query_count_and_group_column_public(self, column, value, table):
""" USE FOR GROUPING CUISINE AND COURSE FOR A SHARED RECIPE SEARCH """
try:
with Db() as cursor:
recipes_query = """ SELECT COUNT(%s) as Total, %s
FROM Recipe
JOIN %s on Recipe.RecipeId = %s.RecipeId
WHERE MakePublic = %s
GROUP BY %s;""" % (column, column, table,
table, value, column)
cursor.execute(recipes_query)
recipes = [row for row in cursor]
return recipes
except pymysql.err.OperationalError as e:
message = "FAILED: query_count_and_group_column_public method in db_read.QueryReadRecipes."
log = WriteErrorToLog(str(e), message, log_file,
str(datetime.now()))
log.write_to_doc()
else:
print("query_count_and_group_column_public completed")
def update_stats(self):
stat_queries = [[UpdateQuery.single_column_query(self,'Cuisine','CuisineName',
self.cuisine_name, self.recipe_id)],
[UpdateQuery.single_column_query(self,'Course','CourseName',
self.course_name, self.recipe_id)],
[UpdateQuery.single_column_query(self,'Health','Calories',
self.calories, self.recipe_id)],
[UpdateQuery.single_column_query(self,'Cost','Price',
self.cost, self.recipe_id)],
[UpdateQuery.single_column_query(self,'Servings','Servings',
self.servings, self.recipe_id)]]
try:
with Db(commit=True) as cursor:
for stat in stat_queries:
cursor.execute(stat[0])
except pymysql.err.OperationalError as e:
message = " FAILED: update_recipe in db_read.QueryUpdateRecipes."
log = WriteErrorToLog(str(e), message, log_file, str(datetime.now()))
log.write_to_doc()
else:
print("update_recipe completed")
def query_all_mini_recipes(self, search_by, search_value, order_by,
direction):
""" GET ALL MINI RECIPES ORDERED BY GIVEN USER SELECTION, AND FILTERED BY
GIVEN USER SELECTION, PUBLIC AND USER SPECIFIC """
try:
with Db() as cursor:
condition = """ WHERE %s = %s
ORDER BY %s %s; """ % (search_by, search_value,
order_by, direction)
recipes_query = QuerySelections().main_selection() + condition
cursor.execute(recipes_query)
recipes = [row for row in cursor]
return recipes
except pymysql.err.OperationalError as e:
message = " FAILED: query_all_mini_recipes method in db_read.QueryReadRecipes."
log = WriteErrorToLog(str(e), message, log_file,
str(datetime.now()))
log.write_to_doc()
else:
print("query_all_mini_recipes completed")
def query_search_ingredient(self, search_by, search_value, ingredient,
order_by, direction):
""" GET ALL MINI RECIPES FOR GIVEN INGREDIENT """
try:
with Db() as cursor:
condition = """ WHERE %s = %s
AND MATCH(Ingredient.IngredientName)
AGAINST ('%s' IN NATURAL LANGUAGE MODE)
ORDER BY %s %s;""" % (
search_by, search_value, ingredient, order_by, direction)
recipes_query = QuerySelections().search_ingredient(
) + condition
cursor.execute(recipes_query)
recipes = [row for row in cursor]
return recipes
except pymysql.err.OperationalError as e:
message = "FAILED: query_search_ingredient method in db_read.QueryReadRecipes."
log = WriteErrorToLog(str(e), message, log_file,
str(datetime.now()))
log.write_to_doc()
else:
print("query_search_ingredient completed")