使用flask框架实现前后端分离接口编写，并返回分页数据

1 数据模型编写

class TerminalObserveModel(db.Model):
    __tablename__ = "ods_terminal_observe_data"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    getDatatime = db.Column(db.String(200))
    addr = db.Column(db.String(200))
    loadLen = db.Column(db.String(200))
    BCCCheck = db.Column(db.String(200))
    version = db.Column(db.String(200))
    deviceID = db.Column(db.String(200))
    deviceType = db.Column(db.String(200))
    messageID = db.Column(db.String(200))
    systemTime = db.Column(db.String(200))
    CHID = db.Column(db.Text)
    CHState = db.Column(db.Text)
    CHPower = db.Column(db.Text)
    CHTime = db.Column(db.Text)
    CHTimeModify = db.Column(db.Text)
    CHWaveModify = db.Column(db.Text)
    CHWaveCycle = db.Column(db.Text)
    CHlon = db.Column(db.Text)
    CHlat = db.Column(db.Text)
    CHalt = db.Column(db.Text)
    LocationType = db.Column(db.String(200))
    lon = db.Column(db.String(200))
    lat = db.Column(db.String(200))
    alt = db.Column(db.String(200))
    header = db.Column(db.Text)
    message = db.Column(db.Text)

    # # 外键
    # ProjectID = db.Column(db.Integer, db.ForeignKey("fact_pnt_project_info.ProjectID"))
    # # 关系
    # ProjectName = db.relationship("ProjectModel", backref="terminals")
    def to_dict(self):
        return {
            "id": self.id,
            "getDatatime": self.getDatatime,
            "addr": self.addr,
            "loadLen": self.loadLen,
            "BCCCheck": self.BCCCheck,
            "version": self.version,
            "deviceID": self.deviceID,
            "deviceType": self.deviceType,
            "messageID": self.messageID,
            "systemTime": self.systemTime,
            "CHID": self.CHID,
            "CHState": self.CHState,
            "CHPower": self.CHPower,
            "CHTime": self.CHTime,
            "CHTimeModify": self.CHTimeModify,
            "CHWaveModify": self.CHWaveModify,
            "CHWaveCycle": self.CHWaveCycle,
            "CHlon": self.CHlon,
            "CHlat": self.CHlat,
            "CHalt": self.CHalt,
            "LocationType": self.LocationType,
            "lon": self.lon,
            "lat": self.lat,
            "alt": self.alt,
            "header": self.header,
            "message": self.message
        }

2 蓝图编写

蓝图中，不仅实现了分页获取接口数据，还能够通过前端传过来的参数deviceID以及开始和结束时间对数据进行一定程度的过滤，并且getDatatime字段和deviceID字段对返回的数据进行排序。

除此之外，代码中还考虑了对deviceID如果传入all，便返回所有deviceID字段的内容，如果查询条件查不到数据，便给前端返回一个空数组。这些必要的数据保护均在程序中有所考虑。

bp = Blueprint("terminal", __name__, url_prefix="/terminal")


@bp.route("/retrieve", methods=['GET', 'POST'])
def retrieveTerminal():
    if request.method == "GET":
        return jsonify({
            "status": 500,
            "info": f"request method error, please use POST method",
            "data": []
        })
    else:
        params = request.form if request.form else request.json
        page = int(params.get('page', 1))  # 设置默认的每页记录数和页码
        perpage = int(params.get('perpage', 10))  # 设置默认的每页记录数和页码
        deviceID = params.get('deviceID', 'all')
        start = params.get('startTime', None)
        end = params.get('endTime', None)
        if deviceID.lower() == "all":
            pagination = TerminalObserveModel.query \
                .filter(TerminalObserveModel.systemTime.between(start, end)) \
                .order_by(TerminalObserveModel.getDatatime.desc(), TerminalObserveModel.deviceID.asc()) \
                .paginate(page=page, per_page=perpage, error_out=False)
        else:
            pagination = TerminalObserveModel.query \
                .filter(TerminalObserveModel.systemTime.between(start, end)) \
                .filter_by(deviceID=deviceID) \
                .order_by(TerminalObserveModel.getDatatime.desc(), TerminalObserveModel.deviceID.asc()) \
                .paginate(page=page, per_page=perpage, error_out=False)
        BSqueryParams = pagination.items
        if BSqueryParams:
            response = {
                'BSqueryParams': [BSqueryParam.to_dict() for BSqueryParam in BSqueryParams],  # TerminalObserveModel 模型有一个to_dict()方法将实例转换为字典
                'total_items': pagination.total,
                'pages': pagination.pages,
                'current_page': pagination.page
            }
        else:
            response = []
        return jsonify({
            "status": 200,
            "info": f"get data success !!!",
            "data": response
        })

3 请求模拟

4 注意事项

4.1 每页记录数和页码两个参数必须是int类型，否则会报错。因为前端传过来后，无论传之前是啥类型，到后端后都是字符串类型，所以在后端代码里一定要进行int转换。见蓝图编写的代码中：

page = int(params.get('page', 1))  # 设置默认的每页记录数和页码
perpage = int(params.get('perpage', 10))  # 设置默认的每页记录数和页码

4.2 在paginate参数中一定要加上error_out=False参数，防止前端请求页码数超出实际页码数而报错：Requested URL was not found on the server.

pagination = TerminalObserveModel.query \
    .filter(TerminalObserveModel.systemTime.between(start, end)) \
    .order_by(TerminalObserveModel.getDatatime.desc(), TerminalObserveModel.deviceID.asc()) \
    .paginate(page=page, per_page=perpage, error_out=False)