测试开发【提测平台】分享9-DBUntils优化数据连接&实现应用搜索和分页功能

阅读原文时间：2021年09月13日阅读：1

微信搜索【大奇测试开】，关注这个坚持分享测试开发干货的家伙。

从本期开始知识点讲以思维导图的形式给出，内容点会按照讲解-应用-展示的形式体现，这样会更清晰些。

DBUntils连接池

在项目中链接数据是直接通过pymysql去做的链接请求关闭，每次操作都要独立重复请求，其实是比较浪费资源，在并发不大的小项目虽然无感知，但如果有频繁请求的项目中，就会有性能问题，那么可以通过使用连接池技术，管理来进行优化，DBUnitls是一套Python的数据库连接池包，对链接对象进行自动链接和释放，提高高频高并发下数据访问性能，大概的原理如：

DBUntils 按照配置初始化多个数据库连接存储在连接池中

在程序创建连接的时候，从空闲的连接池中获取，不需要重新初始化连接，提升链接速度；
在程序使用完毕后，把连接放回连接池，并不真正的关闭，等待其他请求使用，减少频繁数据的打开和关闭操作。

DBUntils 提供两种方式，并都能自动管理

PersistentDB (persistent_db) 提供线程专用的连接
PooledDB (pooled_db) 提供线程间可共享的连接

官方 [注解-1] 给出的示意图如下，仅够参考下，不进行扩展

使用此功能需要安装依赖

pip install DBUtils

一般比较常用就是共享的方式，以 pooled_db为例子，使用方法很简单

import pymysql
from dbutils.pooled_db import PooledDB

初始化数据库连接，使用pymysql连接，

pool = PooledDB(pymysql,3,host='',port='',user='',passwd='',database='')

一般连接

db = pool.connection()
cur = db.cursor()
cur.execute(…)
res = cur.fetchone()
cur.close() # 关闭使用游标
db.close() # 关闭使用连接

或者通过with方式

with pool.connection() as db:
with db.cursor as cur:
cur.execute(…)
res = cur.fetchone()

一些参数说明，上边说明中第一参数为 creator 指定那种连接模式，第二参数为mincached

mincached ：启动时开启的空的连接数量
maxcached ：连接池最大可用连接数量
maxshared : 连接池最大可用共享连接数量
maxconnections : 最大允许连接数量
maxusage ：单个丽娜姐最大复用次数
blocking ：达到最大数量时是否阻塞

这里只是对连接的管理，DBUntils的基本知识点讲完了，关于性能对比可以网上搜索相关内容，另外其他的数据库操作还按照自己使用方法进行操作即可。

SQL中limit用法

项目中对于较多的数据显示，需要实现分页数据查询，这可以利用SQL语法关键词 limit ，它可以限制查询结果返回的数量，也可以指定起始索引，来完成分页查询效果，语法形式

# 基本用法
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

简化形式

select * from tableName limit i,n

i：指定第一个返回记录行的偏移量（可省略即表示从0开始）
n：第二个参数指定返回记录行的最大数目

两个示例如：

… limit 10 # 查询前10条数据, 也可以表示为limit 0,10
… limit 10,15 # 从11条开始返回后15条数据即11-25

这里有个 注意事项，网上好多关于limit最后一个参数用 -1 可以表示，指定偏移量到最后，这个目前在新的MYSQL版本已经无效，会报语法错误，办法是用个较大的数代替。

如何实现分页查询，通过观察我们可以这样，如果前端传递的page页数（初始为1），每页的数量为page_size，那么第一个参数计算偏移量可设置为 (page-1) * page_size，第二参数表可设置为page_size，例如前端请求第一页20个，则计算后后两个参数为（ 0, 20），再如请求第2页20个，则计算后两个参数为 ( 20, 20 )，这样就可以实现分页数据查询效果。

这个知识点最后，扩展一个小优化，如果是分页数据特别，第一个参数偏移量太大的时候会带来性能的问题，优化的方式是使用子查询优化（前提条件是有自增ID的表），即先通过查询偏移量ID，然后条件 “>=ID + limit 数量" 进行查询

SELECT * FROM apps where id >=(SELECT id FROM apps LIMIT 100000,1) LIMIT 20

前端组件

