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()