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)