def rem_rating(isbn, rating_id): query = "DELETE FROM Ratings WHERE Ratings.rating_id = %s" values = (rating_id) db_query(query, values) code = "23" # rating delete success code url = ("/book/" + isbn + "/update/" + code) return redirect(url)
def add_author(): if request.method == 'GET': select = "select book.isbn, book.book_title from Books book order by book.book_title ASC;" result = fetch(select) return render_template('add_author.html', books=result) elif request.method == 'POST': select = "SELECT MAX(Authors.author_id) FROM Authors" result = fetch(select) # Step 1: Query to get max PK value of author_id author_id = result[0]['MAX(Authors.author_id)'] author_id += 1 # Step 2: Fetch Author information from form author_name = request.form['author_name'] author_description = request.form['author_description'] isbn = request.form['author_book'] query = 'INSERT INTO Authors (author_id, author_name, author_description) VALUES (%s,%s,%s)' values = (author_id, author_name, author_description) db_query(query, values) # Step 3: Insert new Authors entry query = 'INSERT INTO Books_Authors (isbn, author_id) VALUES (%s,%s)' values = (isbn, author_id) db_query(query, values) # Step 4: Insert Books_Authors entry to link new Author to Book url = ("/authors/" + str(author_id) + "/add_success/" + author_name + "/") return redirect(url)
def edit_rating(isbn, rating_id): star_rating = request.form['update_rating'] query = "UPDATE Ratings SET star_rating = %s WHERE rating_id = %s" values = (star_rating, rating_id) db_query(query, values) code = "19" # Rating edit success code url = ("/book/" + isbn + "/update/" + code) return redirect(url)
def rem_review(isbn, review_id): query = "DELETE FROM Reviews WHERE Reviews.review_id = %s" values = (review_id) db_query(query, values) code = "17" # Review delete success code url = ("/book/" + isbn + "/update/" + code) return redirect(url)
def edit_review(isbn, review_id): content = request.form['update_review_content'] content = stringsafe(content) query = "UPDATE Reviews SET review_content = %s WHERE review_id = %s" values = (content, review_id) db_query(query, values) code = "13" # Review edit success code url = ("/book/" + isbn + "/update/" + code) return redirect(url)
def edit_genre(genre_id): new_name = request.form['update_genre_name'] name_string = stringsafe(new_name) query = "UPDATE Genres SET genre_name = %s WHERE genre_id = %s" values = (name_string, genre_id) db_query(query, values) url = ("/genre/" + genre_id + "/edit_success/" + new_name + "/") print(url) return redirect(url)
def rem_author(): author_id = request.form['author_id'] # Step 1: Get Author info query = "DELETE FROM Books_Authors WHERE Books_Authors.author_id = %s" values = (author_id) db_query(query, values) query = "DELETE FROM Authors WHERE Authors.author_id = %s" db_query(query, values) url = ("/authors/rem_success") return redirect(url)
def add_genre(): if request.method == 'GET': return render_template('add_genre.html') elif request.method == 'POST': select = "SELECT MAX(Genres.genre_id) FROM Genres" result = fetch(select) # query 1: get max PK value of genre_id genre_id = result[0]['MAX(Genres.genre_id)'] genre_id += 1 genre_name = request.form['genre_name'] query = 'INSERT INTO Genres (genre_id, genre_name) VALUES (%s,%s)' values = (genre_id, genre_name) db_query(query, values) # query 2: insert new value to Genres return ("Genre added! <a href='/'>(back to paperstacks)</a>");
def edit_author(author_id): # Step 1: Update name name = request.form['update_author_name'] name = stringsafe(name) query = "UPDATE Authors SET author_name = %s WHERE author_id = %s" values = (name, author_id) db_query(query, values) # Step 2: Update author bio bio = request.form['update_author_bio'] bio = stringsafe(bio) # add escape characters to single and double quotes query = "UPDATE Authors SET author_description = %s WHERE author_id = %s" values = (bio, author_id) db_query(query, values) url = ("/author/" + author_id + "/edit_success/") return redirect(url)
def rem_genre(id): select = "SELECT COUNT(genre.genre_id) AS `count` FROM Genres genre JOIN Genres_Books gb ON gb.genre_id = genre.genre_id JOIN Books book ON gb.isbn = book.isbn WHERE genre.genre_id = " + id result = fetch(select) # Step 1: Check to make sure no Books associated with this Genre # if there ARE books still in the genre if result[0]['count'] != 0: url = ("/genre/" + id + "/" + "error/") print(url) return redirect(url) # Delete Genre else: select = "select genre.genre_name from Genres genre where genre.genre_id = " + id result = fetch(select) # get the name of the Genre we're removing genre_to_remove = result[0]['genre_name'] query = "DELETE FROM Genres WHERE Genres.genre_id = %s" values = (id) db_query(query, values) # delete the Genre url = ("/genres/rem_success/" + genre_to_remove + "/") return redirect(url)
def add_review(): if request.method == 'GET': select = "select book.isbn, book.book_title from Books book order by book.book_title ASC;" result = fetch(select) return render_template('add_review.html', books=result) elif request.method == 'POST': isbn = request.form['author_book'] # Adding a Rating from this form is optional, so see if the user # chose to add one, or not if request.form['user_rating'] != 'null': # Step 1: Need new rating_id PK select = "SELECT MAX(Ratings.rating_id) FROM Ratings" result = fetch(select) rating_id = result[0]['MAX(Ratings.rating_id)'] rating_id += 1 # Step 2: Fetch form info for Rating star_rating = request.form['user_rating'] rating_date = time.strftime('%Y-%m-%d') # Step 3: Insert Rating, Note: review_id initially disregarded as FK to avoid insert errors query = 'INSERT INTO Ratings (rating_id, isbn, star_rating, rating_date) VALUES (%s,%s,%s,%s)' values = (rating_id, isbn, star_rating, rating_date) db_query(query, values) else: rating_id = None # Step 4: If Review not empty... if request.form['user_review'] != '': # 4a. First, need a new review_id PK for our new Review entry select = "SELECT MAX(Reviews.review_id) FROM Reviews" result = fetch(select) review_id = result[0]['MAX(Reviews.review_id)'] review_id += 1 # 4b. Second, fetch Review info from form and system review_content = request.form['user_review'] review_date = time.strftime('%Y-%m-%d') query = 'INSERT INTO Reviews (review_id, rating_id, isbn, review_content, review_date) VALUES (%s,%s,%s,%s,%s)' values = (review_id, rating_id, isbn, review_content, review_date) db_query(query, values) # 4c. Connect to database and add Review # 4d. Last, update the Rating if we inserted one above with FK review_id if rating_id is not None: query = 'UPDATE Ratings set review_id = %s WHERE rating_id = %s' values = (review_id, rating_id) db_query(query, values) code = "15" # Review/Rating add success url = ("/book/" + isbn + "/update/" + code) return redirect(url)
def rem_book(isbn): # Before removing a Book, check to make sure no Authors would be left without at least one Book # Step 1: Get the list of authors for this book select = "SELECT ba.author_id from Books_Authors ba where ba.isbn = " + isbn author_ids = fetch(select) # Step 2: For all authors listed on this book, see how many books they have in the database. If any of them only have 1 book counted, that means they would be left without books if this one were removed, so the removal needs to be aborted and the user notified of the reason. for author in author_ids: auth = str(author['author_id']) select = "SELECT COUNT(ba.isbn) AS `book_count` FROM Books_Authors ba WHERE ba.author_id = " + auth result = fetch(select) # If author only has one book, abort and redirect if result[0]['book_count'] == 1: code = "6" url = ("/book/" + isbn + "/update/" + code) return redirect(url) # Step 3: If there were no issues with orphaned authors, delete all Books_Authors entries for this book query = "DELETE FROM Books_Authors WHERE Books_Authors.isbn = %s" values = (isbn) db_query(query, values) query = "DELETE FROM Genres_Books WHERE Genres_Books.isbn = %s" values = (isbn) db_query(query, values) # Step 4: Finally, delete the Book query = "DELETE FROM Books WHERE Books.isbn = %s" values = (isbn) db_query(query, values) code = "5" # Report book delete success url = ("/books/" + code) return redirect(url)
def edit_book(isbn): code = "0" # Update Author(s) if len(request.form.getlist('update_author')) != 0: # Get the current author list for this book select = 'SELECT ba.author_id FROM Books_Authors ba WHERE ba.isbn = ' + isbn current_authors = fetch(select) author_list = [] for x in current_authors: v = x['author_id'] author_list.append(v) # Determine which authors may only have one book to their name in our database must_have = [] for auth in author_list: select = "SELECT COUNT(ba.isbn) AS `book_count` FROM Books_Authors ba WHERE ba.author_id = " + str(auth) book_count = fetch(select) count = [] for x in book_count: v = x['book_count'] count.append(v) if count[0] == 1: must_have.append(str(auth)) # Then get our new author list new_authors = request.form.getlist('update_author') # Check that all authors we could be leaving without a book are in the user's new selections flag = all(x in new_authors for x in must_have) # If the author selections are acceptable, go through with editing them if flag == True: # Delete all previous Books_Authors entries first, so there are no orphans query = "DELETE FROM Books_Authors WHERE Books_Authors.isbn = %s" values = (isbn) db_query(query, values) # Insert our new list authors = request.form.getlist('update_author') for author_id in authors: query = 'INSERT INTO Books_Authors (isbn, author_id) VALUES (%s,%s)' values = (isbn, author_id) db_query(query, values) # Insert one or more Books_Authors entries # Else, the author selections are unacceptable, notify user and abort editing book else: code = "2" url = ("/book/" + isbn + "/update/" + code) return redirect(url) # Update Book Title if request.form['update_title'] != '': title = request.form['update_title'] title = stringsafe(title) query = "UPDATE Books SET book_title = %s WHERE isbn = %s" values = (title, isbn) db_query(query, values) # Update Book Description if request.form['update_book_description'] != '': description = request.form['update_book_description'] description = stringsafe(description) # add escape characters to single and double quotes query = "UPDATE Books SET book_description = %s WHERE isbn = %s" values = (description, isbn) # this automatically adds '' around strings. do not add manually db_query(query, values) # Update Year Published if request.form['update_year'] != '': year = request.form['update_year'] if (int(year) >= 0) and (int(year) < 2025): query = "UPDATE Books SET year_published = %s WHERE isbn = %s" values = (year, isbn) db_query(query, values) else: code = "33" # Invalid year input url = ("/book/" + isbn + "/update/" + code) return redirect(url) # Update Genre(s) if len(request.form.getlist('update_genre')) != 0: # Delete all previous Genres_Books entries first, so there are no orphans query = "DELETE FROM Genres_Books WHERE Genres_Books.isbn = %s" values = (isbn) db_query(query, values) # Then get our new list genres = request.form.getlist('update_genre') for genre_id in genres: query = 'INSERT INTO Genres_Books (isbn, genre_id) VALUES (%s,%s)' values = (isbn, genre_id) db_query(query, values) # Insert one or more Genres_Books entries if code == "0": # If no known issues with book edit thus far code = "1" # Report book edit success url = ("/book/" + isbn + "/update/" + code) return redirect(url)
def add_book(): if request.method == 'GET': select = "select genre.genre_id, genre.genre_name from Genres genre;" GenresSQL = fetch(select) # Get Genres information select = "select auth.author_id, auth.author_name from Authors auth;" AuthorsSQL = fetch(select) # Get Current Authors information return render_template('add_book.html', genres=GenresSQL, authors=AuthorsSQL) elif request.method == 'POST': code = "0" # Status code set to default # Operation 1: Fetch Book information from form book_title = request.form['book_title'] isbn = request.form['book_isbn'] year_published = int(request.form['book_year']) book_description = request.form['book_description'] book_description = stringsafe(book_description) # Insert New Book query = 'INSERT INTO Books (isbn, book_title, year_published, book_description) VALUES (%s, %s, %s, %s)' values = (isbn, book_title, year_published, book_description) db_query(query, values) # Associate Book with one or more Genres, via Genres_Books entries genre_ids = request.form.getlist('book_genre') # use getlist to get data from select multiple genre_ids = list(map(int, genre_ids)) # list comprehension: turn into ints that can be inserted # results = list(map(int, results)) # functional programming solution: map list to ints # results = [int(i) for i in results] # more pythonic solution: list comprehension for genre in genre_ids: query = 'INSERT INTO Genres_Books (isbn, genre_id) VALUES (%s, %s)' values = (isbn, genre) db_query(query, values) # Chose Existing Author(s), add Books_Authors entries if len(request.form.getlist('book_author')) != 0: author_ids = request.form.getlist('book_author') # use getlist for select multiple author_ids = list(map(int, author_ids)) # list comprehension: turn into ints that can be inserted for author in author_ids: query = 'INSERT INTO Books_Authors (isbn, author_id) VALUES (%s, %s)' values = (isbn, author) db_query(query, values) # Chose New Author, add Author then add Books_Authors entries elif len(request.form['author_name']) != 0 and len(request.form['author_description']) != 0: author_name = request.form['author_name'] author_name = stringsafe(author_name) author_description = request.form['author_description'] author_description = stringsafe(author_name) select = "SELECT MAX(Authors.author_id) FROM Authors" result = fetch(select) author_id = result[0]['MAX(Authors.author_id)'] author_id += 1 query = 'INSERT INTO Authors (author_id, author_name, author_description) VALUES (%s,%s,%s)' values = (author_id, author_name, author_description) db_query(query, values) query = 'INSERT INTO Books_Authors (isbn, author_id) VALUES (%s,%s)' values = (isbn, author_id) db_query(query, values) code = "32" # Successfully added book AND author # Did not enter Authors, can do later else: code = "31" # Book added but without authors if code == "0": code = "3" # If nothing went wrong, assume success. 3 is add book success code isbn = str(isbn) url = ("/book/" + isbn + "/update/" + code) return redirect(url)