当数据量过多时，使用分页分解数据。[注解-2]

基本样式和用法

@size-change="handleSizeChange" @current-change="handleCurrentChange" :current-page="currentPage4" :page-sizes="\[100, 200, 300, 400\]" :page-size="100" layout="total, sizes, prev, pager, next, jumper" :total="400">

设置layout，表示需要显示的内容，用逗号分隔，布局元素会依次显示。prev表示上一页，next为下一页，pager表示页码列表，除此以外还提供了->后的元素会靠右显示，jumper表示跳页元素，total表示总条目数，sizes表示每页显示条数，size用于设置每页显示的页码数量（默认10），通过pager-count属性可以设置最大页码按钮数（默认大于7页的时候6页后…缩略），使用了size-change和 current-change事件来处理页码大小和当前页变动时候触发的事件。

当选项过多时，使用下拉菜单展示并选择内容。[注解-3]

基本用法 ：控件标记，v-model绑定数据，选项集。

自定义用法：显示自定义的左右内容

样式如下，具体使用和其他样式参数参考官方

以上是本章在实现“应用管理”模块显示功能中新用到的两个组件，对于要实现分页，前端请求数据需要给页码current-page和页数page_size ，后端数据还要返回一个总量total数据。

模糊查询与分页功能实现

通过上边的新增知识点，下边按照上一篇的产品需求和原型，进行实际的应用实现，这里会给出实现步骤，可以尝试利用上边和以往的内容点，自己去实现，然后不会点再参考实际罗列的代码编程，还是那观点，通过自己动手实践出来的才印象

详细需求参考历史文章【平台规划和需求分析】

根据需求罗列所需字段创建 apps应用数据库表，并插入一些测试数据大于10条以上；
后端创建一个配置文件管理数据相关字段信息；
后端创建application.py 用来实现DBUntils连接和应用管理api；
接口返回的Response抽取出来定一个通用json格式返回；
GET 实现/api/application/product 归属分类（products表）查询，只需要；id,keyCode,title 数据；
POST 实现/api/application/search 多关关键词（apps表）查询，同时要根据前端传的分页；和数量进行分页查询，并要返回个表总数；
前端menu菜单实现新目录结构
创建新的api请求，实现对后端提供的接口请求
前端查询产品，并使用自定义展示数据，选择数据后可清除，其他搜索条件参照以往input用法；
使用Table组件绑定数据，使用Pagination 绑定分页数据和实现切换时候动作方法，样式使用带背景，总数，数量选择，页码，以及上、下页切换。

1.1 数据库apps表创建

