最新消息:20190717 VPS服务器:Vultr新加坡,WordPress主题:大前端D8,统一介绍入口:关于

【已解决】Python的Flask中pymysql中mysql返回分页查询结果

Flask crifan 237浏览 0评论

现有Flask项目,已经可以用pymysql中sql语句去查询到已有的mysql的数据库中:

对应的数据了:

相关代码是:

pageNumber = parsedArgs["pageNumber"]
pageSize = parsedArgs["pageSize"]

searchByUserSql = "SELECT * FROM `user_storybook_list` WHERE `userId` = %d" % userId
searchByUserOk, resultDict = sqlConn.executeSql(searchByUserSql)
log.debug("%s -> %s, %s", searchByUserSql, searchByUserOk, resultDict)
if searchByUserOk and resultDict["data"]:
    foundItemList = resultDict["data"]
    respDict["data"] = foundItemList
    return jsonify(respDict)
else:
    return genRespFailDict(NotFound.code, "Not found user storybook list from userId %d" % userId)

现在需要去:

实现返回结果的分页数据

根据page的size和number返回对应数据,最好加上:

【记录】Flask项目中新增获取用户历史评测报告

中的字段:

respData = {
    "evaluationList": evaluationList,
    "curPageNum": pageNumber,
    "numPerPage": pageSize,
    "totalNum": totalCount,
    "totalPageNum": totalPageNum,
    "hasPrev": hasPrev,
    "hasNext": hasNext,
}

便于前端去分页显示

mysql select paging

MySQL Data – Best way to implement paging? – Stack Overflow

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

好像就基本够用了。

还要加上sort才行,根据此处的modifyTime

不过OFFSET含义更明确些:

SELECT column FROM table LIMIT {someLimit} OFFSET {someOffset};

其他评论又说,为了性能,避免用OFFSET,和(当数量很大时)避免用LIMIT,

不过,此处希望,在不返回所有数据的情况下,统计总数

Optimized Pagination using MySQL • Code is poetry

SELECT COUNT(*) FROM city;

好像可以用COUNT(*)去计算

Efficient Pagination Using MySQL

解释:如果用OFFSET,则被跳过的数据,其实也还是返回了,放在内存里了,所以导致占用资源,速度变慢

如果是非常大的数据量,都会导致内存不够用

