def insertUser(tel, password, username):
result = 1
try:
if selectUserByTel(tel) is None:
session = DBSession()
signTime = datetime.datetime.now()
iconUrl = '/static/userImg/usertile' + str(random.randint(
10, 44)) + '.jpg'
edUser = User(tel=tel,
password=password,
username=username,
signTime=signTime,
iconUrl=iconUrl,
checkTime=signTime)
session.add(edUser)
else:
result = 2
return result
except Exception:
session.rollback()
raise
else:
session.commit()
result = 0
session.close()
return result
def selectUserByUserId(userId):
try:
session = DBSession()
user = session.query(User).filter(User.userId == userId).first()
if user is None:
result = None
else:
result = {
'tel': user.tel,
'username': user.username,
'gender': user.gender,
'age': user.age,
'height': user.height,
'weight': user.weight,
'area': user.area,
'job': user.job,
'iconUrl': user.iconUrl,
'integral': user.integral,
}
except Exception:
raise
else:
session.close()
return result
def insertTopic(userId, content, picture1, picture2, picture3, picture4,
picture5):
topicTime = datetime.datetime.now()
lastTime = datetime.datetime.now()
edTopic = Topic(content=content,
topicTime=topicTime,
userId=userId,
lastTime=lastTime,
picture1=picture1,
picture2=picture2,
picture3=picture3,
picture4=picture4,
picture5=picture5,
likes=0,
replyNum=0,
comNum=0)
effect_row = 0
try:
session = DBSession()
session.add(edTopic)
except Exception:
session.rollback()
raise
else:
session.commit()
effect_row = 1
session.close()
return effect_row
def insertSportPlan(userId, sport1, sport2, sport3, sport4, time1, time2,
time3, time4, week1, week2, week3, week4):
sportTime = datetime.datetime.now()
edSportPlan = SportPlan(userId=userId,
sport1=sport1,
sport2=sport2,
sport3=sport3,
sport4=sport4,
time1=time1,
time2=time2,
time3=time3,
time4=time4,
week1=week1,
week2=week2,
week3=week3,
week4=week4,
sportTime=sportTime)
effect_row = 0
try:
session = DBSession()
session.add(edSportPlan)
except Exception:
session.rollback()
raise
else:
session.commit()
effect_row = 1
session.close()
return effect_row
def updateUser(userId, username, gender, age, height, weight, area, job):
try:
session = DBSession()
user = session.query(User).filter(User.userId == userId).first()
if user is None:
effect_raw = 0
else:
effect_raw = 1
user.username = username
user.gender = gender
user.age = age
user.height = height
user.weight = weight
user.area = area
user.job = job
'''
session.query(User).filter(User.userId == userId).update(
{
User.username: username,
User.gender: gender,
User.age: age
}, synchronize_session=False)
'''
except Exception:
session.rollback()
raise
else:
session.commit()
session.close()
return effect_raw
def updateUserPrivacy(userId, isTel, isGender, isAge, isHeight, isWeight,
isArea, isJob, isIntegral):
try:
session = DBSession()
userPrivacy = session.query(UserPrivacy).filter(
UserPrivacy.userId == userId).first()
if userPrivacy is None:
effect_raw = 0
else:
userPrivacy.isTel = isTel
userPrivacy.isGender = isGender
userPrivacy.isAge = isAge
userPrivacy.isHeight = isHeight
userPrivacy.isWeight = isWeight
userPrivacy.isArea = isArea
userPrivacy.isJob = isJob
userPrivacy.isIntegral = isIntegral
effect_raw = 1
except Exception:
session.rollback()
raise
else:
session.commit()
session.close()
return effect_raw
def updateDietPlan(userId, change, cereals, fruit, meat, milk, fat,
vegetables):
try:
session = DBSession()
dietPlan = session.query(DietPlan).filter(
DietPlan.userId == userId).first()
if dietPlan is None:
effect_row = insertDietPlan(userId, change, cereals, fruit, meat,
milk, fat, vegetables)
if effect_row == 1:
result = 1
else:
result = 0
else:
dietPlan.change = change
dietPlan.cereals = cereals
dietPlan.fruit = fruit
dietPlan.meat = meat
dietPlan.milk = milk
dietPlan.fat = fat
dietPlan.vegetables = vegetables
dietPlan.dietTime = datetime.datetime.now()
result = 1
except Exception:
session.rollback()
raise
else:
session.commit()
session.close()
return result
def updateBloodbeforeBF(userId, bLevel, bTime, bIndex, bloodDate):
effect_raw = 0
try:
session = DBSession()
blood = session.query(Blood).filter(Blood.userId == userId, Blood.bloodDate == bloodDate).first()
if blood is None:
level = {'0': '0', '1': '0', '2': '0', '3': '0', '4': '0', '5': '0', '6': '0'}
bloodTime = {'0': '0', '1': '0', '2': '0', '3': '0', '4': '0', '5': '0', '6': '0'}
level[bIndex] = bLevel
bloodTime[bIndex] = bTime
effect_raw = insertGBlood(userId, json.dumps(level), json.dumps(bloodTime), bloodDate)
else:
level = json.loads(blood.level)
bloodTime = json.loads(blood.bloodTime)
level[bIndex] = bLevel
bloodTime[bIndex] = bTime
blood.level = json.dumps(level)
blood.bloodTime = json.dumps(bloodTime)
effect_raw = 1
except Exception:
session.rollback()
raise
else:
session.commit()
session.close()
return effect_raw
def updateControlPlan(userId, min1, max1, min2, max2, sleep1, sleep2):
try:
session = DBSession()
controlPlan = session.query(ControlPlan).filter(
ControlPlan.userId == userId).first()
if controlPlan is None:
effect_row = insertControlPlan(userId, min1, max1, min2, max2,
sleep1, sleep2)
session.close()
if effect_row == 1:
result = 1
else:
result = 0
else:
controlPlan.min1 = min1
controlPlan.max1 = max1
controlPlan.min2 = min2
controlPlan.max2 = max2
controlPlan.sleep1 = sleep1
controlPlan.sleep2 = sleep2
controlPlan.controlTime = datetime.datetime.now()
result = 1
except Exception:
session.rollback()
raise
else:
session.commit()
session.close()
return 1
def insertArticle(title, content, labelOne, labelTwo, labelThree, labelFour,
labelFive, imgId):
session = DBSession()
articleTime = datetime.datetime.now()
edArticle = Article(title=title,
content=content,
articleTime=articleTime,
labelOne=labelOne,
labelTwo=labelTwo,
labelThree=labelThree,
labelFour=labelFour,
labelFive=labelFive,
imgId=imgId,
views=0,
comNumber=0)
effect_row = 0
try:
session.add(edArticle)
except Exception:
session.rollback()
raise
else:
session.commit()
effect_row = 1
session.close()
return effect_row
def selectSumHealth(userId):
try:
session = DBSession()
result = session.query(Health).filter(Health.userId == userId).count()
except Exception:
raise
else:
session.close()
return result
def selectSumBlood(userId):
try:
session = DBSession()
result = session.query(Blood).filter(Blood.userId == userId).count()
except Exception:
raise
else:
session.close()
return result
def selectSumFamily(userId):
try:
session = DBSession()
result = session.query(Family).filter(Family.userId == userId).count()
except Exception:
raise
else:
session.close()
return result
def selectSumSubReply():
result = 0
try:
session = DBSession()
result = session.query(SubReply).count()
except Exception:
raise
else:
session.close()
return result
def selectSumTopicLikeContent(content):
try:
session = DBSession()
result = session.query(Topic).filter(
Topic.content.like('%' + content + '%')).count()
except Exception:
raise
else:
session.close()
return result
def selectSumSubReplyByReplyId(replyId):
result = 0
try:
session = DBSession()
result = session.query(SubReply).filter(SubReply.replyId == replyId).count()
except Exception:
raise
else:
session.close()
return result
def selectSumArticle():
result = 0
try:
session = DBSession()
result = session.query(Article).count()
except Exception:
raise
else:
session.close()
return result
def selectSumReplyByUserId(userId):
result = 0
try:
session = DBSession()
result = session.query(Reply).filter(Reply.userId == userId).count()
except Exception:
raise
else:
session.close()
return result
def selectSumFavoriteArticleByUserId(userId):
result = 0
try:
session = DBSession()
result = session.query(FavoriteArticle).filter(
FavoriteArticle.userId == userId).count()
except Exception:
raise
else:
session.close()
return result
def selectFromXGetNTopic(x, n):
try:
session = DBSession()
offset = x
num = x + n
topic = session.query(Topic).slice(offset, num).all()
except Exception:
raise
else:
session.close()
return topic
def selectSumTopicByTopicId(topicId):
try:
session = DBSession()
result = session.query(FavoriteTopic).filter(
FavoriteTopic.topicId == topicId).count()
except Exception:
session.rollback()
raise
else:
session.close()
return result
def selectSumCommentByArticleId(articleId):
result = 0
try:
session = DBSession()
result = session.query(Comment).filter(
Comment.articleId == articleId).count()
except Exception:
raise
else:
session.close()
return result
def selectFromXGetNUser(x, n):
try:
session = DBSession()
offset = x
num = x + n
user = session.query(User).slice(offset, num).all()
except Exception:
raise
else:
session.close()
return user
def selectSumFollowByFollowId(userId):
result = 0
try:
session = DBSession()
result = session.query(Follow).filter(
Follow.followId == userId).count()
except Exception:
raise
else:
session.close()
return result
def updateArticleComNumber(articleId):
try:
session = DBSession()
article = session.query(Article).filter(
Article.articleId == articleId).first()
article.comNumber = article.comNumber + 1
except Exception:
session.rollback()
raise
else:
session.commit()
session.close()
def selectTopicByTopicId(topicId):
try:
session = DBSession()
topic = session.query(Topic).filter(Topic.topicId == topicId).first()
if topic is None:
result = None
else:
result = topic
except Exception:
raise
else:
session.close()
return result
def selectUserByTel(tel):
try:
session = DBSession()
user = session.query(User).filter(User.tel == tel).first()
if user is None:
result = None
else:
result = user
except Exception:
raise
else:
session.close()
return result
def selectSubReplyBySubReplyId(subreplyId):
try:
session = DBSession()
subreply = session.query(SubReply).filter(SubReply.subreplyId == subreplyId).first()
if subreply is None:
result = None
else:
result = subreply
except Exception:
raise
else:
session.close()
return result
def selectReplyByReplyId(replyId):
try:
session = DBSession()
reply = session.query(Reply).filter(Reply.replyId == replyId).first()
if reply is None:
result = None
else:
result = reply
except Exception:
raise
else:
session.close()
return result
def selectFloorByTopicId(topicId):
try:
session = DBSession()
reply = session.query(Reply).filter(Reply.topicId == topicId).all()
if reply is None:
result = None
else:
result = reply
except Exception:
raise
else:
session.close()
return result
