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)