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