这里写自定义目录标题
背景
测试工作过程中,常常碰到开发人员通过SQL批量插入权限,测试人员进行校对的场景。业务方甩一份各权限配置详情的Excel过来,而开发人员按照Excel写好SQL执行后提测。测试人员则苦逼的人工校对数据,苦不堪言。
本文以我实际碰到的场景为例,使用pandas进行数据处理+对比,以提高测试工作效率。
场景描述
现有两份需对比数据源形式如下:
- Excel:形式如下
user_id | username | auth_type |
---|---|---|
1 | usera | type1 |
2 | userb | type2 |
3 | userc | type1+type2 |
4 | userd | type1+type2 |
… | … | … |
甲方写好的Excel中对每个用户名所需要的权限都做了标注,比如usera需要type1权限,userb需要type2权限,userc既需要type1权限也需要type2权限。
- 数据库:权限在数据库中存在形式如下
user_id | auth_id |
---|---|
1 | 55 |
1 | 56 |
2 | 55 |
3 | 56 |
… | … |
权限表的模式是每条记录对应一个权限,使用两个字段user_id和auth_id来记录。比如用户ID为1的用户有两条记录,说明他同时拥有authID对应55和56的两个权限,其他同理。
思路梳理
涉及对比,就需要将两份数据源都转成形状相同(主要是列)的两份二维DataFrame:
-
对于Excel,先将其读入Pandas,然后梳理出
auth_type
涉及到的权限及其对应ID,这一步骤是手动的,但是只有几个数据量,所以不会太麻烦,本例中,只有三种权限,所以只要找一下对应的3种ID即可。 -
对于数据库,要用SQL语句读出所有Excel中涉及到的用户和其拥有的三种权限的情况。
梳理完思路后,就可以让开发在测试环境执行SQL了。
Jupyter操作
为方便查看数据,回溯修改,这里使用Jupyter Lab完成后续操作。使用 pandas
和 sqlalchemy
这两个主要的库,都是2.0以上的版本。
import pandas as pd
1. Excel处理
先进行Excel的处理,将其读入pandas后,依据auth_type的内容转换成对应的数据库ID
# 只读取第一和第三列
pd_excel = pd.read_excel('auth.xlsx', usecols=[0, 2])
# 定义函数,依据auth_type列的值来决定auth_id
def assign_id(value):
if value == 'type1+type2':
return [55, 56]
elif value == 'type1':
return [55]
elif value == 'type2':
return [56]
# 生成新列
pd_excel['auth_id'] = pd_excel['auth_type'].apply(assign_id)
这样就获得了一个三列的df,分别是用户ID,用户权限(字符串形式),对应权限ID列表
user_id | auth_type | auth_id |
---|---|---|
1 | type1 | [55] |
2 | type2 | [56] |
3 | type1+type2 | [55, 56] |
4 | type1+type2 | [55, 56] |
… | … | … |
然后从中提取出所有涉及到的用户ID和权限,供后续SQL使用
# 提取所有用户ID,转成元组
user_id_tp = tuple(pd_excel['user_id'].to_list())
# 这里其实只涉及两种权限,所以随便找一个type1+type2的对应ID转成元组即可,下面假设第三个
auth_id_tp = tuple(pd_excel['auth_id'][2])
2. DB处理
接着处理数据库,sqlalchemy
连接不同的数据库需要不同的依赖,这里以MySQL举例,使用 pymysql
,自行安装即可。
pip install pymysql -U
from sqlalchemy import create_engine, URL
# 创建好数据库连接的字符串URL,这里用sqlalchemy内置的URL.create方法
url_obj = URL.create(
"mysql+pymysql",
username="username",
password="password",
host="x.x.x.x",
database="db_name",
port=3306
)
# 首先创建pandas需要的连接engine
engine = create_engine(url_obj)
# 依据是上面提取出来的所有用户ID列表和涉及权限列表
df_db = pd.read_sql(
f"SELECT userID, authID FROM table WHERE userID in {user_id_tp} AND {auth_id_tp}",
engine
)
上述操作完成后, df_db
的结果大致为这种形式:
userID | authID |
---|---|
1 | 55 |
1 | 56 |
2 | 55 |
3 | 56 |
… | … |
这种形式没法对比,所以还需要进一步处理,即按照用户分组,将authID聚合成一个列表,也就是上文处理后 df_excel
的形式
df_db = df_db.sort_values(['userID', "authID"]) # 先排序
.groupby('userID') # 按userID分组
.agg({'authID': list}) # 将authID聚合成列表
.reset_index() # 重置索引
处理完毕后,结果就是这样的了:
userID | authID |
---|---|
1 | [55, 56] |
2 | [55] |
3 | [56] |
… | … |
3. 数据对比
3.1 手动导出对比
经过上面的处理后,其实已经完成了绝大部分操作,考虑到实际错误很少或几乎没有,在数据量不大(小于一千条)时,可以直接将其导出,然后用文本编辑器进行对比
# 下面是分别导出为csv,然后用文本编辑器对比
df_excel.to_csv('df_excel.txt', sep='\t', header=True, index=False)
df_db.to_csv('df_db.txt', sep='\t', header=True, index=False)
可用的文本对比编辑器包括VS Code、Notepad++、EmEditor、BCompare 等等,大部分情况种,要对比的数据其实都没什么错误,这种方式适合做快速检验
3.2 compare方法对比
我们也可以使用pandas提供的 compare
方法,但该方法要求行列的名字和数量都一致,所以有时会有局限性
# 统一列名
df_db.columns = ['user_id', 'auth_id']
# 对比
df_excel.compare(df_db)
最终结果类似下图,如果相同,显示NaN,不同则会在self和other处显示各自的值
3.3 合并后对比
3.3.1
我遇到的场景,列是一致的,但是行数可能不一致,要解决无法使用 compare
方法的问题,要先进行合并 merge
# 将df_excel和df_db合并,参考值为user_id,outer方式连接
merged_df = pd.merge(df_excel, df_db, on='user_id', how='outer', indicator=True)
合并后是一个大的df,同时会生成 auth_id_x
和 auth_id_y
两列对应 df_excel
和 df_db
各自 user_id
对应的 auth_id
,outer
方式连接会将不存在的一并合并,不存在的一方值置空,indicator=True
会新增 _merge
列,该列表明是左列还是右列还是双方的值都合并了
形状统一后,就可以对比了
merged_df['auth_id_x'].compare(merged_df['auth_id_y'])
执行后,就会显示类似 3.2节图中的输出结果,也可以加上 keep_shape
参数显示所有值,不论相同还是不同。
merged_df['auth_id_x'].compare(merged_df['auth_id_y'], keep_shape=True)
3.3.2
也可以不使用 compare
方法,直接对两列进行对比
merged = pd.merge(df_excel, df_db, left_on='user_id', right_on='user_id', how='outer')
merged[merged['auth_id_x'] != merged['auth_id_y']].rename(columns={'auth_id_x': 'excel中应赋权限', 'auth_id_y': '数据库中实赋'})```
总结
以上就是我在实际工作过程中碰到的一个数据对比质检场景,为提高测试效率,想到利用pandas强大的数据处理能力而思考的的解决方案。
因为实际的数据和表结构不能写明,全是虚拟数据,看起来可能有点乱,敬请谅解。这里权当抛砖引玉,为各位提供一个思路。
如果你有更好更优的解决方案,也欢迎一起沟通交流,共同进步成长。