def post(self): """User sign up""" userData = request.get_json() firstname = userData['firstname'] secondname = userData['secondname'] confirmPassword = userData['confirm_password'] phone = userData['phone'] email = userData['email'] password = userData["password"] if email == "" or phone.strip() == ""\ or confirmPassword.strip() == "" or password.strip() == ""\ or firstname.strip() == "" or secondname.strip() == "": return {"message": "Please ensure all fields are non-empty."}, 400 if len(password) < 6: return {'message': 'password should be 6 characters or more.'}, 400 if not validate_email(email): return {"message": "Email is invalid"}, 400 if not password == confirmPassword: return {'message': 'Passwords do not match'}, 400 try: query = "select email from users where email='%s'\ or phone='%s'" % (email, phone) result = db.execute(query) user = result.fetchone() if user is None: if userData['driver'] == True: userObject = Driver(userData) userObject.save() else: userObject = Passenger(userData) userObject.save() return {'message': 'Account created.'}, 201 return {'message': 'User exists.'}, 409 except Exception as e: print(e) return {'message': 'Request not successful'}, 500
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 login(): if session.get('username'): return redirect(url_for('index')) form = LoginForm() if form.validate_on_submit(): email = form.email.data password = form.password.data user = db.execute("SELECT * FROM users WHERE email = :email", { "email": email }).fetchone() if user and check_password_hash(user.password, password): flash(f"{user.first_name}, you are sucessfully logged in!", "success") session['user_id'] = user.id session['username'] = user.first_name return redirect(url_for('index')) else: flash("Sorry, wrong username or password.", "danger") return render_template("login.html", title="login", form=form, login=True)
def api_logged_in() -> bool: """ Check whether the user is logged in, using API key or session """ # Check session if session and "user_id" in session and session["user_id"] > 0: return True # Get API key key = None if request.method == "GET" and "key" in request.args: key = request.args["key"] if request.method == "POST" and "key" in request.form: key = request.form["key"] if key is None: return False # Check API key from application import db user = db.execute("SELECT * FROM users WHERE api=?", request.args["key"]) return len(user) == 1
def checkDb(self): check = db.execute("SELECT * FROM users WHERE email = :email", { "email": self.email.data.lower() }).rowcount db.commit() return check
def update_diary_entry(self, entry_id): """ get diary entry by id """ query = "select * from entries where entry_id='{}'".format(entry_id) result = db.execute(query) entry = result.fetchone() """ if not found """ if entry is None: return {'message': 'diary entry with given id does not exist'}, 404 current_user_email = get_jwt_identity() query = "select user_id from users where email='{}'"\ . format(current_user_email) result = db.execute(query) user_id = result.fetchone() """ if entry owner id doesnot match id of current user """ if not entry[1] == user_id[0]: return {'message': 'You cannot change \ details of diary entry that you do not own'}, 401 try: data = request.get_json() title_json = data['title'] body_json = data['body'] title = title_json.strip() body = body_json.strip() """ validate for duplicate entry titles """ query = "select * from entries where title='{}'".format(title) result = db.execute(query) rows = result.fetchall() if(len(rows) > 0): return {'message': 'diary entry with such title already exists'}, 403 """ validate for alphanumeric characters """ ''' if not re.match('^[a-zA-Z0-9_]+$',title): return {'message': 'title can only be letters or numbers.'}, 403 ''' """ validate for empty fields """ if title and body: """ retrieve date_created field of this entry """ date_created = entry[4] """ convert string to date type """ datetime_obj = datetime.datetime.strptime( date_created, '%Y-%m-%d').date() """ compare date_created with current date """ if datetime_obj == datetime.date.today(): date_modified = datetime.date.today() query = "update entries set title='{}',body='{}', date_modified='{}' where entry_id='{}'"\ .format(data['title'], data['body'], date_modified, int(entry_id)) db.execute(query) return {'message': 'diary entry updated succesfully'}, 200 else: return {'message': 'diary entry can only be updated on the day it was created'}, 403 else: return {'message': 'Missing title or body fields.'}, 403 query = "select * from entries where entry_id='{}'".format( entry_id) result = db.execute(query) entry = result.fetchone() return {'id': entry[0], 'title': entry[2], 'body': entry[3], 'date_created': entry[4], 'date_modified': entry[5]} except (KeyError): return {'message': 'missing title or body keys'}, 403
def validate_email(self, field): if db.execute("SELECT * FROM users WHERE email = :email", { 'email': field.data }).fetchone(): raise ValidationError('Email already registered.')
def home(): form = SearchForm() ids = tuple([random.randint(1, 5000) for _ in range(10)]) result = db.execute("SELECT * FROM books WHERE books.id IN :ids", {"ids": ids}) return render_template("home.html", form=form, result=result)
def book(book_isbn): form = ReviewForm() # call application own API to get book details r_api = requests.get(f'{api_host}{book_isbn}') if r_api.status_code != 200: return abort(404) try: book_json = r_api.json() except JSONDecodeError: return abort(500) # call goodreads API to get book details try: r_gr_api = requests.get( "https://www.goodreads.com/book/review_counts.json", params={"key": gr_api_key, "isbns": book_isbn}) except requests.RequestException: r_gr_api = None try: gr_api_json = r_gr_api.json() except JSONDecodeError: gr_api_json = None # check for book id book_id = db.execute( 'SELECT id FROM public.book ' 'WHERE isbn = :isbn', {'isbn': book_json['isbn']} ).fetchone() # insert or update review if form submitted if request.method == 'POST' and form.validate_on_submit(): db.execute( 'INSERT INTO public.user_book (user_id, book_id, score, review) ' 'VALUES (:user_id, :book_id, :score, :review) ' 'ON CONFLICT (user_id, book_id) ' 'DO UPDATE SET score = :score, review = :review', {"user_id": session['user_id'], "book_id": book_id[0], "score": form.rating.data, "review": form.review.data }) db.commit() return redirect(url_for('book', book_isbn=book_isbn)) # get reviews (newest first) r_q = db.execute( 'SELECT public.user_book.*, ' 'public.user.name ' 'FROM public.user_book ' 'JOIN public.user ' 'ON public.user.id = public.user_book.user_id ' 'WHERE book_id = :book_id ' 'ORDER BY (id) DESC ', {"book_id": book_id[0]} ).fetchall() # build list of reviews and check if current user already send a review reviews = [] already_review = False for r_api in r_q: if r_api[5] == session['user']: already_review = True form.review.default = r_api[4] form.rating.default = int(r_api[3]) form.process() reviews.append( {'user': r_api[5], 'rating': r_api[3], 'review': r_api[4]}) return render_template( 'book.html', form=form, book_json=book_json, gr_api_json=gr_api_json, reviews=reviews, already_review=already_review )
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()
def post(self, ride_id): """Sends user request to join a ride offer""" try: # sample user current_user_email = get_jwt_identity() # Get user ID query = "SELECT users.user_id \ from users where email='{}'"\ . format(current_user_email) result = db.execute(query) user_row = result.fetchone() if user_row is None: return {'message': 'User not found'}, 404 user_id = user_row[0] # Find the particular ride offer to check its availability query = "SELECT * from rides where ride_id = '{}'" . format( ride_id) result = db.execute(query) row = result.fetchone() if row is None: return {'message': 'That ride does not exist'}, 404 # check whether this ride offer belongs to the user if user_id == row[1]: return {'message': 'You cannot request to join your own offer'}, 403 request_data = request.get_json() if 'pick-up point' not in request_data or 'drop-off point' not in request_data or\ 'seats_booked' not in request_data: return {'message': 'provide pick-up and drop-off points, and number of seats you want to book. '}, 400 pick_up = request_data['pick-up point'] drop_off = request_data['drop-off point'] seats_booked = request_data['seats_booked'] status = 'pending' # # check whether ride offer has any remaining space if row[6] < seats_booked: return {'message': 'No available space for you.'}, 403 # check whether ride offer is expired time = (row[4]) if time > datetime.now(): # check whether users has alread requested given ride offer query = "SELECT * from requests where user_id = (SELECT users.user_id \ from users where email='{}') and ride_id = {}"\ . format(current_user_email, ride_id) result = db.execute(query) result = result.fetchone() if result is None: # save user requests now query = "INSERT INTO requests (date_created, ride_id, user_id, pick_up_point,\ drop_off_point, seats_booked, status)\ values('{}', '{}', '{}', '{}', '{}', '{}', '{}')"\ . format(datetime.now(), ride_id, user_id, pick_up, drop_off, \ seats_booked, status) db.execute(query) return {'message': 'Your request has been recorded.'}, 201 # user has already requested to join this ride offer return{'message': 'You already requested this ride.'}, 403 else: return {'message': 'The ride requested has already expired'}, 403 except Exception as e: print(e) return {'message': 'Request not successful.'}, 500
def validate_email(self, email): if db.execute("SELECT email FROM users WHERE email = :email", { "email": email.data }).rowcount != 0: raise ValidationError("Email is already in use. Pick another one.")
def idDb(self): id = db.execute("SELECT id FROM books WHERE isbn = :isbn", { "isbn": self.isbn }).fetchall() db.commit() return id[0].id
def checkDb(self, id): check = db.execute("SELECT * FROM users WHERE id = :id", { "id": id }).fetchall() db.commit() return check
def checkDbEmail(self): check = db.execute("SELECT * FROM users WHERE email = :email", { "email": self.email.data.lower() }).fetchall() db.commit() return check
def infoDb(self): info = db.execute("SELECT * FROM users WHERE id = :id", { "id": self.id }).fetchall() db.commit() return info
import csv from application import db with open('books.csv') as file: reader = csv.reader(file) for isbn, title, author, year in reader: db.execute( "INSERT INTO Books (isbn, title, author, year) VALUES(:isbn, :title, :author, :year) where isbn != :isbn", { "isbn": isbn, "title": title, "author": author, "year": year, "isbn": isbn }) print( f"Book with isbn{isbn} and title {title} and author{author} and year {year} is added" ) db.commit()
def put(self, request_id): """Driver can accept or reject the ride offer.And users can take an accepted request""" try: action = request.args['action'] response = '' # check whether driver already accepted the offer. query = "select status,seats_booked,ride_id from requests where\ req_id='{}'".format(request_id) result = db.execute(query) result_rows = result.fetchone() query = "select available_space from rides where ride_id='{}'"\ .format((result_rows[2])) result = db.execute(query) seats = result.fetchone() available_seats = seats[0] print(action) if action.lower() == 'taken': query = "update requests set status='{}' where requests.req_id='{}'"\ . format('taken', int(request_id)) db.execute(query) return {'message': 'Your request has been updated.'} elif action.lower() == 'abandoned': query = "update requests set status='{}' where requests.req_id='{}'"\ . format('abadoned', int(request_id)) db.execute(query) return {'message': 'Your request has been updated.'} # check for action to take elif action.lower() == 'accept': if result_rows[0] == 'accepted': return {'message': 'You already accepted this user request'},403 action = 'accepted' # Decrement the available seats by one available_seats -= result_rows[1] # set message to be returned to user after request update cycle response = {'message': 'Request accepted'} else: # Reject an already accepted request; this means available seats should be incremented if result_rows[0] == 'accepted' : available_seats += result_rows[1] response = {'message': 'Request canceled'} action = 'canceled' elif result_rows[0] == 'rejected': return {'message': 'Request already rejected'} elif result_rows[0] == 'taken': return {'message': 'Reuest which is already taken cannot be modified.'} else: action = 'rejected' response = {'message': 'Request rejected'} query = "update requests set status='{}' where requests.req_id='{}'" \ . format(action, int(request_id)) db.execute(query) query = "update rides set available_space='{}' where ride_id='{}'"\ .format(int(available_seats), result_rows[2]) db.execute(query) return response except Exception as e: return e, 500
def checkPswDb(self): checkPsw = db.execute("SELECT * FROM users WHERE email = :email", { "email": self.reset_email.data.lower() }).fetchall() db.commit() return checkPsw
from application import db import requests #api_key = "3ZdRtZuPYHNnTuIgTI7QHw" #res = requests.get("https://www.goodreads.com/book/review_counts.json", params={"key": api_key, "isbns": "0060096187"}) #data = res.json() #name = 'YOUSEF' #print(name.swapcase()) row = db.execute("select id from books where isbn = :isbn ", { 'isbn': "0380795272" }).fetchone() print(row[0])
def updatePsw(self, hash_pass, id): db.execute("UPDATE users SET hash_psw = :hash_psw WHERE id = :id", { "hash_psw": hash_pass, "id": id }) db.commit()
import csv from application import db with open("books.csv") as f: reader = csv.reader(f) # Skip header next(reader) for isbn, title, author, pub_year in reader: db.execute( "INSERT INTO books(isbn, title, author, pub_year) VALUES(:isbn, :title, :author, :pub_year)", { "isbn": isbn, "title": title, "author": author, "pub_year": pub_year }) db.commit()
import csv from application import db # same import and setup statements as above f = open("books.csv") reader = csv.reader(f) for isbn, title, author, year in reader: # loop gives each column a name db.execute( "INSERT INTO books (isbn, title, author, year) VALUES (:isbn, :title, :author, :year)", { "isbn": isbn, "title": title, "author": author, "year": int(year) } ) # substitute values from CSV line into SQL command, as per this dict db.commit() # transactions are assumed, so close the transaction finished
def update_dyn_score(contest_id, problem_id, update_curr_user=True): from application import db """ Updates the dynamic scoring of contest_id/problem_id, using the db object For details see: https://www.desmos.com/calculator/eifeir81wk https://github.com/jdabtieu/CTFOJ/issues/2 Prereqs for using this function: user solve entry must already be in contest_solved """ db.execute("BEGIN") check = db.execute( ("SELECT * FROM contest_problems WHERE contest_id=:cid AND " "problem_id=:pid"), cid=contest_id, pid=problem_id) solves = len( db.execute( "SELECT user_id FROM contest_solved WHERE contest_id=:cid AND problem_id=:pid", cid=contest_id, pid=problem_id)) N_min = check[0]["score_min"] N_max = check[0]["score_max"] N_users = check[0]["score_users"] d = 11 * math.log(N_max - N_min) + N_users old_points = min(math.ceil(math.e**((d - solves + 1) / 11) + N_min), N_max) new_points = min(math.ceil(math.e**((d - solves) / 11) + N_min), N_max) point_diff = new_points - old_points # Set new point value of problem db.execute(("UPDATE contest_problems SET point_value=:pv WHERE " "contest_id=:cid AND problem_id=:pid"), pv=new_points, cid=contest_id, pid=problem_id) if update_curr_user: db.execute( ("UPDATE contest_users SET lastAC=datetime('now'), " "points=points+:points WHERE contest_id=:cid AND user_id=:uid"), cid=contest_id, points=old_points, uid=session["user_id"]) # Update points of all users who previously solved the problem db.execute(("UPDATE contest_users SET points=points+:point_change " "WHERE contest_id=:cid AND user_id IN " "(SELECT user_id FROM contest_solved WHERE " "contest_id=:cid AND problem_id=:pid)"), point_change=point_diff, cid=contest_id, pid=problem_id) db.execute("COMMIT")
def get_wishes_by_user(uid): stmt = text("SELECT Wish.id FROM Wish WHERE Wish.account_id = " + uid) res = db.execute(smtm)
def rejudge_contest_problem(contest_id, problem_id, new_flag): from application import db """ Rejudges a contest problem """ data = db.execute( "SELECT * FROM contest_problems WHERE contest_id=:cid AND problem_id=:pid", cid=contest_id, pid=problem_id)[0] # Reset all previously correct submissions db.execute(( "UPDATE contest_users SET points=points-:points WHERE user_id IN (SELECT " "user_id FROM contest_solved WHERE contest_id=:cid AND problem_id=:pid)" ), points=data["point_value"], cid=contest_id, pid=problem_id) db.execute( "UPDATE submissions SET correct=0 WHERE contest_id=:cid AND problem_id=:pid", cid=contest_id, pid=problem_id) db.execute( "DELETE FROM contest_solved WHERE contest_id=:cid AND problem_id=:pid", cid=contest_id, pid=problem_id) if data["score_users"] >= 0: # Reset dynamic scoring update_dyn_score(contest_id, problem_id, update_curr_user=False) # Set all new correct submissions db.execute(("UPDATE submissions SET correct=1 WHERE contest_id=:cid AND " "problem_id=:pid AND submitted=:flag"), cid=contest_id, pid=problem_id, flag=new_flag) db.execute(( "INSERT INTO contest_solved (user_id, contest_id, problem_id) " "SELECT DISTINCT user_id, contest_id, problem_id FROM submissions WHERE " "contest_id=:cid AND problem_id=:pid AND correct=1"), cid=contest_id, pid=problem_id) if data["score_users"] == -1: # Instructions for static scoring old_points = data["point_value"] else: # Instructions for dynamic scoring old_points = data["score_max"] update_dyn_score(contest_id, problem_id, update_curr_user=False) db.execute(( "UPDATE contest_users SET points=points+:points WHERE user_id IN (SELECT " "user_id FROM contest_solved WHERE contest_id=:cid AND problem_id=:pid)" ), points=old_points, cid=contest_id, pid=problem_id)
import csv # Get database info from the main app from application import db # Open csv file with open("books.csv") as csvfile: # Read as a dictionary reader = csv.DictReader(csvfile) for row in reader: db.execute( "INSERT INTO books (isbn, title, author, year) VALUES (:isbn, :title, :author, :year)", { "isbn": row["isbn"], "title": row["title"], "author": row["author"], "year": row["year"] }) print(row) # Commit to db db.commit()