def getRecipe():
sql = "select id, cuisine from recipe"
db.ping(reconnect=True)
cursor.execute(sql)
result = cursor.fetchall()
return pd.DataFrame(result)
def getEvaluationById(userId):
sql = "select e.user_id, e.recipe_id, r.cuisine, e.favor " \
"from recipe r inner join evaluation e on(r.id=e.recipe_id) "\
f"where e.user_id={userId}"
cursor.execute(sql)
result = cursor.fetchall()
return result
def getRecipeByFavor():
sql = "select recipe_id,count(recipe_id) "\
"from evaluation "\
"group by recipe_id "\
"order by count(recipe_id) desc"
cursor.ping(reconnect=True)
db.ping(reconnect=True)
result = cursor.fetchall()
return pd.DataFrame(result)
def getEvaluation():
# 여기에 keyword도 포함해야하지않나?
sql = "select e.user_id, e.recipe_id, r.cuisine, e.favor " \
"from recipe r inner join evaluation e on(r.id=e.recipe_id)"
db.ping(reconnect=True)
cursor.execute(sql)
result = cursor.fetchall()
return pd.DataFrame(result)
def getCBFDataFrame():
sql = "select b.recipe_id,group_concat(keyword separator ' ') as keyword,avg(b.favor) as favor_average,count(b.evaluation_id) as count "\
"from (select r.id as recipe_id, evk.id as evaluation_id, evk.keyword,evk.favor "\
"from recipe r left outer join(select ev.recipe_id,ev.id,ek.keyword,ev.favor "\
"from evaluation ev left outer join (select e.evaluation_id, k.keyword "\
"from evaluation_has_keyword e inner join keyword k "\
"on(e.keyword_id=k.id)) ek "\
"on(ev.id=ek.evaluation_id)) evk "\
"on(r.id = evk.recipe_id)) as b "\
"where b.favor is not null "\
"group by b.recipe_id"
db.ping(reconnect=True)
cursor.execute(sql)
result = cursor.fetchall()
return pd.DataFrame(result)
def getRecipeByIngredient(ingredients):
size = len(ingredients)
ingredients_str = "\",\"".join(ingredients)
# sql = "select distinct(r.recipe_id)\n" \
# "from recipe_has_ingredient r left outer join ingredient i\n" \
# "on r.ingredient_id = i.id\n" \
# f"where i.name in (\"{ingredients_str}\")\n" \
# "group by r.recipe_id"
sql = "select distinct(r.recipe_id) "\
"from recipe_has_ingredient r left outer join ingredient i "\
"on r.ingredient_id = i.id "\
f"where i.name in (\"{ingredients_str}\")" \
"group by r.recipe_id "\
"order by count(r.recipe_id)>1 desc"
db.ping(reconnect=True)
cursor.execute(sql)
result = [item['recipe_id'] for item in cursor.fetchall()]
return result
def getIngredients():
sql = "select name from ingredient"
db.ping(reconnect=True)
cursor.execute(sql)
result = cursor.fetchall()
return result