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