def likeMsgDelete(msg_id, user_id): cursor.execute("DELETE FROM like_msg where msg_id = %s AND user_id = %s;", ( msg_id, user_id, )) conn.commit()
def userUnfollow(following_id, follower_id): cursor.execute( "DELETE FROM relation where following_id = %s AND follower_id = %s;", (following_id, follower_id)) conn.commit() cursor.execute("SELECT nickname FROM users WHERE user_id = %s;", (following_id, )) return ""
def likeMsgCreate(msg_id, user_id, c_time): cursor.execute( "INSERT INTO like_msg(msg_id, user_id, c_time) VALUES(%s,%s,%s);", ( msg_id, user_id, c_time, )) conn.commit()
def userUpdatePasswordByEmail(password, email): cursor.execute( "UPDATE users SET password = crypt(%s, gen_salt('bf', 8)) where email = %s", ( password, email, )) conn.commit()
def messageCreate(user_id, content, c_time): cursor.execute( "INSERT INTO message(user_id,content,c_time) VALUES(%s,%s,%s);", ( user_id, content, c_time, )) conn.commit()
def userFollow(following_id, follower_id): cursor.execute( 'SELECT * FROM relation WHERE following_id = %s AND follower_id = %s', (following_id, follower_id)) if cursor.fetchone() is None and following_id != follower_id: c_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S') cursor.execute( "INSERT INTO relation(following_id, follower_id, c_time) VALUES(%s,%s,%s);", (following_id, follower_id, c_time)) conn.commit() cursor.execute("SELECT nickname FROM users WHERE user_id = %s;", (following_id, )) return ""
def userUpdateNicknameByEmail(nickname, email): cursor.execute("UPDATE users SET nickname = %s where email = %s", ( nickname, email, )) conn.commit()
def userDelete(email): cursor.execute("DELETE FROM users WHERE email = %s;", (email, )) conn.commit()
def userCreate(email, nickname, password, c_time): cursor.execute( "INSERT INTO users(email,nickname,password,c_time) VALUES(%s,%s,crypt(%s, gen_salt('bf', 8)), %s);", (email, nickname, password, c_time)) conn.commit()
def messageUpdateContentById(content, msg_id): cursor.execute("UPDATE message SET content = %s where msg_id = %s;", ( content, msg_id, )) conn.commit()
def messageDeleteByUserId(user_id): cursor.execute("DELETE FROM message where user_id = %s;", (user_id, )) conn.commit()
def likeCmtDelete(cmt_id, user_id): cursor.execute("DELETE FROM like_cmt where cmt_id = %s AND user_id = %s;", (cmt_id, user_id)) conn.commit()
def likeCmtCreate(cmt_id, user_id, c_time): cursor.execute( "INSERT INTO like_cmt(cmt_id, user_id,c_time) VALUES(%s,%s,%s);", (cmt_id, user_id, c_time)) conn.commit()
def commentDeleteById(cmt_id): cursor.execute("DELETE FROM comment where cmt_id = %s;", (cmt_id, )) conn.commit()
def commentUpdateById(content, cmt_id): cursor.execute("UPDATE comment SET content = %s where cmt_id = %s;", (content, cmt_id)) conn.commit()
def commentCreate(msg_id, user_id, content, c_time): cursor.execute( "INSERT INTO comment(msg_id,user_id,content,c_time) VALUES(%s,%s,%s,%s);", (msg_id, user_id, content, c_time)) conn.commit()
def relationCreate(following_id, follower_id, c_time): cursor.execute( "INSERT INTO relation(following_id, follower_id, c_time) VALUES(%s,%s,%s);", (following_id, follower_id, c_time)) conn.commit()
def relationDelete(following_id, follower_id): cursor.execute( "DELETE FROM relation where following_id = %s AND follower_id = %s;", (following_id, follower_id)) conn.commit()
# -*- coding: utf-8 -*- from helpers import conn, cursor sql = "DROP TABLE IF EXISTS users; " + \ "CREATE TABLE users ( " + \ "user_id serial primary key not null, " + \ "email text, " + \ "password text, " + \ "nickname text, " + \ "c_time timestamp );" cursor.execute(sql) conn.commit() sql = "DROP TABLE IF EXISTS message; " + \ "CREATE TABLE message ( " + \ "msg_id serial primary key not null, " + \ "user_id int not null, " + \ "content text , " + \ "c_time timestamp , " + \ "status integer );" cursor.execute(sql) conn.commit() sql = "DROP TABLE IF EXISTS comment; " + \ "CREATE TABLE comment ( " + \ "cmt_id serial primary key not null, " + \ "msg_id int not null, " + \ "user_id int not null, " + \ "content text , " + \ "c_time timestamp );" cursor.execute(sql)
def messageDeleteById(msg_id): cursor.execute("DELETE FROM message where msg_id = %s;", (msg_id, )) conn.commit()