def getRecipe(): sql = "select id, cuisine from recipe" db.ping(reconnect=True) cursor.execute(sql) result = cursor.fetchall() return pd.DataFrame(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