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