def insert_img(self,
imgName,
headPic,
imgUser,
imgArticle=None,
imgType=1):
'''
上传图片
imgName: 图片名
headPic: 图片地址
imgUser: 图片所属用户
imgArticle: 图片所属动态
imgType: 1动态 2头像 0系统图片
return True or False
'''
# 动态图片
if imgType == 1:
strSql = 'insert into Img (imgName,headPic,imgType,imgUser, imgArticle) values (?,?,?,?,?)'
return DB.ExecSqlNoQuery(strSql, imgName, headPic, imgType,
imgUser, imgArticle)
# 头像图片
elif imgType == 2:
strSql = 'insert into Img (imgName,headPic,imgType,imgUser) values (?,?,?,?)'
return DB.ExecSqlNoQuery(strSql, imgName, headPic, imgType,
imgUser)
def update_article(self, artid, likes=None, isPublic=None, comments=None):
'''
更新动态数据
设置公开状态、 更新点赞数和评论数。
artid: 动态id
isPublic: 动态状态 true or false
likes: 点赞 1 or -1
comments: 评论 1 or -1
'''
if isPublic:
strSql = 'update Article set isPublic=? where seqid=?'
return DB.ExecSqlNoQuery(strSql, isPublic, artid)
elif likes:
resDict = self.get_user_one_art(artid, 2)
if isinstance(resDict, dict):
likes = resDict.get('likes') + int(likes)
strSql = 'update Article set likes=? where seqid=?'
return DB.ExecSqlNoQuery(strSql, likes, artid)
elif comments:
resDict = self.get_user_one_art(artid, 2)
if isinstance(resDict, dict):
comments = resDict.get('comments') + int(comments)
strSql = 'update Article set comments=? where seqid=?'
return DB.ExecSqlNoQuery(strSql, comments, artid)
def delete_letter(self, seqid=None, userid=None, friendid=None):
'''
删除聊天记录
return True or false
'''
# 删除用户聊天记录
if userid and friendid:
strSql = f"delete Letter where userid={userid} and friendid={friendid}"
return DB.ExecSqlNoQuery(strSql)
# 删除一条聊天记录
if seqid:
strSql = f"delete Letter where seqid={seqid}"
return DB.ExecSqlNoQuery(strSql)
def delete_comments(self, relationArtId):
'''
删除动态的所有评论
return true or false
'''
strSql = 'delete T_Comment where relationArticlesId=?'
return DB.ExecSqlNoQuery(strSql, relationArtId)
def add_comment(self, userid, text, isPublic, relationArtId, commentSeqid = None):
'''
新增评论
text: 评论正文
isPublic: 是否公开
relationArtId: 关联的动态id
commentSeqid: 回复评论的id
return true or false
'''
doTime = str(datetime.datetime.strptime(str(datetime.datetime.now()), '%Y-%m-%d %H:%M:%S.%f'))[:-3]
if commentSeqid:
strSql = 'insert into T_Comment (userId,text,isPublic,relationArticlesId,relationComment,doTime) values (?,?,?,?,?,?)'
return DB.ExecSqlNoQuery(strSql, userid, text, isPublic, relationArtId, commentSeqid, doTime)
else:
strSql = 'insert into T_Comment (userId,text,isPublic,relationArticlesId,doTime) values (?,?,?,?,?)'
return DB.ExecSqlNoQuery(strSql, userid, text, isPublic, relationArtId, doTime)
def delete_art(self, seqid):
'''
删除动态
seqid: 动态id
return: ture or false
'''
strSql = 'delete Article where seqid=?'
return DB.ExecSqlNoQuery(strSql, int(seqid))
def withdrawn_letter(self, senderid, receiverid, sendTime):
'''
撤回聊天记录
return True or false
'''
if senderid and receiverid and sendTime:
strSql = f"delete Letter where senderid={senderid} and receiverid={receiverid} and sendTime='{sendTime}'"
return DB.ExecSqlNoQuery(strSql)
def delete_comment(self, seqid):
'''
删除评论
seqid: 评论seqid
return true or false
'''
strSql = 'delete T_Comment where seqid=?'
return DB.ExecSqlNoQuery(strSql, seqid)
def delete_img(self, imgUser, imgArticle=None, imgType=1):
'''
删除图片
imgName: 图片名
headPic: 图片地址
imgUser: 图片所属用户
imgArticle: 图片所属动态
imgType: 1动态 2头像 0系统图片
return True or False
'''
# 动态图片
if imgType == 1:
strSql = 'delete Img where imgArticle=?'
return DB.ExecSqlNoQuery(strSql, imgArticle)
# 头像图片
elif imgType == 2:
strSql = 'delete Img where imgUser=? and imgType=?'
return DB.ExecSqlNoQuery(strSql, imgUser, imgType)
def set_read(self, userid, friendid):
'''
设置已读
userid: 用户seqid
friendid: 好于seqid
return True or false
'''
strSql = f"update Letter set status=1 where userid={userid} and friendid={friendid}"
return DB.ExecSqlNoQuery(strSql)
def answer_friend(self, userSeqid, friendSeqid, answer):
'''
回应好友请求
'''
if answer:
strSql = 'update RelationUsers set isReceive=1 where userid=? and friendid=?'
return DB.ExecSqlNoQuery(strSql, userSeqid, friendSeqid)
else:
return self.delete_friend(userSeqid, friendSeqid)
def save_letter(self, userid, friendid, text, status):
'''
保存聊天记录
return True or false
'''
msgType = 1
sendTime = str(
datetime.datetime.strptime(str(datetime.datetime.now()),
'%Y-%m-%d %H:%M:%S.%f'))[:-3]
strSql = "insert into Letter (userid,friendid,senderid,receiverid,msgType,text,sendTime,status) values ()"
_res1 = DB.ExecSqlNoQuery(strSql, userid, friendid, userid, friendid,
msgType, text, sendTime, 1)
_res2 = DB.ExecSqlNoQuery(strSql, friendid, userid, friendid, userid,
msgType, text, sendTime, status)
if _res1 and _res1:
return True
else:
return False
def Table_Comment(cls):
strSql = """
CREATE TABLE T_Comment
{
seqid int NOT NULL PRIMARY KEY,
text varchar(200) NOT NULL,
is_public bool DEFAULT True,
relation_id int FOREIGN KEY REFERENCES Article(seqid),
}
"""
DB.ExecSqlNoQuery(strSql)
def Table_Article(cls):
strSql = """
CREATE TABLE Article
{
seqid int NOT NULL PRIMARY KEY,
text varchar(2000) NOT NULL,
is_public bool DEFAULT False,
like int DEFAULT 0,
relation_id int FOREIGN KEY REFERENCES [User](seqid),
}
"""
DB.ExecSqlNoQuery(strSql)
def like_art(self, seqid, artid):
'''
点赞动态
seqid: 用户seqid
artid: 动态seqid
'''
strSql = 'insert into RelationLikes (userid,artid) values (?,?)'
if DB.ExecSqlNoQuery(strSql, seqid, int(artid)):
if not self.update_article(artid, likes=1):
return None
else:
return True
return None
def reset_like_art(self, seqid, artid):
'''
取消点赞
seqid: 用户seqid
artid: 动态seqid
'''
strSql = 'delete RelationLikes where userid=? and artid=?'
if DB.ExecSqlNoQuery(strSql, seqid, int(artid)):
if not self.update_article(artid, likes=-1):
return None
else:
return True
return None
def update_user(self, seqid, updateDict):
'''
更新用户数据
seqid: 用户id
update_dct: 更新字段字典
return: 成功or失败
'''
_tempStr = ''
for key, value in updateDict.items():
_tempStr += f"{key}='{value}',"
_tempStr = _tempStr[:-1]
strSql = f"update [User] set {_tempStr} where seqid = {int(seqid)}"
return DB.ExecSqlNoQuery(strSql)
def insert_user(self, phoneNumber, email, password, nickname, sex):
'''
插入新用户
phoneNumber: 电话
email: 邮箱
password: 密码
nickname: 昵称
return: 成功or失败
'''
strSql = 'insert into [User] (phoneNumber,email,password,nickname,sex,registerTime) values (?,?,?,?,?,?)'
return DB.ExecSqlNoQuery(
strSql, phoneNumber, email, password, nickname, sex,
str(
datetime.datetime.strptime(str(datetime.datetime.now()),
'%Y-%m-%d %H:%M:%S.%f'))[:-3])
def Table_User(cls):
strSql = """
CREATE TABLE [User]
{
seqid int NOT NULL PRIMARY KEY,
phone_number varchar(11) NOT NULL,
email varchar(25),
password varchar(100) NOT NULL,
nickname varchar(20) NOT NULL,
sex varchar(2),
relationships varchar(120),
register_time varchar(100) NOT NULL,
token varchar(255),
}
"""
DB.ExecSqlNoQuery(strSql)
def Table_Letter(cls):
strSql = """
CREATE TABLE Letter
{
seqid int NOT NULL PRIMARY KEY,
userid int NOT NULL FOREIGN KEY REFERENCES [User](seqid),
friendid int NOT NULL FOREIGN KEY REFERENCES [User](seqid),
senderid int NOT NULL FOREIGN KEY REFERENCES [User](seqid),
receiverid int NOT NULL FOREIGN KEY REFERENCES [User](seqid),
msg_type int NOT NULL,
text varchar(1000) NOT NULL,
send_time datetime NOT NULL,
read_time datetime NOT NULL,
status bool NOT NULL DEFAULT 0,
}
"""
DB.ExecSqlNoQuery(strSql)
def ban_art(self, seqid, ban):
strSql = f'update Articles set ban={ban} where seqid={seqid}'
return DB.ExecSqlNoQuery(strSql)
def delete_friend(self, userSeqid, friendSeqid):
'''
删除好友
'''
strSql = 'delete RelationUsers where userid=? and friendid=?'
return DB.ExecSqlNoQuery(strSql, userSeqid, friendSeqid)
def update_token(self, seqid, token):
strSql = f'update T_admin set token={token} where seqid ={seqid}'
return DB.ExecSqlNoQuery(strSql)
def add_admin(self, userName=None, password=None, superadmin=None):
strSql = 'insert into T_admin (userName,password,superadmin) values(?,?,?)'
return DB.ExecSqlNoQuery(strSql, userName, password, superadmin)
def ban_user(self, seqid, ban):
strSql = f'update [User] set ban={ban} where seqid={seqid}'
return DB.ExecSqlNoQuery(strSql)
def add_friend(self, userSeqid, friendSeqid):
'''
添加好友
'''
strSql = f'insert into RelationUsers (userid,friendid,isReceive) values ({userSeqid},{friendSeqid},1),({friendSeqid},{userSeqid},0)'
return DB.ExecSqlNoQuery(strSql)
def ban_admin(self, seqid, ban=1):
strSql = f'update T_admin set ban={ban} where seqid={seqid}'
return DB.ExecSqlNoQuery(strSql)
def change_passw(self, seqid, newpassw):
strSql = f'update T_admin set password={newpassw} where seqid={seqid}'
return DB.ExecSqlNoQuery(strSql)
