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

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

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)

最终输出样式：

优化后通用函数

union all 生成临时数据表-oracle

def create_tmp_sql_fun(df):
    import pandas as pd
    import numpy as np
    # 当最外层循环结束，保存数据
    sql = []
    for i in range(df.shape[0]):
        # 进行语句拼接，每行数据拼接完成后，sentence回到初始值
        sentence = []
        for j in range(df.shape[1]):
            for col_inde, col_name in enumerate(df.columns):
                if j == col_inde:
                    # 如果元素是字符类型
                    if isinstance(df.iloc[i,j], str):
                        # 如果数据是多维列索引
                        if type(df.columns) == pd.MultiIndex:
                            con_str = f"'{df.iloc[i,j]}' as {col_name[1]},"
                        else:
                            con_str = f"'{df.iloc[i,j]}' as {col_name},"
                    # 如果元素是数字类型
                    elif not isinstance(df.iloc[i,j], str):
                        # 如果数据是多维列索引
                        if type(df.columns) == pd.MultiIndex:
                            con_str = f"{df.iloc[i,j]} as {col_name[1]},"
                        else:
                            con_str = f"{df.iloc[i,j]} as {col_name},"
            sentence.append(con_str)
        # sentence.append(f"'{df.index[i]}' as {df.index.name},")
        sen = 'select'+ ' ' + ''.join(sentence) + 'from dual' + '\n' + 'union all' +'\n'
        sql.append(sen.replace(',from', ' from'))

    # 补充字段名 
    fields = []
    for field_name in df.columns:
        if type(df.columns) == pd.MultiIndex:
            fields.append(field_name[1])
        else:
            fields.append(field_name)

    # 剔除末尾的 union all 和 换行
    sql = ("select {} \nfrom(\n" + ''.join(sql)[0:-10] + ')a').format(','.join(fields))
    return sql

将临时数据表数据插入正式库表-oracle

# 将数据插入正式的库表中
def create_insert_db_func(table_name, sql, df):
    # 将数据插入数据库表中
    # 定义 create 时 字段的数据类型
    sen_1 = f'create table {table_name} \n(\n'
    sen_2 = []
    for field_name in df.columns:
        # 当读取的excel表格存在多维列索引，字段名的提取
        if type(df.columns) == pd.MultiIndex:
            field_name_1 = field_name[1]
        else:
            field_name_1 = field_name
        # 入表数据类型判断
        if df[field_name].apply(lambda x: isinstance(x, str)).unique()[0]:
            data_type = 'varchar2(50)'
        elif df[field_name].apply(lambda x: isinstance(x, int)).unique()[0]:
            data_type = 'int'
        else:
            data_type = 'float'
        sen_2.append(f"{field_name_1}\t\t{data_type},\n")

    # 去除掉最后一个逗号
    create_sentence = sen_1 + re.sub(r',([^,]*)$', r'\1', ''.join(sen_2)) + '\n)'

    insert_sentence = f'insert into {table_name}\n\n{sql}\n;'
    
    return create_sentence, insert_sentence