建议:

  • 用LIMIT OFFEST N

  • 性能:3700次查询/秒

  • 不要用 LIMIT M,N

    • 难道和OFFSET的逻辑是一样的?

    • 性能:600次查询/秒

    mysql – pagination sql query syntax – Stack Overflow

    SELECT * FROM {$statement}
    ORDER BY datetime ASC LIMIT {$startpoint} , {$limit}

    php – Pagination using MySQL LIMIT, OFFSET – Stack Overflow

    Why Order By With Limit and Offset is Slow – Faster Pagination in Mysql

    解释是:

    LIMIT 50000, 20

    意思是:

    mysql先读取出50020个,扔掉前50000个,留下最后20个

    -》所以会导致性能很低

    实例:

    SELECT
        *
    FROM
        events
    WHERE
        date > '2010-01-01T00:00:00-00:00'
            AND event = 'editstart'
    ORDER BY date
    LIMIT 50;

    性能:

    建议:

    改用Seek

    找到对应的,想要的要跳过的一堆的值的最后一个,然后从那之后再去取

    比如:

    SELECT    *
    FROM
        events
    WHERE
        (date,id) > ('2010-07-12T10:29:47-07:00',111866)
            AND event = 'editstart'
    ORDER BY date, id
    LIMIT 10

    或:

    SELECT    *
    FROM
        events
    WHERE
        date>='2010-07-12T10:29:47-07:00' and not (date='2010-07-12T10:29:47-07:00' and id < 111866)
            AND event = 'editstart'
    ORDER BY date, id
    LIMIT 10

    但是很明显需要业务逻辑中对应数据和字段满足这种条件才能这么去查

    算了,目前总体来说:

    要查询的表的数据量很小,所以:

    还是用LIMIT M,N

    比较合适,不影响什么性能

    以后数据量大了,再去优化性能

    mysql LIMIT

    MySQL的limit用法和分页查询的性能分析及优化 – 唐成勇 – SegmentFault 思否

    【总结】

    最后用代码:

    userId = parsedArgs["userId"]
    if userId is None:
        return genRespFailDict(BadRequest.code, "Empty userId")
    
    if userId <= 0:
        return genRespFailDict(BadRequest.code, "Invalid userId %d" % userId)
    
    pageNumber = parsedArgs["pageNumber"]
    if pageNumber < 1:
        return genRespFailDict(BadRequest.code, "Invalid pageNumber %d" % pageNumber)
    
    pageSize = parsedArgs["pageSize"]
    pageIndex = pageNumber - 1
    offset = pageSize * pageIndex
    orderBy = "modifyTime"
    
    totalCount = -1
    totalPageNum = -1
    
    totalCountSql = "SELECT COUNT(*) from `user_storybook_list` WHERE `userId`=%d" % userId
    totalCountOk, resultDict = sqlConn.executeSql(totalCountSql)
    log.debug("%s -> %s, %s", totalCountSql, totalCountOk, resultDict)
    if totalCountOk and resultDict["data"]:
        totalCount = resultDict["data"][0]["COUNT(*)"]
    
    if totalCount > 0:
        totalPageNum = int(totalCount / pageSize)
    
        if (totalCount % pageSize) > 0:
            totalPageNum += 1
    
        if pageNumber > totalPageNum:
            return genRespFailDict(BadRequest.code, "Current page number %d exceed max page number %d" % (
            pageNumber, totalPageNum))
    
    hasPrev = False
    if pageNumber > 1:
        hasPrev = True
    
    hasNext = False
    if totalPageNum > 0:
        if pageNumber < totalPageNum:
            hasNext = True
    
    searchByUserSql = "SELECT * FROM `user_storybook_list` WHERE `userId`=%d ORDER BY `%s` ASC LIMIT %d OFFSET %d" % (userId, orderBy, pageSize, offset)
    searchByUserOk, resultDict = sqlConn.executeSql(searchByUserSql)
    log.debug("%s -> %s, %s", searchByUserSql, searchByUserOk, resultDict)
    if searchByUserOk and resultDict["data"]:
        foundItemList = resultDict["data"]
    
        respData = {
            "evaluationList": foundItemList,
            "curPageNum": pageNumber,
            "numPerPage": pageSize,
            "totalNum": totalCount,
            "totalPageNum": totalPageNum,
            "hasPrev": hasPrev,
            "hasNext": hasNext,
        }
    
        respDict["data"] = respData
        return jsonify(respDict)

    结果:

    {
        "code": 200,
        "data": {
            "curPageNum": 1,
            "evaluationList": [
                {
                    "active": "Y",
                    "createTime": "Wed, 23 Jan 2019 08:40:58 GMT",
                    "id": 5,
                    "lastReadDuration": 0,
                    "modifyTime": "Wed, 23 Jan 2019 08:40:58 GMT",
                    "storybookId": "5bd7bd31bfaa44fe2c73736b",
                    "type": 0,
                    "userId": 28
                },
                {
                    "active": "Y",
                    "createTime": "Wed, 23 Jan 2019 08:45:04 GMT",
                    "id": 6,
                    "lastReadDuration": 0,
                    "modifyTime": "Wed, 23 Jan 2019 08:45:04 GMT",
                    "storybookId": "5bd7bd31bfaa44fe2c73736c",
                    "type": 0,
                    "userId": 28
                },
                {
                    "active": "Y",
                    "createTime": "Wed, 23 Jan 2019 08:45:32 GMT",
                    "id": 7,
                    "lastReadDuration": 0,
                    "modifyTime": "Wed, 23 Jan 2019 08:45:32 GMT",
                    "storybookId": "5bd7bd31bfaa44fe2c73736e",
                    "type": 0,
                    "userId": 28
                }
            ],
            "hasNext": true,
            "hasPrev": false,
            "numPerPage": 3,
            "totalNum": 9,
            "totalPageNum": 3
        },
        "message": "Get user storybook list ok"
    }

    转载请注明:在路上 » 【已解决】Python的Flask中pymysql中mysql返回分页查询结果

    发表我的评论
    取消评论

    表情

    Hi,您需要填写昵称和邮箱!

    • 昵称 (必填)
    • 邮箱 (必填)
    • 网址
    67 queries in 0.131 seconds, using 18.86MB memory