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()
