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