def get(self, other_email): if other_email is None: return 'Missing other_email', 400 # lookup userid for requested user c = db.cursor(cursor_factory=RealDictCursor) c.execute("SELECT id FROM users WHERE cppemail = %s", (other_email + '@cpp.edu',)) # check if we got a result row = c.fetchone() if row is None: return 'User does not exist', 404 # get id other_id = row['id'] # lookup message history c.execute("SELECT * FROM messages WHERE receive_userid = %s and send_userid = %s or receive_userid = %s and send_userid = %s ORDER BY timestamp ASC", (other_id, request.id, request.id, other_id)) messages = c.fetchall() for m in messages: m['outgoing'] = m['send_userid'] == request.id # mark as read in db c.execute("UPDATE messages SET read = true WHERE receive_userid = %s and send_userid = %s", (request.id, other_id)) db.commit() return jsonify(results=messages)
def post(self): altemail = get_form('altemail') addressline1 = get_form('addressline1') addressline2 = get_form('addressline2') # not required city = get_form('city') zipcode = get_form('zip') schedule = get_form('schedule') drivingpref = get_form('drivingpref') maxdist = get_form('maxdist') # make sure required fields are not missing if addressline1 is None or city is None or zipcode is None or schedule is None or drivingpref is None or maxdist is None: return 'Missing fields', 400 # make sure the required fields are not empty if len(addressline1) == 0 or len(city) == 0 or len(zipcode) == 0 or len(schedule) == 0 or len(drivingpref) == 0 or len(maxdist) == 0: return 'Empty fields', 400 # write to db c = db.cursor(cursor_factory=RealDictCursor) c.execute("UPDATE users SET altemail = %s, addressline1 = %s, city = %s, zip = %s, drivingpref = %s, maxdist = %s, profilecomplete = true WHERE id = %s", (altemail, addressline1, city, zipcode, drivingpref, maxdist, request.id)) schedule = json.loads(schedule) # write schedule to db for i in range(len(schedule)): day = schedule[i] c.execute("UPDATE schedule SET arrive = %s, depart = %s WHERE userid = %s AND dayofweek = %s", (day['arrive'], day['depart'], request.id, i,)) db.commit() return "OK", 200
def add(cls, topic, winner_olid, candidate_olids, username, description=""): """ params: :topic (api.Topic): :winner_golid: ANY OL ID (e.g. OL123M or OL234W) """ topic = Topic.upsert(topic) winner = Book.upsert_by_olid(Book.clean_olid(winner_olid)) candidates = [] candidates.append(winner) for olid in candidate_olids: olid = Book.clean_olid(olid) # xxx candidates.append(Book.upsert_by_olid(olid)) r = cls(topic_id=topic.id, book_id=winner.id, description=description, username=username, candidates=candidates).create() db.commit() return r
def post(self): # if no file if 'picture' not in request.files: return 'Missing file', 400 # get filename picture = request.files['picture'] # check if the file is one of the allowed filetypes / extensions or empty if picture is None or not allowed_file(picture.filename): return 'Bad file', 400 image = Image.open(picture.stream) croppedpicture = resizeimage.resize_cover(image, [256, 256]) imgByteArr = io.BytesIO() croppedpicture.save(imgByteArr, format='JPEG') imgByteArr = bytearray(imgByteArr.getvalue()) # write to db c = db.cursor(cursor_factory=RealDictCursor) c.execute("UPDATE users SET picture = %s WHERE id = %s", (imgByteArr, request.id)) db.commit() return 'OK', 200
def put(self, current_user, todo_id): todo = Todo.query.filter_by(id=todo_id, user_id=current_user.public_id).first() if not todo: return {"message": "Todo item not found."} todo.complete = True db.commit() return {"message": f"Todo item: '{todo.title}' completed"}
def post(self): # make sure user is not reviewing themself reviewee_email = get_form('email') if reviewee_email == request.email: return 'You can not review yourself', 400 # lookup reviewer id c = db.cursor(cursor_factory=RealDictCursor) c.execute("SELECT id FROM users WHERE cppemail = %s", (request.email, )) # check if we got a result row = c.fetchone() if row is None: return 'You do not exist', 404 # get id reviewer_id = dict(row)['id'] # lookup reviewee id c.execute("SELECT id FROM users WHERE cppemail = %s", (reviewee_email, )) # check if we got a result row = c.fetchone() if row is None: return 'User does not exist', 404 # get id reviewee_id = dict(row)['id'] # make sure user has not already reviewed this person c.execute( "SELECT id FROM reviews WHERE reviewee_userid = %s and reviewer_userid = %s", (reviewee_id, reviewer_id)) # check if we got a result row = c.fetchone() if row is not None: return 'You can not review more than once', 400 # get # of stars stars_unparsed = get_form('stars') if stars_unparsed is None: return 'Missing field: stars', 400 stars = int(stars_unparsed) if stars < 1 or stars > 5: return 'Stars out of range', 400 content = get_form('content') if content is None or len(content) == 0: return 'Missing field: content', 400 # insert new record into reviews table c.execute( "INSERT INTO reviews (reviewer_userid, reviewee_userid, stars, content) VALUES (%s, %s, %s, %s)", (reviewer_id, reviewee_id, stars, content)) db.commit() return 'OK', 201
def create(username): user_id = users.get_user_id(username) expires = datetime.datetime.now() + datetime.timedelta(hours=2) cursor = db.cursor() cursor.execute('INSERT INTO Sessions (user_id, expires) VALUES (%s, %s);', (user_id, expires)) db.commit() cursor.execute('SELECT LAST_INSERT_ID();') session_id = cursor.fetchone()[0] cursor.close() return session_id
def post(self): to_userid = get_form('to_userid') text = get_form('message') # make sure required fields are not empyty if to_userid is None or text is None: return 'Missing fields', 400 # write to db c = db.cursor(cursor_factory=RealDictCursor) c.execute("INSERT INTO messages (send_userid, receive_userid, message, timestamp) VALUES (%s, %s, %s, CURRENT_TIMESTAMP)", (request.id, to_userid, text)) db.commit() return 'OK', 200
def insert_draws(draws: list) -> bool: sql = "INSERT INTO draws (draw_id, numbers, stars, date, prize, has_winner) VALUES " vars = [] for i, draw in enumerate(draws): if i == 0: sql += "(%s, %s, %s, %s, %s, %s)" else: sql += ", (%s, %s, %s, %s, %s, %s)" vars.extend(draw) db.getCursor().execute(sql, vars) db.commit() return True
def main(): if len(sys.argv) == 1: csvdir = os.path.join(os.path.dirname(os.path.dirname( os.path.dirname(os.path.realpath(__file__)))), '../', 'assets') else: csvdir = sys.argv[1] db.session.query(api.Meter).delete() db.session.query(api.Garage).delete() db.session.commit() with open(os.path.join(csvdir, 'on-street-metered-parking.csv'), 'rU') as f: for row in csv.reader(f): (street, address, side, street_from, street_to, id, mech_id, model, door, vault, issues) = row meter = api.Meter() meter.street = street.rstrip('E') meter.address = address meter.side = side meter.street_from = street_from meter.street_to = street_to meter.id = id meter.mech_id = int(mech_id) meter.door = door meter.vault = vault meter.issues = issues db.session.add(meter) with open(os.path.join(csvdir, 'parking-garages.csv'), 'rU') as f: for row in csv.reader(f): (location_number, facility, street, address, lat, lon, spaces, cost) = row garage = api.Garage() garage.id = int(location_number) garage.facility = facility garage.street = street garage.address = address garage.lat = float(lat) garage.lon = float(lon) garage.spaces = int(spaces) garage.cost = int(cost[1:]) db.session.add(garage) db.commit()
def update_item(todo): ''' PUT an update the todo item with id <item_id> or DELETE it ''' if request.method == 'PUT': if not request.json: return jsonify({"message": "no json data supplied"}) if 'text' not in request.json: return jsonify({"message": "no text supplied"}) todo.text = request.json.get('text') db.session.add(todo) db.commit() return jsonify({"message": "success"}) if request.method == 'DELETE': db.session.delete(todo) db.session.commit() return jsonify({"message": "success"})
def delete(self, param): reviewid = int(param) # lookup review id and enforce reviewer_userid c = db.cursor(cursor_factory=RealDictCursor) c.execute( "SELECT id FROM reviews WHERE reviewer_userid = %s and id = %s", (request.id, reviewid)) # check if we got a result row = c.fetchone() if row is None: return 'Review does not exist', 404 # get all reviews for user, join with users table c.execute("DELETE FROM reviews WHERE id = %s", (reviewid, )) db.commit() return 'OK', 202
def create(post_id, commentor_id, content): cursor = db.cursor() cursor.execute( "INSERT INTO Comment (post_id, commentor_id, content) VALUES (%s, %s, %s)", (post_id, commentor_id, content['content'])) last_id = cursor.lastrowid db.commit() cursor.execute("SELECT * FROM Comment WHERE id=%s", (last_id, )) comment_info = cursor.fetchone() cursor.close() comment = { "id": comment_info[0], "post_id": comment_info[1], "commentor_id": comment_info[2], "content": comment_info[3], "created": comment_info[4], "updated": comment_info[5] } return comment
def post(self): oldpassword = get_form('oldpassword') newpassword = get_form('newpassword') # make sure required fields are not empty if oldpassword is None or newpassword is None: return 'Missing fields', 400 # salt password c = db.cursor(cursor_factory=RealDictCursor) c.execute("SELECT * FROM users WHERE cppemail = %s", (request.email,)) salt = os.urandom(32).encode('hex') passhash = hashlib.sha256(newpassword + salt).hexdigest() # write to db c.execute("UPDATE users SET salt = %s, passhash = %s WHERE id = %s", (salt, passhash, request.id)) db.commit() return 'OK', 200
def create(post_dictionary): post = {} cursor = db.cursor() if not post_dictionary['title'] or not post_dictionary['content']: return cursor.execute("INSERT INTO Posts (author_id, title, content) Values(%s, %s, %s)", (1, post_dictionary['title'], post_dictionary['content'])) db.commit() last_id = cursor.lastrowid cursor.execute("SELECT * FROM Posts WHERE id=%s", (last_id,)) post_info = cursor.fetchone() post = { "id": post_info[0], "author_id": post_info[1], "title": post_info[2], "content": post_info[3], "created": post_info[4], "updated": post_info[5] } cursor.close() return post
def create(account_dictionary): cursor = db.cursor() cursor.execute( "INSERT INTO Users (username, password) VALUES (%s, %s);", (account_dictionary['username'], account_dictionary['password'])) last_id = cursor.lastrowid db.commit() cursor.execute("SELECT * FROM Users WHERE id=%s", (last_id, )) account_info = cursor.fetchone() cursor.close() account = { "id": account_info[0], "username": account_info[1], "password": bcrypt.hashpw(account_info[2], bcrypt.gensalt()), "nickname": account_info[3], "picture": account_info[4], "user_level": account_info[5], "deleted": account_info[6], "created": account_info[7], "updated": account_info[8] } return account
def post(self): email = get_form('email') alt = get_form('altemail') password = get_form('password') fullname = get_form('fullname') # make sure required fields are not empyty if email is None or password is None or fullname is None or len(fullname) == 0: return 'Missing fields', 400 # use regex to enforce .cpp email if re.match(r'^[a-zA-Z]+@cpp\.edu$', email) is None: return 'cpp email', 400 # make sure user doesn't already exist c = db.cursor() c.execute("SELECT id FROM users WHERE cppemail = %s", (email,)) if c.fetchone() is not None: return 'account exists', 400 # salt and hash password salt = os.urandom(32).encode('hex') passhash = hashlib.sha256(password + salt).hexdigest() verified = True # TODO: implement email verification # write to db c.execute("INSERT INTO users (cppemail, fullname, altemail, salt, passhash, verified, timestamp) VALUES (%s, %s, %s, %s, %s, %s, CURRENT_TIMESTAMP) RETURNING id", (email, fullname, alt, salt, passhash, verified)) userid = c.fetchone()[0] values = [] for day in range(7): values.append(userid) values.append(day) # write empty schedule to db c.execute("INSERT INTO schedule (userid, dayofweek) VALUES (%s, %s), (%s, %s), (%s, %s), (%s, %s), (%s, %s), (%s, %s), (%s, %s)", tuple(values)) db.commit() return 'OK', 200
def add_value(self, v): self.schema['values'].append(v) flag_modified(self, "schema") db.add(self) db.commit()
def delete(comment_id): cursor = db.cursor() cursor.execute("DELETE FROM Comment WHERE id=%s", (comment_id, )) db.commit() cursor.close() return
def delete(self, session): db.session.delete(session) db.commit() return {"result": True}