def new_issue():
form = request.form
required_keys = [
"name", "priority", "version", "operating_system", "reported_by"
]
for k in required_keys:
if k not in form.keys():
abort(400)
name = form["name"]
description = form.get("description", "")
priority = form.get("priority")
status = form.get("status", 0)
version = form.get("version")
operating_system = form.get("operating_system")
reported_by = form.get("reported_by")
issue = Issue(name=name,
description=description,
priority=priority,
status=status,
version=version,
operating_system=operating_system,
reported_by=reported_by)
db.session.add(issue)
db.session.commit()
file = request.files.get('file')
if file and file.filename != '' and allowed_file(file.filename):
filename = secure_filename(file.filename)
filepath = os.path.join(app.config["UPLOAD_FOLDER"], filename)
file.save(filepath)
attachment = Attachment(filepath, issue_id=issue.id)
db.session.add(attachment)
db.commit()
return "", 200
def register():
""" This function for registering the user """
form = RegistrationForm()
if form.validate_on_submit():
hashed_password = bcrypt.generate_password_hash(
form.password.data).decode('utf-8')
users = db.execute(
"SELECT * FROM users WHERE username= :username OR email= :email", {
"username": form.username.data,
"email": form.email.data
}).fetchall()
if users:
flash('username or email taken please choose another one',
'danger')
else:
db.execute(
'INSERT INTO users(username, email, password) values (:username, :email, :password)',
{
"username": form.username.data,
"email": form.email.data,
"password": hashed_password
})
db.commit()
flash(f'Account created you can now log in', 'success')
return redirect(url_for('login'))
return render_template('register.html', form=form)
def register():
form = RegisterForm()
if request.method == 'POST' and form.validate_on_submit():
u = db.execute(
'SELECT * FROM public.user '
'WHERE name = :name',
{"name": form.login.data}
).fetchone()
if u:
flash(f'username {form.login.data} not available', 'alert')
return render_template('register.html', form=form)
db.execute(
'INSERT INTO public.user (name, hash)'
'VALUES (:name, :hash)',
{"name": form.login.data,
"hash": generate_password_hash(form.passw.data)}
)
db.commit()
flash(
'Registration completed successfully. '
'Now you can <a href="./login" class="alert-link">log in</a>.',
'success')
return redirect(url_for('index'))
else:
for field in form.errors:
for err in form.errors[field]:
flash(f'{field}: {err}', 'alert')
return render_template('register.html', form=form)
def review():
# Only allowed logged in users
if "username" not in session:
flash("You have to be logged in to see this page", "danger")
return redirect(url_for("auth.login"))
form = ReviewForm()
if form.validate_on_submit():
isbn = form.isbn.data
username = form.username.data
rating = form.rating.data
review = form.review.data
if db.execute(
"SELECT * FROM reviews WHERE isbn=:isbn and username=:username",
{
'isbn': isbn,
'username': username
}).fetchone() is not None:
flash("You can only review each book once", "danger")
else:
db.execute(
"INSERT INTO reviews (isbn, username, rating, review) VALUES (:isbn, :username, :rating, :review)",
{
'isbn': isbn,
'username': username,
'rating': rating,
'review': review
})
db.commit()
flash("Review submitted", "primary")
return redirect(url_for("main.book", isbn=isbn))
def register():
if session.get('usename'):
return redirect(url_for('index'))
form = RegisterForm()
if form.validate_on_submit():
email = form.email.data
password = form.password.data
first_name = form.first_name.data
last_name = form.last_name.data
user = db.execute(
"INSERT INTO users (email, password, first_name, last_name) VALUES (:email, :password, :first_name, :last_name)",
{
"email": email,
"password": generate_password_hash(password),
"first_name": first_name,
"last_name": last_name
})
db.commit()
flash("You are successfully registered!", "success")
return redirect(url_for('index'))
return render_template("register.html",
title="Register",
form=form,
register=True)
def register():
if "username" in session:
flash("You are already logged in", "secondary")
return redirect(url_for("main.index"))
form = RegistrationForm()
if form.validate_on_submit():
# Get data from form
username = form.username.data
password = generate_password_hash(form.password.data, method="SHA256")
# If username already exists, reject
if db.execute("SELECT * FROM users WHERE username=:username", {
"username": username
}).first() is not None:
flash("User already exists", "warning")
return redirect(url_for("auth.register"))
# Register user
db.execute(
"INSERT INTO users (username, password) VALUES (:username, :password)",
{
"username": username,
"password": password
})
db.commit()
session["username"] = username
flash("Registration successful", "success")
return redirect(url_for("main.index"))
return render_template("auth/register.html", form=form)
def newReview(self, id):
name = db.execute(
"SELECT firstname, lastname, avatar_img FROM users WHERE id = :id",
{
"id": id
}).fetchall()
db.commit()
return name
def avgCount(self, id):
avg_count = db.execute(
"SELECT round(AVG(score),2), COUNT(id) FROM reviews WHERE books_id = :id",
{
"id": id
}).fetchall()
db.commit()
return avg_count
def countDb(self):
count = db.execute(
"select count(review_text), count(score) from reviews WHERE users_id = :id",
{
"id": self.id
}).fetchall()
db.commit()
return count
def infoApi(self):
infoApi = db.execute(
"SELECT books.title, books.author, books.year, books.isbn, rating.review_count, "
"rating.average_score FROM books JOIN rating ON rating.books_id = books.id WHERE isbn = "
":isbn", {
"isbn": self.isbn
}).fetchall()
db.commit()
return infoApi
def updateDb(self, id, verification_code):
db.execute(
"UPDATE users SET email = :email, verification = 0, verification_code = :verification_code WHERE id = :id",
{
"verification_code": verification_code,
"id": id,
"email": self.email.data.lower()
})
db.commit()
def insertDb(self, user, id):
db.execute(
"INSERT INTO reviews (review_text, score, users_id, books_id)"
"VALUES (:review_text, :score, :users_id, :books_id)", {
"review_text": self.text,
"score": self.score,
"users_id": user,
"books_id": id
})
db.commit()
def main():
f = open('books2.csv')
booksReader = csv.reader(f)
for isbn, title, author, year in booksReader:
book = Book(isbn=isbn, title=title, author=author, year=year)
db.add(book)
# In cases of large datasets, expect a delay in committing as with books.csv
# estimated time to commit ~20 mins
db.commit()
f.close()
def updateLikes():
star = request.args['image_id']
print(star)
cur.execute(
"""
UPDATE images
SET likes=likes+1
WHERE img_id= %s""", star)
db.commit()
return "Success"
def updateRating():
img_id = request.args['image_id']
rating = request.args['rating_form']
cur.execute(
"""
UPDATE images
SET stars=%s
WHERE img_id= %s""", (rating, img_id))
db.commit()
return "Success"
def reviewDb(self):
review = db.execute(
"SELECT reviews.review_text, reviews.score, reviews.users_id, users.firstname, users.lastname,"
" users.avatar_img FROM reviews "
"JOIN books ON books.id = reviews.books_id "
"JOIN users ON reviews.users_id = users.id "
"WHERE books.isbn = :isbn", {
"isbn": self.isbn
}).fetchall()
db.commit()
return review
def isbn(isbn):
# Fetch the book details
book = db.execute("SELECT * FROM books WHERE isbn = :isbn", {
"isbn": isbn
}).fetchone()
good_read_review = get_good_reads_review(isbn)
review_count = good_read_review['books'][0]['work_ratings_count']
average_score = good_read_review['books'][0]['average_rating']
book_cover = get_book_cover(isbn)
# Fetch the reviews
reviews = db.execute(
"SELECT first_name, last_name, rate, comment, date, isbn_no FROM reviews LEFT JOIN users ON users.id = reviews.user_id WHERE reviews.isbn_no = :isbn",
{
"isbn": isbn
}).fetchall()
form = ReviewForm()
# Check is a logged user is posting a review
if request.method == 'POST':
if session.get('user_id') is None:
flash("You need to log in to write a review", "danger")
elif db.execute(
"SELECT * FROM reviews WHERE user_id = :user_id and isbn_no = :isbn",
{
"user_id": session.get('user_id'),
"isbn": isbn
}).rowcount != 0:
flash("Oops! You already reviewed this book!", "danger")
else:
rate = form.rate.data
review = form.review.data
user_id = session.get('user_id')
db.execute(
"INSERT INTO reviews (rate, comment, user_id, isbn_no, date) VALUES (:rate, :comment, :user_id, :isbn_no, :date)",
{
"rate": rate,
"comment": review,
"user_id": user_id,
"isbn_no": isbn,
"date": datetime.datetime.today().strftime('%d-%m-%Y')
})
db.commit()
flash("Thank you for reviewing this book!", "success")
return redirect(url_for('isbn', isbn=isbn))
else:
return render_template('isbn.html',
book=book,
form=form,
ratings_count=review_count,
avg_rating=average_score,
cover=book_cover,
reviews=reviews)
def insertDb(self, hash_pass, hash_email):
db.execute(
"INSERT INTO users (firstname, lastname, email, hash_psw, verification_code)"
"VALUES (:firstname, :lastname, :email, :hash_psw, :verification_code)",
{
"firstname": self.firstname.data,
"lastname": self.lastname.data,
"email": self.email.data.lower(),
"hash_psw": hash_pass,
"verification_code": hash_email
})
db.commit()
def register():
if current_user.is_authenticated:
return redirect(url_for('index'))
form = RegistrationForm()
if form.validate_on_submit():
user = User(username=form.username.data, email=form.email.data)
user.set_password(form.password.data)
db.add(user)
db.commit()
flash("Congratulations, you're in {}!".format(user.username))
return redirect(url_for('index'))
return render_template('register.html', title='Register', form=form)
def infoBookIsbn(self):
info = db.execute(
"SELECT books.isbn, books.title, books.author, "
"books.year, img_text.less, img_text.more, img_text.img_name, "
"rating.review_count, rating.average_score "
"FROM books "
"LEFT JOIN img_text ON img_text.books_id = books.id "
"LEFT JOIN rating ON rating.books_id = books.id "
"WHERE isbn = :isbn", {
"isbn": self.isbn
}).fetchall()
db.commit()
return info
def updateRadio():
star = request.form['star']
print(star)
cur.execute(
"""
UPDATE images
SET stars=%s
WHERE img_id= '1'""", star)
db.commit()
query = "SELECT * from images"
cur.execute(query)
data = cur.fetchall()
return render_template("view.html", data=data)
def create_tables():
print(".................CREATING TABLES.................................")
db.execute(
"CREATE TABLE IF NOT EXISTS users (id SERIAL PRIMARY KEY,username VARCHAR UNIQUE NOT NULL,password VARCHAR NOT NULL,email VARCHAR UNIQUE NOT NULL)"
)
db.execute(
"CREATE TABLE IF NOT EXISTS books(id SERIAL PRIMARY KEY,isbn VARCHAR NOT NULL,title VARCHAR NOT NULL,author VARCHAR NOT NULL,year INTEGER)"
)
db.execute(
"CREATE TABLE IF NOT EXISTS reviews(id SERIAL PRIMARY KEY, userid INTEGER REFERENCES users,bookid INTEGER REFERENCES books,review VARCHAR NOT NULL)"
)
db.commit()
print(".................TABLES ARE CREATED..............................")
def book(book_id):
""" This function going to add reviews submited by the user and desplaying information about each book"""
form = ReviewSumbission()
if form.validate_on_submit():
db.execute(
"INSERT INTO reviews(review, stars, book_id, user_id) VALUES (:review, :stars, :book_id, :user_id)",
{
"review": form.review.data,
"stars": float(form.select.data),
"book_id": book_id,
"user_id": session['user_id']
})
db.commit()
#return redirect(url_for('book'))
# quering the data base for the book information and then rendring a page with all the info.
book_info = db.execute("SELECT * FROM books WHERE books.id= :id", {
"id": book_id
}).fetchall()[0]
book_isbn = book_info['isbn']
reviwes = db.execute("SELECT * FROM reviews WHERE reviews.book_id = :id", {
"id": book_id
}).fetchall()
rating = db.execute(
"SELECT AVG(stars) as rating FROM reviews WHERE reviews.book_id = :id",
{
"id": book_id
}).fetchall()[0]['rating']
review_user = db.execute(
"SELECT * FROM reviews WHERE reviews.book_id = :id and reviews.user_id = :user_id",
{
"id": book_id,
"user_id": session['user_id']
}).fetchall()
## get the goodreads infromations
res = requests.get("https://www.goodreads.com/book/review_counts.json",
params={
"key": KEY,
"isbns": book_isbn
})
data = res.json()
data = data['books'][0]
return render_template('book.html',
book_info=book_info,
rating=rating,
review_user=review_user,
reviwes=reviwes,
form=form,
data=data)
def deleteImg():
img_id = request.args['image_id']
query = "select img_url from images where img_id=%s"
cur.execute(query, img_id)
data = cur.fetchall()
file_name = data[0][0].split('/')
img_name = file_name[3]
s3Client.delete_object(Bucket='aws-photo-bucket', Key=img_name)
query2 = "delete from images where img_id=%s"
cur.execute(query2, img_id)
db.commit()
query3 = "SELECT * from images"
cur.execute(query3)
data = cur.fetchall()
return render_template("view.html",
data=data,
msg="Image deleted successfully")
def uploadPhoto():
# print(index().name)
if request.method == 'POST':
if 'name' in session:
username = session['name']
if 'file' not in request.files:
# flash('No file part')
return render_template("homepage.html",
msg="No file part",
user_name=username)
f = request.files['file']
if f.filename == '':
return render_template("homepage.html",
msg="No selected file",
user_name=username)
if f and allowed_file(f.filename):
s3Client.upload_fileobj(f,
'aws-photo-bucket',
f.filename,
ExtraArgs={
"ACL": "public-read",
"ContentType": f.content_type
})
else:
return render_template("homepage.html",
msg="Only images are allowed to upload",
user_name=username)
url = "{}{}".format(S3_LOCATION, f.filename)
title_form = request.form['title']
print(url, title_form)
# timestamp of files:
for key in s3Client.list_objects(
Bucket='aws-photo-bucket')['Contents']:
# print key
filename = key['Key']
date = key['LastModified']
# print(date)
sqlq = "INSERT INTO images(img_url,title,likes,stars,timestamp,user_name) VALUES (%s,%s,%s,%s,%s,%s)"
print(sqlq)
cur.execute(sqlq, (url, title_form, 0, 0, date, username))
db.commit()
return render_template("homepage.html",
msg="Image uploaded suceesfully",
user_name=username)
def get_preview_urls(self, song_ids):
token = self.request.session.get('spotify_token')
code_payload = {
"grant_type": "client_credentials",
}
encoded_code_payload = urllib.urlencode(code_payload)
base64encoded = base64.b64encode("{}:{}".format(
CLIENT_ID_SPOTIFY, CLIENT_SECRETS_SPOTIFY))
spotify_auth_headers = {
"Authorization": "Basic {}".format(base64encoded)
}
if not token:
self.request.session['oauth_redirect'] = 'get_preview_url'
post_request = urllib2.Request(SPOTIFY_TOKEN_URL,
data=encoded_code_payload,
headers=spotify_auth_headers)
response = urllib2.urlopen(post_request)
response_data = json.loads(response.read())
self.request.session["spotify_token"] = response_data[
"access_token"]
print "len before exe", len(song_ids)
for song_id in song_ids:
song = db.query(Song).filter(Song.id == song_id).first()
if song.preview_url:
print "already have preview url and will be skipping"
continue
for query_type in [' - ', 'by', '-']:
song_title = song.get_full_title().replace(" - ", query_type)
search_results = self.make_search_query(song_title)
tracks = search_results.get('tracks')
items = tracks.get('items')
if len(items) == 0:
continue
if items[0].get('preview_url'):
song.preview_url = items[0].get('preview_url')
db.commit()
break
def main():
create_tables()
with open('books.csv') as f:
reader = csv.reader(f)
next(reader, None)
for isbn, title, author, year in reader:
print(
"adding book isbn = {} title = {} author = {} year={}".format(
isbn, title, author, year))
db.execute(
"INSERT INTO books(isbn,title,author,year) VALUES (:isbn,:title,:author,:year)",
{
"isbn": isbn,
"title": title,
"author": author,
"year": int(year)
})
print("added")
db.commit()
def updateDB(self, count, score, id):
if db.execute("SELECT * FROM rating WHERE books_id = :id", {
"id": id
}).fetchall():
db.execute(
"UPDATE rating SET review_count = :count, average_score = :score WHERE books_id = :id",
{
"count": count,
"score": score,
"id": id
})
db.commit()
else:
db.execute(
"INSERT INTO rating (review_count, average_score, books_id) "
"VALUES (:count, :score, :id)", {
"count": count,
"score": score,
"id": id
})
db.commit()
def userLogin():
if request.method == 'POST':
userName = request.form['username']
if not userName.strip():
return render_template(
"index.html",
error_msg="Username empty, please enter a username.")
session['name'] = userName
query = "SELECT COUNT(*) from users where user_name= %s"
cur.execute(query, userName)
result = cur.fetchone()
rows = result[0]
if rows > 0:
return render_template('homepage.html', user_name=userName)
sqlq = "INSERT INTO users(user_name) VALUES (%s)"
cur.execute(sqlq, userName)
db.commit()
return render_template('homepage.html', user_name=userName)
return render_template('index.html')
import csv
from application import db
f = open("books.csv")
reader = csv.reader(f)
for isbn, title, author, year in reader:
db.execute(
'INSERT INTO books(isbn, title, author, year) VALUES (:isbn, :title, :author, :year)',
{
'isbn': isbn,
'title': title,
'author': author,
'year': year
})
db.commit()
