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 get_user_by(self,
seqid=None,
phoneNumber=None,
email=None,
token=None,
nickname=None):
'''
查询用户信息
return: 用户对象
'''
if seqid:
strSql = 'select * from [User] where seqid=?'
return DB.ExecSqlQuery(strSql, seqid)
elif phoneNumber:
strSql = 'select * from [User] where phoneNumber=?'
return DB.ExecSqlQuery(strSql, phoneNumber)
elif email:
strSql = 'select * from [User] where email=?'
return DB.ExecSqlQuery(strSql, email)
elif token:
strSql = 'select * from [User] where token=?'
return DB.ExecSqlQuery(strSql, token)
elif nickname:
strSql = 'select * from [User] where nickname=?'
return DB.ExecSqlQuery(strSql, nickname)
else:
pass
def get_letter_by(self, userid=None, friendid=None, status=None):
'''
获取聊天记录
return 消息字典
'''
# 获取未读信息
if status == 0 and friendid:
strSql = f"select * from Letter where status={status} and userid={userid} and friendid={friendid} order by seqid"
return DB.ExecSqlQuery(strSql)
# 获取聊天信息
if userid and friendid:
strSql = f"select * from Letter where userid={userid} and friendid={friendid} order by seqid"
return DB.ExecSqlQuery(strSql)
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 get_friends(self, userid):
'''
获取所有好友
userid: 用户seqid
return 好友seqid列表
'''
strSql = f'select * from RelationUsers where userid in ({userid})'
return DB.ExecSqlQuery(strSql)
def get_avatar(self, userid):
'''
获取头像
userid: 用户id
return 图片对象 or false
'''
strSql = 'select * from Img where imgType=2 and imgUser=? order by seqid'
return DB.ExecSqlQuery(strSql, userid)
def get_all_comments(self, relationArtId):
'''
获取动态所有的评论
relationArtId: 评论关联的动态seqid
return: 所有评论数据 字典
'''
strSql = 'select * from T_Comment where relationArticlesId=? order by doTime DESC'
return DB.ExecSqlQuery(strSql, relationArtId)
def get_comment(self, seqid):
'''
获取单个评论
seqid: 评论seqid
return: 评论数据 字典
'''
strSql = 'select * from T_Comment where seqid=?'
return DB.ExecSqlQuery(strSql, seqid)
def get_all_art(self, artNum):
'''
获取所有用户所有动态
artNum: 获取的动态数量
return 多个动态字典
'''
strSql = f'select TOP ({artNum}) {strSqlItem} where Article.isPublic=1 order by Article.doTime DESC'
return DB.ExecSqlQuery(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_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 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 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 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 get_friend(self, userid, friendid):
'''
查询好友
userid: 用户seqid
return true or false
'''
strSql = 'select * from RelationUsers where userid=? and friendid=?'
rows, _ = DB.ExecSqlQuery(strSql, userid, friendid)
if rows:
return True
else:
return False
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 get_img_by(self,
imgName=None,
headPic=None,
picValue=None,
imgType=None,
imgUser=None,
imgArticle=None):
'''
获取多个图片
返回 字典数据
'''
if imgName:
strSql = 'select * from Img where imgName=?'
return DB.ExecSqlQuery(strSql, imgName)
elif headPic:
strSql = 'select * from Img where headPic=?'
return DB.ExecSqlQuery(strSql, headPic)
elif picValue:
strSql = 'select * from Img where picValue=?'
return DB.ExecSqlQuery(strSql, picValue)
elif imgType:
strSql = 'select * from Img where imgType=?'
return DB.ExecSqlQuery(strSql, imgType)
elif imgUser:
strSql = 'select * from Img where imgUser=? and imgType=1'
return DB.ExecSqlQuery(strSql, imgUser)
elif imgArticle:
strSql = 'select * from Img where imgArticle=?'
return DB.ExecSqlQuery(strSql, imgArticle)
else:
pass
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 select_likes(self, seqid=None, artid=None):
'''
查询点赞记录
seqid: 用户seqid
srtid: 动态seqid
return: id列表
'''
# 查询某个用户的某条动态点赞记录
if seqid and artid:
strSql = 'select seqid from RelationLikes where userid=? and artid=?'
return DB.ExecSqlQuery(strSql, seqid, int(artid))
# 查询某个用户所有点赞记录
elif seqid and not artid:
strSql = 'select artid from RelationLikes where userid=?'
return DB.ExecSqlQuery(strSql, int(seqid))
# 查询某个动态点赞的所有用户
elif not seqid and artid:
strSql = 'select userid from RelationLikes where artid=?'
return DB.ExecSqlQuery(strSql, int(artid))
else:
pass
def get_user_all_arts(self, relationUserId, isPublic=1):
'''
搜索某个用户所有动态
relationUserId: 动态所属用户的id
isPublic: 是否公开 0不公开, 1公开, 2所有
return 多个动态字典
'''
if isPublic == 2:
strSql = f'select {strSqlItem} where relationUserId=? order by Article.doTime DESC'
elif isPublic == 1:
strSql = f'select {strSqlItem} where relationUserId=? and Article.isPublic=1 order by Article.doTime DESC'
elif isPublic == 0:
strSql = f'select {strSqlItem} where relationUserId=? and Article.isPublic=0 order by Article.doTime DESC'
return DB.ExecSqlQuery(strSql, int(relationUserId))
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 get_user_one_art(self, artSeqid, isPublic=1):
'''
获取单个动态
artSeqid: 动态id
isPublic: 是否公开 0不公开, 1公开, 2所有
return 单个动态字典
'''
if isPublic == 2:
strSql = f'select {strSqlItem} where Article.seqid=?'
elif isPublic == 1:
strSql = f'select {strSqlItem} where Article.seqid=? and Article.isPublic=1'
elif isPublic == 0:
strSql = f'select {strSqlItem} where Article.seqid=? and Article.isPublic=0'
return DB.ExecSqlQuery(strSql, artSeqid)
