def add_user_goals(user_id, goal_id, value):
with get_db_cursor(commit=True) as cursor:
query = """
insert into user_goals (user_id, goal_id, value) values(%s, %s, %s)
"""
query = cursor.mogrify(query, [user_id, goal_id, value])
cursor.execute(query)
def add_goals(goal):
with get_db_cursor(commit=True) as cursor:
query = """
insert into goals (goal) values(%s) RETURNING *;
"""
query = cursor.mogrify(query, [goal])
cursor.execute(query)
return dict(cursor.fetchone())
def add_task_for_user(task_id, user_id):
with get_db_cursor(commit=True) as cursor:
query = """
INSERT INTO USER_TASKS(task_id, user_id) VALUES(%s, %s);
"""
query = cursor.mogrify(query, [task_id, user_id])
cursor.execute(query)
def get_user_by_username(username):
with get_db_cursor() as cursor:
query = f"""
SELECT * FROM USERS where username = '******';
"""
cursor.execute(query)
result = cursor.fetchone()
return dict(result)
def update_user_by_id(user_id, **kwargs):
with get_db_cursor(commit=True) as cursor:
updates = kwargs
keys = list(updates.keys())
for key in keys:
if updates[key] is None:
updates.pop(key)
sql_template = "UPDATE users SET ({}) = %s WHERE user_id = {} returning *"
sql = sql_template.format(', '.join(updates.keys()), user_id)
params = (tuple(updates.values()), )
query = cursor.mogrify(sql, params)
cursor.execute(query, params)
result = cursor.fetchone()
return dict(result)
def get_all_goals_for_username(username):
with get_db_cursor() as cursor:
query = f"""
SELECT
G.*,UG.value
FROM
USERS U
LEFT JOIN user_goals UG ON
U.USER_ID = UG.USER_ID
LEFT JOIN GOALS G ON
UG.GOAL_ID = G.GOAL_ID
WHERE
U.USERNAME = '******';
"""
cursor.execute(query)
return [dict(i) for i in cursor.fetchall()]
def get_all_tasks_by_username(username):
with get_db_cursor() as cursor:
query = f"""
SELECT
T.*
FROM
USERS U
LEFT JOIN USER_TASKS UT ON
U.USER_ID = UT.USER_ID
LEFT JOIN TASKS T ON
UT.TASK_ID = T.ID
WHERE
U.USERNAME = '******';
"""
cursor.execute(query)
return [dict(i) for i in cursor.fetchall()]
def create_user(**kwargs):
username = kwargs.get('username')
password = kwargs.get('password')
height = kwargs.get('height')
weight = kwargs.get('weight')
age = kwargs.get('age')
gender = kwargs.get('gender')
user_details = None
with get_db_cursor(commit=True) as cursor:
query = "insert into users (username,password,height,weight,age,gender) values (%s,%s,%s,%s,%s,%s) returning *"
query = cursor.mogrify(
query, [username, password, height, weight, age, gender])
cursor.execute(query)
user_details = dict(cursor.fetchone())
goals = kwargs.get('goals')
for key in goals.keys():
add_user_goals(user_details['user_id'], key, goals[key])
return user_details
def add_task(**kwargs):
title = kwargs.get('title', '')
due_date = kwargs.get('due_date', str(datetime.now()))
is_completed = kwargs.get('is_completed', False)
user_id = kwargs.get('user_id')
with get_db_cursor(commit=True) as cursor:
query = """
INSERT INTO TASKS(title, due_date, is_completed) VALUES(%s, %s, %s) RETURNING id;
"""
query = cursor.mogrify(query, [title, due_date, is_completed])
cursor.execute(query)
result = cursor.fetchone()
task_id = result['id']
cursor.execute(f"SELECT * FROM TASKS WHERE id = '{task_id}'")
result = dict(cursor.fetchone())
add_task_for_user(task_id, user_id)
return result
def get_all_goals():
with get_db_cursor() as cursor:
cursor.execute("SELECT * FROM goals")
return [dict(i) for i in cursor.fetchall()]
def check_if_username_present(username):
with get_db_cursor() as cursor:
cursor.execute(f"select * from users where username = '******'")
result = cursor.fetchone()
return result is not None
def get_user_by_id(user_id):
with get_db_cursor() as cursor:
cursor.execute(f"select * from users where user_id = '{user_id}'")
result = cursor.fetchone()
return dict(result)
def get_task_by_id(task_id):
with get_db_cursor() as cursor:
cursor.execute(f"SELECT * FROM TASKS WHERE id = '{task_id}'")
result = cursor.fetchone()
return dict(result)