def getAll(self, cls_:object, filters:set, order:str = 'id desc', field:tuple = (), limit:int = 0)->list:
"""
查询全部
@param object cls_ 数据库模型实体类
@param set filters 查询条件
@param str order 排序
@param tuple field 字段
@param int $limit 取多少条
@return dict
"""
if not filters:
res = dBSession.query(cls_)
else:
res = dBSession.query(cls_).filter(*filters)
orderArr = order.split(' ')
if orderArr[1] == 'desc':
res = res.order_by(desc(orderArr[0]))
else:
res = res.order_by(asc(orderArr[0]))
if limit != 0:
res = res.limit(limit).all()
else:
res = res.all()
if not field:
res = [c.to_dict() for c in res]
else:
res = [c.to_dict(only=field) for c in res]
return res
def getList(self, filters, order, field=(), offset=0, limit=15):
res = {}
res['page'] = {}
res['page']['count'] = dBSession.query(AddressBook).filter(
*filters).count()
res['list'] = []
res['page']['total_page'] = self.get_page_number(
res['page']['count'], limit)
res['page']['current_page'] = offset
if offset != 0:
offset = (offset - 1) * limit
if res['page']['count'] > 0:
res['list'] = dBSession.query(AddressBook).filter(*filters)
order = order.split(' ')
if order[1] == 'desc':
res['list'] = res['list'].order_by(desc(
order[0])).offset(offset).limit(limit).all()
else:
res['list'] = res['list'].order_by(asc(
order[0])).offset(offset).limit(limit).all()
if not field:
res['list'] = [c.to_dict() for c in res['list']]
else:
res['list'] = [c.to_dict(only=field) for c in res['list']]
return res
def getList(self, cls_:object, filters:set, order:str="id desc", field:tuple=(), offset:int = 0, limit:int = 15)->dict:
"""
列表
@param object cls_ 数据库模型实体类
@param set filters 查询条件
@param str order 排序
@param tuple field 字段
@param int offset 偏移量
@param int limit 取多少条
@return dict
"""
res = {}
res['page'] ={}
res['page']['count'] = dBSession.query(cls_).filter(*filters).count()
res['list'] = []
res['page']['total_page'] = self.get_page_number(res['page']['count'], limit)
res['page']['current_page'] = offset
if offset != 0:
offset = (offset - 1) * limit
if res['page']['count'] > 0:
res['list'] = dBSession.query(cls_).filter(*filters)
orderArr = order.split(' ')
if orderArr[1] == 'desc':
res['list'] = res['list'].order_by(desc(orderArr[0])).offset(offset).limit(limit).all()
else:
res['list'] = res['list'].order_by(asc(orderArr[0])).offset(offset).limit(limit).all()
if not field:
res['list'] = [c.to_dict() for c in res['list']]
else:
res['list'] = [c.to_dict(only=field) for c in res['list']]
return res
def updateLastMsgRoom(room_uuid, data, updated_at):
dBSession.query(Room).filter(Room.room_uuid == room_uuid).update({
'last_msg':
json.dumps(data),
'updated_at':
updated_at,
})
return True
def cleanUnreadNumber(room_uuid, user_id):
filter = {
AddressBook.be_focused_user_id == user_id,
AddressBook.room_uuid == room_uuid
}
dBSession.query(AddressBook).filter(
*filter).update({'unread_number': 0, 'updated_at': time.time()})
return dBSession.commit()
def updateLastMsgRoom(room_uuid, msg):
dBSession.query(Room).filter(Room.room_uuid == room_uuid).update({
'last_msg':
msg,
'updated_at':
time.time()
})
return True
def updateUnreadNumber(room_uuid, user_id):
filter = {
AddressBook.be_focused_user_id != user_id,
AddressBook.room_uuid == room_uuid
}
dBSession.query(AddressBook).filter(*filter).update({
AddressBook.unread_number: AddressBook.unread_number+1,
AddressBook.updated_at: time.time()
})
return dBSession.commit()
def getCount(self, cls_:object, filters:set, field = None)->int:
"""
统计数量
@param object cls_ 数据库模型实体类
@param set filters 条件
@param obj field 字段
@return int
"""
if field == None:
return dBSession.query(cls_).filter(*filters).count()
else:
return dBSession.query(cls_).filter(*filters).count(field)
def updateUnreadNumber(room_uuid, user_id):
# 更新关注者未读消息
filter = {
UserRoomRelation.user_id != user_id,
UserRoomRelation.room_uuid == room_uuid
}
dBSession.query(UserRoomRelation).filter(*filter).update({
UserRoomRelation.unread_number:
UserRoomRelation.unread_number + 1,
UserRoomRelation.updated_at:
time.time()
})
return dBSession.commit()
def cleanUnreadNumber(room_uuid, user_id):
# 清除关注者未读消息次数
filter = {
UserRoomRelation.user_id == user_id,
UserRoomRelation.room_uuid == room_uuid
}
dBSession.query(UserRoomRelation).filter(*filter).update({
'unread_number':
0,
'updated_at':
time.time()
})
return dBSession.commit()
def getRoomList(user_id):
filters = {
AddressBook.be_focused_user_id == user_id
}
data = dBSession.query(AddressBook).order_by(AddressBook.updated_at.desc()).filter(*filters).all()
data = Base.formatBody(Utils.db_l_to_d(data))
return data
def getRoomList(user_id):
filters = {AddressBook.be_focused_user_id == user_id}
res = dBSession.query(AddressBook).order_by(
AddressBook.updated_at.desc()).filter(*filters).all()
data = {"list": Utils.db_l_to_d(res)}
return data
def getRoomList(user_id):
# 获取消息房间列表
filters = {UserRoomRelation.user_id == user_id}
data = dBSession.query(UserRoomRelation).order_by(
UserRoomRelation.updated_at.desc()).filter(*filters).all()
data = Base.formatBody(Utils.db_l_to_d(data))
return data
def getOne(self,
cls_: object,
filters: set,
order: str = 'id desc',
field: tuple = ()):
"""
获取一条
@param object cls_ 数据库模型实体类
@param set filters 查询条件
@param str order 排序
@param tuple field 字段
@return dict
"""
res = dBSession.query(cls_).filter(*filters)
orderArr = order.split(' ')
if orderArr[1] == 'desc':
res = res.order_by(desc(orderArr[0])).first()
else:
res = res.order_by(asc(orderArr[0])).first()
if res == None:
return None
if not field:
res = res.to_dict()
else:
res = res.to_dict(only=field)
return res
def getAdddressBookByFocusedUserId(focused_user_id, be_focused_user_id):
filters = {
AddressBook.focused_user_id == focused_user_id,
AddressBook.be_focused_user_id == be_focused_user_id
}
data = dBSession.query(AddressBook).filter(*filters).first()
return data
def delete(self, cls_:object, filters:set)->int:
"""
删除
@param object cls_ 数据库模型实体类
@param set filters 条件
@return int
"""
return dBSession.query(cls_).filter(*filters).delete(synchronize_session=False)
def getAll(self, filters, order, field = (), limit = 0):
if not filters:
res = dBSession.query(Config)
else:
res = dBSession.query(Config).filter(*filters)
if limit != 0:
res = res.limit(limit)
order = order.split(' ')
if order[1] == 'desc':
res = res.order_by(desc(order[0])).all()
else:
res = res.order_by(asc(order[0])).all()
if not field:
res = [c.to_dict() for c in res]
else:
res = [c.to_dict(only=field) for c in res]
return res
def edit(self, cls_:object, data:dict, filters:set)->bool:
"""
修改
@param object cls_ 数据库模型实体类
@param dict data 数据
@param set filters 条件
@return bool
"""
return dBSession.query(cls_).filter(*filters).update(data, synchronize_session=False)
def getList(self, filters, order, field=(), offset=0, limit=15):
res = {}
res['page'] = {}
res['page']['count'] = dBSession.query(UserRoomRelation).filter(
*filters).count()
res['list'] = []
res['page']['total_page'] = self.get_page_number(
res['page']['count'], limit)
res['page']['current_page'] = offset
if offset != 0:
offset = (offset - 1) * limit
if res['page']['count'] > 0:
res['list'] = dBSession.query(UserRoomRelation).filter(*filters)
res['list'] = res['list'].order_by(order).offset(offset).limit(
limit).all()
if not field:
res['list'] = [c.to_dict() for c in res['list']]
else:
res['list'] = [c.to_dict(only=field) for c in res['list']]
return res
def getOne(self, filters, order = 'id desc', field = ()):
res = dBSession.query(Config).filter(*filters)
order = order.split(' ')
if order[1] == 'desc':
res = res.order_by(desc(order[0])).first()
else:
res = res.order_by(asc(order[0])).first()
if res == None:
return None
if not field:
res = res.to_dict()
else:
res = res.to_dict(only=field)
return res
def getCount(self, filters, field = None):
if field == None:
return dBSession.query(Config).filter(*filters).count()
else:
return dBSession.query(Config).filter(*filters).count(field)
def delete(self, filters):
dBSession.query(Config).filter(*filters).delete(synchronize_session=False)
return True
def edit(self, data, filters):
dBSession.query(Config).filter(*filters).update(data, synchronize_session=False)
return True
def leftJoin():
data = dBSession.query(Suggest).outerjoin(
Users, Users.id == Suggest.users_id).all()
data_msg = Utils.db_l_to_d(data)
return data_msg
def on_to_many():
data = dBSession.query(Suggest).filter(
Users.id == Suggest.users_id).all()
# all方法返回列表要进行处理才能调用sqlalchemy_serializer的to_dict方法
data_msg = Utils.db_l_to_d(data)
return data_msg
def rawGetList(page_no, per_page, filters):
dataObj = dBSession.query(AddressBook).order_by(
AddressBook.created_at.desc()).filter(*filters).all()
data = {"addressBookList": Utils.db_l_to_d(dataObj)}
return data
def get(room_uuid):
return dBSession.query(AddressBook).filter(
AddressBook.room_uuid == room_uuid).all()
def filters(message):
filter = {Room.focused_user_id == message['focused_user_id']}
return dBSession.query(Room).filter(*filter).first()
def delete(self, filters):
dBSession.query(AddressBook).filter(*filters).delete(
synchronize_session=False)
return True
def rawGetList(page_no, per_page, filters):
dataObj = dBSession.query(AddressBook).order_by(AddressBook.created_at.desc()).filter(*filters).all()
addressBookList = Utils.db_l_to_d(dataObj)
data = Base.formatBody(
{"addressBookList": addressBookList})
return data