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_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_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', '') # 用户输入的区域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_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_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 search_houses():
"""
1.获取参数
2.校验参数
3.
:return:
"""
# 1.获取所有参数
args = request.args
area_id = args.get("aid", '')
start_date_str = args.get("sd", '')
end_date_str = args.get('ed', '')
# booking(订单量),price_inc(低到高),price_desc(高到底)
sort_key = args.get('sk', 'new')
page = args.get('p', 1)
# 参数校验
try:
page = int(page)
start_date = None
end_date = 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 and end_date:
assert start_date < end_date, Exception('开始时间大于结束时间')
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="参数错误")
# 查询redis,看是否有缓存数据
try:
redis_key = "houses_%s_%s_%s_%s" % (area_id, start_date_str,
end_date_str, sort_key)
response_data = redis_store.hget(redis_key, page)
if response_data:
current_app.logger.info('load data from redis')
return jsonify(errno=RET.OK,
errmsg='获取成功',
data=eval(response_data))
except Exception as e:
current_app.logger.error(e)
# 打印参数
# print "area_id=%s,sd=%s,ed=%s,sk=%s,page=%s" % (area_id, start_date_str, end_date_str, sort_key, page)
# 查询所有房屋
# houses = House.query.all()
# 查询房屋数据
try:
houses_query = House.query
# 如果区域id存在
if area_id:
houses_query = houses_query.filter(House.area_id == area_id)
# 增加时间筛选
conflict_orders = [] # 定义数组保存冲突的订单
if start_date and end_date:
# 如果订单的开始时间 < 结束时间 and 订单的结束时间 > 开始时间
conflict_orders = Order.query.filter(start_date < Order.end_date,
end_date > Order.begin_date)
elif start_date:
# 订单的结束时间 > 开始时间
conflict_orders = Order.quert.filter(
start_date < Order.end_date).all()
elif end_date:
# 订单的开始时间 < 开始时间
conflict_orders = Order.query.filter(
end_date > Order.begin_date).all()
# 通过冲突的订单找出冲突的房子编号, 然后添加到查询条件中
if conflict_orders:
# 取到冲突订单的房屋id
conflict_houses_id = [order.house_id for order in conflict_orders]
# 添加条件:查询出来的房屋不包括冲突订单中的房屋id
houses_query = houses_query.filter(
House.id__notin(conflict_houses_id))
# 2.2判断排序的方式
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.asc())
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())
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据库查询异常")
# 使用paginate进行分页
house_pages = houses_query.paginate(page,
constants.HOUSE_LIST_PAGE_CAPACITY,
False)
# 获取当前页对象
houses = house_pages.items
# 获取总页数
total_page = house_pages.pages
# 将房屋信息转换成字典列表
house_list = []
if houses:
for house in houses:
house_list.append(house.to_basic_dict())
resp = {"houses": house_list, "total_page": total_page}
# 提示 resp 用于缓存
# if len(house_list) != 0: # 判定条件一
# 如果当前page小于总页数,则表明有数据
if page <= total_page: # 判定条件二
# 将查询到的信息缓存到redis中
try:
# 获取管道对象, 用来做事物操作
pipeline = redis_store.pipeline()
# 开始事物
pipeline.multi()
redis_key = "search_%s_%s_%s_%s" % (area_id, sort_key, start_date,
end_date)
# 存储数据,设置有效期
pipeline.hset(redis_key, page, resp)
pipeline.expire(redis_key, constants.HOUSE_LIST_REDIS_EXPIRES)
# 提交事物
pipeline.execute()
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.DBERR, errmsg="数据缓存失败")
# 返回给前端
return jsonify(errno=RET.OK, errmsg="请求成功", data=resp)
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_houses_list():
"""
获取房屋列表页:缓存----磁盘----缓存
业务接口:获取参数/检查参数/业务处理/返回结果
目的:根据用户选择的参数信息,把符合条件的房屋返回给用户.
1/尝试获取参数:aid,sd,ed,sk,p
区域信息/开始日期/结束日期/
排序条件:需要给默认处理
页数:需要给默认处理
2/首先需要对日期进行格式化,
datetime.strptime(start_date_str,'%Y-%m-%d')
3/开始日期必须小于等于结束日期
4/对页数进行格式化,page = int(page)
5/尝试从redis缓存获取房屋的列表信息,使用的哈希数据类型,
redis_key = 'houses_%s_%s_%s_%s' %(area_id,start_date_str,end_date_str,sort_key)
6/判断获取结果,如果有数据,留下记录,直接返回
7/查询磁盘数据库
8/首先定义容器(存储查询语句的过滤条件,用来筛选出符合条件的房屋),存储查询的过滤条件,params_filter = []
9/判断区域参数是否存在,如果存在添加到过滤条件中
10/判断日期参数是否存在,比较用户选择的日期和数据库中订单的日期进行比较,得到满足条件的房屋
11/过滤条件的容器里已经存储了区域信息,和满足条件的房屋
12/判断排序条件,根据排序条件执行查询数据库操作,booking/price-inc/price-des/order_count.desc()
houses = House.query.filter(*params_filter).order_by(House.price.desc())
13/根据排序结果进行分页,paginate给我们的返回结果,包括总页数,房屋数据
hosues_page = houses.paginate(page,每页的条目数,False)
houses_list = houses_page.items
total_page = houses_page.pages
14/遍历房屋数据,调用模型类中的方法,获取房屋的基本数据
15/构造响应报文
resp = {
errno=RET.OK,errmsg='OK',data={"houses":houses_list,"total_page":total_page,"current_page":page}
}
16/序列化数据:resp_json = json.dumps(resp)
17/写入缓存中,判断用户选择的页数小于等于分页后的总页数,本质上是用户选择的页数是有数据的
18/构造redis_key,存储房屋列表页的缓存数据,因为使用的是hash数据类型,为了确保数据的完整性,需要使用事务;开启事务,存储数据,设置有效期,执行事务/
pip = redis_store.pipeline()
19/返回结果resp_json
:return:
"""
# 获取参数,area_id,start_date_str,end_date_str,sort_key,page
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') # 排序条件new为默认排序,房屋发布时间
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')
# 如果开始日期和结束日期都存在的情况下,需要确认用户选择的日期至少为一天
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.PARAMERR, errmsg='日期格式化错误')
# 对页数进行格式化
try:
page = int(page)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg='页数格式化错误')
# 尝试从redis缓存中获取房屋的列表数据,因为多条数据的存储,使用的hash数据类型,首先需要键值
try:
# redis_key相当于hash的对象,里面存储的是页数和对应房屋数据
redis_key = 'houses_%s_%s_%s_%s' % (area_id, start_date_str,
end_date_str, sort_key)
# 根据redis_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 redis houses list info')
# return {"errno":0,"errmsg":"OK"***}
# ret里面已经是完整的响应报文,所以可以直接返回
return ret
# 查询磁盘数据库,目的:过滤条件---->查询数据--->排序---->分页,得到满足条件的房屋
try:
# 定义容器,存储过滤条件,主要是区域信息/日期参数
params_filter = []
# 判断区域信息的存在
if area_id:
"""
a = [1,2,3]
b = 1
a.append(a==b)
a=[1,2,3,false]
"""
# 列表中添加的是sqlalchemy对象<>
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))
# 过滤条件实现后,执行查询排序操作,booking/price-inc/price-des/new
# 按成交次数排序
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())
# 对排序结果进行分页操作,page页数/每页条目数/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 = 'houses_%s_%s_%s_%s' % (area_id, start_date_str,
end_date_str, 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.error(e)
# 返回响应数据
return resp_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", "ner") # 排序打关键字,默认为new最新上新的房子
page = request.args.get("p", 1) # 页数
# 校验参数
# 判断数据是否完整
if not all([start_date_str, end_date_str, area_id, sort_key, page]):
return jsonify(errno=RET.PARAMERR, errmsg="参数不完整")
# 判断日期是否正确
# 如果起始时间,结束时间存在,则转换成%Y-%m-%d类型
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:
current_app.logger.errno(e)
return jsonify(errno=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.errno(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)
# 处理时间,查询订单冲突,获取冲突的房屋id
try:
confilct_orders_li = []
if start_date and end_date:
confilct_orders_li = Order.query.filter(
Order.begin_date <= start_date,
Order.end_date >= end_date).all()
elif start_date:
confilct_orders_li = Order.query.filter(
Order.end_date >= start_date).all()
elif end_date:
confilct_orders_li = Order.query.filter(
Order.begin_date <= end_date).all()
except Exception as e:
current_app.logger.errno(e)
return jsonify(errno=RET.DBERR, errmsg="数据库出现异常")
# 如果冲突的房屋id不为空,
if confilct_orders_li:
# 遍历获取订单中的房子id
confilct_house_id_li = [order.house_id for order in confilct_orders_li]
# 添加条件,查询不冲突的房屋id添加到列表中
filter_params.append(House.id.notin_(confilct_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.errno(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
pipeline = redis_store.pipeline()
# 开启事务 multi()
pipeline.multi()
pipeline.hset(redis_key, page, resp_json)
pipeline.expire(redis_key, constants.IMAGE_CODE_REDIS_EXPIRES)
# 执行事务,redis中事务失败会自行回滚execute()
pipeline.execute()
except Exception as e:
current_app.logger.errno(e)
# 返回数据 获取列表数据成功
return resp_json, 200, {"Content-Type": "application/json"}
def get_houses_list():
"""获取房屋分页数据"""
# 获取参数
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") # 请求的页数
# 日期格式校验
try:
start_date, end_date = None, None
# 将请求的日期字符串参数转换为datetime类型
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.PARAMERR, errmsg="日期格式不正确")
# 页数格式校验
try:
page = int(page)
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="页数格式不正确")
# 先读取redis缓存数据
try:
redis_key = "houses_%s_%s_%s_%s" % (area_id, start_date_str,
end_date_str, sort_key)
# 存放到redis中的数据用的是redis的哈希类型
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 redis")
# redis中缓存的就是完整的报文，所以可以直接返回
return ret
# 操作数据库，查询数据
try:
# 用来存储查询的过滤条件
filter_params = []
# 添加城区条件
if area_id:
filter_params.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_ids = [order.house_id for order in conflict_orders]
# 过滤不在冲突范围内的房屋
if conflict_houses_ids:
filter_params.append(House.id.notin_(conflict_houses_ids))
elif start_date:
conflict_orders = Order.query.filter(
Order.end_date >= start_date).all()
conflict_houses_ids = [order.house_id for order in conflict_orders]
if conflict_houses_ids:
filter_params.append(House.id.notin_(conflict_houses_ids))
elif end_date:
conflict_orders = Order.query.filter(
Order.begin_date <= end_date).all()
conflict_houses_ids = [order.house_id for order in conflict_orders]
if conflict_houses_ids:
filter_params.append(House.id.notin_(conflict_houses_ids))
# 添加排序条件
if "booking" == sort_key:
# 以订单量最多排序
houses = House.query.filter(*filter_params).order_by(
House.order_count.desc())
elif "price-inc" == sort_key:
# 以价格升序排序
houses = House.query.filter(*filter_params).order_by(
House.price.asc())
elif "price-des" == sort_key:
# 以价格降序排序
houses = House.query.filter(*filter_params).order_by(
House.price.desc())
else:
# 默认以最新发布排序
houses = House.query.filter(*filter_params).order_by(
House.create_time.desc())
# 查询出该页面的house模型对象列表结果，使用paginate进行分页
# 页数， 每页的条目数， 分页出错是否报错
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": RET.OK,
"errmsg": "OK",
"data": {
"houses": houses_dict_list,
"total_page": total_page,
"current_page": page
}
}
# 将返回的数据转换为json
resp_json = json.dumps(resp)
# 将数据保存到redis中缓存
# 用户请求的页数小于总页数，即请求的是有数据的页
if page <= total_page:
redis_key = "houses_%s_%s_%s_%s" % (area_id, start_date_str,
end_date_str, sort_key)
# 创建redis的工具pipeline对象，通过该对象可以一次进行多个redis操作
pipe = redis_store.pipeline()
try:
# 开启redis的事务
pipe.multi()
# 使用redis的哈希类型保存数据
pipe.hset(redis_key, page, resp_json)
# 设置保存的数据的有效期
pipe.expire(redis_key, constants.HOME_PAGE_DATA_REDIS_EXPIRES)
# 执行这个事务
pipe.execute()
except Exception as e:
current_app.logger.error(e)
return resp_json
def search_house():
logging = Use_Loggin()
# 获取参数, 可有可无
# http://127.0.0.1:5000/search.html?aid=1&aname=%E4%B8%9C%E5%9F%8E%E5%8C%BA&sd=2020-06-23&ed=2020-07-25
start_date = request.args.get(key="sd",
default="") # 用户想要的起始时间 sd=2020-06-23
end_date = request.args.get(key="ed",
default="") # 结束时间 ed=2020-07-25
area_id = request.args.get(key="aid",
default="") # 区域编号 aname=东城区
sort_key = request.args.get(key="ks", default="new") # 排序关键字
page = request.args.get(key="p", default="") # 页数
# print("1 start_date: ", start_date, type(start_date)) for test
# print("1 end_date: ", end_date, type(end_date)) for test
# print("1 area_id: ", area_id, type(area_id)) for test
# print("1 sort_key: ", sort_key, type(sort_key)) for test
# print("1 page: ",page , type(page)) for test
# 因为参数可传可不传, 所以这里参数校验没必要
# try:
# if start_date is not None:
# start_date = datetime.strftime(start_date, "%Y-%m-%d")
# print("start_date 2: ", start_date)
# if end_date is not None:
# end_date = datetime.strftime(end_date, "%Y-%m-%d")
# print("end_date 2: ", end_date)
#
# if start_date and end_date is not None:
# assert end_date <= start_date
#
# except Exception as e:
# return jsonify(errno=RET.PARAMERR, errmsg="日期输入有误")
# 区域判断
try:
areas = Area.query.get(area_id)
except Exception as e:
return jsonify(errno=RET.PARAMERR, errmsg="区域信息有误")
# 处理页数
try:
page = int(page)
except Exception as 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:
resp_json = None
logging.error("reids 获取缓存失败")
else:
if resp_json:
return resp_json, 200, {"Content-Type": "application/json"}
# 过滤条件的参数列表容器
filter_params = list()
# 形成参数: 填充过滤参数 时间条件
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:
logging.error("Order数据库查询错误")
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:
# 放进去的是表达式: __eq__()方法的执行
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:
logging.error("数据库查询错误")
return jsonify(errno=RET.DBERR, errmsg="数据库异常")
# 获取页面数据
house_li = page_obj.items
houses = []
for house in house_li:
houses.append(house.to_base_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:
logging.error("redis设置错误")
return resp_json, 200, {"Content-Type": "application/json"}
def get_house_list():
"""get inforamtion list"""
start_date = request.args.get("sd", "") # chechin time
end_date = request.args.get("ed", "") # check out time
area_id = request.args.get("aid", "") # loaction id
sort_key = request.args.get("sk", "new") # key
page = request.args.get("p") # page
# time to deal with
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="The date parameter is incorrect")
# location 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="Incorrect zone parameter")
# deal with page
try:
page = int(page)
except Exception as e:
current_app.logger.error(e)
page = 1
# Get cached data
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"}
# The parameter list container for the filter condition
filter_params = []
# Filling filter parameters
# time conditions
conflict_orders = None
try:
if start_date and end_date:
# Query for conflicting orders
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="Database exception")
if conflict_orders:
# Gets the conflicting house ID from the order
conflict_house_ids = [order.house_id for order in conflict_orders]
# If the conflicting house ID is not empty, add a condition to the query parameter
if conflict_house_ids:
filter_params.append(House.id.notin_(conflict_house_ids))
# area condition
if area_id:
filter_params.append(House.area_id == area_id)
# query database
if sort_key == "booking": # maximum
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: # new old
house_query = House.query.filter(*filter_params).order_by(
House.create_time.desc())
# Handle paging
try:
# page
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="Database exception")
# Get page data
house_li = page_obj.items
houses = []
for house in house_li:
houses.append(house.to_basic_dict())
# Get page number
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:
# Set cache data
redis_key = "house_%s_%s_%s_%s" % (start_date, end_date, area_id,
sort_key)
# Hash type
try:
# redis_store.hset(redis_key, page, resp_json)
# redis_store.expire(redis_key, constants.HOUES_LIST_PAGE_REDIS_CACHE_EXPIRES)
# Create a Redis pipe object that can execute multiple statements at once
pipeline = redis_store.pipeline()
# Enables recording of multiple statements
pipeline.multi()
pipeline.hset(redis_key, page, resp_json)
pipeline.expire(redis_key,
constants.HOUES_LIST_PAGE_REDIS_CACHE_EXPIRES)
# execute statement
pipeline.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, '%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='数据库异常')
if not area:
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管道对象，可以一次执行多个语句
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():
"""获取房屋的列表信息（搜索页面）"""
begin_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", "") # 页数
# 处理时间
try:
if begin_date:
# 时间字符串变成时间对象 2014-01-01
begin_date = datetime.strptime(begin_date, "%Y-%m-%d")
if end_date:
# 时间字符串变成时间对象 2014-01-01
end_date = datetime.strptime(end_date, "%Y-%m-%d")
if begin_date and end_date:
assert end_date > begin_date
except Exception as e:
current_app.logger.error(e)
return jsonify(errno=RET.PARAMERR, errmsg="日期参数有误")
# 判断区域id
if area_id:
try:
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
if page <= 0:
page = 1
# 从缓存中获取数据
redis_key = "house_%s_%s_%s_%s" % (begin_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("从redis中获取到缓存数据 house_%s_%s_%s_%s")
return resp_json, 200, {"Content-Type": "application/json"}
# 过滤条件的参数列表容器
filter_params = []
# 时间条件
confict_orders = None
try:
# 填充过滤参数,,,查询冲突的订单
if begin_date and end_date:
# 查询下过订单的房子，时间和我们冲突。然后把冲突的过滤掉(逆向思维)
# 查询冲突的房子
# select * from order where order.begin_date<=end_date
# and order.end_date>=begin_date;
# 查询冲突的订单
confict_orders = Order.query().filter(
Order.begin_date <= end_date,
Order.end_date >= begin_date).all()
elif begin_date:
confict_orders = Order.query.filter(
Order.end_date >= begin_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.DBERR, errmsg="数据库异常")
if confict_orders:
confict_house_ids = [order.id for order in confict_orders] # 列表推导式
if confict_house_ids:
# 这个涉及到等号参数的说明，看看demo
filter_params.append(House.id.notin_(confict_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()) # 拆包，还可以拆列表
# House.query.paginate()
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_li = page_obj.items
houses = []
for house in houses_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)
# 将数据存入redis缓存
if page <= total_page:
redis_key = "house_%s_%s_%s_%s" % (begin_date, end_date, area_id,
sort_key)
try:
# redis_store.hset(redis_key, page, resp_json)
# redis_store.expire(redis_store, constants.HOUSE_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"}
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_houses_list():
"""
获取房屋列表信息
缓存----磁盘-----缓存
获取参数/校验参数/查询数据/返回结果
1/尝试获取参数
area_id,start_date_str,end_date_str,sort_key(需要给默认值),page(需要默认值)
2/对日期参数进行格式化,datetime模块,strptime(start_date_str,'%Y-%m-%d')
3/确认用户选择开始日期和结束日期至少是1天,开始日期小于等于结束日期
4/对页数进行格式化转换,page = int(page)
5/尝试从redis数据库中,获取房屋列表信息
6/构造键:因为不同的页数和不同日期或区域,对应的是不同的房屋,需要使用hash数据类型
redis_key = 'houses_%s_%s_%s_%s' %(area_id,start_date_str,end_date_str,sort_key)
7/判断获取结果,如果有数据,记录访问的的时间,直接返回
8/查询mysql数据库
9/定义容器,存储查询的过滤条件
params_filter = [],过滤条件里存储的应该是真实的区域信息,和满足日期条件的房屋
10/对满足条件的房屋数据,进行排序操作:
booking/price-inc/price-des/new
houses = House.query.filter(*params_filter).order_by(House.create_time.desc())
11/对排序后的房屋数据进行分页操作,paginate方法返回的分页的房屋数据,分页后的总页数
houses_page = houses.paginate(page,每页的条目数,False)
houses_list = houses_page.items 分页后的房屋数据
total_page = houses_page.pages 分页后的总页数
12/定义容器,遍历分页后的房屋数据,调用模型类中to_basic_dict()
13/构造响应报文
resp = {"errno":0,"errmsg":"OK","data":{"houses":houses_dict_list,"total_page":total_page,"current_page":page}}
14/对响应数据进行序列化,转成json,存入到缓存中
resp_json = json.dumps(resp)
15/判断用户请求的页数小于等于分页后的总页数
16/多条数据写入到redis数据库中,需要使用事务
pip = redis_store.pipeline()
pip.multi() 开启事务
pip.hset(redis_key,page,resp_json) # 存储数据
pip.expire(redis_key,过期时间) # 设置过期时间
pip.execute() # 执行事务
17/返回结果
:return:
"""
# 尝试获取参数,aid,sd,ed,sk,p,区域信息/开始日期/结束日期/排序条件/页数
area_id = request.args.get('aid', '')
start_date_str = request.args.get('sd', '')
end_date_str = request.args.get('ed', '')
# 如果用户未传排序条件,默认排序new,房屋发布时间
sort_key = request.args.get('sk', 'new')
# 如果用户未传具体的页数,默认加载第一页
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:
assert start_date <= end_date
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)
return jsonify(errno=RET.PARAMERR, errmsg='页数参数错误')
# 尝试从redis中获取缓存的房屋列表信息,一个键对应多条数据的存储,需要使用hash数据类型
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:
"""
a=[1,2,3]
b=1
a.append(a==b)
a=[1,2,3,false]
b=[1,2,3]
a=[1,2,3,true]
"""
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))
# 判断排序条件,booking/price-inc/price-des/new
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())
# 对排序后的房屋数据进行分页,page表示页数/每页条目数/False分页发生异常不报错
houses_page = houses.paginate(page, constants.HOUSE_LIST_PAGE_CAPACITY,
False)
# 获取分页后的房屋数据,以及分页后的总页数
houses_list = houses_page.items # 分页后的房屋数据
total_page = houses_page.pages # 分页后的总页数
# 定义容器,遍历分页房屋数据,调用模型类中to_basic_dict()
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
redis_key = 'houses_%s_%s_%s_%s' % (area_id, start_date_str,
end_date_str, sort_key)
# 多条数据往redis中存储,为了确保有效期的一致,需要使用事务
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.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():
"""获取房屋列表信息"""
# 一. 获取参数
# 注意: 参数可以不传, 不传就把参数设为空值或者默认值
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_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():
"""
搜索房屋的信息:
"""
aid = request.args.get("aid") # 城区id
# new: 最新上线 booking: 入住最多 price-inc: 价格低->高 price-des:价格高->低
sort_key = request.args.get("sk", "new") # 排序方式，默认按照最新上线进行排序
page = request.args.get("p", 1) # 页码
sd = request.args.get("sd") # 搜索起始时间
ed = request.args.get("ed") # 搜索结束时间
start_date = None
end_date = None
print request.args
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 = houses_query.all()
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={"houses": house_dict_li})
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_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 = 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():
"""获取房屋列表页"""
#获取参数,有的需要给默认值,区域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", "")
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_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
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="日期参数有误")
# 2.2判断页数
try:
page = int(page)
except Exception:
page = 1
# 三. 业务逻辑处理
# 3.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"}
# 3.2 定义查询数据的参数空列表
filter_params = []
# 3.3 处理区域信息
if area_id:
filter_params.append(House.area_id == area_id)
# 3.4 处理时间, 获取不冲突的房屋信息
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))
# 3.5 排序
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的分页
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="数据库异常")
# 3.7 将数据转为JSON
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)
# 3.8 将结果缓存到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') #页数
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:
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 = 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")
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="日期参数有误")
try:
page = int(page)
except Exception as e:
current_app.logger.error(e)
page = 1
li = []
filter_params = []
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()
if conflict_orders:
conflict_orders_ids = [order.house_id for order in conflict_orders]
if conflict_orders_ids:
filter_params.append(House.id.notin_(conflict_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())
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())
page_obj = house_query.pageinate(page=page,
per_page=constants.HOUSE,
err_out=False)
house_li = page_obj.items
houses = []
for house in houses:
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)
pipeline = redis_store.pipeline()
pipeline.multi()
pipeline.hset(redis_key, page, resp_json)
pipeline.expire(redis_key, constants.xxxxx)
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"}
