def register():
if request.method == 'GET':
if session.get('logged_in'):
return render_template('search.html', username=session.get('logged_in'))
else:
return render_template("register.html")
else:
# get form information
username = request.form.get("username")
password = request.form.get("password")
rePassword = request.form.get("rePassword")
if not username or not password or not rePassword:
return render_template("error.html", message="Fields can't be empty!")
# checks if the username exists
if db.execute("SELECT * FROM users WHERE username = :username", {"username": username}).rowcount != 0:
return render_template("error.html", message=username+" has already been used!")
# checks if the password is correct
elif password != rePassword:
return render_template("error.html", message="Password doesn't match")
# insert it to the database
db.execute("INSERT INTO users (username, password) VALUES (:username, :password)",
{"username": username, "password": generate_password_hash(password)})
db.commit()
return redirect(url_for('index'))
def update(self):
columns = ", ".join(
[column + "=?" for column in self.DB_COLUMNS.keys()])
query = "UPDATE %s SET trackable=?, %s WHERE id=?" % (self.DB_TABLE,
columns)
print(query)
db.execute(query, (self.trackable, *self.get_db_values(), self.id))
db.commit()
def insert(self):
query = "INSERT INTO %s VALUES (NULL, ?, ?, ?, ?, %s)" % (
self.DB_TABLE, ", ".join(["?"] * len(self.DB_COLUMNS.keys())))
print(query)
db.execute(query, (ASSET_ID, self.frame, self.label_id, self.trackable,
*self.get_db_values()))
db.commit()
self.id = db.lastrowid()
def remove(self):
query = "DELETE FROM %s WHERE id=?" % (self.DB_TABLE, )
print(query)
db.execute(query, (self.id, ))
db.execute(
"DELETE FROM interpolated_markers WHERE src_marker_table=? AND src_marker_id=?",
(self.DB_TABLE, self.id))
db.execute(
"DELETE FROM interpolated_markers WHERE dst_marker_table=? AND dst_marker_id=?",
(self.DB_TABLE, self.id))
db.commit()
def addRecords():
with open('books.csv') as csv_file:
csv_reader = csv.reader(csv_file, delimiter=',')
next(csv_reader)
for isbn, title, author, yearPublished in tqdm(csv_reader):
db.execute(
"INSERT INTO books (isbn, title, author, yearPublished) VALUES (:isbn, :title, :author, :yearPublished)",
{
"isbn": isbn,
"title": title,
"author": author,
"yearPublished": yearPublished
})
db.commit()
db.connect(DB_PATH)
#db.execute("DROP TABLE labels")
db.execute(
"CREATE TABLE IF NOT EXISTS labels (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE)"
)
#db.execute("DROP TABLE assets")
db.execute(
"CREATE TABLE IF NOT EXISTS assets (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, frame_count INTEGER, width INTEGER, height INTEGER)"
)
#db.execute("DROP TABLE images")
db.execute(
"CREATE TABLE IF NOT EXISTS images (asset_id INTEGER, frame INTEGER, data BLOB)"
)
db.commit()
# Prepare labels
for label_name in USED_LABELS:
try:
db.execute("INSERT INTO labels (name) VALUES (?)", (label_name, ))
db.commit()
except Exception:
pass
db.execute("SELECT id, name FROM labels")
LABELS = db.fetchall()
# Prepare asset
if INPUT_FILE_NAME == "NO_INPUT":
ASSET_ID = 0
def deleteRecords():
db.execute("DELETE FROM books")
db.commit()
def delete():
db.execute("DELETE FROM users")
db.commit()
return redirect(url_for('index'))
def result():
if not session.get('logged_in'):
return render_template('error.html', message="ERROR 401 Unauthorised - Please log in first!")
# retrieve old reviews
if request.method == 'GET':
isbn = request.args.get("search")
if not isbn:
return render_template('error.html', message="GET Query not found!")
# get book data
book = db.execute("SELECT isbn, title, author, yearPublished \
FROM books \
WHERE isbn = :isbn", {"isbn": isbn}).fetchone()
# get user's review
review = db.execute("SELECT review, star \
FROM reviews \
WHERE id = :id AND isbn = :isbn", {"id" : session.get('logged_in'), "isbn" : isbn}).fetchone()
# get all reviews
reviews = db.execute("SELECT * \
FROM reviews \
WHERE isbn = :isbn", {"isbn" : isbn}).fetchall()
if not book:
return render_template("error.html", message = "Book not found!")
# get goodread's review
res = requests.get("https://www.goodreads.com/book/review_counts.json",
params={"key": os.environ.get("GOODREAD_KEY"), "isbns": isbn}).json()['books'][0]
if res:
rating_count = res['ratings_count']
avg_rating = res['average_rating']
return render_template("result.html", book = book, review = review, reviews = reviews, rating_count = rating_count,
avg_rating = avg_rating, starDesc = zip(starRating, starValue, starSize, starTitle))
# if user wants to add a new review
else:
# retrieve info from forms
rating = request.form.get("rating")
review = request.form.get("review")
isbn = request.form.get("search")
currentTime = time.time()
ts = datetime.datetime.fromtimestamp(currentTime).strftime('%Y-%m-%d %H:%M:%S')
username = db.execute("SELECT username FROM users WHERE id = :id", {"id" : session.get('logged_in')}).fetchone()[0]
if not rating:
return render_template("error.html", message="Rating can't be empty!")
elif not review:
return render_template("error.html", message="Review field can't be empty!")
# insert it to the database
db.execute("INSERT INTO reviews (id, username, isbn, star, review, review_timestamp) \
VALUES (:id, :username, :isbn, :star, :review, :ts)",
{"id": session.get('logged_in'), "username": username, "isbn": isbn,
"star": rating, "review": review, "ts": ts})
db.commit()
return redirect(url_for('results', search=session['query'], page=session['pageNo']))
