def increment_marshmallows_num (goal_id): with connection.cursor() as cursor: sql = """UPDATE goals SET marshmallows_num = ( SELECT marshmallows_num + 1 FROM ( SELECT * FROM goals ) AS c1 WHERE c1.id = %s ), last_update = NOW() WHERE id = %s AND active = 1 AND 1 <= DATEDIFF( NOW(), ( SELECT last_update FROM ( SELECT * FROM goals ) AS c2 WHERE c2.id = %s ) );""" execution_result = cursor.execute(sql,(goal_id, goal_id, goal_id)); connection.commit(); if execution_result: with connection.cursor() as cursor: sql = """INSERT INTO activity_date (goal_id, date) VALUES (%s, NOW());""" cursor.execute(sql,(goal_id)); connection.commit(); return execution_result;
def is_valid_jwt (str_jwt): state = True; reflesh_jwt = ""; user_id = ""; try: # change str type to byte type; encoded_jwt = str_jwt.encode("utf-8"); decoded = jwt.decode(encoded_jwt, secret, verify=True); user_id = decoded["user_id"]; with connection.cursor() as cursor: sql = """SELECT jwt FROM use_jwt WHERE users_id = %s;""" cursor.execute(sql,(user_id)); results = cursor.fetchone(); use_jwt = results["jwt"]; except Exception as e: print("JwtError:", str(e)) return; try: if str_jwt != use_jwt: raise UnauthenticationError(str_jwt); except UnauthenticationError as e: print("UnauthenticationError",str(e)); return; return state, user_id;
def delete_goal (goal_id): with connection.cursor() as cursor: sql = """DELETE FROM goals WHERE id = %s;""" cursor.execute(sql,(goal_id)); connection.commit();
def update_goal (goal_id, value): with connection.cursor() as cursor: sql = """UPDATE goals SET value = %s WHERE id = %s""" cursor.execute(sql,(value, goal_id)); connection.commit();
def update_todo (todo_id, value): with connection.cursor() as cursor: sql = """UPDATE todos SET value = %s WHERE id = %s;""" cursor.execute(sql,(value, todo_id)); connection.commit(); return;
def delete_todo (todo_id): with connection.cursor() as cursor: sql = """DELETE FROM todos WHERE id = %s;""" cursor.execute(sql, (todo_id)); connection.commit(); return;
def achieve_toggle_todo (todo_id): with connection.cursor() as cursor: sql = """SELECT achieve FROM todos WHERE id = %s;"""; cursor.execute(sql, (todo_id)); result = cursor.fetchone(); int_bool = 0 if result["achieve"] else 1 with connection.cursor() as cursor: sql = """UPDATE todos SET achieve = %s WHERE id = %s;""" cursor.execute(sql, (int_bool, todo_id)); connection.commit(); return;
def get_user_info (user_id): with connection.cursor() as cursor: sql = """SELECT user_name FROM users WHERE user_id = %s""" cursor.execute(sql,(user_id)); result = cursor.fetchone(); return result;
def create_goal (user_id, value): with connection.cursor() as cursor: sql = """INSERT INTO goals (user_id, value, create_date) VALUES (%s, %s, NOW());""" cursor.execute(sql,(user_id, value)); connection.commit(); return get_active_goal(user_id);
def create_todo (goal_id, value): with connection.cursor() as cursor: sql = """INSERT INTO todos(goals_id, value) VALUES (%s, %s);""" cursor.execute(sql,(goal_id, value)); connection.commit(); return read_todo(goal_id);
def read_todo (goal_id): with connection.cursor() as cursor: sql = """SELECT id, value, achieve FROM todos WHERE goals_id = %s ORDER BY id DESC;""" __result = cursor.execute(sql, (goal_id)); result = cursor.fetchall(); return result;
def twitter_callback(): session = request.environ.get("beaker.session"); oauth_verifier = request.query['oauth_verifier'] access_token, access_token_secret = twitter.get_access_token( session["twitter"]["request_oauth_token"], session["twitter"]["request_oauth_token_secret"], oauth_verifier) twitter_user = twitter.verify_credentials(access_token, access_token_secret) int(twitter_user.id); with connection.cursor() as cursor: sql = """SELECT user_id FROM users WHERE user_id = %s""" cursor.execute(sql,(twitter_user.id)); results = cursor.fetchone(); if results is None: with connection.cursor() as cursor: sql = """INSERT INTO users (user_id, user_name) SELECT %s,%s FROM dual WHERE NOT EXISTS ( SELECT * FROM users WHERE user_id = %s )""" cursor.execute(sql,(twitter_user.id,twitter_user.screen_name,twitter_user.id)) # connection.commit(twitter_user.id); connection.commit(); jwt = generate_jwt(twitter_user.id); else: user_id = results["user_id"]; jwt = generate_jwt(user_id); redirect(root.SERVER_URL +"?jwt="+ jwt);
def generate_jwt(user_id): exp = datetime.datetime.utcnow() + datetime.timedelta(days = 7); encoded = jwt.encode({"exp": exp,"user_id": user_id}, secret, algorithm="HS256").decode('utf-8'); with connection.cursor() as cursor: sql = """INSERT INTO use_jwt(jwt, users_id) VALUES (%s, %s) ON DUPLICATE KEY UPDATE jwt= %s""" cursor.execute(sql, (encoded, user_id, encoded)); connection.commit(); return encoded;
def wrapper(): payload = request.json["payload"]; __jwt = payload["jwt"]; try: status, user_id = jwt.is_valid_jwt(__jwt); except: json_data = { "api":{"status": "UnAuthentication", "message": "failure authentication", "data": None} } parsed_json = json.dumps(json_data); r = HTTPResponse(status=301, body=parsed_json, content_type = "application/json"); return r; if status: # last_update確認して日にちをまたいでいたらtodoのachiveをFalseにする result_goal_data = get_active_goal(user_id); if result_goal_data is not None: goals_id = result_goal_data["id"]; # NOW()時間がずれている with connection.cursor() as cursor: sql = """UPDATE todos SET achieve = 0 WHERE goals_id = %s AND 1 <= DATEDIFF(NOW(),(SELECT last_update FROM users WHERE user_id = %s))""" cursor.execute(sql,(goals_id, user_id)); connection.commit(); with connection.cursor() as cursor: sql = """UPDATE users SET last_update = NOW() WHERE user_id = %s""" cursor.execute(sql,(user_id)); connection.commit(); result = func(user_id); return result;
def get_active_goal (user_id): with connection.cursor() as cursor: sql = """SELECT id, value, marshmallows_num, create_date FROM goals WHERE user_id = %s and active = 1""" __result = cursor.execute(sql,(user_id)); result = cursor.fetchone(); if result is None: return result; result["create_date"] = result["create_date"].strftime("%Y-%m-%d"); return result;
def sign_out (): payload = request.json["payload"]; __jwt = payload["jwt"]; result = jwt.is_valid_jwt(__jwt); user_id = result[1]; with connection.cursor() as cursor: sql = """INSERT INTO use_jwt(jwt, users_id) VALUES ("", %s) ON DUPLICATE KEY UPDATE jwt = "" FROM users WHERE user_id = %s;""" cursor.execute(sql, (user_id)); connection.commit(); redirect(root.SERVER_URL);
def get_actived_goal (goal_id): with connection.cursor() as cursor: sql = """SELECT date FROM activity_date WHERE goal_id = %s"""; cursor.execute(sql, (goal_id)); result = cursor.fetchall(); date_list = []; for li in result: now = datetime.datetime.now(); diff = now - li["date"] diff_date = diff.days; if diff_date <= 91: date_str = li["date"].strftime("%Y/%m/%d"); date_list.append(date_str); return date_list;
def get_achive_goal (user_id): with connection.cursor() as cursor: sql = """SELECT id, value, marshmallows_num FROM goals WHERE user_id = %s and active = 0 and achieve = 1""" result = cursor.execute(sql,(user_id));
def achieve_goal (goal_id): with connection.cursor() as cursor: sql = """UPDATE goals SET achieve = 1, active = 0; WHERE goal_id = %s"""; cursor.execute(sql,(goal_id));