def hash_plaintext(): (db, cur) = util.get_dict_cursor() cur.execute(""" SELECT name, password FROM users """) fetch = cur.fetchall() for account in fetch: password = account["password"] user = account["name"] print user print password try: hashpw("password", password) except: pw_hash = hashpw(password, gensalt()) print pw_hash cur.execute(""" UPDATE users SET name = %s, password = %s WHERE name = %s """, (user, pw_hash, user)) fetch = cur.fetchall() db.commit()
def main(): params = cookie_params(request) # params = {'username': request.cookies.get['username']} (db, cur) = util.get_dict_cursor() cur.execute(""" SELECT users.name AS user_name, users.full_name AS full_name, reviews.date AS review_date, reviews.score AS review_score, reviews.body_text AS text, products.name AS product_name, manufacturers.name AS manufacturer_name, products.id AS product_id FROM reviews INNER JOIN users INNER JOIN user_reviews INNER JOIN products INNER JOIN manufacturers ON (user_reviews.user_id = users.id) AND (user_reviews.review_id = reviews.id) AND (reviews.product_id = products.id) AND (products.manufacturer_id = manufacturers.id) ORDER BY reviews.date DESC, products.name ASC LIMIT 5 """) params['reviews'] = cur.fetchall() return params
def submit_review_vote(): params = cookie_params(request) (db, cur) = util.get_dict_cursor(None) cur.execute(""" REPLACE INTO votes (review_id, user_id, value) VALUES (%s, %s, %s) """, ( request.form['review_id'], login.current_user.data['user_id'], request.form['value'] ) ) db.commit() # Now return the new value cur.execute(""" SELECT COALESCE(SUM(value), 0) AS sum FROM votes WHERE review_id = %s """, (request.form['review_id'],)) score = int(cur.fetchall()[0]['sum']) return flask.jsonify({'score': score})
def delete_review(review_id): params = cookie_params(request) (db, cur) = util.get_dict_cursor(None) deleted = cur.execute(""" DELETE FROM reviews WHERE id = %s AND id IN ( SELECT review_id FROM user_reviews INNER JOIN users ON (users.id = user_reviews.user_id) WHERE users.name = %s ) """, (review_id, params['username'])) db.commit() if deleted: flask.flash('Review deleted', 'success') else: flask.flash('Unable delete review (written by somebody else?)', 'error') if request.args.has_key('product_id'): return flask.redirect(flask.url_for('product', product_id=request.args['product_id'])) elif request.args.has_key('username'): return flask.redirect(flask.url_for('user_profile', username=request.args['username'])) else: flask.abort(404)
def post_product_review(product_id): params = cookie_params(request) (db, cur) = util.get_dict_cursor() cur.execute(""" INSERT INTO reviews VALUES (NULL, NOW(), %s, %s, %s) """, (request.form['score'], product_id, request.form['reviewText']) ) cur.execute(""" INSERT INTO user_reviews VALUES ( NULL, LAST_INSERT_ID(), ( SELECT users.id FROM users WHERE users.name = %s ) ) """, (params['username'],) ) db.commit() flask.flash("Posted review!", "success") return flask.redirect(flask.url_for('product', product_id=product_id))
def get_history_results(ts): params = cookie_params(request) query = """ SELECT products.id AS id, products.name AS name, manufacturers.name AS manufacturer, product_categories.id AS category_id, product_categories.name AS category, COUNT(DISTINCT scraped_reviews.id) AS scraped_reviews_count, metascore_with_date(%s,products.id,%s) AS avg_score, COUNT(DISTINCT user_reviews.id) AS user_reviews_count, CAST(AVG(reviews_u.score) AS DECIMAL(3, 1)) AS avg_user_score FROM products INNER JOIN product_categories ON (products.category_id = product_categories.id) INNER JOIN manufacturers ON (products.manufacturer_id = manufacturers.id) LEFT JOIN (reviews, scraped_reviews) ON (reviews.product_id = products.id AND scraped_reviews.review_id = reviews.id) LEFT JOIN (reviews AS reviews_u, user_reviews) ON (reviews_u.product_id = products.id AND user_reviews.review_id = reviews_u.id) """ if request.args.get('c') != 'all': query += """ WHERE products.category_id = %s """ query += """ GROUP BY products.id HAVING avg_score > 0 ORDER BY avg_score DESC, products.name ASC """ sql_args = (login.current_user.data["user_id"], ts) if request.args.get('c') != 'all': sql_args += (request.args.get('c'),) (db, cur) = util.get_dict_cursor(None) cur.execute(query, sql_args) params['products'] = cur.fetchall() params['categories'] = util.get_product_categories() params['has_categories'] = True params['has_avg_scores'] = True # Uncomment this to include average user scores in the products table return params
def get_specification_names(): params = cookie_params(request) (db, cur) = util.get_dict_cursor(None) cur.execute(""" SELECT DISTINCT name FROM specifications """) spec_names = map(lambda d: d['name'], cur.fetchall()) return flask.jsonify({'spec_names': spec_names})
def scrape(): params = cookie_params(request) redir = flask.redirect(flask.url_for('product', product_id=request.form['product_id'])) # Create the appropriate scraper scraper = ReviewScraper.from_url(request.form['url']) if not scraper: flask.flash("Couldn't create the scraper, boo", "error") return redir scraper.parse_page() if not scraper.score or not scraper.timestamp: flask.flash("Couldn't find the result, boo", "error") return redir # Add the scraped review to the database (db, cur) = util.get_dict_cursor() cur.execute(""" INSERT INTO reviews (date, score, product_id, body_text) VALUES (%s, %s, %s, %s) """, (scraper.timestamp, scraper.score, request.form['product_id'], scraper.body) ) cur.execute(""" INSERT INTO scraped_reviews VALUES ( NULL, LAST_INSERT_ID(), ( SELECT review_sources.id FROM review_sources WHERE review_sources.name = %s ), %s, %s ) """, (scraper.__class__.pretty_site_name, request.form['url'], scraper.blurb or '') ) db.commit() build_tags.extract_keywords() flask.flash("Successfully scraped. They gave this product a %s." % scraper.score, "success") return redir
def delete_review_comment(): params = cookie_params(request) (db, cur) = util.get_dict_cursor(None) deleted = cur.execute(""" DELETE FROM comments WHERE id = %s AND user_id = %s """, (request.form['comment_id'], login.current_user.data['user_id'])) db.commit() return flask.jsonify({'deleted': deleted})
def update_scraped_reviews(): (db, cur) = util.get_dict_cursor() cur.execute(""" SELECT r.id, sr.id, sr.review_source_id, sr.url FROM reviews r INNER JOIN scraped_reviews sr ON r.id = sr.review_id """) query_result = cur.fetchall() for result in query_result: scraper = ReviewScraper.from_url(result["url"]) scraper.parse_page() reviewId = result["id"] scrapedReviewId = result["sr.id"] print "Updating %s" % result print "Score: %s, Words in body text: %s, New Date: %s" % \ (scraper.score, len(scraper.body.split()), scraper.timestamp) cur.execute(""" UPDATE reviews SET score = %s, body_text = %s, date = %s WHERE reviews.id = %s """, (scraper.score, scraper.body, scraper.timestamp, reviewId) ) cur.execute(""" UPDATE scraped_reviews SET blurb = %s WHERE id = %s """, (scraper.blurb, scrapedReviewId) ) db.commit()
def get_review_votes(review_id): params = cookie_params(request) (db, cur) = util.get_dict_cursor(None) cur.execute(""" SELECT COALESCE(SUM(value), 0) AS sum FROM votes WHERE review_id = %s """, (review_id,)) score = int(cur.fetchall()[0]['sum']) return flask.jsonify({'score': score})
def product_category(category_id): params = cookie_params(request) (db, cur) = util.get_dict_cursor() # We request *all* categories so that we can display them on the right # sidebar, then pick out just the one we need for this page from that. params['categories'] = util.get_product_categories(cur) params['category'] = filter( lambda d: d['id'] == int(category_id), params['categories'] )[0]['name'] cur.execute(""" SELECT products.id AS id, products.name AS name, manufacturers.name AS manufacturer, COUNT(DISTINCT scraped_reviews.id) AS scraped_reviews_count, metascore_with_date(%s,products.id,NOW()) AS time_score, metascore(%s,products.id) AS avg_score, CAST(STDDEV_POP(reviews.score) AS DECIMAL(3, 2)) AS stddev, COUNT(DISTINCT user_reviews.id) AS user_reviews_count, CAST(AVG(reviews_u.score) AS DECIMAL(3, 1)) AS avg_user_score FROM products INNER JOIN manufacturers ON (products.manufacturer_id = manufacturers.id) LEFT JOIN (reviews, scraped_reviews) ON (reviews.product_id = products.id AND scraped_reviews.review_id = reviews.id) LEFT JOIN (reviews AS reviews_u, user_reviews) ON (reviews_u.product_id = products.id AND user_reviews.review_id = reviews_u.id) WHERE products.category_id = %s GROUP BY products.id ORDER BY time_score DESC, products.name ASC """, (login.current_user.data["user_id"], login.current_user.data["user_id"], category_id,)) params['products'] = cur.fetchall() params['has_avg_scores'] = True params['has_avg_user_scores'] = True params['has_stddev'] = False return params
def update_user_preference(): params = cookie_params(request) (db, cur) = util.get_dict_cursor(None) deleted = cur.execute(""" REPLACE INTO user_preferences (user_id, review_sources_id, priority) VALUES ( %s, %s, %s ) """, (login.current_user.data['user_id'], request.form['source_id'], request.form['priority'])) db.commit() return flask.jsonify({'resp': True})
def validateUser(username, password): (db, cur) = util.get_dict_cursor() cur.execute(""" SELECT password FROM users WHERE name = %s """, username) fetch = cur.fetchall() if len(fetch) != 1: return False pw_hash = fetch[0]["password"] return hashpw(password, pw_hash) == pw_hash
def validateUser(username, password): (db, cur) = util.get_dict_cursor() cur.execute( """ SELECT password FROM users WHERE name = %s """, username) fetch = cur.fetchall() if len(fetch) != 1: return False pw_hash = fetch[0]["password"] return hashpw(password, pw_hash) == pw_hash
def products_list(): params = cookie_params(request) (db, cur) = util.get_dict_cursor() cur.execute(""" SELECT products.id AS id, products.name AS name, manufacturers.name AS manufacturer, product_categories.id AS category_id, product_categories.name AS category, COUNT(DISTINCT scraped_reviews.id) AS scraped_reviews_count, metascore(%s,products.id) AS avg_score, metascore_with_date(%s,products.id,NOW()) AS time_score, COUNT(DISTINCT user_reviews.id) AS user_reviews_count, CAST(AVG(reviews_u.score) AS DECIMAL(3, 1)) AS avg_user_score FROM products INNER JOIN product_categories ON (products.category_id = product_categories.id) INNER JOIN manufacturers ON (products.manufacturer_id = manufacturers.id) LEFT JOIN (reviews, scraped_reviews) ON (reviews.product_id = products.id AND scraped_reviews.review_id = reviews.id) LEFT JOIN (reviews AS reviews_u, user_reviews) ON (reviews_u.product_id = products.id AND user_reviews.review_id = reviews_u.id) GROUP BY products.id ORDER BY time_score DESC, products.name ASC """, (login.current_user.data["user_id"],login.current_user.data["user_id"],)) params['products'] = cur.fetchall() params['categories'] = util.get_product_categories() params['manufacturers'] = util.get_manufacturers() params['has_categories'] = True params['has_avg_scores'] = True # Uncomment this to include average user scores in the products table # params['has_avg_user_scores'] = True return params
def source(review_source_id): params = cookie_params(request) # Returns a relation whose attributes have # 1. Name of the source (ex: The Verge) # 2. The URL of the source (ex: www.theverge.com) (db, cur) = util.get_dict_cursor() cur.execute(""" SELECT name, url FROM review_sources WHERE review_sources.id = %s """, review_source_id) source_data = cur.fetchall() params['source_data'] = source_data # Returns a relation that contains # 1. A product name # 2. The source's review for the product # 3. URL to the review cur.execute(""" SELECT products.name AS product_name, manufacturers.name AS manufacturer, scraped_reviews.blurb AS blurb, scraped_reviews.url AS url FROM scraped_reviews INNER JOIN reviews ON scraped_reviews.review_id = reviews.id INNER JOIN products ON reviews.product_id = products.id INNER JOIN manufacturers ON products.manufacturer_id = manufacturers.id WHERE scraped_reviews.review_source_id = %s """, review_source_id) reviews = cur.fetchall() # contains all of the reviews params['reviews'] = reviews return params
def get_review_comments(review_id): params = cookie_params(request) (db, cur) = util.get_dict_cursor(None) cur.execute(""" SELECT comments.id, comments.body_text, users.id AS user_id, users.name, users.full_name FROM comments INNER JOIN users ON comments.user_id = users.id WHERE review_id = %s ORDER BY comments.id ASC """, (review_id,)) comments = cur.fetchall() return {'comments': comments}
def post_review_comment(): params = cookie_params(request) (db, cur) = util.get_dict_cursor(None) cur.execute(""" INSERT INTO comments (user_id, review_id, body_text) VALUES (%s, %s, %s) """, ( login.current_user.data['user_id'], request.form['review_id'], request.form['body_text'] ) ) db.commit() cur.execute(""" SELECT comments.id, comments.body_text, users.id AS user_id, users.name, users.full_name FROM comments INNER JOIN users ON comments.user_id = users.id WHERE review_id = %s ORDER BY comments.id ASC """, (request.form['review_id'],)) comments = cur.fetchall() return {'comments': comments}
def add_product(): params = cookie_params(request) redir = flask.redirect(flask.url_for('products_list')) # Prepare form values product_name = request.form['product_name'] category_id = request.form['category_id'] specs_url = request.form['specs_url'] mfg_in_db = request.form['manufacturer_present'] mfg_not_in_db = request.form['manufacturer_not_present'] # Verify data invalid = False if not product_name: flask.flash('Product name required', 'error') invalid = True if not (mfg_in_db or mfg_not_in_db): flask.flash('Manufacturer must either be selected or written', 'error') invalid = True if invalid: return redir # Scrape specifications specs_scraper = SpecificationScraper(specs_url) specs_scraper.parse_page() if not specs_scraper.specs: flask.flash('Unable to scrape specs', 'error') return redir # Insert into database (db, cur) = util.get_dict_cursor() if mfg_in_db: cur.execute(""" INSERT INTO products VALUES ( NULL, %s, %s, %s ) """, (product_name, category_id, mfg_in_db) ) else: util.add_manufacturer(cur, request.form['manufacturer_not_present']) cur.execute(""" INSERT INTO products VALUES ( NULL, %s, %s, LAST_INSERT_ID() ) """, (product_name, category_id) ) product_id = cur.lastrowid cur.executemany(""" INSERT INTO specifications VALUES ( NULL, %s, %s, %s, %s ) """, [(product_id, k, v, util.ugly_str_to_float(v)) for k, v in specs_scraper.specs.iteritems()] ) db.commit() flask.flash('Added new product!', 'success') return redir
def execute_search(): params = cookie_params(request) query = request.args.get("query","") query_words = query.split() query_sql = """ SELECT products.id AS id, products.name AS name, manufacturers.name AS manufacturer, product_categories.id AS category_id, product_categories.name AS category, COUNT(DISTINCT scraped_reviews.id) AS scraped_reviews_count, metascore_with_date(%s,products.id,NOW()) AS time_score, metascore(%s,products.id) AS avg_score, COUNT(DISTINCT user_reviews.id) AS user_reviews_count, CAST(AVG(reviews_u.score) AS DECIMAL(3, 1)) AS avg_user_score, COALESCE(product_tags.tags, "") AS tags FROM products INNER JOIN product_categories ON (products.category_id = product_categories.id) INNER JOIN manufacturers ON (products.manufacturer_id = manufacturers.id) LEFT JOIN (reviews, scraped_reviews) ON (reviews.product_id = products.id AND scraped_reviews.review_id = reviews.id) LEFT JOIN (reviews AS reviews_u, user_reviews) ON (reviews_u.product_id = products.id AND user_reviews.review_id = reviews_u.id) LEFT JOIN product_tags ON (products.id = product_tags.product_id) """ if query_words: query_sql += "WHERE\n " query_sql += ' AND '.join( ["(CONCAT_WS(' ', manufacturers.name, products.name, product_categories.name) REGEXP %s)"]*len(query_words) ) query_sql += """ GROUP BY products.id ORDER BY products.name ASC """ sql_elements = [login.current_user.data["user_id"], login.current_user.data["user_id"]] sql_elements.extend(query_words) (db, cur) = util.get_dict_cursor() cur.execute(query_sql, sql_elements) params['products'] = [] params['products'].extend(cur.fetchall()) for i in params['products']: if i["avg_score"]: i["avg_score"] = float(i["avg_score"]) if i["avg_user_score"]: i["avg_user_score"] = float(i["avg_user_score"]) # Unscored products start at an even 5 for product in params['products']: product["rec_score"] = product["time_score"] or 5 product["rec_state"] = (1, 1) # (All recs pass, all recs fail) for tag in util.strip_tags(request.args.get('tags')).split(): for product in params['products']: if tag in util.strip_tags(product['tags']).split(): print product["name"], tag product["rec_score"] += 1 # Rebuild requirements list requirements = [] for i in range(int(request.args.get('num_requirements'))): requirements.append(request.args.getlist('requirements[%s][]' % i)) for requirement in requirements: print "REQUIREMENT: ", requirement cur.execute(""" SELECT product_id, name, value, value_decimal FROM specifications WHERE name = %s""", (requirement[0],)) raw_specs = cur.fetchall() specs = {} for s in raw_specs: specs[s["product_id"]] = s for product in params['products']: (product["rec_score"], product["rec_state"]) = \ modify_score(product["rec_score"], product["rec_state"], specs.get(int(product["id"])), requirement) print "SCORE:", product["rec_score"], "STATE:", product["rec_state"] for product in params['products']: if product['rec_state'] == (1, 0): # All the requirements passed product['rec_class'] = 'success' product['rec_score_with_state'] = product['rec_score'] + 100 elif product['rec_state'] == (0, 1): # All the requirements failed product['rec_class'] = 'error' product['rec_score_with_state'] = product['rec_score'] - 100 elif product['rec_state'] == (0, 0): product['rec_score_with_state'] = product['rec_score'] elif product['rec_state'] == (1, 1): product['rec_score_with_state'] = product['rec_score'] params['products'] = sorted(params['products'], key=lambda x: -x.get("rec_score_with_state")) # Find tags if len(params['products']) > 0: cur.execute(""" SELECT tags FROM product_tags WHERE product_id = %s """, (params['products'][0]['id'],)) product_tags = cur.fetchall() if len(product_tags) > 0: params['tags'] = product_tags[0]['tags'] print params['tags'] params['has_categories'] = True params['has_avg_scores'] = True params['has_rec_scores'] = True return params
def source(review_source_id): params = cookie_params(request) if params["username"] != "anonymous": params['active_user'] = True else: params['active_user'] = False # Returns a relation whose attributes have # 1. Name of the source (ex: The Verge) # 2. The URL of the source (ex: www.theverge.com) (db, cur) = util.get_dict_cursor() cur.execute(""" SELECT id, name, url FROM review_sources WHERE review_sources.id = %s """, review_source_id) source_data = cur.fetchone() if not source_data: flask.abort(404) params['source_data'] = source_data # Returns a relation that contains # 1. A product name # 2. The source's review for the product # 3. URL to the review cur.execute(""" SELECT reviews.id AS id, reviews.date AS date, reviews.score AS score, scraped_reviews.blurb AS blurb, scraped_reviews.url AS url, review_sources.name AS source_name, products.id AS product_id, products.name AS product_name, manufacturers.name AS manufacturer FROM scraped_reviews INNER JOIN reviews ON scraped_reviews.review_id = reviews.id INNER JOIN review_sources ON scraped_reviews.review_source_id = review_sources.id INNER JOIN products ON reviews.product_id = products.id INNER JOIN manufacturers ON products.manufacturer_id = manufacturers.id WHERE scraped_reviews.review_source_id = %s """, review_source_id) reviews = cur.fetchall() # contains all of the reviews params['reviews'] = reviews cur.execute(""" SELECT priority FROM user_preferences WHERE user_id = %s AND review_sources_id = %s """, (login.current_user.data["user_id"], source_data['id'])) preferences = cur.fetchone() if preferences: params['original_priority'] = (preferences['priority']) else: params['original_priority'] = 1.0 # Modify the behavior of inc/review.html linking params['product_page'] = False params['source_page'] = True return params
def user_profile(username): params = cookie_params(request) if username == login.current_user.data['username']: params['active_user'] = True (db, cur) = util.get_dict_cursor() cur.execute(""" SELECT * FROM users WHERE users.name = %s """, username) user_profile = cur.fetchone() if user_profile: params['user'] = user_profile else: flask.abort(404) cur.execute(""" SELECT reviews.date AS date, reviews.score AS score, reviews.product_id AS product_id, reviews.body_text AS body_text, reviews.id AS id, users.id AS user_id, products.id AS product_id, products.name AS product_name, manufacturers.name AS manufacturer FROM users INNER JOIN user_reviews INNER JOIN reviews INNER JOIN products INNER JOIN manufacturers ON users.id = user_reviews.user_id AND user_reviews.review_id = reviews.id AND products.id = reviews.product_id AND manufacturers.id = products.manufacturer_id WHERE users.name = %s ORDER BY reviews.date DESC, products.name ASC """, username) products = cur.fetchall() cur.execute(""" SELECT user_preferences.id AS user_preference_id, review_sources_id AS source_id, review_sources.name AS source_name, priority FROM user_preferences INNER JOIN users ON users.id = user_preferences.user_id INNER JOIN review_sources ON review_sources_id = review_sources.id WHERE users.name = %s ORDER BY review_sources.name ASC """, username) preferences = cur.fetchall() params['reviews'] = [] for product in products: params['reviews'].append(product) params['dispUsername'] = username params['preferences'] = preferences return params
def edit_product(product_id): params = cookie_params(request) (db, cur) = util.get_dict_cursor() if request.method == 'GET': # We need to query the database for all the existing properties so that # we can autofill the form. We also need the options for the # manufacturers, etc. cur.execute(""" SELECT id, name, category_id, manufacturer_id FROM products WHERE id = %s """, (product_id,) ) params['product'] = cur.fetchone() if not params['product']: flask.abort(404) params['manufacturers'] = util.get_manufacturers(cur) params['categories'] = util.get_product_categories(cur) return render_template('product_edit.html', **params) # Otherwise we're a POST. Did they click cancel? if request.form.has_key('cancel'): flask.flash('Changes canceled', 'info') return flask.redirect(flask.url_for('product', product_id=product_id)) if request.form.has_key('delete'): deleted = cur.execute(""" DELETE FROM products WHERE id = %s """, (product_id,)) db.commit() if deleted: flask.flash('Product deleted', 'success') return flask.redirect(flask.url_for('products_list')) else: flask.flash('Failed to delete product', 'error') return flask.redirect(flask.url_for('edit_product', product_id=product_id)) # Prepare form values product_name = request.form['product_name'] category_id = request.form['category_id'] mfg_in_db = request.form['manufacturer_present'] mfg_not_in_db = request.form['manufacturer_not_present'] # Verify data invalid = False if not product_name: flask.flash('Product name required', 'error') invalid = True if not (mfg_in_db or mfg_not_in_db): flask.flash('Manufacturer must either be selected or written', 'error') invalid = True if invalid: # We need to rerender the form with what they submitted. This is a # pain in the ass. # # The `manufacturer_id` is the tricky bit. It should only be possible # to have an invalid state when they selected nothing in both fields, # so I think we can just not give an ID here. params['product'] = { 'id': product_id, 'name': product_name, 'category_id': category_id, 'manufacturer_id': None } params['manufacturers'] = util.get_manufacturers(cur) params['categories'] = util.get_product_categories(cur) return render_template('product_edit.html', **params) # FIXME: Delete the old manufacturer if it is now orphaned. (Can this be # done in SQL?) # FIXME: Don't let duplicates get into the manufacturers database! Ew ew ew! # Update the database if mfg_in_db: cur.execute(""" UPDATE products SET name = %s, category_id = %s, manufacturer_id = %s WHERE id = %s """, (product_name, category_id, mfg_in_db, product_id) ) else: util.add_manufacturer(cur, request.form['manufacturer_not_present']) cur.execute(""" UPDATE products SET name = %s, category_id = %s, manufacturer_id = LAST_INSERT_ID() WHERE id = %s """, (product_name, category_id, product_id) ) db.commit() flask.flash('Updated product!', 'success') return flask.redirect(flask.url_for('product', product_id=product_id))
def product(product_id=None): params = cookie_params(request) # Find the product properties (db, cur) = util.get_dict_cursor() cur.execute(""" SELECT manufacturers.name AS manufacturer, products.id AS id, products.name AS name, product_categories.name AS category, metascore(%s,products.id) AS avg_score FROM products INNER JOIN product_categories INNER JOIN manufacturers ON products.category_id = product_categories.id AND products.manufacturer_id = manufacturers.id WHERE products.id = %s """, (login.current_user.data["user_id"], product_id,)) params['product'] = cur.fetchone() cur.execute(""" SELECT name, value, value_decimal FROM specifications WHERE product_id = %s """, (product_id,) ) params['specs'] = cur.fetchall() # Find the product scraped reviews cur.execute(""" SELECT reviews.id, scraped_reviews.url AS url, date, score, blurb, review_sources.id AS review_source_id, review_sources.name AS source_name, COALESCE(SUM(votes.value), 0) AS sum FROM reviews INNER JOIN scraped_reviews ON reviews.id = scraped_reviews.review_id INNER JOIN review_sources ON scraped_reviews.review_source_id = review_sources.id LEFT JOIN votes ON reviews.id = votes.review_id AND scraped_reviews.review_source_id = review_sources.id WHERE product_id = %s GROUP BY reviews.id ORDER BY sum DESC, reviews.score DESC """, (product_id,)) params['scraped_reviews'] = cur.fetchall() # Find the product user reviews cur.execute(""" SELECT reviews.id, date, score, body_text, users.id AS user_id, users.name AS username, users.full_name AS full_name, COALESCE(SUM(votes.value), 0) AS sum FROM reviews INNER JOIN user_reviews ON reviews.id = user_reviews.review_id INNER JOIN users ON user_reviews.user_id = users.id LEFT JOIN votes ON reviews.id = votes.review_id WHERE product_id = %s GROUP BY reviews.id ORDER BY sum DESC, reviews.score DESC """, (product_id,)) params['user_reviews'] = cur.fetchall() # Find tags cur.execute(""" SELECT tags FROM product_tags WHERE product_id = %s """, (product_id,)) product_tags = cur.fetchall() if len(product_tags) > 0: params['tags'] = product_tags[0]['tags'] # Modify the behavior of inc/review.html linking params['product_page'] = True params['source_page'] = False return params
def extract_keywords(): (db, cur) = util.get_dict_cursor() cur.execute(""" SELECT reviews.body_text FROM reviews INNER JOIN scraped_reviews ON reviews.id = scraped_reviews.review_id ORDER BY reviews.date DESC """) blm = {} smoothed_blm = {} total = 0 query_result = cur.fetchall() for item in query_result: string = item["body_text"] words = string.split() for word in words: total += 1 if word in blm: blm[word] += 1 else: blm[word] = 1 for word in blm: smoothed_blm[word] = (blm[word]+1) / float(total+20000) product_ids = [] cur.execute(""" SELECT DISTINCT reviews.product_id FROM reviews INNER JOIN scraped_reviews ON reviews.id = scraped_reviews.review_id ORDER BY reviews.product_id ASC """) query_result = cur.fetchall() for item in query_result: product_ids.append(item["product_id"]) for product_id in product_ids: tlm = {} sn_tlm = {} total = 0 cur.execute(""" SELECT reviews.body_text, reviews.product_id FROM reviews INNER JOIN scraped_reviews ON reviews.id = scraped_reviews.review_id WHERE reviews.product_id = %s """, product_id) query_result = cur.fetchall() for item in query_result: string = item["body_text"] words = string.split() for word in words: total += 1 if word in tlm: tlm[word] += 1 else: tlm[word] = 1 for word in tlm: tlm[word] = max(tlm[word], 0) / float(total) # divide by smoothed background language model sn_tlm[word] = tlm[word] / smoothed_blm[word] sorted_sn_tlm = sorted(sn_tlm.iteritems(), key=operator.itemgetter(1), reverse=True) count = 0 # print product_id tags = [] for pair in sorted_sn_tlm: if len(pair[0]) == 1 or is_number(pair[0]) or "cnet" in pair[0] or\ "verge" in pair[0] or "mags" in pair[0] or "wired" in pair[0]: continue count += 1 # print str(pair[1])[:9], pair[0] tags.append(pair[0]) if count >= 40: break stringified_tags = ",".join(tags) # print stringified_tags # print "" cur.execute(""" INSERT INTO product_tags VALUES (NULL, %s, %s) ON DUPLICATE KEY UPDATE product_id = %s, tags = %s; """, (product_id, stringified_tags, product_id, stringified_tags)) db.commit()
def extract_keywords(): (db, cur) = util.get_dict_cursor() cur.execute(""" SELECT reviews.body_text FROM reviews INNER JOIN scraped_reviews ON reviews.id = scraped_reviews.review_id ORDER BY reviews.date DESC """) blm = {} smoothed_blm = {} total = 0 query_result = cur.fetchall() for item in query_result: string = item["body_text"] words = string.split() for word in words: total += 1 if word in blm: blm[word] += 1 else: blm[word] = 1 for word in blm: smoothed_blm[word] = (blm[word] + 1) / float(total + 20000) product_ids = [] cur.execute(""" SELECT DISTINCT reviews.product_id FROM reviews INNER JOIN scraped_reviews ON reviews.id = scraped_reviews.review_id ORDER BY reviews.product_id ASC """) query_result = cur.fetchall() for item in query_result: product_ids.append(item["product_id"]) for product_id in product_ids: tlm = {} sn_tlm = {} total = 0 cur.execute( """ SELECT reviews.body_text, reviews.product_id FROM reviews INNER JOIN scraped_reviews ON reviews.id = scraped_reviews.review_id WHERE reviews.product_id = %s """, product_id) query_result = cur.fetchall() for item in query_result: string = item["body_text"] words = string.split() for word in words: total += 1 if word in tlm: tlm[word] += 1 else: tlm[word] = 1 for word in tlm: tlm[word] = max(tlm[word], 0) / float(total) # divide by smoothed background language model sn_tlm[word] = tlm[word] / smoothed_blm[word] sorted_sn_tlm = sorted(sn_tlm.iteritems(), key=operator.itemgetter(1), reverse=True) count = 0 # print product_id tags = [] for pair in sorted_sn_tlm: if len(pair[0]) == 1 or is_number(pair[0]) or "cnet" in pair[0] or\ "verge" in pair[0] or "mags" in pair[0] or "wired" in pair[0]: continue count += 1 # print str(pair[1])[:9], pair[0] tags.append(pair[0]) if count >= 40: break stringified_tags = ",".join(tags) # print stringified_tags # print "" cur.execute( """ INSERT INTO product_tags VALUES (NULL, %s, %s) ON DUPLICATE KEY UPDATE product_id = %s, tags = %s; """, (product_id, stringified_tags, product_id, stringified_tags)) db.commit()