526互联

利用python自动生成sql测试数据脚本

发布时间 2023-04-07 14:44:33作者: 没有风格的Wang

在实际工作中遇到了真实生产数据尚未拿到，需要先造一些假数据完成某些功能的开发，想到了利用python来实现创建临时表数据：
提前造好的数据样式：
image

import pandas as pd
# xlrd 不支持读取xlsx文件
# https://pylightxl.readthedocs.io/en/latest/quickstart.html
import pylightxl as xl

file_path = '/Users/Desktop/事先造好的接口测试数据.xlsx'

xlsx = xl.readxl(fn=file_path)
tab = 'rsrc'
for sheet_name in xlsx.ws_names:
    df = pd.read_excel(file_path, sheet_name=sheet_name, index_col=False)
    sql = []
    for i in range(df.shape[0]):
        for j in range(df.shape[1]):
            if j == 0:
                col_1 = df.iloc[i,j]
                col_name_1 = df.columns[0]
            else:
                col_2 = df.iloc[i,j]
                col_name_2 = df.columns[1]
        sentence = f"select '{col_1}'  as {col_name_1}, {col_2} as {col_name_2}, '{tab}' as tab from dual"
        sql.append(sentence + '\n' + 'union all' + '\n')

    sql = ' '.join(sql)
    sql_copy = sql
	# 去除末尾存在的union all
    sql_copy =sql[0:len(sql)-10]

    with open(r'/Users/Desktop/测试临时表语句.txt','r+') as f:
        content = f.read()
        f.seek(0, 0)
        f.write("【" + sheet_name +"】"+":" +"\n" + sql_copy + "\n" + content)

最终输出样式：
image