DROP TABLE IF EXISTS `apps`;
CREATE TABLE `apps` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '自增id',
`appId` varchar(50) DEFAULT NULL COMMENT '应用服务ID',
`productId` bigint DEFAULT NULL COMMENT '外键关联产品所属',
`note` varchar(100) DEFAULT NULL COMMENT '应用描述',
`tester` varchar(300) DEFAULT NULL COMMENT '测试负责人',
`developer` varchar(300) DEFAULT NULL COMMENT '默认研发负责人',
`producer` varchar(300) DEFAULT NULL COMMENT '默认产品经理',
`CcEmail` varchar(500) DEFAULT NULL COMMENT '默认抄送邮件或组',
`gitCode` varchar(200) DEFAULT NULL COMMENT '代码地址',
`wiki` varchar(200) DEFAULT NULL COMMENT '项目说明地址',
`more` text COMMENT '更多的信息',
`status` char(1) DEFAULT '0' COMMENT '状态0正常1删除',
`creteUser` varchar(20) DEFAULT NULL COMMENT '创建人',
`createDate` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updateUser` varchar(20) DEFAULT NULL COMMENT '修改人',
`updateDate` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`id`),
UNIQUE KEY `apps_id_uindex` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10014 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='应用管理';

1.2 方便的测试数据

BEGIN;
INSERT INTO `apps` VALUES (10000, 'goods_server', 11, '商品中心服务', 'test1@qq.com', 'dev1@qq.com', 'prod1@qq.com', 'test_group@qq.com;dev_group@qq.com', 'https://github.com/mrzcode/TestProjectManagement', 'https://www.cnblogs.com/mrzcode/p/15103321.html', '商品中心为订单的基础上游服务，有订单业务团队负责', '0', 'MrzCode', '2021-09-05 17:50:09', 'MrzCodeUpdate', '2021-09-07 09:59:51');
INSERT INTO `apps` VALUES (10001, 'payment_core', 2, '支付核心服务', 'test2@qq.com', 'dev2@qq.com', 'prod2@qq.com', NULL, NULL, 'https://www.cnblogs.com/mrzcode/p/15118881.html', NULL, '0', 'MrzCode', '2021-09-05 17:50:16', 'MrzCodeUpdate', '2021-09-07 10:00:48');
INSERT INTO `apps` VALUES (10002, 'risk_control', 16, '风控服务', 'test2@qq.com', 'dev1@qq.com;dev2@qq.com', 'prod2@qq.com', NULL, 'https://github.com/mrzcode/TestProjectManagement', 'https://www.cnblogs.com/mrzcode/p/15130629.html', NULL, '0', 'MrzCode', '2021-09-05 17:50:20', 'MrzCodeUpdate', '2021-09-07 10:00:53');
INSERT INTO `apps` VALUES (10003, 'order_new', 16, '新订单服务', 'test3@qq.com', 'dev3@qq.com', NULL, 'test_group@qq.com;dev_group@qq.com', NULL, 'https://www.cnblogs.com/mrzcode/p/15139227.html', NULL, '0', 'MrzCode', '2021-09-05 17:50:23', 'MrzCodeUpdate', '2021-09-07 10:00:55');
INSERT INTO `apps` VALUES (10004, 'message_server', 16, '消息服务中心', 'test1@qq.com;test3@qq.com', 'dev1@qq.com;dev2@qq.com;dev3@qq.com', 'prod3@qq.com', NULL, 'https://github.com/mrzcode/TestProjectManagement', 'https://www.cnblogs.com/mrzcode/p/15136302.html', NULL, '0', 'MrzCode', '2021-09-05 17:50:27', 'MrzCodeUpdate', '2021-09-07 10:00:57');
INSERT INTO `apps` VALUES (10005, 'user_center', 10, '用户服务中心', 'test3@qq.com', 'dev3@qq.com', 'prod3@qq.com', NULL, NULL, 'https://www.cnblogs.com/mrzcode/p/15160329.html', '基础服务，用户模块，管理公司内部用户和外网所有用户信息', '0', 'MrzCode', '2021-09-05 17:50:31', 'MrzCodeUpdate', '2021-09-07 10:00:10');
INSERT INTO `apps` VALUES (10006, 'finance_web', 10, '财务服务系统', 'test4@qq.com', 'dev4@qq.com', NULL, NULL, NULL, NULL, NULL, '0', 'MrzCode', '2021-09-05 17:55:41', NULL, '2021-09-07 10:00:23');
INSERT INTO `apps` VALUES (10007, 'search_all', 17, '全域搜索', 'test4@qq.com', 'dev4@qq.com', NULL, NULL, NULL, NULL, NULL, '0', 'MrzCode', '2021-09-05 17:55:47', NULL, '2021-09-07 10:00:20');
INSERT INTO `apps` VALUES (10008, 'domain_server', 17, '域名服务系统', 'test4@qq.com', 'dev4@qq.com', NULL, NULL, 'https://github.com/mrzcode/TestProjectManagement', 'https://www.cnblogs.com/mrzcode/p/15216885.html', NULL, '0', 'MrzCode', '2021-09-05 20:21:12', NULL, '2021-09-07 10:00:18');
INSERT INTO `apps` VALUES (10009, 'email_server', 16, '邮件系统', 'test4@qq.com', 'dev4@qq.com', NULL, NULL, NULL, NULL, NULL, '0', 'MrzCode', '2021-09-05 20:21:29', NULL, '2021-09-07 10:00:58');
INSERT INTO `apps` VALUES (10010, 'hr_services', 10, '人力资源系统', 'test5@qq.com', 'dev5@qq.com', 'prod5@qq.com', 'test_group@qq.com', NULL, NULL, NULL, '0', 'DaQi', '2021-09-05 20:21:58', NULL, '2021-09-07 10:00:07');
INSERT INTO `apps` VALUES (10011, 'config_center', 16, '配置中心', 'test5@qq.com', 'dev5@qq.com', NULL, NULL, NULL, 'https://www.cnblogs.com/mrzcode/p/15216885.html', NULL, '0', 'DaQi', '2021-09-05 20:22:28', NULL, '2021-09-07 10:01:00');
INSERT INTO `apps` VALUES (10012, 'case_platform', 16, '用例管理平台', 'test5@qq.com', 'dev5@qq.com', NULL, NULL, NULL, NULL, NULL, '0', 'DaQi', '2021-09-05 20:23:24', NULL, '2021-09-07 10:01:02');
INSERT INTO `apps` VALUES (10013, 'qa_manger', 16, '测试中心', 'oktest@qq.com', 'okdev@qq.com', 'okprod@qq.com', '', '', '', '', '0', 'DaQi', '2021-09-05 20:27:07', 'DaQi', '2021-09-07 10:01:01');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

2. 创建一个常量配置

目录/configs/config.py

# config.py

数据库配置

MYSQL_HOST= 'localhost'
MYSQL_PORT = 3306
MYSQL_DATABASE = 'TPMStore'
MYSQL_USER = 'mrzcode'
MYSQL_PASSWORD = 'mrzcode'

3. 创建新的蓝图api应用管理模块，实现DBUntils初始化连接

路径 /apis/application.py ，并导入config数据库配置，以及dbutils模块

from flask import Blueprint
from dbutils.pooled_db import PooledDB
from configs import config

import pymysql.cursors

使用数据库连接池的方式链接数据库，提高资源利用率

pool = PooledDB(pymysql, mincached=2, maxcached=5,host=config.MYSQL_HOST, port=config.MYSQL_PORT,
user=config.MYSQL_USER, passwd= config.MYSQL_PASSWORD, database=config.MYSQL_DATABASE,
cursorclass=pymysql.cursors.DictCursor)

app_application = Blueprint("app_application", __name__)

4. 创建一个统一格式类

目录/configs/ 添加一个统一Response返回

# format.py
resp_format_success = {
"code": 20000,
"message": "success",
"data": [],
"total": 0
}

5. 实现所属分类查询用于条件筛选

@app_application.route("/api/application/product",methods=['GET'])
def getProduct():
# 使用连接池链接数据库
connection = pool.connection()

with connection.cursor() as cursor:  
    # 查询产品信息表-按更新时间新旧排序  
    sql = "SELECT id,keyCode,title FROM \`products\` WHERE \`status\`=0 ORDER BY \`update\` DESC"  
    cursor.execute(sql)  
    data = cursor.fetchall()

# 按返回模版格式进行json结果返回  
response = format.resp\_format\_success  
response\['data'\] = data  
return response

6. 实现分页查询应用

需要对查询条件进行按需拼接，还需要通过两次查询，一次查询总数量，一次是limit分页查询

@app_application.route("/api/application/search",methods=['POST'])
def searchBykey():
body = request.get_data()
body = json.loads(body)

# 基础语句定义  
sql = ""

# 获取pageSize和  
pageSize = 10 if body\['pageSize'\] is None else body\['pageSize'\]  
currentPage = 1 if body\['currentPage'\] is None else body\['currentPage'\]

# 拼接查询条件  
if 'productId' in body and body\['productId'\] != '':  
    sql = sql + " AND \`productId\` = '{}'".format(body\['productId'\])  
if 'appId' in body and body\['appId'\] != '':  
    sql = sql + " AND \`appId\` LIKE '%{}%'".format(body\['appId'\])  
if 'note' in body and body\['note'\] != '':  
    sql = sql + " AND \`note\` LIKE '%{}%'".format(body\['note'\])  
if 'tester' in body and body\['tester'\] != '':  
    sql = sql + " AND \`tester\` LIKE '%{}%'".format(body\['tester'\])  
if 'developer' in body and body\['developer'\] != '':  
    sql = sql + " AND \`developer\` LIKE '%{}%'".format(body\['developer'\])  
if 'producer' in body and body\['producer'\] != '':  
    sql = sql + " AND \`producer\` LIKE '%{}%'".format(body\['producer'\])

# 排序和页数拼接  
sql = sql +' ORDER BY \`updateDate\` DESC LIMIT {},{}'.format((currentPage-1)\*pageSize, pageSize)  
print(sql)

# 使用连接池链接数据库  
connection = pool.connection()

with connection:  
    # 先查询总数  
    with connection.cursor() as cursor:  
        cursor.execute('SELECT COUNT(\*) as \`count\` FROM \`apps\` WHERE \`status\`=0' + sql)  
        total = cursor.fetchall()

    # 执行查询  
    with connection.cursor() as cursor:  
        # 按照条件进行查询  
        cursor.execute('SELECT P.title, A.\* FROM apps AS A,products AS P WHERE A.productId = P.id and A.\`status\`=0' + sql)  
        data = cursor.fetchall()

# 按分页模版返回查询数据  
response = format.resp\_format\_success  
response\['data'\] = data  
response\['total'\] = total\[0\]\['count'\]  
return response

接口文件全部实现完可不要忘记app.py文件中添加注册

from apis.application import app_application
….
app.register_blueprint(app_application)

7. vue前端对左侧目录进行重新整理

路径 /src/router/index.js 变更，其中涉及到子菜单用法，用children，这没有而外讲，直接照着代码一看就明白了。

{
path: '/settings',
component: Layout,
redirect: '/settings',
meta: { title: '基础管理', icon: 'dashboard' },
children: [{
path: 'product',
name: 'Product',
component: () => import('@/views/product/product'),
meta: { title: '项目产品分类', icon: 'dashboard' }
},
{
path: 'apps',
name: 'apps',
component: () => import('@/views/product/apps'),
meta: { title: '服务应用管理', icon: 'dashboard' }
}
]
},

8. 创建应用页面的api请求文件和方法

目录/src/api/apps.js 组装产品列表和应用搜索接口请求

import request from '@/utils/request'

// 应用搜索接口
export function apiAppsSearch(requestBody) {
return request({
url: '/api/application/search',
method: 'post',
data: requestBody
})
}

// 产品选择项目列表
export function apiAppsProduct() {
return request({
url: '/api/application/product',
method: 'get'
})
}

9. 实现搜索区域功能

先要创建app管理页面，路径/src/views/product/apps.vue ，然后分别利用上边的知识点实现自定义选择框的绑定，和其他input字段的绑定，另外有个添加按钮（本篇不实现）占位，html区域如下，js内容最后统一给出。

10.1 表格数据的绑定

历史table的内容，可按照产品页实现来，这里注意依然有时间格式的问题，另外还需要添加个操作列是实现编辑功能（本篇不实现）占位。

10.2 表格下方的分页控件实现

带背景的页码，以及其他上下页，总数，自定义数量选择。

最后所有 script 部分的代码如下

如果你是一步步自己走下来，或者参考下来，实际上还是方式，完成一个步骤进行一个验证，有问题解决掉，然后进行下个，那么你到最后应该都不是有有问题，分别启动前后的页面实现效果如下：

问题集锦

1. Python Myql查询数据返回的是元组类型，而非字典类型

在编码中优化使用DBUnitls创建数据连接时候，没有给定参数cursorclass，导致默认返回数据为元组类型，这样对返回数据还要进行二次转换操作，比较麻烦，添加 cursorclass=pymysql.cursors.DictCursor 可以设定返回类型为字典，之前项目中使用的时候还没太注意这个参数的意义，现在通过问题发现它的功能了。

2. 分别更改对其方式

element-ui的分页组件使用后，默认是居左侧对其的，但一般常见产品页面都会放在中间或者右侧对其，但组件本身又没有提供对其的属性可设定，通过搜索，可以通过在样式代码块中，自定义下 .el-pagination 的css样式就行。

【代码更新】

地址：https://github.com/mrzcode/TestProjectManagement
TAG：TPMShare9

【注解&参考】

[注解-1] :https://webwareforpython.github.io/DBUtils/main.html
[注解-2] :https://element.eleme.io/#/zh-CN/component/pagination
[注解-3] :https://element.eleme.io/#/zh-CN/component/select

坚持原创，坚持实践，坚持干货，如果你觉得有用，请点击推荐，也欢迎关注我博客园和微信公众号。

手机扫一扫

移动阅读更方便

你可能感兴趣的文章

gitlab与LDAP 联调