Pandas 辅助进行 Excel 和数据库的数据对比检查

背景

测试工作过程中，常常碰到开发人员通过SQL批量插入权限，测试人员进行校对的场景。业务方甩一份各权限配置详情的Excel过来，而开发人员按照Excel写好SQL执行后提测。测试人员则苦逼的人工校对数据，苦不堪言。

本文以我实际碰到的场景为例，使用pandas进行数据处理+对比，以提高测试工作效率。

场景描述

现有两份需对比数据源形式如下：

1. 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权限。

2. 数据库：权限在数据库中存在形式如下

user_id	auth_id
1	55
1	56
2	55
3	56
…	…

权限表的模式是每条记录对应一个权限，使用两个字段user_id和auth_id来记录。比如用户ID为1的用户有两条记录，说明他同时拥有authID对应55和56的两个权限，其他同理。

思路梳理

涉及对比，就需要将两份数据源都转成形状相同（主要是列）的两份二维DataFrame：

1. 对于Excel，先将其读入Pandas，然后梳理出 auth_type 涉及到的权限及其对应ID，这一步骤是手动的，但是只有几个数据量，所以不会太麻烦，本例中，只有三种权限，所以只要找一下对应的3种ID即可。

2. 对于数据库，要用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强大的数据处理能力而思考的的解决方案。

因为实际的数据和表结构不能写明，全是虚拟数据，看起来可能有点乱，敬请谅解。这里权当抛砖引玉，为各位提供一个思路。

如果你有更好更优的解决方案，也欢迎一起沟通交流，共同进步成长。