def edit_book(book_id): # retrieve original book details to put in the form by default c,conn = connection() c.execute(''' select name, author, description from books where book_id=(%s);''', (int(book_id), )) book_data = c.fetchone() original_name = book_data[0] original_author = book_data[1] original_desc = book_data[2] if request.method == 'POST': book_name = request.form['book_name'].encode('utf-8') author_name = request.form['author_name'].encode('utf-8') description = request.form['description'].encode('utf-8') super_category = request.form.get('super_category', None) try: c, conn = connection() c.execute(''' update books set name=(%s), author=(%s), description=(%s), super_category=(%s) where book_id=(%s);''', (book_name, author_name, description, super_category, book_id)) conn.commit() except Exception as e: flash(str(e)) finally: conn.close() return redirect(url_for('user_bookshelf', username=session['username'])) return render_template("edit_book.html", book_id=book_id, original_name=original_name, original_author=original_author, original_desc=original_desc)
def reading_list(username): try: user_id=username_to_userid(username) c, conn = connection() #fetch list of book ids added to the list reading_list = c.execute('''select distinct b.book_id, name, author, description, file, ub.user_id, u.username from users u, books b, user_books ub, reading_list rl where b.book_id = ub.book_id and u.user_id = ub.user_id and rl.user_id = (%s) and rl.book_id = b.book_id;''', (user_id, )) user_book_list = c.fetchall() total = len(user_book_list) page = request.args.get('page', type=int, default=1) per_page = 9 pagination = Pagination(total, per_page, page) return render_template("reading_list.html", p=pagination, book_list = user_book_list, username=username, userid = user_id) except Exception as e: flash(e)
def login(): try: c, conn = connection() error = None if request.method == 'POST': #check email match data = c.execute("select * from users where email = (%s)", (request.form['email_username'], )) if int(data) <= 0: data = c.execute("select * from users where username = (%s)", (request.form['email_username'], )) if int(data) <= 0: flash("This username or email address does not exist.") if int(data) > 0: data_row = c.fetchone() data = data_row[3] if sha256_crypt.verify(request.form['password'], data): session['logged_in'] = True session['username'] = data_row[2] flash('You are now logged in as ' + str(session['username'])) return redirect(url_for('browsebooks')) else: flash('Invalid password, try again.') gc.collect() return render_template('login.html', error=error) except Exception as e: flash(str(e)) return render_template('login.html', error=e)
def user_bookshelf(username): try: c, conn = connection() user_data = c.execute("select user_id from users where email = (%s)", (username, )) if int(user_data) <= 0: user_data = c.execute("select user_id from users where username = (%s)", (username, )) user_id = c.fetchone()[0] c.execute('''select users.user_id, books.book_id, name, author, description from users, user_books, books where users.user_id=user_books.user_id and user_books.book_id=books.book_id and users.user_id=(%s);''', (user_id, )) user_book_list = c.fetchall() total = len(user_book_list) page = request.args.get('page', type=int, default=1) per_page = 6 pagination = Pagination(total, per_page, page) return render_template('user_bookshelf.html', user_book_list=user_book_list, username=username, p = pagination) except Exception as e: flash(str(e))
def insert_book(username, book_name, author_name, description, book_type, book, super_category): # insert user_id, book_id into user-books table # insert book name, author, description, type, size and the file into books table try: c, conn = connection() if request.method == 'POST': # check if email matches username user_data = c.execute("select user_id from users where email = (%s)", (username, )) if int(user_data) <= 0: user_data = c.execute("select user_id from users where username = (%s)", (username, )) if int(user_data) > 0: # insert book into books user_id = c.fetchone()[0] # insert book c.execute('''insert into books (name, author, description, type, file, super_category) values (%s, %s, %s, %s, %s, %s)''', (book_name, author_name, description, book_type, book, super_category)) book_data = c.execute("select last_insert_id();") book_id = c.fetchone()[0] #insert user_id and book_id c.execute("insert into user_books (user_id, book_id) values (%s, %s)", (user_id, book_id)) conn.commit() conn.close() return('You have successfully uploaded the book.') gc.collect() except Exception as e: return(str(e))
def signup(): try: form = RegistrationForm(request.form) if request.method == 'POST' and form.validate(): username = form.username.data email = form.email.data if username is None: flash("Please choose a username.") password = sha256_crypt.encrypt(str(form.password.data)) c, conn = connection() x = c.execute("select * from users where username = (%s)", (username, )) if int(x) > 0: flash("Sorry! This username is already taken, please choose another one.") return render_template('signup.html', form=form) else: c.execute('''insert into users (email, username, password) values (%s, %s, %s)''', (email, username, password)) conn.commit() flash("Thanks for registering.") conn.close() gc.collect() session['logged_in'] = True session['username'] = username return redirect(url_for('browsebooks')) gc.collect() return render_template('signup.html', form=form) except Exception as e: return(str(e))
def username_to_userid(username): c, conn = connection() user_data = c.execute("select user_id from users where email = (%s)", (username, )) if int(user_data) <= 0: user_data = c.execute("select user_id from users where username = (%s)", (username, )) user_id = c.fetchone()[0] conn.close() return user_id
def delete_book(book_id): try: c, conn = connection() c.execute(''' delete from user_books where book_id=(%s);''', (int(book_id), )) #c.execute(''' delete from books where book_id = (%s);''', (int(book_id), )) conn.commit() return redirect(redirect_url()) except Exception as e: flash(e) finally: conn.close()
def oauth_callback(provider): # if not current_user.is_anonymous(): # return redirect(url_for('browsebooks')) oauth = OAuthSignIn.get_provider(provider) username, email = oauth.callback() session['logged_in'] = True session['username'] = email if email is None: flash('Authentication failed. Can you try using another account?') return(url_for('browsebooks')) # Look if the user already exists, if no then sign him up try: c, conn = connection() emails = c.execute(''' select email from users where email = (%s);''', (email, )) if int(emails) == 0: # Create the user if username is None or username == "": username = email.split('@')[0] c.execute(''' insert into users (email, username) value (%s, %s);''', (email, username)) conn.commit() except Exception as e: flash(str(e)) finally: conn.close() # if email is None: # # I need a valid email address for my user identification # flash('Authentication failed.') # return redirect(url_for('browsebooks')) # # Look if the user already exists # # user=User.query.filter_by(email=email).first() # if not user: # # Create the user. Try and use their name returned by Google, # # but if it is not set, split the email address at the @. # nickname = username # if nickname is None or nickname == "": # nickname = email.split('@')[0] # # We can do more work here to ensure a unique nickname, if you # # require that. # user=User(nickname=nickname, email=email) # db.session.add(user) # db.session.commit() # Log in the user, by default remembering them for their next visit # unless they log out. # login_user(user, remember=True) flash("You are logged in as {0}".format(email)) return redirect(url_for('browsebooks'))
def remove_from_reading_list(username, book_id): try: user_id = username_to_userid(username) c, conn = connection() c.execute('''delete from reading_list where user_id=(%s) and book_id=(%s);''', (user_id, book_id)) conn.commit() flash('The book has been removed from your reading list.') return redirect(redirect_url()) except Exception as e: flash(e) finally: conn.close()
def browsebooks(category='all'): try: page = request.args.get('page', type=int, default=1) per_page = 27 c, conn = connection() if category == 'all': c.execute('''select a.book_id, a.name, a.author, a.description, a.file, a.user_id, a.username, num, a.email from (select b.book_id, name, author, description, file, u.user_id, u.username, u.email from users u, books b, user_books ub where b.book_id=ub.book_id and u.user_id = ub.user_id) as a left join nlikes on a.book_id=nlikes.book_id order by num desc;''') if category == 'fiction': c.execute('''select a.book_id, a.name, a.author, a.description, a.file, a.user_id, a.username, num, a.email from (select b.book_id, name, author, description, file, u.user_id, u.username, u.email from users u, books b, user_books ub where b.book_id=ub.book_id and u.user_id = ub.user_id and b.super_category=(%s)) as a left join nlikes on a.book_id=nlikes.book_id order by num desc;''', ('Fiction', )) if category == 'non_fiction': c.execute('''select a.book_id, a.name, a.author, a.description, a.file, a.user_id, a.username, num, a.email from (select b.book_id, name, author, description, file, u.user_id, u.username, u.email from users u, books b, user_books ub where b.book_id=ub.book_id and u.user_id = ub.user_id and b.super_category=(%s)) as a left join nlikes on a.book_id=nlikes.book_id order by num desc;''', ('Non-Fiction', )) book_list = c.fetchall() total = len(book_list) pagination = Pagination(total, per_page, page) return render_template("browse_books.html", book_list=book_list, p = pagination, category=category) except Exception as e: flash(str(e))
def add_to_likes(book_id, username='******'): try: c, conn = connection() user_id = username_to_userid(username) # insert into likes table c.execute(''' insert into likes (user_id, book_id) values (%s, %s);''', (user_id, book_id)) conn.commit() return redirect(redirect_url()) except Exception as e: flash(str(e)) finally: conn.close()
def search(): if True: search_string = request.args.get('search_string') if search_string is None: return redirect(redirect_url()) try: c, conn = connection() # search by book or author ''' select a.book_id, a.name, a.author, a.description, a.file, a.user_id, a.username, num from (select b.book_id, name, author, description, file, u.user_id, u.username from users u, books b, user_books ub where b.book_id=ub.book_id and u.user_id = ub.user_id and (b.name like %s or b.author like %s or u.username like %s)) as a left join nlikes on a.book_id=nlikes.book_id''' b = c.execute('''select a.book_id, a.name, a.author, a.description, a.file, a.user_id, a.username, num from (select b.book_id, name, author, description, file, u.user_id, u.username from users u, books b, user_books ub where b.book_id=ub.book_id and u.user_id = ub.user_id and (b.name like %s or b.author like %s or u.username like %s)) as a left join nlikes on a.book_id=nlikes.book_id;''', ('%{0}%'.format(search_string), '%{0}%'.format(search_string), '%{0}%'.format(search_string))) book_list = c.fetchall() if int(b) == 0: flash("Sorry! There's no book, author or user by this name." ) return redirect(redirect_url()) else: total = len(book_list) per_page = 18 page = request.args.get('page', type=int, default=1) pagination = Pagination(total, per_page, page) return render_template("browse_books.html", book_list=book_list, p = pagination, category='all', search_string = search_string) except Exception as e: flash(str(e))
def requestbook(): book_name = request.args.get('book_name', default = "") author_name = request.args.get('author_name', default = "") description = request.args.get('description', default="") username = session['username'] user_id = username_to_userid(username) try: c, conn = connection() c.execute(''' insert into requests (user_id, book_name, author_name, description) values (%s, %s, %s, %s);''', (user_id, book_name, author_name, description)) conn.commit() flash("Your request will be notified to other users.") return redirect(redirect_url()) except Exception as e: flash(e)
def notifications(username): try: c, conn = connection() user_id = username_to_userid(username) r = c.execute(''' select username, book_name, author_name, description, year(ts), month(ts), r.user_id from users u, requests r where u.user_id=r.user_id and u.user_id != (%s);''', (user_id, )) all_requests = c.fetchall() if int(r) == 0: flash('There are no notifications.') return redirect(redirect_url()) else: # months = [calendar.month_abbr[all_requests[x][4].month] for x in range(len(all_requests))] return render_template('notifications.html', all_requests=all_requests) except Exception as e: flash(e)
def add_to_reading_list(book_id, username='******'): try: c, conn = connection() user_data = c.execute("select user_id from users where email = (%s)", (username, )) if int(user_data) <= 0: user_data = c.execute("select user_id from users where username = (%s)", (username, )) user_id = c.fetchone()[0] # insert into reading _list c.execute(''' insert into reading_list (user_id, book_id) values (%s, %s);''', (user_id, book_id)) conn.commit() flash('The book has been added to your reading list.') return redirect(redirect_url()) except Exception as e: flash(str(e)) finally: conn.close()
def other_user_shelf(other_user_id): # get all the books uploaded by other_user_id try: c, conn = connection() user_id = other_user_id c.execute('''select users.user_id, books.book_id, name, author, description, file, username from users, user_books, books where users.user_id=user_books.user_id and user_books.book_id=books.book_id and users.user_id=(%s);''', (user_id, )) user_book_list = c.fetchall() total = len(user_book_list) page = request.args.get('page', type=int, default=1) per_page = 6 pagination = Pagination(total, per_page, page) return render_template("other_user.html", p=pagination, user_book_list=user_book_list, user_id=user_id) except Exception as e: flash(str(e))