def get_house_list():
"""
搜索房屋的信息
:return:
"""
aid = request.args.get("aid") # 城区id
# new：最新上线 booking：入住最多 price-inc： 价格低->高 price-des：价格高->低
sort_key = request.args.get("sk") # 排序方式,默认按照最新上线排序
page = request.args.get("p", 1) # 页码
sd = request.args.get("sd") # 搜索起始时间
ed = request.args.get("ed") # 搜索结束时间
start_date = None
end_date = None
try:
if aid:
aid = int(aid)
page = int(page)
# 处理搜索时间
if sd:
start_date = datetime.strptime(sd, "%Y-%m-%d")
if ed:
end_date = datetime.strptime(ed, "%Y-%m-%d")
if start_date and end_date:
assert start_date < end_date, Exception("搜索起始时间大于结束时间")
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="参数错误")
# 尝试从缓存中获取搜索结果
try:
key = "%s:%s:%s:%s" % (aid, sd, ed, sort_key)
res_str = redis_store.hget(key, page)
if res_str:
return jsonify(errno=RET.OK, errmsg="OK", data=json.loads(res_str))
except Exception as e:
current_app.logger.error(e)
# 获取所有房屋的信息
try:
houses_query = House.query
# 根据城区id对房屋信息进行过滤
if aid:
houses_query = houses_query.filter(
House.area_id == aid) # BaseQuery
# 排除和搜索时间冲突的房屋信息
try:
conflict_orders_li = []
if start_date and end_date:
conflict_orders_li = Order.query.filter(
end_date > Order.begin_date,
start_date < Order.end_date).all()
elif start_date:
conflict_orders_li = Order.query.filter(
start_date < Order.end_date).all()
elif end_date:
conflict_orders_li = Order.query.filter(
end_date > Order.begin_date).all()
if conflict_orders_li:
# 获取和搜索时间冲突房屋id列表
conflict_houses_id = [
order.house_id for order in conflict_orders_li
]
# 排除冲突房屋信息
houses_query = houses_query.filter(
House.id.notin_(conflict_houses_id))
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="排除冲突房屋信息失败")
# 对查询结果排序
if sort_key == "booking":
# 按照房屋订单数量排序
houses_query = houses_query.order_by(House.order_count.desc())
elif sort_key == "price-inc":
# 价格低->高
houses_query = houses_query.order_by(House.price)
elif sort_key == "price-des":
# 价格高->低
houses_query = houses_query.order_by(House.price.desc())
else:
# 按照最新上线进行排序
houses_query = houses_query.order_by(House.create_time.desc())
# 进行分页操作
paginate = houses_query.paginate(page,
constants.HOUSE_LIST_PAGE_CAPACITY,
False)
# 获取搜索结果
houses = paginate.items
total_page = paginate.pages # 分页之后总页数
current_page = paginate.page # 当前页码
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="获取房屋信息失败")
# 组织数据，返回应答
house_dict_li = []
for house in houses:
house_dict_li.append(house.to_basic_dict())
resp = {
"houses": house_dict_li,
"total_page": total_page,
"current_page": current_page,
}
# 在redis中缓存搜索结果
try:
key = "%s:%s:%s:%s" % (aid, sd, ed, sort_key)
# 创建一个redis管道对象
pipeline = redis_store.pipeline()
# 开启redis事务
pipeline.multi()
# 向管道中添加命令
pipeline.hset(key, page, json.dumps(resp))
pipeline.expire(key, constants.HOUSE_LIST_REDIS_EXPIRES)
# 执行事务
pipeline.execute()
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.OK, errmsg="OK", data=resp)
def get_house_list():
'''获取房屋的列表信息，及按条件搜索排序'''
start_date = request.args.get('sd', '')
end_date = request.args.get('ed', '')
area_id = request.args.get('aid', '')
sort_key = request.args.get('sk', 'new')
page = request.args.get('p')
# 校验时间参数格式
try:
if start_date:
start_date = datetime.strptime(start_date, '%Y-%m-%d')
if end_date:
end_date = datetime.strptime(end_date, '%Y-%m-%d')
if all([start_date, end_date]):
assert start_date <= end_date
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="时间格式错误")
# 判断区域编号
if area_id:
try:
area = Area.query.get(area_id)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="区域信息错误")
# 处理页数
try:
page = int(page)
except Exception as e:
current_app.logger.error(e)
page = 1
# 设置缓存
redis_key = 'house_list_{}_{}_{}_{}'.format(start_date, end_date, area_id,
sort_key)
try:
resp_json = redis_store.hget(redis_key, page).decode()
except Exception as e:
current_app.logger.error(e)
else:
if resp_json:
print('hit redis list')
return resp_json, 200, {'Content-Type': 'application/json'}
# 查询数据库
# 过滤条件的参数容器
filter_params = list()
# 时间条件
conflict_orders = list()
# 查询冲突订单，提取冲突订单中的房屋id
# 此处，直接消除条件即设置为无限
# ①当结束时间不存在时，可设置结束时间为起始时间同一天，也可设置为无限(查询只对订单的结束时间做出了限制，无论何时起始都冲突)
# ②当起始时间不存在时，可以设置起始时间为今天，也可设置为无限(即只对订单的起始时间做出限制，无论何时结束都冲突)
if start_date or end_date:
if not start_date:
start_date = datetime.now()
if not end_date:
end_date = start_date
try:
conflict_orders = Order.query.filter(
Order.begin_date <= end_date,
Order.end_date >= start_date).all()
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据出错")
if conflict_orders:
conflict_house_ids = [order.house_id for order in conflict_orders]
if conflict_house_ids:
filter_params.append(House.id.notin_(conflict_house_ids))
# 区域条件
if area_id:
filter_params.append(House.area_id == area_id)
# 排序条件
if sort_key == 'new':
order_by_param = House.create_time.desc()
elif sort_key == 'booking':
order_by_param = House.order_count.desc()
elif sort_key == 'price-inc':
order_by_param = House.price.asc()
elif sort_key == 'price-des':
order_by_param = House.price.desc()
else:
order_by_param = House.create_time.desc()
# 查询数据库
house_query = House.query.filter(*filter_params).order_by(order_by_param)
try:
page_obj = house_query.paginate(
page=page,
per_page=constants.HOUSE_LIST_PER_PAGE_CAPACITY,
error_out=False)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据出错")
# 组织页面数据
houses = list()
houses_li = page_obj.items
for house in houses_li:
houses.append(house.to_basic_dict())
total_page = page_obj.pages
resp_dict = dict(errno=RET.OK,
errmsg='OK',
data={
'total_page': total_page,
'houses': houses,
'current_page': page
})
resp_json = json.dumps(resp_dict)
# 设置缓存
if page <= total_page:
redis_key = 'house_list_{}_{}_{}_{}'.format(start_date, end_date,
area_id, sort_key)
try:
redis_store.hset(redis_key, page, resp_json)
redis_store.expire(redis_key,
constants.HOUSE_LIST_PAGE_REDIS_EXPIRE)
except Exception as e:
current_app.logger.error(e)
return resp_json, 200, {'Content-Type': 'application/json'}
def get_houses_list():
"""获取房屋信息列表页(搜索页)"""
# 获取参数
start_date = request.args.get("sd", "") # 开始日期
end_date = request.args.get("ed", "") # 结束日期
area_id = request.args.get("aid", "") # 区域编号
sorted_key = request.args.get("sk", "new") # 排序方式, 默认以new方式排序
page = request.args.get("page") # 页数
# 转换成日期形式，并判断是否合法
try:
if start_date:
start_date = datetime.strptime("%Y-%m-%d")
if end_date:
end_date = datetime.strptime("%Y-%m-%d")
if start_date and end_date:
# 当两个日期都存在时，判断开始日期是否小于结束日期
assert start_date <= end_date
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="日期参数错误")
# 判断区域编号是否存在
if area_id:
try:
area = Area.query.get(area_id)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="区域参数错误")
if not area:
return jsonify(errno=RET.NODATA, errmsg="区域不存在")
# 判断页数是否是数字
try:
page = int(page)
except Exception as e:
current_app.logger.error(e)
page = 1 # 默认设置为第一页
# 先再redis中查询缓存数据
redis_hash_key = "house_index_%s_%s_%s_%s" % (start_date, end_date,
area_id, sorted_key)
try:
resp_json = redis_store.hget(redis_hash_key, page)
except Exception as e:
current_app.logger.error(e)
else:
if resp_json:
return resp_json, 200, {"Content-Type": "application/json"}
# 由于查询的参数多，并且可能有的有，有的没有，所以使用*拆包的方法来做
filter_params = [] # 存放过滤条件的列表
# 填充该列表
# 时间条件
conflict_orders = None
try:
if start_date and end_date:
# 查询冲突的订单
conflict_orders = Order.query.filter(
Order.begin_date <= end_date,
Order.end_date >= start_date).all() # 在时间上冲突的订单
elif start_date:
conflict_orders = Order.query.filter(
Order.end_date >= start_date).all() # 在时间上冲突的订单
elif end_date:
conflict_orders = Order.query.filter(
Order.begin_date <= end_date).all()
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
if conflict_orders:
# 获取这些冲突订单的房屋id
conflict_orders_house_ids = [
order.house_id for order in conflict_orders
]
if conflict_orders_house_ids:
# 可能没有订单冲突,有冲突房屋id时在添加到过滤条件
filter_params.append(
House.id.notin_(conflict_orders_house_ids)
) # 条件是：找到不存在时间冲突的房屋id(如果仅按时间过滤，那么会少掉那些没有过订单的房屋)
# 区域条件
if area_id:
# ==号实际上是调用对象的__eq__()方法，只不过sqlalchemy重写了这个方法，使得返回值不是True或者False
# [<sqlalchemy.sql.elements.BinaryExpression object at 0x0000023E1BC40278>]
filter_params.append(House.area_id == area_id)
# 查询数据库，在按排序关键字sk排序
if sorted_key == "booking": # 订单量 (入住最多)
house_query = House.query.filter(*filter_params).order_by(
House.order_count.desc()) # 此时还没有查询数据，house_query只是sql语句
elif sorted_key == "price-inc": # 价格升序
house_query = House.query.filter(*filter_params).order_by(
House.price.asc())
elif sorted_key == "price-des": # 价格降序
house_query = House.query.filter(*filter_params).order_by(
House.price.desc())
else:
house_query = House.query.filter(*filter_params).order_by(
House.create_time.desc()) # 默认按新旧顺序
# 处理分页 参数：当前页数, 每页的数据量, 出错时输出404
# page_obj是一个分页对象 <flask_sqlalchemy.Pagination object at 0x000001E63F160E48>
try:
page_obj = house_query.paginate(
page=page,
per_page=constants.HOUSE_LIST_PAGE_CAPACITY,
error_out=False)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
# 获取数据
houses = []
houses_list = page_obj.items # 此时才真正查询数据库，获取到列表形式的数据 [<House 1>]
for house in houses_list:
houses.append(house.to_dict())
# 获取总页数
total_pages = page_obj.pages
# 将每种条件组合成的查询结果保存为缓存记录
resp_dict = dict(errno=RET.OK,
errmsg="OK",
data={
"total_pages": total_pages,
"houses": houses,
"current_page": page
})
resp_json = json.dumps(resp_dict)
if page <= total_pages:
# 保存到redis中，使用hash类型
redis_hash_key = "house_index_%s_%s_%s_%s" % (start_date, end_date,
area_id, sorted_key)
try:
# redis_store.hset(redis_hash_key, page, resp_json)
# 设置有效期
# redis_store.expire(redis_hash_key, constants.HOUSE_LIST_PAGE_CACHE_EXPIRES)
# 上面两条语句可能成功一条失败一条，所以采用pipeline来执行这两条命令
pipeline = redis_store.pipeline() # 创建pipeline管道对象,可以一次执行多条语句
pipeline.multi() # 开启多个语句
pipeline.hset(redis_hash_key, page, resp_json)
pipeline.expire(redis_hash_key,
constants.HOUSE_LIST_PAGE_CACHE_EXPIRES)
pipeline.execute() # 执行语句
except Exception as e:
current_app.logger.error(e)
# 返回
return resp_json, 200, {"Content-Type": "application/json"}
def get_houses():
'''搜索房屋'''
area_id = request.args.get('aid', '') # 区域id
start_date_str = request.args.get('sd', '') # 开始时间
end_date_str = request.args.get('ed', '') # 结束时间
sort_key = request.args.get('sk', 'new') # 排序
page = request.args.get('p', '') # 页码
# 判断时间
try:
start_date = None
if start_date_str:
start_date = datetime.strftime(start_date_str, "%Y-%m-%d")
end_date = None
if end_date_str:
end_date = datetime.strftime(end_date_str, "%Y-%m-%d")
# 判断时间不为空的前提下,开始时间不能大于结束时间
if start_date and end_date:
assert start_date > end_date
except Exception as e:
logging.error(e)
return jsonify(errno=RET.DATAEXIST, errmsg='时间格式错误')
# 判断页码
try:
page = int(page)
except Exception as e:
logging.error(e)
page = 1
try:
redis_key = "houses_%s_%s_%s_%s" % (start_date_str, end_date_str,
area_id, sort_key)
resp_json = redis_store.hget(redis_key, page)
except Exception as e:
logging.error(e)
resp_json = None
if resp_json:
return '{"errno":0,"errmsg":"ok", "data":%s}' % resp_json
# 查询城区信息
filter_params = []
if area_id:
# SQLAllchemy对'=='重写了__eq__函数, 不会返回True或False的信息
filter_params.append(House.area_id == area_id)
# 4. 处理时间, 需要获取不冲突的房屋信息 参数可能全填, 也可能不全
# 4.1 找到冲突的订单(order_list = [XXX, XXX, XXX])
try:
conflict_orders_li = []
if start_date and end_date:
# 都有入住时间和离开时间情况
conflict_orders_li = Order.query.filter(
Order.begin_date >= end_date,
Order.end_date <= start_date).all()
elif start_date:
# 查询只有入住时间的情况
conflict_orders_li = Order.query.filter(
Order.end_date <= start_date).all()
elif end_date:
# 查询只有离开时间的情况
conflict_orders_li = Order.query.filter(
Order.begin_date >= end_date).all()
except Exception as e:
logging.error(e)
return jsonify(errno=RET.DBERR, errmsg='查询数据错误')
if conflict_orders_li:
# 当不冲突的房屋信息不为空
# 添加不冲突的房屋查询条件
filter_params.append(House.id.in_(conflict_orders_li))
# 下一步处理排序
# 5.处理排序filter().order_by(new / price desc)
# "new">最新上线 / "booking">入住最多 / "price-inc">价格 低-高 / "price-des">价格 高-低
if sort_key == 'booking':
# 安入住最多查询
house_query = House.query.filter(*filter_params).order_by(
House.order_count.desc())
elif sort_key == 'price-inc':
# 按照价格从低到高,生序
house_query = House.query.filter(*filter_params).order_by(House.price)
elif sort_key == 'price-des':
# 按照价格从高到低,降序
house_query = House.query.filter(*filter_params).order_by(
House.price.desc())
else:
# 剩下最后的就是最新上线的
house_query = House.query.filter(*filter_params).order_by(
House.create_time.desc())
# 6.分页处理
try:
# paginate: 是查询执行器 和all(), first(), count)
# 页数 每页数量 错误输出
house_data = house_query.paginate(page, HOUSE_LIST_PAGE_CAPACITY,
False)
except Exception as e:
logging.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
if not house_data:
return jsonify
# 获取分页后的数据
# 7. 将数据转换JSON格式的字符串
house_li = house_data.items # 获取当前页对应的数据
total_page = house_data.pages # 获取总页码
houses = []
for house in house_li:
houses.append(house.to_base_dict())
resp = dict(errno=RET.OK,
errmsg="查询成功",
data={
"houses": houses,
"total_page": total_page,
"current_page": page
})
resp_json = json.dumps(resp)
# 8.缓存房屋信息
if page <= total_page:
# 页码不能超出分页后的总页码数
# 用redis的哈希类型保存分页数据
redis_key = "houses_%s_%s_%s_%s" % (start_date_str, end_date_str,
area_id, sort_key)
try:
# 使用redis的事务保存数据
pipeline = redis_store.pipeline()
# 开启事务
pipeline.multi()
# 保存哈希数据, 并设置有效期
pipeline.hset(redis_key, page, resp_json)
pipeline.expire(redis_key, HOUSE_LIST_PAGE_REDIS_EXPIRES)
# 执行事务
pipeline.execute()
except Exception as e:
logging.error(e)
# redis的缓存如果没有设置成功, 也可以不用返回错误信息. 下一次的请求再去设置也行
# pipeline: 失败会自动回滚
# 四. 数据返回
return resp_json
def get_house_list():
# 1. 获取参数
start_date = request.args.get("sd", "")
end_date = request.args.get("ed", "")
area_id = request.args.get("aid", "")
sort_key = request.args.get("sk", "new")
page = request.args.get("page", "")
# 2. 校验参数
# 2.1 校验时间
try:
if start_date:
start_date = datetime.strptime(start_date, "%Y-%m-%d")
if end_date:
end_date = datetime.strptime(end_date, "%Y-%m-%d")
if start_date and end_date:
assert start_date <= end_date
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg='日期参数有误')
# 2.2 校验区域ID
if area_id:
try:
area = Area.query.get(area_id)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg='区域参数有误')
# 2.3 校验页码
try:
page = int(page)
except Exception as e:
current_app.logger.error(e)
page = 1
# 尝试获取redis缓存
redis_key = "house_%s_%s_%s_%s" % (start_date, end_date, area_id, sort_key)
try:
ret_json = redis_store.hget(redis_key, page)
except Exception as e:
current_app.logger.error(e)
else:
if ret_json:
return ret_json, 200, {"Content-Type": "application/json"}
# 3. 业务逻辑
# 3.1 创建一个用于查询数据库的过滤条件容器
filter_params = []
# 3.2 填充过滤参数
# 3.2.1 时间条件
conflict_order = None # 防止没有冲突订单带来的查询错误
try:
if start_date and end_date:
# 如果有起始和结束时间的冲突订单
conflict_order = Order.query.filter(
Order.begin_date <= end_date,
Order.end_date >= start_date).all()
elif start_date:
# 如果只规定开始时间时的冲突订单
conflict_order = Order.query.filter(
Order.end_date >= start_data).all()
elif end_date:
# 如果只规定结束时间时的冲突订单
conflict_order = Order.query.filter(
Order.begin_date <= end_date).all()
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库查询失败")
if conflict_order: # 如果存在冲突订单
# 获取冲突订单的房屋id
conflict_house_ids = [order.house_id for order in conflict_order]
# 如果冲突的房屋id不为空，则添加过滤条件
if conflict_house_ids:
filter_params.append(House.id.notin_(conflict_house_ids))
# 3.2.2 区域条件
if area_id:
filter_params.append(House.area_id == area_id)
# 3.3 补充排序条件（此时还没有开始查询数据库）
if sort_key == "booking":
house_query = House.query.filter(*filter_params).order_by(
House.order_count.desc())
elif sort_key == "price-inc":
house_query = House.query.filter(*filter_params).order_by(House.price)
elif sort_key == "price-des":
house_query = House.query.filter(*filter_params).order_by(
House.price.desc())
else:
house_query = House.query.filter(*filter_params).order_by(
House.create_time.desc())
# 3.4 处理分页
# paginate()的参数： page：当前页码， per_page:每页数目， error_out:自动的错误输出
try:
page_obj = house_query.paginate(page=page,
per_page=HOUSE_LIST_PAGE_CAPACITY,
error_out=False)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg='数据库查询失败')
# page_obj.items : 表示当前页的数据
# page_obj.pages : 表示总页数
total_page = page_obj.pages
# 获取对应页面数据，转成字典存放到一个列表中
houses_li = []
for house in page_obj.items:
houses_li.append(house.to_basic_dict())
# 4. 返回应答
# 4.1 添加redis缓存，缓存格式为hash， house_起始_终止_区域id_排序 { 页码1 ： 数据1 ， 页码2 ： 数据2 ......}
# 先把数据转成字典，在转成字符串
ret_dict = dict(errno=RET.OK,
errmsg='OK',
data={
"total_page": total_page,
"houses": houses_li,
"current_page": page
})
ret_json = json.dumps(ret_dict)
# 设置redis缓存
if page <= total_page:
redis_key = "house_%s_%s_%s_%s" % (start_date, end_date, area_id,
sort_key)
try:
# redis_store.hset(redis_key, page, ret_json)
# redis_store.expire(redis_key, HOUSE_LIST_PAGE_REDIS_CACHE_EXPIRES)
# 创建管道对象，可以一次执行多个语句，可以解决多语句中的不完全成功的问题
pipeline = redis_store.pipeline()
# 开启多语句记录
pipeline.multi()
pipeline.hset(redis_key, page, ret_json)
pipeline.expire(redis_key, HOUSE_LIST_PAGE_REDIS_CACHE_EXPIRES)
# 执行语句
pipeline.execute()
except Exception as e:
current_app.logger.error(e)
# 4.2 返回应答
return ret_json, 200, {"Content-Type": "application/json"}
def get_house_list():
"""房屋列表信息（搜索页面）"""
start_date = request.args.get("sd", "") # 用户想要的起始时间
end_date = request.args.get("ed", "") # 用户想要的结束时间
area_id = request.args.get("aid", "") # 区域编号
sort_key = request.args.get("sk", "") # 排序关键字
page = request.args.get("p") # 页数
# 处理时间
try:
if start_date:
start_date = datetime.strptime(start_date, "%Y-%m-%d")
if end_date:
end_date = datetime.strptime(end_date, "%Y-%m-%d")
if start_date and end_date:
assert start_date <= end_date
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="日期参数错误")
# 判断area_id
if area_id:
try:
area = Area.query.get(area_id)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="查询区域信息数据库异常")
else:
if not area:
return jsonify(errno=RET.PARAMERR, errmsg="区域参数错误")
# 判断page
try:
page = int(page)
except Exception as e:
current_app.logger.error(e)
page = 1
redis_keys = "house_%s_%s_%s_%s" % (start_date, end_date, area_id,
sort_key)
# 从redis缓存获取数据
try:
resp_json = redis_store.hget(redis_keys, page)
except Exception as e:
current_app.logger.error(e)
else:
if resp_json:
current_app.logger.info("get house info from redis success")
return resp_json, 200, {"Content-Type": "application/json"}
# 过滤条件的参数列表容器
filter_params = []
# 时间条件
conflict_orders = None
try:
if start_date and end_date:
# 查询冲突订单: select * from Order where Order.begin_date <= end_date and Order.end_date >= start_date;
conflict_orders = Order.query.filter(
Order.begin_date <= end_date,
Order.end_date >= start_date).all()
elif start_date:
conflict_orders = Order.query.filter(
Order.end_date >= start_date).all()
elif end_date:
conflict_orders = Order.query.filter(
Order.begin_date <= end_date).all()
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
if conflict_orders:
# 从订单中获取冲突的房屋id
conflict_house_ids = [order.house_id for order in conflict_orders]
# 如果不为空，讲条件添加到条件查询列表
if conflict_house_ids:
filter_params.append(House.house_id.notin_(conflict_house_ids))
# 区域条件
if area_id:
filter_params.append(House.area_id == area_id)
# 查询数据库, 补充排序条件
if sort_key == "booking": # 订单量 入住最多
house_query = House.query.filter(*filter_params).order_by(
House.order_count.desc())
elif sort_key == "price-inc": # 价格由低到高
house_query = House.query.filter(*filter_params).order_by(
House.price.asc())
elif sort_key == "price-des": # 价格由高到低
house_query = House.query.filter(*filter_params).order_by(
House.price.desc())
else: # 新旧
house_query = House.query.filter(*filter_params).order_by(
House.create_time.desc())
# 处理分页
try:
# 当前页数 每页显示数据量 自动错误输出
paginate_obj = house_query.paginate(
page=page,
per_page=constants.HOUSE_LIST_PAGE_CAPACITY,
error_out=False)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
# 获取页面数据
page_houses = paginate_obj.items
houses = []
for house in page_houses:
houses.append(house.to_basic_dict())
# 获取总页数
total_page = paginate_obj.pages
# return jsonify(errno=RET.OK, errmsg="OK", data={"total_page": total_page, "current_page": page, "houses": houses})
resp_dict = dict(errno=RET.OK,
errmsg="OK",
data={
"total_page": total_page,
"current_page": page,
"houses": houses
})
resp_json = json.dumps(resp_dict, ensure_ascii=False)
# 保存到Redis缓存
if page <= total_page:
try:
# redis_store.hset(redis_keys, page, resp_json)
# redis_store.expire(redis_keys, constants.HOUSE_LIST_REDIS_EXPIRE)
# 创建管道对象，可以一次执行多个语句
pipe_line = redis_store.pipeline()
# 开启多个语句记录
pipe_line.multi()
pipe_line.hset(redis_keys, page, resp_json)
pipe_line.expire(redis_keys, constants.HOUSE_LIST_REDIS_EXPIRE)
# 执行
pipe_line.execute()
except Exception as e:
current_app.logger.error(e)
return resp_json, 200, {"Content-Type": "application/json"}
def get_house_list():
"""
获取房屋列表页
:return:
"""
start_date = request.args.get("sd")
end_date = request.args.get("ed")
area_id = request.args.get("aid")
sort_key = request.args.get("sk", "new")
page = request.args.get("p")
try:
if start_date:
start_date = datetime.strptime(start_date)
if end_date:
end_date = datetime.strptime(end_date)
if start_date and end_date:
assert end_date >= start_date
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="时间参数有误")
if area_id:
try:
area = Area.query.get(area_id)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="区域参数有误")
try:
page = int(page)
except Exception as e:
current_app.logger.error(e)
page = 1
redis_key = "house_%s_%s_%s_%s" % (start_date, end_date, area_id, sort_key)
try:
resp_json = redis_store.hget(redis_key, page)
except Exception as e:
current_app.logger.error(e)
else:
if resp_json:
return resp_json, 200, {"Content-Type": "application/json"}
filter_parms = []
confdlict_orders = None
try:
if start_date and end_date:
confdlict_orders = Order.query.filter(
Order.begin_date <= end_date,
Order.end_date >= start_date).all()
elif start_date:
confdlict_orders = Order.query.filter(
Order.end_date >= start_date).all()
elif end_date:
confdlict_orders = Order.query.filter(Order.begin_date <= end_date)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
if confdlict_orders:
# 从订单中获取冲突对房屋id
confdlict_house_ids = [order.house_id for order in confdlict_orders]
# 如果冲突对房屋id不为空，向查询参数中添加条件
if confdlict_house_ids:
filter_parms.append(House.id.notin_(confdlict_house_ids))
if area_id:
filter_parms.append(House.area_id == area_id)
if sort_key == "booking":
house_query = House.query.filter(*filter_parms).order_by(
House.order_count.desc())
elif sort_key == "price-inc":
house_query = House.query.filter(*filter_parms).order_by(
House.price.asc())
elif sort_key == "price-des":
house_query = House.query.filter(*filter_parms).order_by(
House.price.desc())
else:
house_query = House.query.filter(*filter_parms).order_by(
House.create_time.desc())
try:
page_obj = house_query.paginate(
page=page,
per_page=constants.HOUSE_LIST_PAGE_CAPACITY,
error_out=False)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
house_li = page_obj.items
houses = []
for house in house_li:
houses.append(house.to_basic_dict())
total_page = page_obj.pages
resp_dict = dict(errno=RET.OK,
errmsg="OK",
data={
"total_page": total_page,
"houses": houses,
"current_page": page
})
resp_json = json.dumps(resp_dict)
if page <= total_page:
redis_key = "house_%s_%s_%s_s%" % (start_date, end_date, area_id,
sort_key)
try:
pipeline = redis_store.pipeline()
pipeline.multi()
pipeline.hset(redis_key, page, resp_json)
pipeline.expire(redis_key,
constants.HOUSE_LIST_PAGE_REDIS_CACHE_EXPIRES)
except Exception as e:
current_app.logger.error(e)
return resp_json, 200, {"Content-type": "application/json"}
def get_house_list():
'''获取房屋的列表信息'''
start_date = request.args.get('sd', '') # 用户输入的起始时间
end_date = request.args.get('ed', '') # 用户输入的结束时间
area_id = request.args.get('aid', '') # 用户输入的区域id
sort_key = request.args.get('sk', 'new') # 用户输入的排序方式
page = request.args.get('p') # 查询的页数
# todo: 处理时间（复杂）
try:
if start_date:
start_date = datetime.strptime(start_date, "%Y-%m-%d")
if end_date:
end_date = datetime.strptime(end_date, "%Y-%m-%d")
if start_date and end_date:
# 断言
assert start_date <= end_date
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg='日期参数有误')
# 判断区域id
if area_id:
try:
area = Area.query.get(area_id)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="区域参数有误")
# 处理页数
try:
page = int(page)
except Exception as e:
current_app.logger.error(e)
page = 1
# todo:校验参数完成后，尝试获取缓存数据
redis_key = "house_%s_%s_%s_%s" % (start_date, end_date, area_id, sort_key)
try:
resp_json = redis_store.hget(redis_key, page)
except Exception as e:
current_app.logger.error(e)
else:
if resp_json:
current_app.logger.info('hit redis area_info')
return resp_json, 200, {'Content-Type': "application/json"}
# 过滤条件的参数列表容器
filter_params = []
# 填充过滤参数
# 时间条件
conflict_orders = None
try:
if start_date and end_date:
# 查询冲突的订单
conflict_orders = Order.query.filter(
Order.begin_date <= end_date,
Order.end_date >= start_date).all()
elif start_date:
conflict_orders = Order.query.filter(
Order.end_date >= start_date).all()
elif end_date:
conflict_orders = Order.query.filter(
Order.begin_date <= end_date).all()
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
if conflict_orders:
# li.append(House.area_id == 1)
# Out[11]: [ < sqlalchemy.sql.elements.BinaryExpression object at 0x0000000005D96E80 >]
#
# In[12]: House.area_id == 1
# Out[12]: < sqlalchemy.sql.elements.BinaryExpression object at 0x0000000005D97080 >
# 添加的是一个条件对象, 使用的默认__eq__方法 House.area_id.__eq__(1)
# 从订单中获取冲突的房屋id
# 遍历的简写，列表生成式
conflict_house_ids = [order.house_id for order in conflict_orders]
# 如果冲突的房屋id不为空，向查询参数中添加条件
if conflict_house_ids:
filter_params.append(House.id.notin_(conflict_house_ids))
# 区域条件
if area_id:
filter_params.append(House.area_id == area_id)
# 查询数据库
# 补充排序条件
if sort_key == "booking": # 入住做多
house_query = House.query.filter(*filter_params).order_by(
House.order_count.desc())
elif sort_key == "price-inc":
house_query = House.query.filter(*filter_params).order_by(
House.price.asc())
elif sort_key == "price-des":
house_query = House.query.filter(*filter_params).order_by(
House.price.desc())
else: # 新旧
house_query = House.query.filter(*filter_params).order_by(
House.create_time.desc())
try:
# 当前页数 每页数据量 自动的错误输出
page_obj = house_query.paginate(
page=page,
per_page=constants.HOUSE_LIST_PAGE_CAPACITY,
error_out=False)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg='数据库异常')
# 获取页面数据
house_li = page_obj.items
houses = []
for house in house_li:
houses.append(house.to_basic_dict())
# 获取总页数
total_page = page_obj.pages
# todo: 设置缓存
resp_dict = dict(errno=RET.OK,
errmsg='OK',
data={
"total_page": total_page,
"houses": houses,
"current_page": page
})
resp_json = json.dumps(resp_dict)
# 设置存储到redis中的数据格式
redis_key = "house_%s_%s_%s_%s" % (start_date, end_date, area_id, sort_key)
try:
# 使用哈希类型存储
# 使用普通的redis对象存储，如果第一条语句成功，第二条设置时间失败，则变成了永久有效了
# redis_store.hset(redis_key, page, resp_json)
# redis_store.expire(redis_key, constants.HOUES_LIST_PAGE_REDIS_CACHE_EXPIRES)
# 创建redis的管道对象，可以一次性执行多条语句命令，避免上面的问题
pipeline = redis_store.pipeline()
# 开启多个语句的记录
pipeline.multi()
pipeline.hset(redis_key, page, resp_json)
pipeline.expire(redis_key,
constants.HOUES_LIST_PAGE_REDIS_CACHE_EXPIRES)
# 执行语句
pipeline.execute()
except Exception as e:
current_app.logger.error(e)
return resp_json, 200, {"Content-Type": "application/json"}
def get_house_list():
"""获取房屋的列表信息（搜索页面）"""
start_date = request.args.get("sd", "") #用户想要的起始时间
end_date = request.args.get("ed", "") #用户想要的结束时间
area_id = request.args.get('aid', "") #区域编号
sort_key = request.args.get('sk', 'new') #排序关键字,如果没选默认就是new
page = request.args.get('p') #页数
print(sort_key)
#处理时间
try:
if start_date:
start_date = datetime.strptime(start_date, "%Y-%m-%d")
if end_date:
end_date = datetime.strptime(end_date, "%Y-%m-%d")
if start_date and end_date:
assert start_date <= end_date
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="日期参数有误")
#判断区域id
if area_id:
try:
area = Area.query.get(area_id)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="区域参数有误")
#处理页数
try:
page = int(page)
except Exception as e:
current_app.logger.error(e)
page = 1
#获取缓存数据
redis_key = "house_%s_%s_%s_%s" % (start_date, end_date, area_id, sort_key)
try:
resp_json = redis_store.hget(redis_key, page)
except Exception as e:
current_app.logger.error(e)
else:
if resp_json: #这里获取到的是可以直接发送回前端的数据，如果是自己要用就要decode转码
return resp_json, 200, {'Content-Type': 'application/json'}
#过滤条件的参数列表容器
filter_params = []
#填充过滤参数
#时间条件
conflict_orders = None
#查询冲突的订单，三种模式
#1、start和end都有 2、只有start 3、只有end
try:
if start_date and end_date:
conflict_orders = Order.query.filter(
Order.begin_date <= end_date,
Order.end_date >= start_date).all()
elif start_date:
conflict_orders = Order.query.filter(
Order.end_date >= start_date).all()
elif end_date:
conflict_orders = Order.query.filter(
Order.begin_date <= end_date).all()
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
if conflict_orders:
#从订单中获取冲突的房屋id
# 得到的是一个列表
conflict_house_ids = [order.house_id for order in conflict_orders]
#如果冲突的房屋id不为空，向查询参数中添加条件
if conflict_house_ids:
filter_params.append(House.id.notin_(conflict_house_ids))
#区域条件
if area_id:
#这样相当于里面有了[House.area_id==area_id,House.id.notin_(conflict_house_ids)]
filter_params.append(House.area_id == area_id)
#看一下到底append个啥玩意
print(filter_params)
#查询数据库
#补充排序条件
if sort_key == "booking": #入住最多
house_query = House.query.filter(*filter_params).order_by(
House.order_count.desc())
print("booking")
elif sort_key == 'price-inc': #价格低到高
print("price-inc")
house_query = House.query.filter(*filter_params).order_by(
House.price.asc())
elif sort_key == 'price-des': #价格高到低
print("price-des")
house_query = House.query.filter(*filter_params).order_by(
House.price.desc())
else: #最新
print("new")
house_query = House.query.filter(*filter_params).order_by(
House.update_time.desc())
#处理分页
try:
#page：当前页数、per_page：每页数据量、error_out：自动的错误输出
page_obj = house_query.paginate(
page=page,
per_page=constants.HOUSE_LIST_PAGE_CAPACITY,
error_out=False)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
#获取页面数据 page_obj只拿到了前两条
print('page_obj', page_obj)
# 分页后的一个属性，返回当前页的所有数据
house_li = page_obj.items
print('house_li', house_li)
houses = []
for house in house_li:
houses.append(to_basic_dict(house))
#获取总页数
total_page = page_obj.pages
resp_dict = dict(errno=RET.OK,
errmsg="OK",
data={
"total_page": total_page,
"houses": houses,
"current_page": page
})
resp_json = json.dumps(resp_dict)
if page <= total_page:
#设置缓存数据
redis_key = "house_%s_%s_%s_%s" % (start_date, end_date, area_id,
sort_key)
#哈希类型
try:
# redis_store.hset(redis_key, page, resp_json)
# redis_store.expire(redis_key, constants.HOUES_LIST_PAGE_REDIS_CACHE_EXPIRES)
# 创建redis管道对象，可以一次执行多个语句
pipeline = redis_store.pipeline()
#开启多个语句的记录
pipeline.multi()
pipeline.hset(redis_key, page, resp_json)
pipeline.expire(redis_key,
constants.HOUSE_LIST_PAGE_REDIS_CACHE_EXPIRES)
#执行语句
pipeline.execute()
except Exception as e:
current_app.logger.error(e)
return resp_json, 200, {'Content-Type': 'application/json'}
# redis_store
#
# "house_起始_结束_区域id_排序_页数"
# (errno=RET.OK, errmsg="OK", data={"total_page": total_page, "houses": houses, "current_page": page})
#
#
#
# "house_起始_结束_区域id_排序": hash
# {
# "1": "{}",
# "2": "{}",
# }
def get_house_list():
"""获取房屋列表信息"""
# 接收参数
start_date = request.args.get("sd", "")
end_date = request.args.get("ed", "")
area_id = request.args.get("aid", "")
sort_key = request.args.get("sk", "new")
page = request.args.get("p")
# 校验参数
try:
# 判断用户是否填写入住日期
if start_date:
# strptime 是 字符串装换成时间
# strftime 是 时间转换成字符串
start_date = datetime.strptime(start_date, "%Y-%m-%d")
# 判断用户是否填写结束日期
if end_date:
end_date = datetime.strptime(end_date, "%Y-%m-%d")
# 判断入住日期是否小于结束日期
if start_date and end_date:
assert start_date <= end_date
except Exception as e:
current_app.logger.error(e)
return jsonify(errnum=RET.PARAMERR, errmsg=u"日期参数错误")
# 判断城区id是否存在
if area_id:
try:
area = Area.query.get(area_id)
except Exception as e:
current_app.logger.error(e)
return jsonify(errnum=RET.DBERR, errmsg=u"数据库异常")
if area is None:
return jsonify(errnum=RET.NODATA, errmsg=u"城区信息不存在")
# 判断是否传递页码
try:
page = int(page)
except Exception as e:
current_app.logger.error(e)
page = 1
# 创建redis的缓存的哈希值的键
redis_key = "house_%s_%s_%s_%s" % (start_date, end_date, area_id, sort_key)
try:
resp_json = redis_store.hget(redis_key, page)
except Exception as e:
current_app.logger.error(e)
else:
if resp_json:
return resp_json, 200, {"Content-Type": "application/json"}
# 业务处理
# 查询冲突的房子, 因为这里我们不等用订单来查询, 这样的话会忽略那些没下过订单的房子
# 当用户传入了 入住日期 和 结束日期
try:
if start_date and end_date:
orders = Order.query.filter(Order.begin_date <= end_date,
Order.end_date >= start_date).all()
# 当用户只传入 入住日期
elif start_date:
orders = Order.query.filter(Order.end_date >= start_date).all()
# 当用户只传入 结束日期
elif end_date:
orders = Order.query.filter(Order.begin_date <= end_date).all()
else:
orders = None
except Exception as e:
current_app.logger.error(e)
return jsonify(errnum=RET.DBERR, errmsg=u"获取订单信息失败")
# 根据订单对象, 提取出房屋id
if orders:
house_ids = [order.house_id for order in orders]
else:
house_ids = None
# 条件参数列表容器
condition_params = []
if house_ids:
condition_params.append(House.id.notin_(house_ids))
if area_id:
condition_params.append(House.area_id == area_id)
# 查询数据库
if sort_key == "booking": # 入住最多
house_query = House.query.filter(*condition_params).order_by(
House.order_count.desc())
elif sort_key == "price-inc": # 价钱低到高
house_query = House.query.filter(*condition_params).order_by(
House.price.asc())
elif sort_key == "price-des": # 价钱高到低
house_query = House.query.filter(*condition_params).order_by(
House.price.desc())
else:
house_query = House.query.filter(*condition_params).order_by(
House.create_time.desc())
# 获取分页对象
try:
# 档期内页码 每页展示的数据 自动错误警告
page_obj = house_query.paginate(page=page,
per_page=HOUSE_LIST_PAGE_DATE_BARS,
error_out=False)
except Exception as e:
current_app.logger.error(e)
return jsonify(errnum=RET.DBERR, errmsg=u"分页出错")
# 房子列表容器
house_li = []
# 获取当前页的数据
houses = page_obj.items
for house in houses:
house_li.append(house.to_basic_dict())
# 获取总页数
total_page = page_obj.pages
# 返回应答
# 返回当前页的数据
# return jsonify(errnum=RET.OK, errmsg=u"ok", data={"total_page": total_page, "houses": house_li, "current_page": page})
# 将返回的数据转换成字典
resp_dict = dict(errnum=RET.OK,
errmsg=u"ok",
data={
"total_page": total_page,
"houses": house_li,
"current_page": page
})
# 将字典数据转换成json数据
resp_json = json.dumps(resp_dict)
# 创建redis缓存中的键
redis_key = "house_%s_%s_%s_%s" % (start_date, end_date, area_id, sort_key)
# 用户的输入页码大于总页数时不做缓存
if page < total_page:
# 使用redis缓存, 我们这里用哈希, 由于一次执行多条redis语句, 防止语句变成永久有效, 我们这里用到pipeline
try:
# 创建redis管道, 管道的用法和redis的用法一样
pipeline = redis_store.pipeline()
# 开启一次执行多条语句的任务
pipeline.multi()
pipeline.hset(redis_key, page, resp_json)
pipeline.expire(redis_key, HOUSE_LIST_PAGE_REDIS_CACHE_EXPIRES)
# 执行语句
pipeline.execute()
except Exception as e:
current_app.logger.error(e)
return resp_json, 200, {"Content-Type": "application/json"}
def get_house_list():
'''获取房屋的列表信息（搜索页面）'''
start_date = request.args.get("sd", "")
end_date = request.args.get("ed", "")
area_id = request.args.get("aid", "")
sort_key = request.args.get("sk", "new")
page = request.args.get("p")
try:
if start_date:
start_date = datetime.strptime(start_date, "%Y-%m-%d")
if end_date:
end_date = datetime.strptime(end_date, "%Y-%m-%d")
if start_date and end_date:
assert start_date <= end_date
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="日期参数有误")
if area_id:
try:
area = Area.query.get(area_id)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="区域参数有误")
try:
page = int(page)
except Exception as e:
current_app.logger.error(e)
page = 1
redis_key = f"house_{start_date}_{end_date}_{area_id}_{sort_key}"
try:
resp_json = redis_store.hget(redis_key, page)
except Exception as e:
current_app.logger.error(e)
else:
if resp_json:
resp_json = resp_json.decode()
return resp_json, 200, {"Content-Type": "application/json"}
filter_params = []
conflict_orders = None
# select * from order where order.begin_date<=end_date and order.end_date >= start_date
try:
order_status_li = Order.order_status_li
if start_date and end_date:
conflict_orders = Order.query.filter(
Order.begin_date <= end_date, Order.end_date >= start_date,
Order.status.notin_(order_status_li)).all()
elif start_date:
conflict_orders = Order.query.filter(
Order.end_date >= start_date,
Order.status.notin_(order_status_li)).all()
elif end_date:
conflict_orders = Order.query.filter(
Order.begin_date <= end_date,
Order.status.notin_(order_status_li)).all()
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
if conflict_orders:
conflict_house_ids = [order.house_id for order in conflict_orders]
filter_params.append(House.id.notin_(conflict_house_ids))
if area_id:
filter_params.append(House.area_id == area_id)
if sort_key == "booking":
house_query = House.query.filter(*filter_params).order_by(
House.order_count.desc())
elif sort_key == "price-inc":
house_query = House.query.filter(*filter_params).order_by(
House.price.asc())
elif sort_key == "price-des":
house_query = House.query.filter(*filter_params).order_by(
House.price.desc())
else:
house_query = House.query.filter(*filter_params).order_by(
House.create_time.desc())
try:
page_obj = house_query.paginate(
page=page,
per_page=constants.HOUSE_LIST_PAGE_CAPACITY,
error_out=False)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
house_li = page_obj.items
houses = []
for house in house_li:
houses.append(house.to_basic_dict())
total_page = page_obj.pages
resp_dict = dict(errno=RET.OK,
errmsg="OK",
data={
"total_page": total_page,
"houses": houses,
"current_page": page
})
resp_json = json.dumps(resp_dict)
if page <= total_page:
redis_key = f"house_{start_date}_{end_date}_{area_id}_{sort_key}"
try:
pipeline = redis_store.pipeline()
pipeline.multi()
pipeline.hset(redis_key, page, resp_json)
pipeline.expire(redis_key,
constants.HOUES_LIST_PAGE_REDIS_CACHE_EXPIRES)
pipeline.execute()
except Exception as e:
current_app.logger.error(e)
return resp_json, 200, {"Content-Type": "application/json"}
def get_house_list():
"""获取房屋的列表信息(搜索页面）"""
# 获取参数
start_date = request.args.get("sd")
end_date = request.args.get("ed")
area_id = request.args.get("aid")
sort_key = request.args.get("sk", "new")
page = request.args.get("p")
# 处理时间
try:
if start_date:
start_date = datetime.strptime(start_date, "%Y-%m-%d")
if end_date:
end_date = datetime.strptime(end_date, "%Y-%m-%d")
if start_date and end_date:
assert start_date <= end_date
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="日期参数有误")
# 判断区域ID
if area_id:
try:
area = Area.query.get(area_id)
except Exception as e:
return jsonify(errno=RET.PARAMERR, errmsg="区域信息有误")
# 排序关键字， 无关紧要，可以通过默认设置进行
# 处理页数
try:
page = int(page)
except Exception as e:
current_app.logger.error(e)
page = 1
# 首先尝试获取缓存数据
redis_key = "house_%s_%s_%s_%s" % (start_date, end_date, area_id, sort_key)
try:
resp_json = redis_store.hget(redis_key, page)
except Exception as e:
current_app.logger.error(e)
else:
if resp_json:
return resp_json, 200, {"Content-Type": "application/json"}
# 查询数据库
# 过滤条件的参数容器
filter_params = []
# 填充过滤参数
# > 处理时间条件
conflict_orders = None
try:
if start_date and end_date:
# 查询冲突的订单
conflict_orders = Order.query.filter(Order.begin_date <= end_date, Order.end_data >= start_date).all()
elif start_date:
conflict_orders = Order.query.filter(Order.end_data >= start_date).all()
elif end_date:
conflict_orders = Order.query.filter(Order.begin_date <= end_date).all()
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
if conflict_orders:
# 从订单中获取冲突的房屋ID
conflict_house_ids = [conflict_order.house_id for conflict_order in conflict_orders]
# 如果冲突的房屋id不为空，向查询参数中添加条件
if conflict_orders:
filter_params.append(House.id.notin_(conflict_house_ids))
# > 处理区域条件
if area_id:
filter_params.append(House.area_id == area_id)
# 查询数据库
# > 补充排序条件
if sort_key == "booking":
house_query = House.query.filter(*filter_params).order_by(House.order_count.desc())
elif sort_key == "price-inc":
house_query = House.query.filter(*filter_params).order_by(House.priceq.asc())
elif sort_key == "price-des":
house_query = House.query.filter(*filter_params).order_by(House.priceq.desc())
else: # 默认按照新旧排序
house_query = House.query.filter(*filter_params).order_by(House.create_time.desc())
# 处理分页条件
try:
# paginate() params: 当前页数，每页数量，默认错误输出方法
page_obj = house_query.paginate(page=page, per_page=constants.HOUSE_LIST_PAGE_CAPACITY, error_out=False)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
# 获取页面数据 page_obj.itmes
houses = []
for house in page_obj.items:
houses.append(house.to_basic_dict())
# 获取总页数
total_pages = page_obj.pages
resp_dict = dict(errno=RET.OK, errmsg="OK", data={"total_page": total_pages, "houses": houses, "current_page": page})
resp_json = json.dumps(resp_dict)
if page <= total_pages:
# 设置缓存
redis_key = "house_%s_%s_%s_%s" % (start_date, end_date, area_id, sort_key)
try:
# 设置哈希类型缓存数据
redis_store.hset(redis_key, page, resp_json)
redis_store.expire(redis_key, constants.HOUSE_LIST_PAGE_REDIS_CACHE_EXPIRES)
except Exception as e:
current_app.logger.error(e)
return resp_json, 200, {"Content-Type": "application/json"}
def get_house_list():
start_date = request.args.get('sd', '')
end_date = request.args.get('ed', '')
area_id = request.args.get('aid', '')
sort_key = request.args.get('sk', 'new')
page = request.args.get('p')
try:
if start_date:
start_date = datetime.strptime(start_date, '%Y-%m-%d')
if end_date:
end_date = datetime.strptime(end_date, '%Y-%m-%d')
if start_date and end_date:
assert start_date <= end_date
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, msg='日期参数有误')
# 判断区域id
area = None
if area_id:
try:
area = Area.query.get(area_id)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, msg='区域参数有误')
try:
page = int(page)
except Exception as e:
current_app.logger.error(e)
page = 1
# 获取缓存
# key = house_起始_结束_区域id_排序
redis_key = 'house_%s_%s_%s_%s' % (start_date, end_date, area_id, sort_key)
try:
resp_json = redis_store.hget(redis_key, page)
except Exception as e:
current_app.logger.error(e)
resp_json = None
if resp_json:
current_app.logger.info('hit redis list')
return resp_json, 200, {'Content-Type': 'application/json'}
# 构造参数列表容器
filter_params = []
# 时间条件
# 查询所有时间冲突的房子
# select * from order where order.begin_date<=end_date and order.end_date>=start_date
try:
if start_date and end_date:
confict_orders = Order.query.filter(
Order.begin_date <= end_date,
Order.end_date >= start_date).all()
elif start_date:
confict_orders = Order.query.filter(
Order.end_date >= start_date).all()
elif end_date:
confict_orders = Order.query.filter(
Order.begin_date <= end_date).all()
else:
confict_orders = None
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, msg='数据查询失败')
if confict_orders:
confict_house_ids = [order.house_id for order in confict_orders]
# 冲突房屋id不为空
if confict_house_ids:
# 追加条件
filter_params.append(House.id.notin_(confict_house_ids))
# 区域条件
if area:
filter_params.append(House.area_id == area.id)
# 排序
if sort_key == 'booking': # 入住最多
house_query = House.query.filter(*filter_params).order_by(
House.order_count.desc())
elif sort_key == 'price-inc': # 价格从低到高
house_query = House.query.filter(*filter_params).order_by(House.price)
elif sort_key == 'price-des': # 价格从高到低
house_query = House.query.filter(*filter_params).order_by(
House.price.desc())
else: # new 默认 从新到旧
house_query = House.query.filter(*filter_params).order_by(
House.create_time.desc())
# 分页
try:
# 当前页数 每页数量 自动错误输出
paginater = house_query.paginate(
page=page,
per_page=constants.HOUSE_LIST_PAGE_CAPACITY,
error_out=False)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, msg='数据查询失败')
# 获取页面数据
house_li = paginater.items
houses = []
for house in house_li:
houses.append(house.to_basic_dict())
# 获取总页数
total_page = paginater.pages
resp_dict = dict(errno=RET.OK,
msg='OK',
data={
'total_page': total_page,
'houses': houses,
'current_page': page
})
resp_json = json.dumps(resp_dict)
# 设置缓存数据
# value = { '1':{},'2':{} }
if page <= total_page:
try:
# redis pipeline管道，类似mysql事务，但区别于不能回滚
# redis_store.hset(redis_key, page, resp_json)
# redis_store.expire(redis_key, constants.HOUES_LIST_PAGE_REDIS_CACHE_EXPIRES)
# 创建管道
pipeline = redis_store.pipeline()
pipeline.multi() # 事务开始
pipeline.hset(redis_key, page, resp_json)
pipeline.expire(redis_key,
constants.HOUES_LIST_PAGE_REDIS_CACHE_EXPIRES)
pipeline.execute() # 提交
except Exception as e:
current_app.logger.error(e)
return resp_json, 200, {'Content-Type': 'application/json'}
def get_house_list():
"""获取房屋列表信息"""
# 一. 获取参数
# 注意: 参数可以不传, 不传就把参数设为空值或者默认值
start_date_str = request.args.get("sd", "") # 想要查询的起始时间
end_date_str = request.args.get("ed", "") # 想要查询的终止时间
area_id = request.args.get("aid", "") # 区域id
sort_key = request.args.get("sk", "new") # 排序关键字
page = request.args.get("p", 1) # 页数
# 二. 校验参数
# 2.1判断日期
# 需要确保能够转换成日期类, 且开始时间不能小于结束时间
try:
start_date = None
end_date = None
if start_date_str:
start_date = datetime.strptime(start_date_str, '%Y-%m-%d')
if end_date_str:
end_date = datetime.strptime(end_date_str, '%Y-%m-%d')
if start_date and end_date:
assert start_date <= end_date
except Exception as e:
logging.error(e)
return jsonify(errno=RET.PARAMERR, errmsg='日期参数有误')
# 2.2判断页数
# 需要确保页数能够转为int类型
try:
page = int(page)
except Exception as e:
page = 1
# 三. 业务逻辑处理
# 3.1 先从redis缓存中获取数据
# 如果获取了数据, 可以直接返回, 不需要执行下面逻辑
try:
# 将所有的参数条件当做Key(除了页码)
redis_key = "houses_%s_%s_%s_%s" % (start_date_str, end_date_str, area_id, sort_key)
resp_json = redis_store.hget(redis_key, page)
except Exception as e:
logging.error(e)
resp_json = None
# 有缓存直接返回数据
if resp_json:
return resp_json
# 3.2 定义查询数据的参数空列表
# 为了方便设置过滤条件, 先定义空列表, 然后逐步判断添加进来
filter_params = []
# 3.3 处理区域信息--> 拼接查询条件
if area_id:
# 常规后面的内容,可能会存储为true或false.
# 而我们需要的保存查询的条件, 以便于后面展开
# ==:__eq__ 底层会调用该函数,如果重写了__eq__, 比较的结果就会不一样
# SQLAlchemy重写了__eq__函数,对比之后,只会返回查询的条件
filter_params.append(House.area_id == area_id)
# 3.4 处理时间, 获取不冲突的房屋信息
# 需要根据传入的时间参数不同, 获取冲突的房屋, 再从房屋中获取对应的房屋ID
try:
conflict_orders_li = []
if start_date and end_date:
# 从订单表中查询冲突的订单，进而获取冲突的房屋id
conflict_orders_li = Order.query.filter(Order.begin_date <= end_date, Order.end_date >= start_date).all()
elif start_date:
# 从订单表中查询冲突的订单，进而获取冲突的房屋id
conflict_orders_li = Order.query.filter(Order.end_date >= start_date).all()
elif end_date:
# 从订单表中查询冲突的订单，进而获取冲突的房屋id
conflict_orders_li = Order.query.filter(Order.begin_date <= end_date).all()
except Exception as e:
logging.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
if conflict_orders_li:
# 找到了冲突的订单信息, 就找到了冲突的房屋ID
# conflict_house_id_list = [order.house_id for order in conflict_order_list]
conflict_house_id_li = [order.house_id for order in conflict_orders_li]
# 查询不冲突的房屋ID
House.query.filter(Houser.id.notin_([3,5,7]))
filter_params.append(House.id.notin_(conflict_house_id_li))
# 3.5 排序
# 不同的排序, 过滤条件不同
# 排序将来可能是字符串或者是排序ID(排序ID更为多见)
"""
<li class="active" sort-key="new">最新上线</li>
<li sort-key="booking">入住最多</li>
<li sort-key="price-inc">价格 低-高</li>
<li sort-key="price-des">价格 高-低</li>
"""
if sort_key == 'booking':
# 创建查询条件语句
house_query = House.query.filter(*filter_params).order_by(House.order_count.desc())
elif sort_key == 'price-inc':
# 创建查询条件语句
house_query = House.query.filter(*filter_params).order_by(House.price.asc())
elif sort_key == 'price-des':
# 创建查询条件语句
house_query = House.query.filter(*filter_params).order_by(House.price.desc())
else:
house_query = House.query.filter(*filter_params).order_by(House.create_time.desc())
# 3.6 分页 sqlalchemy的分页
# 在之前房屋的过滤条件后面, 使用paginate设置分页
try:
house_data = house_query.paginate(page, constants.HOUSE_LIST_PAGE_CAPACITY, False)
except Exception as e:
logging.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
# house_data.page 当前页码
house_li = house_data.items # 当前页码的数据内容
total_page = house_data.pages # 总页数
# 3.7 将数据转为JSON
houses = []
for house in house_li:
houses.append(house.to_basic_dict())
# 将结果转换json字符串
# 将整个响应结果封装. 而非值封装data
resp = dict(errno=RET.OK, errmsg="查询成功",
data={"houses": houses, "total_page": total_page, "current_page": page})
resp_json = json.dumps(resp)
# 3.8 将结果缓存到redis中
# 用redis的哈希类型保存分页数据, 并使用事务提交保存
if page <= total_page:
# 用redis的哈希类型保存分页数据
redis_key = "houses_%s_%s_%s_%s" % (start_date_str, end_date_str, area_id, sort_key)
try:
# 使用redis中的事务
pipeline = redis_store.pipeline()
# 开启事务
pipeline.multi()
pipeline.hset(redis_key, page, resp_json)
pipeline.expire(redis_key, constants.HOUSE_LIST_PAGE_REDIS_EXPIRES)
# 执行事务
pipeline.execute()
except Exception as e:
logging.error(e)
# 四. 数据返回
return resp_json
def get_house_list():
# 一. 获取参数
# 注意: 参数可以不传, 不传就把参数设为空值或者默认值
start_date_str = request.args.get('sd', '') # 查询的起始时间
end_date_str = request.args.get('ed', '') # 查询的起始时间
sort_key = request.args.get('sk', 'new') # 排序关键字
area_id = request.args.get('aid', '') # 查询的城区信息
page = request.args.get('p', 1) # 查询的页数
# 二. 校验参数
# 2.1判断日期
# 需要确保能够转换成日期类, 且开始时间不能小于结束时间
try:
# 需要确保能够转换成日期类
start_date = None
if start_date_str:
start_date = datetime.strptime(start_date_str, '%Y-%m-%d')
end_date = None
if end_date_str:
end_date = datetime.strptime(end_date_str, '%Y-%m-%d')
if start_date and end_date:
# 开发期间, 可以通过增加断言来帮助挑错
assert start_date <= end_date
except Exception as e:
logging.error(e)
return jsonify(errno=RET.PARAMERR, errmsg='参数错误')
# 且开始时间不能小于结束时间
# 2.2判断页数
# 需要确保页数能够转为int类型
try:
page = int(page)
except Exception as e:
page = 1
# 三. 业务逻辑处理
# 3.1 先从redis缓存中获取数据
# 如果获取了数据, 可以直接返回, 不需要执行下面逻辑
redis_key = 'houses_%s_%s_%s_%s' % (start_date_str, end_date_str, area_id,
sort_key)
try:
resp_json = redis_store.hget(redis_key, page)
except Exception as e:
logging.error(e)
resp_json = None
if resp_json:
# 说明获取了redis的值
# return resp_json, 300
return resp_json
# 3.2 定义查询数据的参数空列表
# 为了方便设置过滤条件, 先定义空列表, 然后逐步判断添加进来
filter_params = []
# 3.3 处理区域信息
if area_id:
# ==: 默认的==(__) / < / > 内部是调用了某个函数
# 数据库框架内部重写了==的__eq__函数, 所以结果是SQL对象
filter_params.append(House.area_id == area_id)
# 3.4 处理时间, 获取不冲突的房屋信息
# 需要根据传入的时间参数不同, 获取冲突的房屋, 再从房屋中获取对应的房屋ID
try:
conflict_orders_li = []
if start_date and end_date:
# 从订单表中查询冲突的订单，进而获取冲突的房屋id
conflict_orders_li = Order.query.filter(
Order.begin_date <= end_date,
Order.end_date >= start_date).all()
elif start_date:
# 从订单表中查询冲突的订单，进而获取冲突的房屋id
conflict_orders_li = Order.query.filter(
Order.end_date >= start_date).all()
elif end_date:
# 从订单表中查询冲突的订单，进而获取冲突的房屋id
conflict_orders_li = Order.query.filter(
Order.begin_date <= end_date).all()
except Exception as e:
logging.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
if conflict_orders_li:
conflict_house_id_li = [order.house_id for order in conflict_orders_li]
# 添加条件，查询不冲突的房屋
filter_params.append(House.id.notin_(conflict_house_id_li))
# 定义查询语句, 将filter_params传入
# House.query.filter_by(*filter_params).order_by()
# 3.5 排序
# 不同的排序, 过滤条件不同
# sort - key = "booking" > 入住最多
# sort - key = "price-inc" > 价格低 - 高
# sort - key = "price-des" > 价格高 - 低 < / li >
# new 默认 > 最新发布的在前
if sort_key == 'booking':
# 如果没有跟上all(), first(), paginate(), 下面的代码只是过滤条件
house_query = House.query.filter(*filter_params).order_by(
House.order_count.desc())
elif sort_key == 'price-inc':
house_query = House.query.filter(*filter_params).order_by(
House.price.asc())
elif sort_key == 'price-des':
house_query = House.query.filter(*filter_params).order_by(
House.price.desc())
else:
house_query = House.query.filter(*filter_params).order_by(
House.create_time.desc())
# 3.6 分页 sqlalchemy的分页
# 在之前房屋的过滤条件后面, 使用paginate设置分页
# paginate三个参数: 当前要查询的页码, 每页数量, 是否要返回错误信息
try:
house_page = house_query.paginate(page,
constants.HOUSE_LIST_PAGE_CAPACITY,
False)
except Exception as e:
logging.error(e)
return jsonify(errno=RET.DBERR, errmsg='数据库查询错误')
# 获取总页数
total_page = house_page.pages
# 获取当前页数
# house_page.page
# 获取分页的数据, 需要调用items
house_li = house_page.items
# 3.7 将数据转为JSON# 定义一个空列表, 遍历拼接转换后的模型数据
houses = []
for house in house_li:
houses.append(house.to_basic_dict())
resp = dict(errno=RET.OK,
errmsg='查询成功',
data={
'houses': houses,
'total_page': total_page,
'current_page': page
})
resp_json = json.dumps(resp)
# 3.8 将结果缓存到redis中
# 用redis的哈希类型保存分页数据, 并使用事务提交保存
# 传入的页码超过了最大页数 6 > 5
if page <= total_page:
redis_key = 'houses_%s_%s_%s_%s' % (start_date_str, end_date_str,
area_id, sort_key)
try:
# 这里使用事务提交. 两个操作必须同时执行才行.
# 如果使用事务提交, 那么失败了, 会自动回滚
pipeline = redis_store.pipeline()
# 开启事务
pipeline.multi()
pipeline.hset(redis_key, page, resp_json)
pipeline.expire(redis_key, constants.HOUSE_LIST_PAGE_REDIS_EXPIRES)
# 执行事务
pipeline.execute()
except Exception as e:
logging.error(e)
# 四. 数据返回
# return resp_json, 200
return resp_json
def get_house_list():
"""获取房屋列表页"""
#获取参数,有的需要给默认值,区域id,用户选择的开始时间，结束时间，排序条件，页数
area_id = request.args.get('aid')
start_date_str = request.args.get('sd')
end_date_str = request.args.get('ed')
sort_key = request.args.get('sk', 'new')
page = request.args.get('p', 1)
#校验参数，把日期参数格式化，页数转int
try:
#定义两个变量来存储格式化之后的日期参数
start_date, end_date = None, None
#当用户只选择了开始时间
if start_date_str:
start_date = datetime.datetime.strptime(start_date_str, '%Y-%m-%d')
#当用户只选择了结束时间
if end_date_str:
end_date = datetime.datetime.strptime(end_date_str, '%Y-%m-%d')
#如果都选了，断言用户选择的时常至少为一天
if start_date_str and end_date_str:
assert end_date >= start_date
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DATAERR, errmsg='日期格式错误')
try:
page = int(page)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DATAERR, errmsg='页数格式错误')
#尝试先从redis获取房屋列表信息
try:
#构造键值
redis_key = 'houses_%s_%s_%s_%s' % (area_id, start_date, end_date,
sort_key)
ret = redis_store.hget(redis_key, page)
except Exception as e:
current_app.logger.error(e)
ret = None
#有就在redis留下访问记录，直接返回结果
if ret:
current_app.logger.info('hit redis house list info')
return ret
#没有就查询数据库
try:
#定义一个容器来存储过滤条件
params_filter = []
#如果有城区信息就把这个对象加入到容器
if area_id:
params_filter.append(House.area_id == area_id)
#查询出和日期不冲突的房屋，先查询出与日期冲突的订单，在查询出与日期冲突的房屋，取反加入到容器中
if start_date and end_date:
conflict_orders = Order.query.filter(
Order.begin_date <= end_date,
Order.end_date >= start_date).all()
if start_date:
conflict_orders = Order.query.filter(
Order.end_date >= start_date).all()
if end_date:
conflict_orders = Order.query.filter(Order.begin_date <= end_date)
#使用列表推导式遍历冲突的订单得到与日期冲突的房屋
conflict_houses_id = [order.house_id for order in conflict_orders]
#如果存在就加入容器中
if conflict_houses_id:
params_filter.append(House.id.notin_(conflict_houses_id))
#按照排序条件排序
#按订单由多到少排序
if 'booking' == sort_key:
houses = House.query.filter(*params_filter).order_by(
House.order_count.desc())
#按价格由低到高排序
if 'price-inc' == sort_key:
houses = House.query.filter(*params_filter).order_by(
House.price.asc())
#按价格从高到低排序
if 'price-desc' == sort_key:
houses = House.query.filter(*params_filter).order_by(
House.price.desc())
#默认按发布时间最新排序
else:
houses = House.query.filter(*params_filter).order_by(
House.create_time.desc())
#分页，获取每页数据和分页之后的总页数
houses_page = houses.paginate(page, constants.HOUSE_LIST_PAGE_CAPACITY,
False)
houses_list = houses_page.items
total_page = houses_page.pages
#遍历分页数据，定义一个容器调用模型类的方法添加到容器中
houses_dict_list = []
for house in houses_list:
houses_dict_list.append(house.to_basic_dict())
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DATAERR, errmsg='查询房屋列表数据失败')
#构造响应结果
resp = {
"errno": 0,
"errmsg": "OK",
"data": {
"houses": houses_dict_list,
"total_page": total_page,
"current_page": page
}
}
#转成json准备存储到redis
resp_json = json.dumps(resp)
#当用户请求页数不大于总页数时，才能存入redis
if page <= total_page:
redis_key = 'houses_%s_%s_%s_%s' % (area_id, start_date, end_date,
sort_key)
pip = redis_store.pipeline()
#需要调用redis的事务保存数据
try:
#开启事务
pip.multi()
#保存数据
pip.hset(redis_key, page, resp_json)
#设置过期时间
pip.expire(redis_key, constants.HOUSE_LIST_REDIS_EXPIRES)
#提交事务
pip.execute()
except Exception as e:
current_app.logger.error(e)
#返回结果
return resp_json
def get_house_list():
"""获取房屋的列表信息(搜索页面)"""
#获取参数
start_date = request.args.get("sd", default="")
end_date = request.args.get("sd", default="")
area_id = request.args.get("sd", default="")
sort_key = request.args.get("sd", default="new")
page = request.args.get("sd")
#校验参数
try:
if start_date:
start_date = datetime.strptime(start_date, "%Y-%m-%d")
elif end_date:
end_date = datetime.strptime(end_date, "%Y-%m-%d")
if start_date and end_date:
# 起始要小于结束时间
assert start_date <= end_date
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.OK, errmsg="日期参数有误")
# 城区编号要合法存在
if area_id:
try:
area = Area.query.get(area_id)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="区域参数有误")
#关键字要合法存在 默认为最新上线
#页码必须整数，否则设置为1
try:
if int(page) > 0:
page = int(page)
except Exception as e:
current_app.logger.error(e)
page = 1
#获取缓存数据
redis_key = 'house_%s_%s_%s_%s' % (start_date, end_date, area_id, sort_key)
try:
resp_json = redis_store.hget(redis_key, page)
except Exception as e:
current_app.logger.error(e)
else:
if resp_json:
return resp_json, 200, {"Content_Type": "application/json"}
#过滤条件的查询容器
filter_params = []
#冲突的订单对象
confict_orders = None
try:
if start_date and end_date:
confict_orders = Order.query.filter(
Order.begin_date <= end_date,
Order.end_data >= start_date).all()
elif start_date:
confict_orders = Order.query.filter(
Order.end_data >= start_date).all()
elif end_date:
confict_orders = Order.query.filter(
Order.begin_date <= end_date).all()
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.OK, errmsg="数据库查询失败")
#如果存在冲突订单
if confict_orders:
#拿出房屋id
confict_orders_ids = [order.house_id for order in confict_orders]
if confict_orders_ids:
#如果存在冲突的房间id，追加订单过滤条件到过滤容器
filter_params.append(House.id.notin_(confict_orders_ids))
#区域条件
if area_id:
#区域合法，追加
filter_params.append(House.area_id == area_id)
#解包过滤容器，补充排序条件
if sort_key == "booking": # 入住人数最多
house_query = House.query.filter(*filter_params).order_by(
House.order_count.desc())
if sort_key == "price-inc": # 价格升序
house_query = House.query.filter(*filter_params).order_by(
House.price.asc())
if sort_key == "price-desc": # 价格降序
house_query = House.query.filter(*filter_params).order_by(
House.price.desc())
else: # 上线时间排序
house_query = House.query.filter(*filter_params).order_by(
House.create_time.desc())
#处理分页
try:
page_obj = house_query.paginate(
page=page,
per_page=constants.HOUSE_LIST_PAGE_CAPACITY,
error_out=False)
except Exception as e:
current_app.logger.error(e)
return jsonify(errnp=RET.DBERR, errmsg="数据库错误")
#获取页面数据
houses = [house.to_basic_dict() for house in page_obj.items]
#获取总页数
total_page = page_obj.pages
#拼接返回数据
resp_dict = dict(errno=RET.OK,
errmsg="OK",
data={
"total_page": total_page,
"current_page": page,
"houses": houses
})
resp_json = json.dumps(resp_dict)
#页面逻辑
if page <= total_page:
try:
#设置缓存
redis_key = 'house_%s_%s_%s_%s' % (start_date, end_date, area_id,
sort_key)
pipeline = redis_store.pipeline()
pipeline.multi()
pipeline.hset(redis_key, page, page, resp_json)
pipeline.expire(redis_key,
constants.HOUES_LIST_PAGE_REDIS_CACHE_EXPIRES)
#执行语句
pipeline.execute()
except Exception as e:
current_app.logger.error(e)
#返回数据
return resp_json, 200, {"Content-Type": "application/json"}
def get_house_list():
'''
获取房屋列表
1 获取参数 area_id start_date_str end_date_str sort_key,page 可选参数
2 可选参数sort_key 需要有默认值 默认按照房屋发布时间进行排序 page 默认加载第一页
3 校验参数 对日期进行格式化处理 datetime.striptime(start_data_str,'%Y-%m-%d')
4 需要判断日期的天数 至少为1天
5 对页数进行格式化 page = int( page )
6 尝试从redis中获取缓存房屋列表信息 redis_key = ''
7 校验查询结果
8 如果未获取数据 查询mysql数据库
9 用户选择的顾虑条件 区域信息 日期信息
10 根据保存的过滤条件 查询数据库 booking price time 默认按照发布时间
11 对查询结果进行分页处理 house,paginate(page,2,False)
13 保存分页胡的房屋数据 和列表总页数
14 存储分页后 构造返回结果
15 存储hash类型的缓存
'''
# 尝试湖区参数 区域
area_id = request.args.get('aid', '')
start_date_str = request.args.get('sd', '')
end_date_str = request.args.get('ed', '')
sort_key = request.args.get('sk', '')
page = request.args.get('p', '1')
try:
#格式化日期
start_date, end_date = None, None
# 如果用户选择了了日期 对用户选择日期格式化
if start_date_str:
start_date = datetime.datetime.strptime(start_date_str, '%Y-%m-%d')
if end_date_str:
end_date = datetime.datetime.strptime(end_date_str, '%Y-%m-%d')
# 校验用户选择日期 至少是1天
if start_date_str and end_date_str:
# 断言如果实在try except 中 可以不用定义异常信息
assert start_date <= end_date, 'false'
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DATAERR, errmsg='日期格式错误')
# 对页数进行格式化
try:
page = int(page)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DATAERR, errmsg='页数格式错误')
# 禅师从redis中获取房屋列表信息
try:
redis_key = 'houses_%s_%s-%s-%s' % (area_id, start_date_str,
end_date_str, sort_key)
# 使用哈希数据类型获取数据
ret = redis_store.hget(redis_key, page)
except Exception as e:
current_app.logger.error(e)
ret = None
# 查询结果
if ret:
# 记录访问缓存数据的时间
current_app.logger.info('hit houses list info redis')
return ret
# 查询mysql 数据库
try:
# 定义列表 用来存储查询过滤的参数 区域信息 日期信息
params_filter = []
# 判断区域参数
if area_id:
params_filter.append(House.area_id == area_id)
if start_date and end_date:
# 对日期进行判断 来判断用户选择的日期和数据库中已经成交订单的日期是不冲突的
conflict_orders = Order.query.filter(
Order.begin_date <= end_date,
Order.end_date >= start_date).all()
# 根据冲突订单 获取冲突的房屋
conflict_houses_id = [order.house_id for order in conflict_orders]
# 判断冲突房屋数据
if conflict_houses_id:
# 对有冲突的房屋的id进行取反
params_filter.append(House.id.notin_(conflict_houses_id))
elif start_date:
conflict_orders = Order.query.filter(
Order.end_date >= start_date).all()
# 根据冲突订单 获取冲突的房屋
conflict_houses_id = [order.house_id for order in conflict_orders]
# 判断冲突房屋数据
if conflict_houses_id:
# 对有冲突的房屋的id进行取反
params_filter.append(House.id.notin_(conflict_houses_id))
elif end_date:
conflict_orders = Order.query.filter(
Order.start_date <= end_date).all()
conflict_houses_id = [order.house_id for order in conflict_orders]
if conflict_houses_id:
# 对有冲突的房屋的id进行取反
params_filter.append(House.id.notin_(conflict_houses_id))
# 根据过滤条件以及用户选择的排序条件和排序条件进行查询
if 'booking' == sort_key: # 按照成交进行查询
houses = House.query.filter(*params_filter).order_by(
House.order_count.desc())
elif 'price-inc' == sort_key:
houses = House.query.filter(*params_filter).order_by(
House.price.asc())
elif 'price-des' == sort_key:
houses = House.query.filter(*params_filter).order_by(
House.price.desc())
else: # 按照房屋发布时间进行排序
houses = House.query.filter(*params_filter).order_by(
House.create_time.desc())
# 对查询结果进行分页
houses_page = houses.pagenate(page, constants.HOUSE_LIST_PAGE_CAPACITY,
False)
# 保存分页后的房屋数据
houses_list = houses_page.items
# 保存分页之后的总页数
total_page = houses_page.page
# 遍历分页后的数据
houses_dict_list = []
for house in houses_list:
houses_dict_list.append(house.to_basic_dict)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg='查询房屋列表信息异常')
# 构造响应数据
resp = {
'errno': 0,
'errmsg': 'OK',
'data': {
'houses': houses_dict_list,
'total_page': total_page,
'current_page': page
}
}
# 序列化数据
resp_json = json.dumps(resp)
if page <= total_page:
redis_key = 'houses_%s_%s_%s_%s' % (area_id, start_date_str,
end_date_str, sort_key)
# 一个jian 对应多个zhi
pip = redis_store.pipline()
try:
# 开启事物
pip.multi()
# 存储缓存数据
pip.hset(redis_key, page, resp_json)
# 设置过期时间
pip.expire(redis_key, constants.HOUSE_LIST_REDIS_EXPIRES)
# 执行事务
pip.execute()
except Exception as e:
current_app.logger.error(e)
# 返回房屋列表页数据
return resp_json
def get_house_search_list():
"""
# GET 127.0.0.1/houses/search/sd=xxxx&ed=xxx&aid=xxx&sk=xxxx&p
获取房屋列表信息，在搜索页面
请求参数: sd开始时间 ed结束时间 aid查询地址的id值 sk排序的方式 p-page 页数
:return: 相关的列表数据
"""
# 参数获取
req = request.get_json()
start_time = req.get('sd', None) # 查询开始时间
end_time = req.get('ed', None) # 查询结束时间
aid = req.get('aid', None) # 查询地区id值
sk = req.get('sk', "new") # 查询排序关键字
page = req.get('p', '1')
# 参数检验
# 检查开始时间和结束时间
if start_time:
start_time = datetime.strftime(start_time,
"%Y-%m-%d") # 格式转换 str->time
if end_time:
end_time = datetime.strftime(end_time, "%Y-%m-%d") # 格式转换 str->time
# 当开始时间和结束时间都存在的时候，检查结束时间是否小于开始时间
if start_time and end_time:
if start_time >= end_time:
return jsonify(errno=RET.PARAMERR, errmsg="时间参数错误")
# 判断要查询的地区是否存在
if aid:
try:
aid = Area.query.get(aid)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="数据库异常")
else:
if aid is None:
return jsonify(errno=RET.PARAMERR, errmsg="要查询的地区并不存在")
# 页数处理
if page:
try:
page = int(page)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="页数格式异常")
# 在redis数据库中查询是否存在当前查询条件的数据 house_search_sd_ed_aid_sk_page hash
# 要查询的key
redis_key = "house_search_%s_%s_%s_%s" % (start_time, end_time, aid, sk)
try:
house_search = redis_store.hget(redis_key.page)
except Exception as e:
current_app.logger.error(e)
# 存在就直接返回数据
return house_search, 200, {"Content-Type": "application/json"}
# 不存在去数据中查询数据
# 获取查询条件
filter_params = [] # 过滤条件
# 时间条件，冲突的订单
conflict_orders = None
try:
if start_time and end_time:
# 查询冲突的订单
conflict_orders = Order.query.filter(
Order.begin_date <= end_time,
Order.end_date >= start_time).all()
elif start_time:
conflict_orders = Order.query.filter(
Order.end_date <= start_time).all()
elif end_time:
conflict_orders = Order.query.filter(
Order.begin_date >= end_time).all()
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
if conflict_orders:
# 获取冲突订单的所有id值
conflict_house_ids = [order.id for order in conflict_orders]
if conflict_house_ids:
# 向过滤条件中添加查询条件 查询不在冲突订单id值中的house_id
filter_params.append(House.id.notin_(conflict_house_ids))
# 查询地址
if aid:
filter_params.append(House.area_id == aid)
# 补充排序条件
if sk == "booking":
house_query = House.query.filter(*filter_params).order_by(
House.order_count.desc())
elif sk == "price-inc":
house_query = House.query.filter(*filter_params).order_by(
House.price.asc())
elif sk == "price-des":
house_query = House.query.filter(*filter_params).order_by(
House.price.desc())
else:
house_query = House.query.filter(*filter_params).order_by(
House.create_time.desc())
# 处理分页：
try:
# 当前页数 每页数据量 自动的错误输出
page_obj = house_query.paginate(
page=page,
per_page=constants.HOUSE_LIST_PAGE_CAPACITY,
error_out=False)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
# 获取当前页面的数据
house_li = page_obj.items
houses = []
for house in house_li:
houses.append(house.to_full_dict())
# 获取总页数
total_page = page_obj.pages
resp_dict = dict(errno=RET.OK,
errmsg="OK",
data={
"total_page": total_page,
"houses": houses,
"current_page": page
})
resp_json = json.dumps(resp_dict)
if page <= total_page:
# 设置缓存数据
redis_key = "house_search_%s_%s_%s_%s" % (start_time, end_time, aid,
sk)
# 存放数据
try:
# 创建redis管道对象，可以一次执行多个语句
pipeline = redis_store.pipeline()
# 开启多个语句的记录
pipeline.mulit()
pipeline.hset(redis_key, page, resp_json)
pipeline.expire(redis_key,
constants.HOUES_LIST_PAGE_REDIS_CACHE_EXPIRES)
# 执行语句
pipeline.execute()
except Exception as e:
current_app.logger.error(e)
return resp_json, 200, {"Content-Type": "application/json"}
def get_houses_list():
"""
获取房屋列表信息
缓存----磁盘----缓存
获取参数---检查参数---查询数据---返回结果
1/获取参数:aid,sd,ed,sk,p
2/需要对排序条件和页面两个参数,进行默认处理
3/需要对日期参数进行判断,并且进行格式化
4/需要对页数进行格式化
5/尝试从redis缓存中获取房屋列表信息
6/让一个键对应多条数据的存储,需要hash数据类型,构造hash对象的键
redis_key = 'houses_%s_%s_%s_%s' %(aid,sd,ed,sk)
ret = redis_store.hget(redis_key,page)
7/如果有数据,留下访问的记录,直接返回
8/查询mysql数据库
9/首先定义容器存储查询数据库的过滤条件
10/判断区域参数的存在,如果有把区域信息添加到列表中
11/需要判断日期参数的存在,把用户选择的日期和订单表中的日期进行比较,找到日期不冲突的房屋返回给客户端
12/根据容器中存储的过滤条件,执行查询排序,price/crate_time
houses = House.query.filter(过滤条件).order_by(House.order_count.desc())
13/对排序结果进行分页
houses_page = houses.paginate(page,每页条目数,False)
total_page = houses_page.pages
houses_list = houses_page.items
14/定义容器,houses_dict_list遍历分页后的房屋数据,调用模型类中的to_basic_dict()方法
15/构造响应报文
resp = {"errno":0,"errmsg":"OK","data":{"houses":houses_dict_list,"total_page":total_page,"current_page":page}}
16/序列化数据,准备存入缓存中
resp_json = json.dumps(resp)
17/判断用户选择的页数必须小于等于分页后的总页数,本质上用户选择的页数必须要有数据
18/构造hash数据类型的键,为了确保数据的完整性以及有效期的一致性
需要使用事务
pip = redis_store.pipline()
pip.multi()
pip.hset(redis_key,page,resp_json)
pip.expire(redis_key,7200)
pip.execute()
19/返回结果,return resp_json
:return:
"""
# 获取参数
area_id = request.args.get("aid", "")
start_date_str = request.args.get("sd", "")
end_date_str = request.args.get("ed", "")
sort_key = request.args.get("sk", "")
page = request.args.get("p", "")
# 检查日期参数
try:
# 定义变量存储格式化后的日期
start_date, end_date = None, None
# 如果用户选择了开始日期
if start_date_str:
start_date = datetime.datetime.strptime(start_date_str, "%Y-%m-%d")
# 如果用户选择了结束日期
if end_date_str:
end_date = datetime.datetime.strptime(end_date_str, "%Y-%m-%d")
# 如果用户既选择了开始日期，也选择了结束日期，判断用户选择的日期必须至少是一天
if start_date_str and end_date_str:
# 断言用户选择的日器必须至少是一天
assert start_date <= end_date
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DATAERR, errmsg="页数格式错误")
# 尝试从redis中获取房屋列表信息
# 一个键对应多条数据，使用hash数据类型
try:
redis_key = "house_%s_%s_%s_%s" % (area_id, start_date, end_date,
sort_key)
ret = redis_store.hget(redis_key, page)
except Exception as e:
current_app.logger.error(e)
ret = None
# 判断ret是否存在
if ret:
# 留下访问记录，返回结果
current_app.logger.info("hit redis houses list info")
return ret
# 查询mysql数据库
try:
# 定义容器，存储过滤条件
params_filter = []
# 如果城区参数存在
if area_id:
# 添加的是城区数据的对象（ipython中只会返回true或false）
params_filter.append(House.area_id == area_id)
# 如果开始日期和结束日期参数存在，目标是查询日期不冲突的房屋
if start_date and end_date:
# 查询日期冲突的订单
conflict_orders = Order.query.filter(
Order.begin_date <= end_date,
Order.end_date >= start_date).all()
conflict_houses_id = [order.house_id for order in conflict_orders]
if conflict_houses_id:
params_filter.append(House.id.notin_(conflict_houses_id))
# 如果用户只选择了一个开始日期
elif start_date:
conflict_orders = Order.query.filter(
Order.end_date >= start_date).all()
conflict_houses_id = [order.house_id for order in conflict_orders]
if conflict_houses_id:
params_filter.append(House.id.notin_(conflict_houses_id))
# 如果用户只选择了一个结束日期
elif end_date:
conflict_orders = Order.query.filter(
Order.begin_date <= end_date).all()
conflict_houses_id = [order.house_id for order in conflict_orders]
if conflict_houses_id:
params_filter.append(House.id.notin_(conflict_houses_id))
# 判断排序条件，按照房屋成交次数排序
if "booking" == sort_key:
# *params_filter是查询方法进行拆包
houses = House.query.filter(*params_filter).order_by(
House.order_count.desc())
# 按照价格进行升序和降序排序
elif "price-inc" == sort_key:
houses = House.query.filter(*params_filter).order_by(
House.price.asc())
elif "price-des" == sort_key:
houses = House.query.filter(*params_filter).order_by(
House.price.desc())
# 默认按房屋时间排序
else:
houses = House.query.filter(*params_filter).order_by(
House.create_time.desc())
# 对排序后的房屋进行分页，page代表页数，每页x条,false代表分页异常不报错
houses_page = houses.paginate(page, constants.HOUSE_LIST_PAGE_CAPACITY,
False)
# 获取分页后的房屋数据
houses_list = houses_page.items
# 获取分页后的总页数
total_page = houses_page.pages
# 定义容器，遍历分页后的房屋数据，需要调用模型类中的方法
houses_dict_list = []
for house in houses_list:
houses_dict_list.append(house.to_basic_dict())
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="查询房屋列表信息失败")
# 构造响应报文
resp = {
"errno": 0,
"errmsg": "OK",
"data": {
"houses": houses_dict_list,
"total_page": total_page,
"current_page": page
}
}
# 序列化数据
resp_json = json.dumps(resp)
# 判断用户请求的页数必须小鱼等于分页后的总页数
if page <= total_page:
redis_key = "house_%s_%s_%s_%s" % (area_id, start_date, end_date,
sort_key)
# 多条数据的存储，为了去报数据的完整性和一致性，需要使用事务
pip = redis_store.pipeline()
try:
# 开启事务
pip.multi()
# 保存数据
pip.hset(redis_key, page, resp_json)
# 设置过期时间
pip.expire(redis_key, constants.HOUSE_LIST_REDIS_EXPIRES)
# 执行事务
pip.execute()
except Exception as e:
current_app.logger.err(e)
# 返回结果
return resp_json
def get_houses_search():
'''搜索房屋列表
查询所有房屋信息
构造响应数据
响应结果
'''
current_app.logger.debug(request.args)
# 获取地区参数
aid = request.args.get('aid')
# 获取排序参数 new 最新 按照发布时间排序 booking 订单量 安装订单量倒叙 prince-inc 价格从低到高 prince-des 从高到低
sk = request.args.get('sk')
# 获取用户传入的页码
p = request.args.get('p', '1') # 默认第一页
# 获取入住时间
sd = request.args.get('sd', '')
# 获取离开时间
ed = request.args.get('ed', '')
start_date = None
end_date = None
# 校验参数
try:
p = int(p)
if sd:
# 将时间字符串转成时间对象
start_date = datetime.datetime.strptime(sd, '%Y-%m-%d')
if ed:
# 将时间字符串转成时间对象
end_date = datetime.datetime.strptime(ed, '%Y-%m-%d')
# 自己校验入住时间 是否小于离开的时间
if start_date and end_date:
# 断言 入住时间一定小于离开时间,如果不满足 就抛出异常
assert start_date < end_date, Exception('入住时间有误')
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg='参数有误')
# 查询数据之前 先读取缓存数据
try:
name = 'house_list_%s_%s_%s_%s' % (aid, sd, ed, sk)
response_data = redis_store.hget(name, p)
return jsonify(errno=RET.OK, errmsg='OK', data=eval(response_data))
except Exception as e:
current_app.logger.error(e)
# 查询所有的房屋信息
try:
# 无条件查询所有房屋数据
# 得到query对象
house_query = House.query
# 根据用户选中的城区信息 筛选
if aid:
house_query = house_query.filter(House.area_id == aid)
# 根据用户传入的入住时间和离开时间 # 查询出冲突的订单
conflict_orders = []
if start_date and end_date:
conflict_orders = Order.query.filter(end_date > Order.begin_date, start_date < Order.end_date).all()
elif start_date:
conflict_orders = Order.query.filter(start_date < Order.end_date).all()
elif end_date:
conflict_orders = Order.query.filter(end_date > Order.begin_date).all()
# 再通过冲突的订单 查询出里面的house_id 封装到列表中
if conflict_orders:
conflict_house_ids = [order.house_id for order in conflict_orders]
# 最后在查询House是，not_in(conflict_house_ids)
house_query = house_query.filter(House.id.notin_(conflict_house_ids))
# 根据排序规则对数据进行排序
if sk == 'booking':
house_query = house_query.order_by(House.order_count.desc())
elif sk == 'price-inc':
house_query = house_query.order_by(House.price.asc())
elif sk == 'price-des':
house_query = house_query.order_by(House.price.desc())
else:
house_query = house_query.order_by(House.create_time.desc())
# 无条件的从BaseQuery对象中取出数据
# houses = house_query.all()
# 需要使用分页功能，避免一次性查询所有数据，使用分页代码，替换all()
# 每页两条数据 paginate == 一本书，书里面有好多页
paginate = house_query.paginate(p, constants.HOUSE_LIST_PAGE_CAPACITY, False)
# 获取当前页的房屋模型对象 houses == [House, House],
houses = paginate.items
# 获取一共分了多少页，一定要传给前端
total_page = paginate.pages
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg='查询房屋信息失败')
# 2.构造响应数据
house_dict_list = []
for house in houses:
house_dict_list.append(house.to_basic_dict())
# 提示：如果重新构造了响应数据，需要把之前前端界面的house_dict_list的获取修改一下response.data.houses
response_data = {
'houses': house_dict_list,
'total_page': total_page
}
# 缓存房屋列表数据
try:
name = 'house_list_%s_%s_%s_%s' % (aid, sd, ed, sk)
# 创建redis管道:用于存放后面的所有的redis操作的，看做一整整体
pipeline = redis_store.pipeline()
# 开启事务
pipeline.multi()
# 需要看做整体的redis操作
redis_store.hset(name, p, response_data)
redis_store.expire(name, constants.HOUSE_LIST_REDIS_EXPIRES)
# 执行/提交事务
pipeline.execute()
except Exception as e:
current_app.logger.error(e)
# redis发现异常，不需要手动回滚
# 3.响应结果
return jsonify(errno=RET.OK, errmsg='OK', data=response_data)
def get_house_list():
'''获取房屋的列表信息(搜索页面)'''
start_date = request.args.get('sd', '') # 用户想要的起始时间
end_date = request.args.get('ed', '') # 用户想要的结束时间
area_id = request.args.get('aid', '') # 区域编号
sort_key = request.args.get('sk', 'new') # 排序关键字
page = request.args.get('p') # 页数
# 处理时间
try:
if start_date:
start_date = datetime.strptime(start_date, '%Y-%m-%d')
if end_date:
end_date = datetime.strptime(end_date, '%Y-%m-%d')
if start_date and end_date:
assert start_date <= end_date
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg='日期参数有误')
# 区域编号处理
if area_id:
try:
area = Area.query.get(area_id)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg='区域参数有误')
# 处理页数
try:
page = int(page)
except Exception as e:
current_app.logger.error(e)
page = 1
# 从缓存中读取信息
houses_search_coache = 'house_%s_%s_%s_%s' % (start_date, end_date, area_id, sort_key)
try:
resp_json = redis_store.hget(houses_search_coache, page)
except Exception as e:
current_app.logger.error(e)
else:
if resp_json:
return resp_json, 200, {'Content-Type': 'application/json'}
# 过滤条件的参数列表容器
filter_params = []
# 时间条件
conflict_orders = None
try:
if start_date and end_date:
conflict_orders = Order.query.filter(Order.begin_date <= end_date, Order.end_date >= start_date).all()
elif start_date:
conflict_orders = Order.query.filter(Order.end_date >= start_date).all()
elif end_date:
conflict_orders = Order.query.filter(Order.begin_date <= end_date).all()
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg='数据库异常')
if conflict_orders:
# 从订单获取房屋id
conflict_house_list = [order.house_id for order in conflict_orders]
# 如果冲突的房屋id不为空，向查询参数中添加条件
if conflict_house_list:
filter_params.append(House.id.notin_(conflict_house_list))
# 区域条件
if area_id:
filter_params.append(House.area_id == area_id)
# 补充排序条件
if sort_key == 'booking': # 入住最多
house_query = House.query.filter(*filter_params).order_by(House.order_count.desc())
elif sort_key == 'price-inc':
house_query = House.query.filter(*filter_params).order_by(House.price.asc())
elif sort_key == 'price-des':
house_query = House.query.filter(*filter_params).order_by(House.price.desc())
else:
house_query = House.query.filter(*filter_params).order_by(House.create_time.desc())
# 处理分页
try:
# 当前页数 每页数据量 自动输出错误
page_obj = house_query.paginate(page=page, per_page=constants.HOUSE_LIST_PAGE_CAPACITY, error_out=False)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg='数据库异常')
# 获取页面数据
house_list = page_obj.items
houses = []
for house in house_list:
houses.append(house.to_basic_dict())
# 获取页面数据
total_page = page_obj.pages
resp_data = dict(errno=RET.OK, errmsg='OK', data={'total_page':total_page, 'houses':houses, 'current_page':page})
resp_json = json.dumps(resp_data)
if page <= total_page:
# 设置缓存数据
try:
# 使用哈希类型
# redis_store.hset(houses_search_coache, page, resp_json)
# redis_store.expire(houses_search_coache, constants.HOME_PAGE_DATA_REDIS_EXPIRES)
# 使用redis的pipeline可以同时执行多个redis语句，同时成功，同时失败，相当于mysql中的事务
# 创建管道对象，可以一次执行多个语句
pipeline = redis_store.pipeline()
# 开启多个语句的记录
pipeline.multi()
pipeline.hset(houses_search_coache, page, resp_json)
pipeline.expire(houses_search_coache, constants.HOME_PAGE_DATA_REDIS_EXPIRES)
# 执行语句
pipeline.execute()
except Exception as e:
current_app.logger.error(e)
return resp_json, 200, {'Content-Type':'application/json'}
def get_house_list():
"""获取房屋列表信息，房屋搜索页面"""
# 1. 获取参数
start_date = request.args.get('sd', '')
end_date = request.args.get('ed', '')
area_id = request.args.get('aid', '')
sort_key = request.args.get('sk', 'new')
page = request.args.get('p')
# 2. 检查参数
# 检查时间
try:
if start_date:
start_date = datetime.strptime(start_date, '%Y-%m-%d').date()
if end_date:
end_date = datetime.strptime(end_date, '%Y-%m-%d').date()
if start_date and end_date:
assert start_date <= end_date
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg='日期参数错误')
# 检查区域id是否存在
try:
area = Area.query.get(area_id)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg='区域参数错误')
# 页面参数
try:
page = int(page)
except Exception as e:
current_app.logger.error(e)
page = 1
# 优先在redis数据库中查询
redis_key = 'houses_%s_%s_%s_%s' % (start_date, end_date, area_id,
sort_key)
try:
resp = redis_store.hget(redis_key, page)
except Exception as e:
current_app.logger.error(e)
if resp:
current_app.logger.info('hit houses list in redis')
return resp, 200, {'Content-Type': 'application/json'}
# 3. 查询数据库
# 过滤条件的参数列表容器
filter_param = []
# 填充过滤条件
# 区域
if area:
filter_param.append(House.area_id == area_id)
# 时间
confilct_orders = []
try:
if start_date and end_date:
confilct_orders = Order.query.filter(
Order.begin_date <= end_date,
Order.end_date >= start_date).all()
elif start_date:
confilct_orders = Order.query.filter(
Order.end_date >= start_date).all()
elif end_date:
confilct_orders = Order.query.filter(Order.start <= end_date).all()
except Exception as e:
current_app.logger.error(e)
confilct_house_ids = [order.house_id for order in confilct_orders]
filter_param.append(House.id.notin_(confilct_house_ids))
# 排序
# 查询数据库
houses_query = House.query.filter(*filter_param)
if 'booking' == sort_key:
houses_query = houses_query.order_by(House.room_count.desc())
elif 'price-inc' == sort_key:
houses_query = houses_query.order_by(House.price.asc())
elif 'price-des' == sort_key:
houses_query = houses_query.order_by(House.price.desc())
else:
houses_query = houses_query.order_by(House.create_time.desc())
# 分页
try:
# 获取数据时才真正与数据库交互，前面都是构建查询条件
page_obj = houses_query.paginate(
page=page,
per_page=constants.HOUSE_LIST_PAGE_CAPACITY,
error_out=False)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg='数据库异常')
# 4. 组建返回数据
houses = [house.to_basic_dict() for house in page_obj.items]
total_page = page_obj.pages
resp_dict = dict(errno=RET.OK,
errmsg='OK',
data={
'houses': houses,
'page': page,
'total_page': total_page
})
resp_json = json.dumps(resp_dict)
# 5. 添加到redis缓存
if page <= total_page:
try:
pipeline = redis_store.pipeline()
pipeline.multi()
pipeline.hset(redis_key, page, resp_json)
pipeline.expire(redis_key,
constants.HOUSE_LIST_REDIS_CACHE_EXPIRES)
pipeline.execute()
except Exception as e:
current_app.logger.error(e)
return resp_json, 200, {'Content-Type': 'application/json'}
def get_house_list():
"""获取房屋的列表信息（搜索页面）"""
start_date = request.args.get("sd", "") # 用户想要起始时间
end_date = request.args.get("ed", "") # 用户想要的结束时间
area_id = request.args.get("aid", "") # 区域编号
sort_key = request.args.get("sk", "new") # 排序关键字, 默认排序为new
page = request.args.get("p") # 分页页数
# 参数判断——处理时间
try:
if start_date:
# 将时间字符串转换为时间类型 strftime（将时间类型转换为字符串）
start_date = datetime.strptime(start_date, "%Y-%m-%d")
if end_date:
end_date = datetime.strptime(end_date, "%Y-%m-%d")
if start_date and end_date:
assert start_date <= end_date
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="日期参数错误")
# 判断区域id
if area_id:
try:
area = Area.query.get(area_id)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="区域参数有误")
# 处理页数
try:
page = int(page)
except Exception as e:
current_app.logger.error(e)
page = 1
# 获取缓存数据
redis_key = "house_%s_%s_%s_%s" % (start_date, end_date, area_id, sort_key)
try:
resp_json = redis_store.hget(redis_key, page)
except Exception as e:
current_app.logger.error(e)
else:
if resp_json: # 缓存数据存在
return resp_json, 200, {"Content-Type": "application/json"}
# 过滤条件参数列表容器
filter_params = []
# 填充过滤参数——时间条件
conflict_orders = None
try:
if start_date and end_date: # 都传了
conflict_orders = Order.query.filter(
Order.begin_data <= end_date,
Order.end_data >= start_date).all()
elif start_date: # 传入起始时间
conflict_orders = Order.query.filter(
Order.end_data >= start_date).all()
elif end_date: # 传入结束时间
conflict_orders = Order.query.filter(
Order.begin_data <= end_date).all()
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
if conflict_orders:
# 从订单中获取冲突的房屋id
conflict_house_id = [order.house_id for order in conflict_orders]
# 如果冲突的房屋id不为空，向查询参数添加条件
filter_params.append(House.id.notin_(conflict_house_id))
# 区域条件
if area_id:
filter_params.append(House.area_id == area_id)
# 查询数据库——补充排序条件
if sort_key == "booking": # 入住最多
house_query = House.query.filter(*filter_params).order_by(
House.order_count.desc())
elif sort_key == "price-inc": # 价格 低->高
house_query = House.query.filter(*filter_params).order_by(
House.price.asc())
elif sort_key == "price-des": # 高->低
house_query = House.query.filter(*filter_params).order_by(
House.price.desc())
else: # 默认排序时间最新
house_query = House.query.filter(*filter_params).order_by(
House.create_time.desc())
# 分页处理
try:
# 当前页数 每页数据量 自动错误输出
page_obj = house_query.paginate(
page=1,
per_page=constants.HOUSE_LIST_PAGE_CAPACITY,
error_out=False)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
# 获取页面数据
house_li = page_obj.items
houses = []
for house in house_li:
houses.append(house.to_basic_dict())
# 获取总页数
total_page = page_obj.pages
# 将结果转化为json字符串
resp_dict = dict(errno=RET.OK,
errmsg="OK",
data={
"total_page": total_page,
"houses": houses,
"current_page": page
})
resp_json = json.dumps(resp_dict)
# 防止当用户访问页数超过总页数，出现多余的缓存
if page <= total_page:
# 设置缓存——缓存用户查询的页面数据
# "house_起始_结束_区域id_排序_页数"
redis_key = "house_%s_%s_%s_%s" % (start_date, end_date, area_id,
sort_key)
try:
# todo:redis管道需要多加熟悉！
# 创建一个pipeline（管道）对象，可以一次性执行多个语句
pipeline = redis_store.pipeline()
# 开启多个语句的记录
pipeline.multi()
# 设置缓存类型——哈希 "1": "{}",
pipeline.hset(redis_key, page, resp_json)
pipeline.expire(
redis_key,
constants.HOUSE_LIST_PAGE_REDIS_CACHE_EXPIRES) # 设置缓存时间
# 执行语句
pipeline.execute()
except Exception as e:
current_app.logger.error(e)
return resp_json, 200, {"Content-Type": "application/json"}
def get_house_list():
# 获取参数
area_id = request.args.get('aid', '')
start_date = request.args.get('sd', '')
end_date = request.args.get('ed', '')
sort_key = request.args.get('sk', 'new')
page = request.args.get('page')
print('params: %s--%s--%s--%s' % (area_id, start_date, end_date, sort_key))
# 参数校验
# 校验日期
try:
if start_date:
start_date = datetime.strptime(start_date, '%Y-%m-%d')
if end_date:
end_date = datetime.strptime(end_date, '%Y-%m-%d')
if start_date and end_date:
assert start_date <= end_date
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg=u'日期参数错误')
# 校验页码
try:
page = int(page)
except Exception as e:
current_app.logger.error(e)
page = 1
if page <= 0:
page = 1
# 校验区域
if area_id:
try:
area = Area.query.get(area_id)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg=u'区域格式错误')
# 业务处理: 查询并返回满足条件的房屋数据
# 查询缓存
try:
redis_key = 'houses_list_%s_%s_%s_%s' % (start_date, end_date, area_id, sort_key)
resp_json = redis_store.hget(redis_key, page)
except Exception as e:
current_app.logger.error(e)
else:
if resp_json:
current_app.logger.info('hit redis cache: %s' % redis_key)
return resp_json, 200, {'Content-Type': 'application/json'}
# 构造房屋查询条件列表容器
filter_params = []
# 构造时间条件
conflict_orders = None
if start_date and end_date:
conflict_orders = Order.query.filter(Order.begin_date < end_date and Order.end_date > start_date).all()
elif start_date:
conflict_orders = Order.query.filter(Order.end_date > start_date).all()
elif end_date:
conflict_orders = Order.query.filter(Order.begin_date < end_date).all()
if conflict_orders is not None:
conflict_ids = [order.id for order in conflict_orders]
filter_params.append(House.id.notin_(conflict_ids))
# 构造区域条件
print(area_id)
if area_id:
filter_params.append(House.area_id==area_id)
# 查询房屋数据
if sort_key == 'booking':
house_query = House.query.filter(*filter_params).order_by(House.order_count.desc())
elif sort_key == 'price-inc':
house_query = House.query.filter(*filter_params).order_by(House.price.asc())
elif sort_key == 'price-des':
house_query = House.query.filter(*filter_params).order_by(House.price.desc())
else:
house_query = House.query.filter(*filter_params).order_by(House.create_time.desc())
# 分页
try:
paginator = house_query.paginate(page=page, per_page=constants.PER_PAGE_CAPACITY, error_out=False)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg=u'数据库错误')
houses = []
for house in paginator.items:
houses.append(house.to_full_dict())
total_page_num = paginator.pages
resp_data = dict(errno=RET.OK, errmsg=u'查询成功', data={'houses': houses, 'total_page': total_page_num, 'current_page': page})
resp_json = json.dumps(resp_data)
# 添加缓存
if page <= total_page_num:
redis_key = 'houses_list_%s_%s_%s_%s' % (start_date, end_date, area_id, sort_key)
redis_store.hset(redis_key, page, resp_json)
redis_store.expire(redis_key, constants.HOUSE_LIST_REDIS_CACHE_EXPIRE)
return resp_json, 200, {'Content-Type': 'application/json'}
def get_houses_list():
"""获取房屋列表页(搜索页)"""
# 获取参数
start_date = request.args.get("sd", "") # 开始日期
end_date = request.args.get("ed", "") # 结束日期
area_id = request.args.get("aid", "") # 区域日期
sort_key = request.args.get("sk", default="new") # 搜索关键字
page = request.args.get("p") # 当前页数
# 校验时间
try:
if start_date:
start_date = datetime.strptime(start_date, "%Y-%m-%d")
if end_date:
end_date = datetime.strptime(end_date, "%Y-%m-%d")
if start_date and end_date:
assert start_date <= end_date
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="日期格式不对")
# 区域校验
if area_id:
try:
area = Area.query.get(area_id)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="地区不存在")
# 页数校验
try:
page = int(page)
except Exception as e:
current_app.logger.error(e)
page = 1
# 获取redis中的数据
redis_key = "house_list_%s_%s_%s_%s" % (start_date, end_date, area_id,
sort_key)
try:
resp_json = redis_store.hget(redis_key, page)
except Exception as e:
current_app.logger.error(e)
else:
if resp_json:
return resp_json, 200, {"Content-Type": "application/json"}
# 定义与预定时间冲突的订单列表
conflict_orders = None
# 定义数据库查询顾虑条件列表
param_list = []
# 数据库中获取冲突的订单
try:
if start_date and end_date:
conflict_orders = Order.query.filter(
Order.begin_date <= end_date,
Order.end_date >= start_date).all()
elif start_date:
conflict_orders = Order.query.filter(
Order.end_date >= start_date).all()
elif end_date:
conflict_orders = Order.query.filter(
Order.begin_date <= end_date).all()
except Exception as e:
current_app.logger(e)
return jsonify(errno=RET.DBERR, errmsg="数据库查询订单错误")
if conflict_orders:
# 从定单中获取冲突房屋的id
conflict_id = [order.house_id for order in conflict_orders]
# 如果冲突的id不为空拼接到参数列表
if conflict_id:
param_list.append(House.id.notin_(conflict_id))
if area_id:
param_list.append(House.area_id == area_id)
# 获取分页搜索集
if sort_key == "booking": # 订单倒序排列
houses_query = House.query.filter(*param_list).order_by(
House.order_count.desc())
elif sort_key == "price-inc": # 价格由低到高
houses_query = House.query.filter(*param_list).order_by(
House.price.asc())
elif sort_key == "price-des": # 价格由高到低
houses_query = House.query.filter(*param_list).order_by(
House.price.desc())
else: # 时间倒序
houses_query = House.query.filter(*param_list).order_by(
House.create_time.desc())
# 获取查询集的分页对象
try:
# 当前页数 分页每页条数 错误显示
house_obj = houses_query.paginate(page=page,
per_page=constants.HOUSES_PERPAGE,
error_out=False)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="查询数据库房屋错误")
# 分页内容
houses_li = house_obj.items
houses = []
for house in houses_li:
houses.append(house.to_basic_dict())
# 分页总条数
total_page = house_obj.pages
# 判断是否设置缓存
if page <= total_page:
# 设置缓存
resp_dict = dict(errno=RET.OK,
errmsg="获取成功",
data={
"total_page": total_page,
"houses": houses,
"current_page": page
})
resp_json = json.dumps(resp_dict)
redis_key = "house_list_%s_%s_%s_%s" % (start_date, end_date, area_id,
sort_key)
try:
redis_store.hset(redis_key, page, resp_json)
redis_store.expire(redis_key, constants.HOUSES_LIST_REDIS_TIME)
except Exception as e:
current_app.logger.error(e)
return resp_json, 200, {"Content-Type": "application/json"}
def get_house_list():
"""搜索页面，获取房屋的列表页信息"""
start_date = request.args.get("sd", "") # 起始时间
end_date = request.args.get("ed", "") # 结束时间
area_id = request.args.get("aid", "") # 区域编号
sort_key = request.args.get("sk", "new") # 排序关键字
page_num = request.args.get("p", "") # 页数
# 处理时间
try:
if start_date:
# 字符串转换成时间
start_date = datetime.strptime(start_date, "%Y-%m-%d")
if end_date:
end_date = datetime.strptime(end_date, "%Y-%m-%d")
if start_date and end_date:
assert start_date <= end_date
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="日期参数有误")
# 判断区域id
if area_id:
try:
area = Area.query.get(area_id)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="区域参数有误")
# 处理页数
try:
page_num = int(page_num)
except Exception as e:
current_app.logger.error(e)
page_num = 1
# 先从缓存中查询
redis_key = "house_%s_%s_%s_%s" % (start_date, end_date, area_id, sort_key)
try:
resp_json = redis_store.hget(redis_key, page_num)
except Exception as e:
current_app.logger.error(e)
else:
if resp_json:
return resp_json, 200, {"Content-Type": "application/json"}
# 过滤条件的参数容器
filter_params = []
conflict_orders = None
try:
if start_date and end_date:
# 查询出时间冲突的订单，然后取反排除掉
conflict_orders = Order.query.filter(Order.begin_date <= end_date,
Order.end_date >= start_date)
elif start_date:
conflict_orders = Order.query.filter(Order.end_date >= start_date)
elif end_date:
conflict_orders = Order.query.filter(Order.begin_date <= end_date)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
if conflict_orders:
conflict_house_ids = [order.house_id for order in conflict_orders]
if conflict_house_ids:
filter_params.append(House.id.notin_(conflict_house_ids))
# 区域条件
if area_id:
filter_params.append(House.area_id == area_id)
# 查询mysql数据库,结果分页
if sort_key == "booking":
house_query = House.query.filter(*filter_params).order_by(
House.order_count.desc())
elif sort_key == "price-inc":
house_query = House.query.filter(*filter_params).order_by(
House.price.asc())
elif sort_key == "price-des":
house_query = House.query.filter(*filter_params).order_by(
House.price.desc())
else:
house_query = House.query.filter(*filter_params).order_by(
House.create_time.desc())
try:
page_obj = house_query.paginate(
page=page_num,
per_page=constants.HOUSE_LIST_PAGE_CAPACITY,
error_out=False)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
# 获取页面数据
house_li = page_obj.items
houses = []
for house in house_li:
houses.append(house.to_basic_dict())
# 总页数
total_page = page_obj.pages
resp_dict = dict(errno=RET.OK,
errmsg="OK",
data={
"total_page": total_page,
"houses": houses,
"current_page": page_num
})
resp_json = json.dumps(resp_dict)
# 设置缓存数据
if page_num <= total_page:
redis_key = "house_%s_%s_%s_%s" % (start_date, end_date, area_id,
sort_key)
try:
# 使用pipeline，一次执行多条命令
pipeline = redis_store.pipeline()
pipeline.multi()
pipeline.hset(redis_key, page_num, resp_json)
pipeline.expire(redis_key,
constants.HOUSES_LIST_PAGE_REDIS_CACHE_EXPIRES)
# 执行
pipeline.execute()
except Exception as e:
current_app.logger.error(e)
return resp_json, 200, {"Content-Type": "application/json"}
def get_house_list():
"""获取房屋列表信息"""
# 获取参数
start_date_str = request.args.get("sd", "") # 想要查询的起始时间
end_date_str = request.args.get("ed", "") # 想要查询的终止时间
area_id = request.args.get("aid", "") # 区域id
sort_key = request.args.get("sk", "new") # 排序关键字
page = request.args.get("p", 1) # 页数
# 校验参数
# 判断日期
try:
start_date = None
if start_date_str:
start_date = datetime.strptime(start_date_str, "%Y-%m-%d")
end_date = None
if end_date_str:
end_date = datetime.strptime(end_date_str, "%Y-%m-%d")
if start_date and end_date:
assert start_date <= end_date
except Exception as e:
return jsonify(errno=RET.PARAMERR, errmsg="日期参数有误")
# 判断页数
try:
page = int(page)
except Exception:
page = 1
# 先从redis缓存中获取数据
try:
redis_key = "houses_%s_%s_%s_%s" % (start_date_str, end_date_str,
area_id, sort_key)
resp_json = redis_store.hget(redis_key, page)
except Exception as e:
current_app.logger.error(e)
resp_json = None
if resp_json:
# 表示从缓存中拿到了数据
return resp_json, 200, {"Content-Type": "application/json"}
# 查询数据
filter_params = []
# 处理区域信息
if area_id:
filter_params.append(House.area_id == area_id)
# 处理时间
try:
conflict_orders_li = []
if start_date and end_date:
# 从订单表中查询冲突的订单，进而获取冲突的房屋id
conflict_orders_li = Order.query.filter(
Order.begin_date <= end_date,
Order.end_date >= start_date).all()
elif start_date:
# 从订单表中查询冲突的订单，进而获取冲突的房屋id
conflict_orders_li = Order.query.filter(
Order.end_date >= start_date).all()
elif end_date:
# 从订单表中查询冲突的订单，进而获取冲突的房屋id
conflict_orders_li = Order.query.filter(
Order.begin_date <= end_date).all()
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
if conflict_orders_li:
conflict_house_id_li = [order.house_id for order in conflict_orders_li]
# 添加条件，查询不冲突的房屋
filter_params.append(House.id.notin_(conflict_house_id_li))
# 排序
if sort_key == "booking":
house_query = House.query.filter(*filter_params).order_by(
House.order_count.desc())
elif sort_key == "price-inc":
house_query = House.query.filter(*filter_params).order_by(
House.price.asc())
elif sort_key == "price-des":
house_query = House.query.filter(*filter_params).order_by(
House.price.desc())
else:
house_query = House.query.filter(*filter_params).order_by(
House.create_time.desc())
# 分页 sqlalchemy的分页
try:
# 页数 每页数量 错误输出
house_page = house_query.paginate(page,
constants.HOUSE_LIST_PAGE_CAPACITY,
False)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
house_li = house_page.items # 当前页中的数据结果
total_page = house_page.pages # 总页数
houses = []
for house in house_li:
houses.append(house.to_basic_dict())
# 将结果转换json字符串
resp = dict(errno=RET.OK,
errmsg="查询成功",
data={
"houses": houses,
"total_page": total_page,
"current_page": page
})
resp_json = json.dumps(resp)
# 将结果缓存到redis中
if page <= total_page:
# 用redis的哈希类型保存分页数据
redis_key = "houses_%s_%s_%s_%s" % (start_date_str, end_date_str,
area_id, sort_key)
try:
# 使用redis中的事务
pipeline = redis_store.pipeline()
# 开启事务
pipeline.multi()
pipeline.hset(redis_key, page, resp_json)
pipeline.expire(redis_key, constants.HOUSE_LIST_PAGE_REDIS_EXPIRES)
# 执行事务
pipeline.execute()
except Exception as e:
current_app.logger.error(e)
return resp_json, 200, {"Content-Type": "application/json"}
def get_house_list():
"""房源页房屋列表信息"""
# 获取请求参数
start_date = request.args.get("sd", "") # 用户入住日期
end_date = request.args.get("ed", "") # 用户离开日期
area_id = request.args.get("aid", "") # 入住区县
sort_key = request.args.get("sk", "new") # 排序关键字,当未选择排序条件时，默认按最新排序，这个new关键字根据前端定义走的
page = request.args.get("p") # 页数
redis_key = "house_%s_%s_%s_%s" % (start_date, end_date, area_id, sort_key)
# 判断 有没有缓存
try:
resp_json = redis_store.hget(redis_key, page)
except Exception as e:
current_app.logger.error(e)
else:
if resp_json:
return resp_json, 200, {"Content-Type": "application/json"}
# 处理日期 用户可能选择入住日期或者是离开日期，所以要一一进行判断
try:
if start_date:
start_date = datetime.strptime(start_date, "%Y-%m-%d")
if end_date:
end_date = datetime.strptime(end_date, "%Y-%m-%d")
# 当用户两者都选择情况下，需要进行判断，入住日期肯定是小于或等于离开日期的
if start_date and end_date:
assert start_date <= end_date
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="日期参数有误")
# 判断区县id
if area_id:
try:
area = Area.query.get(area_id)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="区县参数有误")
# 处理页数
try:
page = int(page)
except Exception as e:
# 如果出现异常则使page=1
page = 1
current_app.logger.error(e)
# 定义过滤条件的参数列表容器以及存放冲突订单对象
filter_params = []
conflict_orders = None
#在数据库中查询订单表中的冲突订单，
# 这里稍微比较绕，就是以什么条件来筛选订单中冲突的订单，
# 其实简单一句就是用户不管选择的入住日期或者是离开日期又或者是入住以及离开日期，
# 这三种情况中任一情况的日期都不能在Order表中订单起始日期begin_date与end_date结束日期这范围内，
# 即作出以下逻辑判断
try:
if start_date and end_date:
# 查询冲突的订单所有对象
conflict_orders = Order.query.filter(Order.begin_date <= end_date, Order.end_date >= start_date).all()
elif start_date:
# 用户只选择入住日期
conflict_orders = Order.query.filter(Order.end_date >= start_date).all()
elif end_date:
# 用户只选择离开日期
conflict_orders = Order.query.filter(Order.begin_date <= end_date).all()
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
# 当获取的冲突订单对象存在时，获取冲突房屋的id，如果冲突的房屋id不为空，则向查询参数中添加条件
if conflict_orders:
# 从订单中获取冲突的房屋id
conflict_house_ids = [order.house_id for order in conflict_orders] # 使用列表生成式进行简写操作
# 如果冲突的房屋id不为空，向查询参数中添加条件
if conflict_house_ids:
filter_params.append(House.id.notin_(conflict_house_ids))
if area_id:
filter_params.append(House.area_id == area_id)
# 根据过滤参数列表，查询数据库，并进行条件排序
if sort_key == "booking": # 入住做多
house_query = House.query.filter(*filter_params).order_by(House.order_count.desc())
elif sort_key == "price-inc": # 价格低-高
house_query = House.query.filter(*filter_params).order_by(House.price.asc())
elif sort_key == "price-des": # 价格高-低
house_query = House.query.filter(*filter_params).order_by(House.price.desc())
else:
# 如果用户什么都没选择，则按照最新排序（数据库字段创建时间）
house_query = House.query.filter(*filter_params).order_by(House.id.desc())
# 分页处理 paginate方法需传递三个参数，page:分页页数 per_page:每页显示多少条数据 error_out: 错误输出
try:
page_obj = house_query.paginate(page=page, per_page=constants.HOUSE_LIST_PAGE_CAPACITY, error_out=False)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
# 获取分页页面数据
houses = []
house_list = page_obj.items
for house in house_list:
houses.append(house.to_basic_dict())
# 获取总页数，并返回正确响应数据
total_page = page_obj.pages
# 将响应数据构建成json格式数据
resp_dict = dict(errno=RET.OK, errmsg="OK", data={"houses": houses, "total_page": total_page, "current_page": page})
resp_json = json.dumps(resp_dict)
# 设置redis数据库的key
redis_key = "house_%s_%s_%s_%s" % (start_date, end_date, area_id, sort_key)
try:
# 创建redis pipeline 管道对象，可以一次性执行多条语句
pipeline = redis_store.pipeline()
# 开启多个语句的记录
pipeline.multi()
# 使用管道对象管理多条语句
pipeline.hset(redis_key, page, resp_json) # 将数据存入redis数据
pipeline.expire(redis_key, constants.HOUES_LIST_PAGE_REDIS_CACHE_EXPIRES) # 设置有效期
# 执行语句
pipeline.execute()
except Exception as e:
current_app.logger.error(e)
return resp_json, 200, {"Content-Type": "application/json"}
def get_house_list():
"""获取房屋的列表信息（搜索页面）"""
start_date = request.args.get("sd", "") # 用户想要的起始时间
end_date = request.args.get("ed", "") # 用户想要的结束时间
area_id = request.args.get("aid", "") # 区域编号
sort_key = request.args.get("sk", "new") # 排序关键字
page = request.args.get("p") # 页数
# 处理时间
try:
if start_date:
start_date = datetime.strptime(start_date, "%Y-%m-%d")
if end_date:
end_date = datetime.strptime(end_date, "%Y-%m-%d")
if start_date and end_date:
assert start_date <= end_date
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="日期参数有误")
# 判断区域id
if area_id:
try:
area = Area.query.get(area_id)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="区域参数有误")
# 处理页数
try:
page = int(page)
except Exception as e:
current_app.logger.error(e)
page = 1
# 获取缓存数据
redis_key = "house_%s_%s_%s_%s" % (start_date, end_date, area_id, sort_key)
try:
resp_json = redis_store.hget(redis_key, page)
except Exception as e:
current_app.logger.error(e)
else:
if resp_json:
return resp_json, 200, {"Content-Type": "application/json"}
# 过滤条件的参数列表容器
filter_params = []
# 填充过滤参数
# 时间条件
conflict_orders = None
try:
if start_date and end_date:
# 查询冲突的订单
conflict_orders = Order.query.filter(
Order.begin_date <= end_date,
Order.end_date >= start_date).all()
elif start_date:
conflict_orders = Order.query.filter(
Order.end_date >= start_date).all()
elif end_date:
conflict_orders = Order.query.filter(
Order.begin_date <= end_date).all()
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
if conflict_orders:
# 从订单中获取冲突的房屋id
conflict_house_ids = [order.house_id for order in conflict_orders]
# 如果冲突的房屋id不为空，向查询参数中添加条件
if conflict_house_ids:
filter_params.append(House.id.notin_(conflict_house_ids))
# 区域条件
if area_id:
filter_params.append(House.area_id == area_id)
# 查询数据库
# 补充排序条件
if sort_key == "booking": # 入住做多
house_query = House.query.filter(*filter_params).order_by(
House.order_count.desc())
elif sort_key == "price-inc":
house_query = House.query.filter(*filter_params).order_by(
House.price.asc())
elif sort_key == "price-des":
house_query = House.query.filter(*filter_params).order_by(
House.price.desc())
else: # 新旧
house_query = House.query.filter(*filter_params).order_by(
House.create_time.desc())
# 处理分页
try:
# 当前页数 每页数据量 自动的错误输出
page_obj = house_query.paginate(
page=page,
per_page=constants.HOUSE_LIST_PAGE_CAPACITY,
error_out=False)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
# 获取页面数据
house_li = page_obj.items
houses = []
for house in house_li:
houses.append(house.to_basic_dict())
# 获取总页数
total_page = page_obj.pages
resp_dict = dict(errno=RET.OK,
errmsg="OK",
data={
"total_page": total_page,
"houses": houses,
"current_page": page
})
resp_json = json.dumps(resp_dict)
if page <= total_page:
# 设置缓存数据
redis_key = "house_%s_%s_%s_%s" % (start_date, end_date, area_id,
sort_key)
# 哈希类型
try:
# redis_store.hset(redis_key, page, resp_json)
# redis_store.expire(redis_key, constants.HOUES_LIST_PAGE_REDIS_CACHE_EXPIRES)
# 创建redis管道对象，可以一次执行多个语句
pipeline = redis_store.pipeline()
# 开启多个语句的记录
pipeline.multi()
pipeline.hset(redis_key, page, resp_json)
pipeline.expire(redis_key,
constants.HOUES_LIST_PAGE_REDIS_CACHE_EXPIRES)
# 执行语句
pipeline.execute()
except Exception as e:
current_app.logger.error(e)
return resp_json, 200, {"Content-Type": "application/json"}
def get_house_list():
"""获取房屋列表信息"""
# 获取参数
start_date_str = request.args.get("sd", "") # 想要查询的起始时间
end_date_str = request.args.get("ed", "") # 想要查询的终止时间
area_id = request.args.get("aid", "") # 区域id
sort_key = request.args.get("sk", "new") # 排序关键字
page = request.args.get("p", 1) # 页数
# 校验参数
# 判断日期
try:
start_date = None
if start_date_str:
start_date = datetime.strftime(start_date_str, "%Y-%m-%d")
end_date = None
if end_date_str:
end_date = datetime.strftime(end_date_str, "%Y-%m-%d")
if start_date and end_date:
assert start_date <= end_date
except Exception as e:
return jsonify(error_code=RET.PARAMERR, errmsg="日期参数有误")
# 判断页数
try:
page = int(page)
except Exception as e:
page = 1
# 先从redis缓存中获取数据
try:
redis_key = "houses_%s_%s_%s_%s" % (start_date_str, end_date_str, area_id, sort_key)
resp_json = redis_store.hget(redis_key, page)
except Exception as e:
current_app.logger.error(e)
resp_json = None
if resp_json:
# 从redis缓存中获取到了数据
return resp_json, 200, {"Content-Type": "application/json"}
# 查询数据
filter_params = []
# 处理区域信息
if area_id:
filter_params.append(House.area_id == area_id)
# 处理时间
try:
conflict_orders_li = []
if start_date and end_date:
# 从订单表中查询冲突的订单，进而获取冲突的房屋id
conflict_orders_li = Order.query.filter(Order.begin_date <= end_date, Order.end_date >= start_date).all()
elif start_date:
# 从订单表中查询冲突的订单，进而获取冲突的房屋id
conflict_orders_li = Order.query.filter(Order.end_date >= start_date).all()
elif end_date:
# 从订单表中查询冲突的订单，进而获取冲突的房屋id
conflict_orders_li = Order.query.filter(Order.begin_date <= end_date).all()
except Exception as e:
current_app.logger.error(e)
return jsonify(error_code=RET.DBERR, errmsg="数据库异常")
if conflict_orders_li:
conflict_orders_id_li = [order.house_id for order in conflict_orders_li]
# 添加条件，查询不冲突的房屋
filter_params.append(House.id.notin_(conflict_orders_id_li))
# 排序
if sort_key == "booking":
house_query = House.query.filter(*filter_params).order_by(House.order_count.desc())
elif sort_key == "price-inc":
house_query = House.query.filter(*filter_params).order_by(House.price.asc())
elif sort_key == "price-des":
house_query = House.query.filter(*filter_params).order_by(House.price.desc())
else:
house_query = House.query.filter(*filter_params).order_by(House.create_time.desc())
# 分页
try:
house_page = house_query.paginate(page, constants.HOUSE_LIST_PAGE_CAPACITY, False)
except Exception as e:
current_app.logger.error(e)
return jsonify(error_code=RET.DBERR, errmsg="数据库异常")
house_li = house_page.items # 当前页中的数据结果
total_page = house_page.pages # 总页数
houses = []
for house in house_li:
houses.append(house.to_basic_dict())
# 将结果缓存到redis中
# 将结果转换成json字符串
resp = dict(error_code=RET.OK, errmsg="查询成功",
data={"houses": houses, "total_page": total_page, "current_page": page})
resp_json = json.dumps(resp)
if page <= total_page:
# 用redis的哈希类型保存分页数据
redis_key = "houses_%s_%s_%s_%s" % (start_date_str, end_date_str, area_id, sort_key)
try:
# 使用redis中的事务
pipeline = redis_store.pipeline()
# 开启事务
pipeline.multi()
pipeline.hset(redis_key, page, resp_json)
pipeline.expire(redis_key, constants.HOUSE_LIST_PAGE_REDIS_EXPIRES)
# 执行事务
pipeline.execute()
except Exception as e:
current_app.logger.error(e)
return resp_json, 200, {"Content-Type": "application/json"}
