def register_user(register_form): # hash password for security hashed_pw = bcrypt.generate_password_hash( register_form.password.data).decode('utf-8') # check if username and email are unique usernames = db.execute( "SELECT username FROM Users WHERE username=:username", { 'username': register_form.username.data }).fetchone() emails = db.execute("SELECT email FROM Users WHERE email=:email", { 'email': register_form.email.data }).fetchone() if usernames: flash('Username is already taken. Please chose another one', 'danger') return redirect(url_for('register')) if emails: flash('Email address is already taken. Please chose another one', 'danger') return redirect(url_for('register')) else: # create user and add to database user = db.execute( "INSERT INTO Users(username, email, password, avatar) VALUES(:username, :email, :hashed_pw, :avatar)", { "username": register_form.username.data, "email": register_form.email.data, "hashed_pw": hashed_pw, "avatar": 'default.jpg' }) db.commit() flash("Your account has been succesfully created. You can now log in!", "success") return redirect(url_for('login'))
def reset_password(token): if 'user' in session: return redirect(url_for('home')) try: username = jwt.decode(token, app.config['SECRET_KEY'], algorithms=['HS256'])['reset_password'] except: flash(f'Link invalid or expired.', 'danger') return redirect(url_for('home')) if username: user = db.execute(f"SELECT * FROM Users WHERE username=:username", { 'username': username }).fetchone() form = UpdateUserForm() search = SearchForm() if request.method == 'POST' and form.validate(): hashed_pw = bcrypt.generate_password_hash( form.new_password.data).decode('utf-8') db.execute(f"UPDATE Users SET password='******'") db.commit() # log user session['user'] = user flash( f"Welcome back {session['user'][1]}! Your password has been reset and you are now connected!", "success") return redirect(url_for('user_account', username=user.username)) return render_template('reset_password.html', title="Reset Password", user=user, form=form, searchform=search)
def most_reviewed(): isbns = db.execute("SELECT isbn FROM Books LIMIT 350").fetchall() res = review_counts_res(isbns) book_counts = res['books'] book_counts.sort(key=lambda k: k['work_reviews_count'], reverse=True) isbn = [] # ----- Create a list of the 6 most reviewed books isbns for book in book_counts[0:6]: # Check if goodread average is already in db avg = db.execute("SELECT average_rating FROM Books WHERE isbn=:isbn", { 'isbn': book['isbn'] }).fetchone() if avg[0] is None: # ---- Update database with goodread average rating db.execute( "UPDATE Books SET average_rating=:average_rating WHERE isbn=:isbn", { 'average_rating': book['average_rating'], 'isbn': book['isbn'] }) db.commit() # ---- Add 6 most reviewed isbns to the list isbn.append(book['isbn']) # ---- Fetch the 6 most goodread reviewed books from our database isbn = tuple(isbn) most_reviewed = db.execute( f"SELECT * FROM Books WHERE isbn IN {isbn}").fetchall() return most_reviewed
def delete_account(): if 'user' in session: user = session['user'] # log user out session.pop('user', None) # delete all datas from db db.execute(f"DELETE FROM Users WHERE id='{user.id}'") db.commit() flash(f"Your account have been deleted", "info") return redirect(url_for('register')) else: abort(404)
def search_results(results): #Check for goodread average rating books = review_counts_res(results) updated = [] # Create list of updated rating search for book in books['books']: # Check if average rating in database is same as goodreads avg = db.execute( f"SELECT average_rating FROM Books WHERE isbn='{book['isbn']}'" ).fetchone() if avg[0] is None: # Update db if rating is none or different db.execute( "UPDATE Books SET average_rating=:average_rating WHERE isbn=:isbn", { 'average_rating': book['average_rating'], 'isbn': book['isbn'] }) db.commit() update = db.execute( f"SELECT * FROM Books WHERE isbn='{book['isbn']}'").fetchone() updated.append(update) return updated
def book(book_title, book_id): search = SearchForm() review = SubmitReviewForm() login = LoginForm() requestForm = RequestResetForm() book = db.execute("SELECT * FROM Books WHERE id=:id", { 'id': book_id }).fetchone() description = book_description(book.title, book.author) author = same_author(book) recents = db.execute( f"SELECT * FROM Users JOIN Reviews ON Reviews.user_id=Users.id WHERE book_id IN (SELECT id FROM Books WHERE id={book_id})" ).fetchall() recents.sort(reverse=True) now = current_time() if 'user' in session: # Check if user already left a review edit = db.execute( f"SELECT book_id FROM Reviews WHERE user_id={session['user'].id} AND book_id={book_id}" ).fetchall() if len(edit) > 0: return render_template('book.html', title=book_title, searchform=search, book=book, author=author, description=description, edit=edit, recents=recents, user=session['user']) if request.method == "POST" and review.validate(): db.execute( "INSERT INTO Reviews (body_text, rating, datetime, book_id, user_id) VALUES (:body_text, :rating, :datetime, :book_id, :user_id)", { "body_text": review.review.data, "rating": review.rating.data, "datetime": now, "book_id": book_id, "user_id": session['user'].id }) # Add review count to book database db.execute( "UPDATE Books SET review_count = review_count + 1 WHERE id=:id", {"id": book_id}) db.commit() flash(f'Your review has been post', 'success') return redirect( url_for('book', book_title=book.title, book_id=book.id)) return render_template('book.html', title=book_title, searchform=search, book=book, author=author, description=description, review=review, recents=recents, user=session['user']) if request.method == "POST" and login.validate(): log_user(login) return redirect(url_for('book', book_title=book.title, book_id=book.id)) if request.method == "POST" and requestForm.validate(): return reset_pwd_request(requestForm) return render_template('book.html', title=book_title, form=login, searchform=search, book=book, author=author, description=description, recents=recents, requestform=requestForm)
def user_update(form, user): # Check new username and email address are not already use if form.username.data is not user.username and form.email.data is not user.email: username = db.execute( "SELECT username FROM Users WHERE username=:username", { 'username': form.username.data }).fetchone() email = db.execute("SELECT email FROM Users WHERE email=:email", { 'email': form.email.data }).fetchone() if username: flash('Username is already taken. Please chose another one', 'danger') return redirect( url_for('user_account', username=session['user'][1])) if email: flash('Email address is already taken. Please chose another one', 'danger') return redirect( url_for('user_account', username=session['user'][1])) # ------------ USERNAME ----------- # Update username in database if form.username.data: db.execute( f"UPDATE Users SET username='******' WHERE username=:username", {'username': user.username}) db.commit() flash("Your username has been updated!", "info") # ------------ EMAIL ----------- # Update email in database if form.email.data: db.execute( f"UPDATE Users SET email='{form.email.data}' WHERE email=:email", {'email': user.email}) db.commit() flash("Your email address has been updated!", "info") # ------------ PASSWORD ----------- if form.new_password.data: #check current password is correct hashed_pw = db.execute("SELECT password FROM Users WHERE id=:id", { 'id': user.id }).fetchone() pw_checked = bcrypt.check_password_hash(f'{hashed_pw[0]}', form.current_password.data) if pw_checked is False: flash("Invalid password. Please try again", "danger") return redirect( url_for('user_account', username=session['user'][1])) #check if new password is new if bcrypt.check_password_hash(f'{hashed_pw[0]}', form.new_password.data): flash("You are already using this password", "info") return redirect( url_for('user_account', username=session['user'][1])) # Update password in database else: # hash password for security hashed_pw = bcrypt.generate_password_hash( form.new_password.data).decode('utf-8') db.execute( f"UPDATE Users SET password='******' WHERE password=:password", {'password': user.password}) db.commit() flash("Your password has been reset", "info") # -------------- PICTURE ----------- if form.picture.data: picture_file = save_picture(form.picture.data) db.execute(f"UPDATE Users SET avatar='{picture_file}' WHERE id=:id", {'id': user.id}) db.commit() # Query database for updated user and refresh the session variable session['user'] = db.execute( f"SELECT * FROM Users WHERE id='{user.id}'").fetchone() return redirect(url_for('user_account', username=session['user'][1]))