def delete_user_friendRequest(u_id): db = get_db() cur = db.connection.cursor() # Querying cur.execute( '''DELETE FROM FriendRequest WHERE u_id_s = %s OR u_id_r = %s''', (u_id, u_id)) db.connection.commit() cur.close()
def load_logged_in_user(): """If a user id is stored in the session, load the user object from the database into ``g.user``.""" user_id = session.get("user_id") if user_id is None: g.user = None else: g.user = (get_db().execute("SELECT * FROM user WHERE id = ?", (user_id, )).fetchone())
def delete_user_comment(u_id): db = get_db() cur = db.connection.cursor() # Querying cur.execute( '''UPDATE Comments SET u_id = 23, title = NULL WHERE u_id = %s''', [u_id]) db.connection.commit() cur.close()
def test_select_last_start_date(app, Activity3): # I don't want the app to select a startdate that was manually uploaded # it must select a date that came from strava, aka that has a strava activity id with app.app_context(): assert get_last_activity_date() == "2020-12-27T19:45:05Z" activity = Activity3 # activity3 has no id and it's start date is "2021-01-16T14:52:54Z" parse_description(activity) insert_activity(activity) db = get_db() assert get_last_activity_date() == "2020-12-27T19:45:05Z"
def get_all_activities(): """Return all the existing activities. :return: List of activities :rtype: list """ db = get_db() activities = db.execute( 'SELECT * FROM activities ORDER BY start_date DESC' ).fetchall() return activities
def delete(id): """Delete a post. Ensures that the post exists and that the logged in user is the author of the post. """ get_post(id) db = get_db() db.execute("DELETE FROM post WHERE id = ?", (id, )) db.commit() return redirect(url_for("blog.index"))
def add_User(name, email, username, password): db = get_db() cur = db.connection.cursor() cur.execute( '''INSERT INTO User(name, username, email, password) VALUES(%s, %s, %s, %s)''', (name, username, email, password)) db.connection.commit() cur.close() return True
def add_book_for_user(u_id, b_id, status): db = get_db() cur = db.connection.cursor() # Query to add the book for user cur.execute( '''INSERT INTO BookList(u_id, b_id, status) VALUES(%s, %s, %s) ''', (u_id, b_id, status)) # Commit to DB. db.connection.commit() cur.close()
def disks(): """ Show all disks. """ db = get_db() with db.begin(): s = select([schema.disks.c.disk_id, schema.disks.c.disk_name]) result = db.execute(s) disks_list = result.fetchall() return render_template("disks.html", disks=disks_list)
def valid_login(username, password): sql = 'select * from users where login = ?' c = d.get_db().cursor() c.execute(sql, (username,)) row = c.fetchone() if row is not None: print(row) id, login, password_hash = row if check_password_hash(password_hash, password) is True: return True return False
def go_url(path): db = d.get_db() select_sql = 'select url from urls where short_url = ?' data = (path, ) c = db.cursor() r = c.execute(select_sql, data) fetch = c.fetchone() if fetch is not None: url = fetch()[0] return redirect(url) else: return abort(404, 'Not found')
def usersExist(u_id_1, u_id_2): db = get_db() cur = db.connection.cursor() f1 = True f2 = True cur.execute('''SELECT * FROM User WHERE u_id = %s''', [u_id_1]) if not cur.fetchall(): f1 = False cur.execute('''SELECT * FROM User WHERE u_id = %s''', [u_id_2]) if not cur.fetchall(): f2 = False cur.close() return f1 and f2
def _populate_movie(movie_data): db = get_db() cursor = db.execute( "INSERT INTO movie (name, description, rating, released_date) VALUES (?, ?, ?, ?)", (movie_data['general']['name'], movie_data['general']['description'], movie_data['general']['rating'], movie_data['general']['released_date']) ) movie_id = cursor.lastrowid db.executemany( "INSERT INTO movie_detail (movie_id, key, value) VALUES (?, ?, ?)", [(movie_id, *key_value) for key_value in movie_data['movie_detail']] ) db.commit()
def get_post(id, check_author=True): post = get_db().execute( 'SELECT p.id, title, body, created, author_id, username' ' FROM post p JOIN user u ON p.author_id = u.id' ' WHERE p.id = ?', (id,) ).fetchone() if post is None: abort(404, "Post id {0} doesn't exist.".format(id)) if check_author and post['author_id'] != g.user['id']: abort(403) return post
def init(): userID = session.get('user_id') if userID is None: res = {"signedIn": False} session.clear() else: db = get_db() cursor = db.cursor() query = ("SELECT username FROM userinfo WHERE id=%s") cursor.execute(query, (userID, )) username = cursor.fetchone() res = {"signedIn": True, "username": username[0]} resp = cors_res(res=res) return resp
def report_generator(): date = request.args.get('date') if not date: return "No Date Provided</br>Example Query: <a href='/api/report?date=2019-09-29'>/api/report?date=2019-09-29</a>" valid_date = r'([12]\d{3}-(0[1-9]|1[0-2])-(0[1-9]|[12]\d|3[01]))' valid = re.search(valid_date, date) if valid: date = valid.string db = get_db() cursor = db.cursor() report = Report(date, cursor) return report.construct_new_report() else: return "Invalid Date</br>Format: YYYY-MM-DD"
def login(): if request.method == 'OPTIONS': resp = cors_res() return resp else: reqStr = str(request.data) reqArr = reqStr[3:-2] reqArr = reqArr.split(",") reqObj = {} for item in reqArr: itemArr = item.split(":") reqObj[itemArr[0][1:-1]] = itemArr[1][1:-1] username = reqObj["username"] password = reqObj["password"] error = None res = {} db = get_db() cursor = db.cursor(dictionary=True) query = ('SELECT * FROM userinfo WHERE username=%s') cursor.execute(query, (username, )) user = cursor.fetchone() if user is None: error = 'Incorrect username or password' elif not check_password_hash(user['password'], password): error = 'Incorrect username or password' if error is None: session.clear() session.permanent = True session['user_id'] = user['id'] res['actionSuccess'] = True else: res['actionSuccess'] = False res['error'] = error resp = cors_res(res) return resp
def addcart(): if request.method == 'OPTIONS': return cors_res() else: userID = session.get("user_id") reqDict = request.get_json() itemcode = str(reqDict['itemcode']) itemAmt = reqDict['itemAmt'] resBody = {} if userID is not None: db = get_db() db.get_warnings = True cursor = db.cursor() query = ('SELECT ' + itemcode + ' FROM cartdata WHERE id = %s') cursor.execute(query, (userID, )) currentNum = cursor.fetchone()[0] if currentNum is None: stmt = ('UPDATE cartdata SET ' + itemcode + ' = 1 WHERE id = %s') cursor.execute(stmt, (userID, )) cursor.fetchwarnings() else: currentNum = int(currentNum) + int(itemAmt) stmt = ('UPDATE cartdata SET ' + itemcode + ' = %s WHERE id = %s') cursor.execute(stmt, (currentNum, userID)) countQuery = ('SELECT * FROM cartdata WHERE id = %s') cursor.execute(countQuery, (userID, )) rowData = cursor.fetchone() itemCount = -1 for item in rowData: if item is not None and item > 0: itemCount += 1 cursor.close() db.commit() resBody['addSuccess'] = True resBody['itemCount'] = itemCount return cors_res(resBody)
def regisration(): error = None if request.method == 'POST': username, password = request.form['username'], request.form['password'] if len(username) <= 4 or len(password) <= 4: error = "Неправильный username или password." db = d.get_db() select_user = '******' c = db.cursor() c.execute(select_user, (username, )) if c.fetchone() is not None: error = "Пользователь уже существует" if error is None: sql_user = '******' # data = (request.form['username'], request.form['password']) data = (username, generate_password_hash(password)) db.cursor().execute(sql_user, data) db.commit() return render_template('registration.html', error=error)
def search(): if (request.method == 'OPTIONS'): return cors_res() else: reqDict = request.get_json() searchIn = reqDict['searchIn'] #current_app.logger.debug(reqDict) db = get_db() cursor = db.cursor(dictionary=True) query = ( "SELECT * FROM inventory WHERE MATCH (name) AGAINST (%s IN NATURAL LANGUAGE MODE)" ) cursor.execute(query, (searchIn, )) searchRes = cursor.fetchall() #current_app.logger.debug(searchRes) return cors_res(searchRes)
def book(): b_id = request.json['b_id'] db = get_db() cur = db.connection.cursor() response = { 'Book': {}, 'Author': [], 'Comments': [], 'GenreBooks': [] } #Initializing the response to be given by the endpoint #Comments and GenreBooks are Lists instead of dictionaries because there can be multiple comments and genres cur.execute( '''SELECT title, rating, no_ratings, description FROM Book WHERE b_id={0}''' .format(int(b_id))) vr = cur.fetchall() if vr: #If tuple is not empty response['Book'] = vr[0] else: return {'response': 'Invalid Book id'} cur.execute( '''SELECT Author.a_id, name FROM Author inner join AuthorBooks on Author.a_id = AuthorBooks.a_id WHERE AuthorBooks.b_id={0}''' .format(b_id)) vr = cur.fetchall() response['Author'] = vr cur.execute( '''SELECT c_id, title, likes, u_id FROM Comments WHERE b_id={0}'''. format(b_id)) vr = cur.fetchall() response['Comments'] = vr cur.execute( '''SELECT g_id, name FROM Genre WHERE g_id in (SELECT g_id FROM GenreBooks WHERE b_id={0})''' .format(b_id)) vr = cur.fetchall() response['GenreBooks'] = vr return jsonify(response) #Return the response in json
def create(): if request.method == 'POST': title = request.form['title'] body = request.form['body'] error = None if not title: error = 'Title is required.' if error is not None: flash(error) else: db = get_db() db.execute( 'INSERT INTO post (title, body, author_id)' ' VALUES (?, ?, ?)', (title, body, g.user['id']) ) db.commit() return redirect(url_for('blog.index')) return render_template('blog/create.html')
def update(id): """Update a post if the current user is the author.""" post = get_post(id) if request.method == "POST": title = request.form["title"] body = request.form["body"] error = None if not title: error = "Title is required." if error is not None: flash(error) else: db = get_db() db.execute("UPDATE post SET title = ?, body = ? WHERE id = ?", (title, body, id)) db.commit() return redirect(url_for("blog.index")) return render_template("blog/update.html", post=post)
def weekly_summary(): """Shows summary of weekly stats.""" import pandas as pd from application.db import get_db from application.internal_api import convert_meters_miles db = get_db() df = pd.read_sql_query("SELECT * from activities", db) df.start_date = pd.to_datetime(df.start_date, format="%Y-%m-%dT%H:%M:%SZ") df.distance = df.distance.apply(convert_meters_miles) totals = df[["distance", "total_elevation_gain", "start_date"]] totals = totals.groupby(pd.Grouper(key='start_date', freq='W')).sum() ave = df[["start_date", "knee_pain"]] ave = ave.groupby(pd.Grouper(key='start_date', freq='W')).mean().round(2) weekly = totals.join(ave).fillna(0) # figure out average weight per mile df['ave_weight'] = (df['weight']*df['distance']) # group by week weight = df[['ave_weight', 'start_date']].groupby(pd.Grouper(key='start_date', freq='W')).sum() # divide by total distance that week to get ave weight carried per mile weekly['weight'] = (weight['ave_weight']/weekly['distance']).fillna(0) # get values for graph before stringifying date = list(weekly.index.strftime('%Y-%m-%d')) distance = list(weekly.distance) elevation = list(weekly.total_elevation_gain) weight = list(weekly.weight) knee_pain = list(weekly.knee_pain) #stringify values to make decimals nicer def decimal_display(value): return f'{value:.2f}' weekly.distance = weekly.distance.apply(decimal_display) weekly.total_elevation_gain = weekly.total_elevation_gain.apply(decimal_display) weekly.weight = weekly.weight.apply(decimal_display) return render_template('trainer/weekly_summary.html', weekly=weekly.sort_index(ascending=False), date=date, distance=distance, elevation=elevation, weight=weight, knee_pain=knee_pain)
def create(): """Create a new post for the current user.""" if request.method == "POST": title = request.form["title"] body = request.form["body"] error = None if not title: error = "Title is required." if error is not None: flash(error) else: db = get_db() db.execute( "INSERT INTO post (title, body, author_id) VALUES (?, ?, ?)", (title, body, g.user["id"]), ) db.commit() return redirect(url_for("blog.index")) return render_template("blog/create.html")
def login(): if request.method == 'POST': username = request.form['username'] password = request.form['password'] db = get_db() error = None user = db.execute('SELECT * FROM user WHERE username = ?', (username, )).fetchone() if user is None: error = 'Incorrect username.' elif not check_password_hash(user['password'], password): error = 'Incorrect password.' if error is None: session.clear() session['user_id'] = user['id'] return redirect(url_for('reading_list.index')) flash(error) return render_template('auth/login.html')
def activate_username(username): if request.method == "POST": activation_code = request.form["activation"] activation_file = get_activation_file() error = None if not activation_code: error = "Activation code is required" elif not os.path.exists(activation_file) or open( activation_file, 'r').read() != activation_code: error = "Invalid activation code" else: db = get_db() db.execute("UPDATE user SET is_activated = ? WHERE username = ?", (True, username)) db.commit() flash("User activated", category='success') return redirect(url_for("auth.login")) flash(error, category='danger') return render_template("auth/activate_username.html")
def remove(): if request.method == 'OPTIONS': return cors_res() else: userID = session.get('user_id') reqDict = request.get_json() itemcode = reqDict['itemCode'] if userID is not None: db = get_db() cursor = db.cursor() stmt = ('UPDATE cartdata SET ' + itemcode + ' = NULL WHERE id = %s') cursor.execute(stmt, (userID, )) cursor.close() db.commit() res = {'remove': True} return cors_res(res) res = {'remove': False} return cors_res(res)
def update(id): post = get_post(id) if request.method == 'POST': title = request.form['title'] body = request.form['body'] error = None if not title: error = 'Title is required.' if error is not None: flash(error) else: db = get_db() db.execute( 'UPDATE post SET title = ?, body = ?' ' WHERE id = ?', (title, body, id) ) db.commit() return redirect(url_for('blog.index')) return render_template('blog/update.html', post=post)
def login(): """Log in a registered user by adding the user id to the session.""" if request.method == "POST": username = request.form["username"] password = request.form["password"] db = get_db() error = None user = db.execute("SELECT * FROM users WHERE username = ?", (username, )).fetchone() if user is None: error = "Incorrect username." elif not check_password_hash(user["password"], password): error = "Incorrect password." if error is None: # store the user id in a new session and return to the index session.clear() session["user_id"] = user["id"] return redirect(url_for("index")) flash(error) return render_template("auth/login.html")