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 relationByFollowingIdAndFollowerId(following_id, follower_id):
cursor.execute(
'SELECT * FROM relation WHERE following_id = %s AND follower_id = %s',
(
following_id,
follower_id,
))
r = cursor.fetchone()
return r
def userIdByEmailPassword(email, password):
cursor.execute(
"SELECT user_id FROM users WHERE email = %s AND password = crypt(%s, password);",
(
email,
password,
))
u = cursor.fetchone()
if u is None:
return None
return u
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 userGetAll():
cursor.execute("SELECT * FROM users")
users = cursor.fetchall()
return users
def userUpdateNicknameByEmail(nickname, email):
cursor.execute("UPDATE users SET nickname = %s where email = %s", (
nickname,
email,
))
conn.commit()
def likeMsgGetOne(msg_id, user_id):
cursor.execute("SELECT * FROM like_msg where msg_id = %s AND user_id = %s",
(msg_id, user_id))
like = cursor.fetchone()
return like
def userByUserId(user_id):
cursor.execute("SELECT * FROM users where user_id = %s;", (user_id, ))
u = cursor.fetchone()
return u
def userDelete(email):
cursor.execute("DELETE FROM users WHERE email = %s;", (email, ))
conn.commit()
def likeMsgGetAll():
cursor.execute('SELECT * FROM like_msg')
message = cursor.fetchall()
return message
def countcmt(msg_id):
cursor.execute("SELECT COUNT(*) FROM comment where msg_id = %s;",
(msg_id, ))
like_num = cursor.fetchone()
return like_num[0]
def likeMsgDeleteAll():
cursor.execute("truncate table like_msg")
def userByEmail(email):
cursor.execute("SELECT * FROM users where email = %s;", (email, ))
u = cursor.fetchone()
return u
def relationGetFollowingUserByFollowerId(follower_id):
cursor.execute(
"SELECT * FROM relation, users WHERE user_id = following_id AND follower_id = %s;",
(follower_id, ))
followings = cursor.fetchall()
return followings
def searchGetUserResults(search, user_id):
cursor.execute(
"SELECT * FROM users WHERE (nickname LIKE %s OR email LIKE %s) AND user_id != %s;",
(search, search, user_id))
users = cursor.fetchall()
return users
def relationByFollowerId(follower_id):
cursor.execute('SELECT * FROM relation where follower_id = %s',
(follower_id, ))
rs = cursor.fetchall()
return rs
def likeMsgCountLike(msg_id):
cursor.execute("SELECT COUNT(*) AS count FROM like_msg where msg_id = %s;",
(msg_id, ))
like_num = cursor.fetchone()
return like_num['count']
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()
def messageDeleteAll():
cursor.execute("truncate table message")
def likeMsgCount():
cursor.execute("SELECT COUNT(*) AS count FROM like_msg")
nb = cursor.fetchone()
if nb is None:
return 0
return nb['count']
def messageGetAll():
cursor.execute('SELECT * FROM message')
message = cursor.fetchall()
return message
def userByNickname(nickname):
cursor.execute("SELECT * FROM users where nickname = %s;", (nickname, ))
u = cursor.fetchone()
return u
def messageDeleteByUserId(user_id):
cursor.execute("DELETE FROM message where user_id = %s;", (user_id, ))
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()
# -*- 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 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()