def mark_recommandation(form): """ - Retrieve the marks of the user and the mark of other users on the same recipes - compare them to determine the distance between the user and other users - find the recipes that the user is interested in (according to the ingredients) and order them in function of the opinions of "close distance" users @param form form informations @return a list of ids sorted thanks to the "opinions" """ # retrieve the opinions of the users request = """ SELECT DISTINCT recipes.id FROM recipes INNER JOIN recipe_has_ingredients as ingr ON recipes.id LIKE ingr.idRecipe WHERE recipes.type_id LIKE \"{0}\" """.format(form['recipe_type']) for _ingr in form['ingr_dislike']: request += "AND ingr.idIngr NOT LIKE \"{}\"".format(_ingr) request += "AND (ingr.idIngr LIKE \"{}\"".format(form['ingr_like'].pop()) for _ingr in form['ingr_like']: request += "OR ingr.idIngr LIKE \"{}\"".format(_ingr) request += ");" recipe_id = db_execute_out(request) recipe_list = format_recipes(recipe_id)
def create_opinions(user_id): """ retrieve the recipes the user visited and didn't comment, format them then return them in a form intended to be in the left part @param user_id the id of the user @return string containing all the opinion forms """ search_rows = db_execute_out(""" SELECT DISTINCT recipe_id FROM search WHERE user_id LIKE {0} AND recipe_id NOT NULL AND recipe_id NOT IN ( SELECT DISTINCT recipe_id FROM opinions WHERE author LIKE {0} ); """.format(user_id)) if search_rows == [] or search_rows is None: return parse( """ <h4>How did you find theese recipes ?</h4><p>No recipe to comment</p> """, 'lxml').prettify(formatter='html') opinion_list = format_recipes([x[0] for x in search_rows]) # constructing the web page part config = SafeConfigParser() config.read(CONFIG_FILE) with open(config.get('html', 'opinion_form_path')) as _fd: search_panel = _fd.read() soup = parse('<h4>How did you find theese recipes ?</h4><div></div>', 'lxml') form_group = soup.div form_group['class'] = 'container-fluid' # creating a form for each recipe for recipe in opinion_list: form = parse(search_panel, 'lxml') # hidden info r_id = form.select('input#$recipe_info')[0] r_id['id'] = 'recipe_info_{}'.format(str(recipe['id'])) r_id['value'] = str(recipe['id']) u_id = form.select('input#$user_info')[0] u_id['id'] = 'user_info_{}'.format(str(recipe['id'])) u_id['value'] = str(user_id) # the form head = form.select('form#$id_form')[0] head['id'] = '{}_{}_form_head'.format(str(user_id), str(recipe['id'])) # the button button = form.select('button#$id_button')[0] button['id'] = '{}_{}_form'.format(str(user_id), str(recipe['id'])) # the img img = form.select('img')[0] img['src'] = recipe['img'] # the fav button fav_button = form.select('button#$fav_id')[0] fav_button['id'] = 'fav_{}_{}'.format(str(user_id), str(recipe['id'])) form_group.append(form) return soup.prettify(formatter='html')
def add_options_to_form(table_name, form, tag_id): """ Add in the form having the id tag_id the content of the two first rows of the table_name given (id and name typically) @param table_name the name of the table @param form an option in the config file containing the path to an html file @param tag_id the tag id in the form (exemple : select#type) """ config = SafeConfigParser() config.read(CONFIG_FILE) # adding types to the search form types = db_execute_out("SELECT * FROM "+ table_name +" ORDER BY name;") form_path = config.get('html', form) _fd = open(form_path) soup = parse(_fd.read(), "lxml") _fd.close() soup.select(tag_id)[0].string = '' for row in types: opt = soup.new_tag('option') opt.string = row[1] opt['value'] = row[0] soup.select(tag_id)[0].append(opt) # writing the html file html = soup.prettify(formatter='html') with open(form_path, "wb") as _fd: _fd.write(html)
def add_options_to_form(table_name, form, tag_id): """ Add in the form having the id tag_id the content of the two first rows of the table_name given (id and name typically) @param table_name the name of the table @param form an option in the config file containing the path to an html file @param tag_id the tag id in the form (exemple : select#type) """ config = SafeConfigParser() config.read(CONFIG_FILE) # adding types to the search form types = db_execute_out("SELECT * FROM " + table_name + " ORDER BY name;") form_path = config.get('html', form) _fd = open(form_path) soup = parse(_fd.read(), "lxml") _fd.close() soup.select(tag_id)[0].string = '' for row in types: opt = soup.new_tag('option') opt.string = row[1] opt['value'] = row[0] soup.select(tag_id)[0].append(opt) # writing the html file html = soup.prettify(formatter='html') with open(form_path, "wb") as _fd: _fd.write(html)
def create_opinions(user_id): """ retrieve the recipes the user visited and didn't comment, format them then return them in a form intended to be in the left part @param user_id the id of the user @return string containing all the opinion forms """ search_rows = db_execute_out(""" SELECT DISTINCT recipe_id FROM search WHERE user_id LIKE {0} AND recipe_id NOT NULL AND recipe_id NOT IN ( SELECT DISTINCT recipe_id FROM opinions WHERE author LIKE {0} ); """.format(user_id)) if search_rows == [] or search_rows is None: return parse(""" <h4>How did you find theese recipes ?</h4><p>No recipe to comment</p> """, 'lxml').prettify(formatter='html') opinion_list = format_recipes([x[0] for x in search_rows]) # constructing the web page part config = SafeConfigParser() config.read(CONFIG_FILE) with open(config.get('html', 'opinion_form_path')) as _fd: search_panel = _fd.read() soup = parse('<h4>How did you find theese recipes ?</h4><div></div>', 'lxml') form_group = soup.div form_group['class'] = 'container-fluid' # creating a form for each recipe for recipe in opinion_list: form = parse(search_panel, 'lxml') # hidden info r_id = form.select('input#$recipe_info')[0] r_id['id'] = 'recipe_info_{}'.format(str(recipe['id'])) r_id['value'] = str(recipe['id']) u_id = form.select('input#$user_info')[0] u_id['id'] = 'user_info_{}'.format(str(recipe['id'])) u_id['value'] = str(user_id) # the form head = form.select('form#$id_form')[0] head['id'] = '{}_{}_form_head'.format(str(user_id), str(recipe['id'])) # the button button = form.select('button#$id_button')[0] button['id'] = '{}_{}_form'.format(str(user_id), str(recipe['id'])) # the img img = form.select('img')[0] img['src'] = recipe['img'] # the fav button fav_button = form.select('button#$fav_id')[0] fav_button['id'] = 'fav_{}_{}'.format(str(user_id), str(recipe['id'])) form_group.append(form) return soup.prettify(formatter='html')
def get_recipe_ingr_request(recipe_info_list): """ Create a sql request to link recipes with ingredients in the database @param recipe_info_list list of dictionnary containing recipe informations : [{url, img, ingredients, ...}, {...}, ...] @return the sql requests to insert in the table recipe_has_ingredients """ req = [] for recipe in recipe_info_list: recipe_id = db_execute_out("SELECT id FROM recipes WHERE url=\"{}\";".format(recipe['url'])) recipe_id = recipe_id[0][0] for _ingr in recipe['ingredients']: ingr_id = db_execute_out("SELECT id FROM ingredients WHERE name=\"{}\";".format(_ingr)) ingr_id = ingr_id[0][0] req.append(""" INSERT INTO recipe_has_ingredients VALUES (\"{0}\", \"{1}\");""".format(recipe_id, ingr_id)) return req
def web_crawler(enter_url, limit=20): """ Get recipes and insert them in the database with ingredients @param enter_url base url of a web site (http://marmiton.org) @param limit limit number of recipes before ending the search """ _base = enter_url url_to_treat = [enter_url] # the list of url to treat url_treated = [] # th list of url treated requests = [] # contains the requests to insert recipes and ingredients ingr_list = [] # contains the ingredient list recipe_info_list = [] recipe_found = 0 # Create a dictionnary with all the recipe types and their id type_id = {} for row in db_execute_out("SELECT * FROM types"): type_id[row[1]] = row[0] while len(url_to_treat) > 0 and recipe_found < limit: try: # get the recipe in a dictionnary res = get_recipe(url_to_treat.pop(), _base) except urllib2.HTTPError: pass # insert the sql request to add the recipe in the list if 'name' in res.keys(): # put the type id instead of the name of the id res['type'] = type_id[res['type']] # add the sql request to insert the recipe in the list of requests requests.append(get_recipe_request(res)) # add the sql request for the ingredients for _ingr in res['ingredients']: if _ingr not in ingr_list: requests.append(get_ingr_request(_ingr)) ingr_list.append(_ingr) # showing the number of recipes found recipe_found += 1 print '{0}/{1} recipes found'.format(str(recipe_found), str(limit)) # keep recipes info to add to recipe_has_ingredients table recipe_info_list.append(res) # Adding urls to the stack of urls to treat url_treated.append(res['url']) for i in res['add_urls']: if i not in url_treated and i not in url_to_treat: url_to_treat.append(i) # recording all the recipes and ingredients in the database db_execute_in(requests) add_options_to_form('ingredients', 'search_form_path', 'select#ingr-like') add_options_to_form('ingredients', 'search_form_path', 'select#ingr-dislike') requests = get_recipe_ingr_request(recipe_info_list) db_execute_in(requests)
def get_recipes(id_user, id_recipe_types, id_wanted_ingredients, id_refused_ingredients): weights = {} weights['default'] = 0 weights['wanted_ingredients'] = 2 weights['past_wanted_ingredients'] = 1 weights['refused_ingredients'] = -2 # Get recipes from database selectRecipes = db_execute_out(get_select_recipes(id_recipe_types, id_wanted_ingredients, 0)) if selectRecipes is None: return [] # Create a dict to put together ingredients to the same recipe recipes = {} for recipe in selectRecipes: if(not recipes.has_key(recipe[0])): recipes[recipe[0]] = {} recipes[recipe[0]]['id'] = recipe[0] recipes[recipe[0]]['ingredients'] = [] recipes[recipe[0]]['ingredients'].append(recipe[1]) # Find the past ingredients preferred already searched in the past id_past_ingredients = [] id_past_ingredients = get_select_last_preferred_ingredients(id_user, 10) # Compute the weight of each recipe listRecipeWeight = [] for key in recipes: recipe = recipes[key] recipe['weights'] = [weights['default']] * len(recipe['ingredients']) for ingredient in id_wanted_ingredients: if ingredient in recipe['ingredients']: recipe['weights'][recipe['ingredients'].index(ingredient)] = weights['wanted_ingredients'] for ingredient in id_past_ingredients: if ingredient in recipe['ingredients']: recipe['weights'][recipe['ingredients'].index(ingredient)] = weights['past_wanted_ingredients'] for ingredient in id_refused_ingredients: if ingredient in recipe['ingredients']: recipe['weights'][recipe['ingredients'].index(ingredient)] = weights['refused_ingredients'] recipe['weight'] = 0 for i in recipe['weights']: recipe['weight'] += i listRecipeWeight.append((recipe['id'],recipe['weight'])) # Sort recipes to get the must of recipes listRecipeWeight.sort(key = operator.itemgetter(1), reverse = True) # Get just id recipes to return if listRecipeWeight == []: return [] idRecipes, w = map(list, zip(*listRecipeWeight)) # Return recipes return idRecipes
def get_recipe_ingr_request(recipe_info_list): """ Create a sql request to link recipes with ingredients in the database @param recipe_info_list list of dictionnary containing recipe informations : [{url, img, ingredients, ...}, {...}, ...] @return the sql requests to insert in the table recipe_has_ingredients """ req = [] for recipe in recipe_info_list: recipe_id = db_execute_out( "SELECT id FROM recipes WHERE url=\"{}\";".format(recipe['url'])) recipe_id = recipe_id[0][0] for _ingr in recipe['ingredients']: ingr_id = db_execute_out( "SELECT id FROM ingredients WHERE name=\"{}\";".format(_ingr)) ingr_id = ingr_id[0][0] req.append(""" INSERT INTO recipe_has_ingredients VALUES (\"{0}\", \"{1}\");""".format(recipe_id, ingr_id)) return req
def create_favs(user_id): """ retrieve the favorites recipes of the user and format them then return them @param user_id the id of the user @return favorites recipes formatted in html """ fav_rows = db_execute_out(""" SELECT idRecipe FROM user_has_favorite_recipes WHERE idUser LIKE \"{}\"; """.format(user_id)) if fav_rows == []: return parse( """ <h4>Favorite List :</h4><p>No favorite</p> """, 'lxml').prettify(formatter='html') favorite_list = format_recipes([x[0] for x in fav_rows]) # constructing the web page part config = SafeConfigParser() config.read(CONFIG_FILE) _fd = open(config.get('html', 'fav_panel')) fav_panel = _fd.read() _fd.close() soup = parse('<h4>Favorite List :</h4><div></div>', 'lxml') panel_group = soup.div panel_group['class'] = 'container-fluid' # creating a panel for each recipe for recipe in favorite_list: panel = parse(fav_panel, 'lxml') # the well well = panel.select('div#$id_fav')[0] well['id'] = 'well_unfav_{}_{}'.format(str(user_id), str(recipe['id'])) unfav = panel.select('button#$unfav_id')[0] unfav['id'] = 'unfav_{}_{}'.format(str(user_id), str(recipe['id'])) # the img img = panel.select('img#$fav_img')[0] img['id'] = str(recipe['id']) + '_favimg' img['src'] = recipe['img'] # the url url = panel.select('a#$fav_url')[0] url['id'] = str(recipe['id']) + '_favurl' url['href'] = recipe['url'] panel_group.append(panel) return soup.prettify(formatter='html')
def create_favs(user_id): """ retrieve the favorites recipes of the user and format them then return them @param user_id the id of the user @return favorites recipes formatted in html """ fav_rows = db_execute_out(""" SELECT idRecipe FROM user_has_favorite_recipes WHERE idUser LIKE \"{}\"; """.format(user_id)) if fav_rows == []: return parse(""" <h4>Favorite List :</h4><p>No favorite</p> """, 'lxml').prettify(formatter='html') favorite_list = format_recipes([x[0] for x in fav_rows]) # constructing the web page part config = SafeConfigParser() config.read(CONFIG_FILE) _fd = open(config.get('html', 'fav_panel')) fav_panel = _fd.read() _fd.close() soup = parse('<h4>Favorite List :</h4><div></div>', 'lxml') panel_group = soup.div panel_group['class'] = 'container-fluid' # creating a panel for each recipe for recipe in favorite_list: panel = parse(fav_panel, 'lxml') # the well well = panel.select('div#$id_fav')[0] well['id'] = 'well_unfav_{}_{}'.format(str(user_id), str(recipe['id'])) unfav = panel.select('button#$unfav_id')[0] unfav['id'] = 'unfav_{}_{}'.format(str(user_id), str(recipe['id'])) # the img img = panel.select('img#$fav_img')[0] img['id'] = str(recipe['id'])+'_favimg' img['src'] = recipe['img'] # the url url = panel.select('a#$fav_url')[0] url['id'] = str(recipe['id'])+'_favurl' url['href'] = recipe['url'] panel_group.append(panel) return soup.prettify(formatter='html')
def format_recipes(recipe_list): """ retrieve recipes in the database and format them in dictionnaries @param recipe_list a list string containing recipe's ids @return a lit of dictionnaries containing recipes : [{id, name, url, img, ingredients, opinions}, {...}, ...] """ if recipe_list == []: return [] _req = """ SELECT recipes.id, recipes.name, recipes.url, recipes.photo_url FROM recipes WHERE recipes.id LIKE \"{}\" """.format(recipe_list.pop()) for i in recipe_list: _req += 'OR recipes.id LIKE \"{}\"'.format(i) _req += ';' rows = db_execute_out(_req) result = [] for _row in rows: # general informations recipe = { 'id': _row[0], 'name': unicodedata.normalize('NFD', _row[1]).encode('ascii', 'ignore'), 'url': unicodedata.normalize('NFD', _row[2]).encode('ascii', 'ignore'), 'img': unicodedata.normalize('NFD', _row[3]).encode('ascii', 'ignore'), 'ingredients': [], 'opinions': [] } if recipe['img'] == '': recipe['img'] = 'https://upload.wikimedia.org/wikipedia/commons/a/ac/No_image_available.svg' # adding ingredients ingredient_rows = db_execute_out(""" SELECT ingredients.name FROM ingredients INNER JOIN recipe_has_ingredients ON ingredients.id LIKE recipe_has_ingredients.idIngr WHERE recipe_has_ingredients.idRecipe LIKE \"{}\"; """.format(_row[0])) for _ingr in ingredient_rows: recipe['ingredients'].append( unicodedata.normalize('NFD', _ingr[0]).encode('ascii', 'ignore')) # adding opinions opinion_rows = db_execute_out(""" SELECT opinions.mark, opinions.comment, users.email FROM opinions INNER JOIN users ON opinions.author=users.id WHERE opinions.recipe_id LIKE \"{}\"; """.format(_row[0])) for _op in opinion_rows: recipe['opinions'].append({ 'mark': _op[0], 'comment': unicodedata.normalize('NFD', _op[1]).encode('ascii', 'ignore'), 'author': unicodedata.normalize('NFD', _op[2]).encode('ascii', 'ignore') }) # adding the recipe to the list result.append(recipe) return result