def add_watch_info(self, activity_id):
try:
session.query(WatchInfoModel.times).filter(WatchInfoModel.activityID == activity_id).\
update({WatchInfoModel.times: WatchInfoModel.times+1})
return "success"
except:
raise SystemError
def get_activity_list(self, begin, is_old):
try:
activity_list = []
if is_old:
activity_list = session.query(ActivityModel)\
.filter(ActivityModel.selectEndTime < datetime.datetime.now()).all()
else:
activity_list = session.query(ActivityModel)\
.filter(ActivityModel.selectEndTime <= datetime.datetime.now()).all()
return activity_list
except:
raise SystemError
finally:
session.close()
def get_user_info(self, user_id, is_stable):
try:
if is_stable:
user_info = session.query(UserInfoModel).filter(UserInfoModel.id == user_id).first()
else:
user_info = session.query(CheckingUserInfoModel).filter(CheckingUserInfoModel.id == user_id).first()
if not user_info:
raise NotFoundException
return user_info
except NotFoundException:
raise NotFoundException
except:
traceback.print_exc()
finally:
session.close()
def get_pool_by_user(self, user_id):
try:
pools = session.query(PoolModel).join(PoolJoinModel).filter(PoolJoinModel.userID == user_id)
return pools
except:
raise SystemError
finally:
session.close()
def get_pool_list(self, begin):
try:
pools = session.query(PoolModel).all()
return pools
except:
raise SystemError
finally:
session.close()
def get_all(self):
try:
posts = session.query(PostsModel).all()
return posts
except:
raise SystemError
finally:
session.close()
def get_all(self):
try:
user_info = session.query(UserInfoModel).all()
return user_info
except:
traceback.print_exc()
finally:
session.close()
def get_posts_by_user(self, user_id):
try:
posts = session.query(PostsModel).filter(
PostsModel.userID == user_id).all()
return posts
except:
raise SystemError
finally:
session.close()
def get_my(self, user_id):
try:
posts = session.query(RecruitModel).join(PostsModel). \
filter(PostsModel.userID == user_id).filter(PostsModel.id == RecruitModel.postsID).all()
return posts
except:
raise SystemError
finally:
session.close()
def get_activity_by_user(self, userID):
try:
activity = session.query(ActivityModel).join(
ActivityJoinModel).filter(ActivityJoinModel.userID == userID)
return activity
except:
raise SystemError
finally:
session.close()
def get_user_in_pool(self, pool_id):
try:
pools = session.query(UserInfoModel).join(PoolJoinModel, PoolJoinModel.userID == UserInfoModel.id)\
.filter(PoolJoinModel.poolID == pool_id).all()
return pools
except:
raise SystemError
finally:
session.close()
def get_watch_info(self, activity_id):
try:
watch_info = session.query(WatchInfoModel.times).filter(
WatchInfoModel.activityID == activity_id)
return watch_info
except:
raise SystemError
finally:
self.session.close()
def get_your_love(self, user_id, pool_id):
try:
lovers = session.query(UserInfoModel).join(LoveRelationModel,LoveRelationModel.toID == UserInfoModel.id)\
.filter(LoveRelationModel.fromID == user_id)\
.filter(LoveRelationModel.poolID == pool_id).all()
return lovers
except:
raise SystemError
finally:
session.close()
def check_user_info(self, user_id, result):
if result:
"""把一个数据库中的数据直接拿到另一个数据库中"""
user_info = session.query(CheckingUserInfoModel).filter(CheckingUserInfoModel.id == user_id).first()
if not user_info:
raise NotFoundException
current_info = session.query(UserInfoModel).filter(UserInfoModel.id == user_id).first()
if current_info:
self.delete(current_info)
self.insert(UserInfoConverter().mockToReal(user_info))
self.delete(user_info)
self.session.commit()
else:
session.query(CheckingUserInfoModel).filter(CheckingUserInfoModel.id == user_id) \
.update(dict(isReject=True))
# user_info.isRejected = True
self.session.commit()
user_info = [i[0] for i in session.query(CheckingUserInfoModel.id).all()]
session.close()
return user_info
def getActivityJoin(self, user_id, activity_id):
try:
join = session.query(ActivityJoinModel).filter(ActivityJoinModel.userID == user_id). \
filter(ActivityJoinModel.activityID == activity_id).first()
return join
except AlreadyExists:
raise AlreadyExists
except:
raise SystemError
finally:
session.close()
def getPoolJoin(self, user_id, pool_id):
try:
join = session.query(PoolJoinModel).filter(PoolJoinModel.userID == user_id).\
filter(PoolJoinModel.poolID == pool_id).first()
print(join)
return join
except AlreadyExists:
raise AlreadyExists
except:
raise SystemError
finally:
session.close()
def get_pool(self, pool_id):
try:
pool = session.query(PoolModel).filter(PoolModel.id == pool_id).first()
if not pool:
raise NotFoundException
return pool
except NotFoundException:
raise NotFoundException
except:
raise SystemError
finally:
session.close()
def get_recruit(self, user_id, posts_id):
try:
recruit = session.query(RecruitModel).filter(RecruitModel.userID == user_id).\
filter(RecruitModel.postsID == posts_id).first()
if recruit:
raise AlreadyExists
except AlreadyExists:
raise AlreadyExists
except:
raise SystemError
finally:
session.close()
def get_relation(self, from_id, to_id, pool_id):
try:
relation = session.query(LoveRelationModel).filter(LoveRelationModel.fromID == from_id).\
filter(LoveRelationModel.toID == to_id).filter(LoveRelationModel.poolID == pool_id).first()
if relation:
raise AlreadyExists
except AlreadyExists:
raise AlreadyExists
except:
raise SystemError
finally:
session.close()
def get_activity(self, activity_id):
try:
activity = session.query(ActivityModel).filter(
ActivityModel.id == activity_id).first()
if not activity:
raise NotFoundException
return activity
except NotFoundException:
raise NotFoundException
except:
traceback.print_exc()
raise SystemError
finally:
session.close()
def get_true_love(self, user_id, pool_id):
try:
alaised1 = aliased(LoveRelationModel)
alaised2 = aliased(LoveRelationModel)
lovers = (session.query(alaised1.toID).join(alaised2).filter(
alaised1.fromID == alaised2.toID).filter(
alaised2.toID == alaised1.fromID).filter(
alaised1.fromID == user_id).filter(
alaised1.poolID == pool_id).all())
return [i[0] for i in lovers]
except:
raise SystemError
finally:
session.close()
def update_user_info(self, userInfoModel: UserInfoModel):
try:
self.session = db.session
user_info: UserInfoModel = session.query(UserInfoModel).filter(
UserInfoModel.userID == userInfoModel.userID).first()
"""先设置成删除然后去增加的，后面再修改"""
if not user_info:
raise NotFoundException
print(user_info.__dict__)
print(userInfoModel.__dict__)
self.delete(user_info)
session.add(userInfoModel)
session.commit()
except NotFoundException:
raise NotFoundException
except IntegrityError:
raise AlreadyExists
finally:
session.close()
session.commit()
print "Importing Source Codes"
with open('data/SRC_CD.txt', 'rb') as src_cd_file:
utility_csv = csv.reader(src_cd_file, delimiter='^')
for row in stripTilde(utility_csv):
new_src_cd = SourceCode()
new_src_cd.src_Cd = row[0]
new_src_cd.srcCd_Desc = row[1]
session.add(new_src_cd)
session.commit()
print "Importing Food Descriptions"
with open('data/FOOD_DES.txt', 'rb') as food_des_file:
foodGrpQuery = session.query(FoodGroupDescription)
utility_csv = csv.reader(food_des_file, delimiter='^')
for row in stripTilde(utility_csv):
new_food_des = FoodDescription()
new_food_des.nDB_No = row[0]
# Item one is a foreign key!
new_food_des.long_Desc = row[2]
new_food_des.shrt_Desc = row[3]
new_food_des.comName = row[4]
new_food_des.manufacName = row[5]
if row[6]:
survey = True
else:
survey = False
new_food_des.survey = survey
str = str.strip('~').decode('latin-1').encode('utf-8')
if not str:
return None
return str
def stripTilde(table):
for row in table:
yield [fixString(str) for str in row]
createTables()
print "Importing Food Descriptions"
with open('data/USDA/FOOD_DES.txt', 'rb') as food_des_file:
foodGrpQuery = session.query(FoodGroupDescription)
utility_csv = csv.reader(food_des_file, delimiter='^')
for row in stripTilde(utility_csv):
food_description = FoodDescription()
food_description.id = row[0]
food_description.foodGroup_id = row[1]
food_description.longDesc = row[2]
food_description.shrtDesc = row[3]
food_description.commonName = row[4]
food_description.manufactureName = row[5]
if row[6]:
survey = True
else:
def get_un_checking_list(self):
user_list = session.query(UserModel).join(CheckingUserInfoModel).all()
session.close()
return user_list