数据库库导入2w测试数据3种方法_数据库添加测试数据-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_36841447/article/details/143903367

方法1，SQL导入【这个方式是针对PG库的】

其中stk_out_rtn_id,bill_no是可变的，其他字段是不变的

INSERT INTO stk_out_rtn_his (stk_out_rtn_id,bill_no,stk_out_rtn_type,rtn_org_id,rtn_org_code,rtn_org_name,rtn_wh_id,rtn_wh_code,rtn_wh_name,dep_code,dep_name,sup_org_id,sup_org_code,sup_org_name,pur_cnt_id,pur_cnt_code,main_org_id,main_org_code,main_org_name,dis_cnt_id,source_biil_no,rel_rtn_in_tax_sum,rel_rtn_num,rel_rtn_term_num,pur_rtn_in_tax_sum,rtn_term_num,rtn_num,rtn_reason,remark,crt_date,crt_user_id,crt_user_code,crt_user_name,crt_org_id,crt_org_code,crt_org_name,upd_date,upd_user_id,upd_user_code,upd_user_name,upd_org_id,upd_org_code,upd_org_name,acc_date,acc_user_id,acc_user_code,acc_user_name,acc_org_id,acc_org_code,acc_org_name,acc_status,bill_status,com_rtn_order_reason,ref_bill_no,ref_bill_type,ref_bill_type_dsc,business_date,drc_rtn_order_reason,pur_user_id,pur_user_code,pur_user_name,biz_bill_type,is_rebate,is_specified_price,print_num,print_date,dis_rtn_in_total,rel_dis_rtn_in_total,red_status,source_type,audit_date,audit_user_id,audit_user_code,audit_user_name,audit_status,audit_opinion,rtn_org_contact,rtn_org_contact_tel,rtn_addr,udp1,mov_date,pur_rtn_tax_sum,rel_rtn_tax_sum,pur_cnt_name,dis_cnt_code,dis_cnt_name,pur_rtn_ex_tax_sum,rel_rtn_ex_tax_sum,rel_dis_rtn_ex_total,rel_dis_rtn_tax_total,dis_rtn_ex_total,ord_bill_no,ord_biz_type,pre_bill_no,pre_biz_type,"version")

SELECT s.id ,'BillNo' || s.id AS data,'0',10027777,'109901','金蝶门店',1446973253661757312,'01','卖场仓库',NULL,NULL,1444645888193464894,'LJN','江苏卤江南食品有限公司',1445681263074607751,'XSJMB0050',1445587793848829539,'FJZX003','无锡分拣中心仓库',NULL,NULL,0.00000000,4.0000,2,0.00000000,2.0000,4.0000,NULL,NULL,'2023-09-16 15:00:00.000',1,'admin','admin',10027777,'109901','金蝶门店',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0','0',NULL,'THDD20230911000005','1',NULL,'2023-09-25',NULL,NULL,NULL,NULL,'30501',0,'0',0,NULL,0.00000000,0.00000000,NULL,'0',NULL,NULL,NULL,NULL,'0',NULL,NULL,NULL,NULL,'2023-09-15',NULL,0.00000000,0.00000000,'test001',NULL,NULL,0.00000000,0.00000000,0.00000000,0.00000000,0.00000000,NULL,NULL,NULL,NULL,0

FROM generate_series(1, 200000) AS s(id);

方法2：python代码【这个方式是针对PG库的，如果要是MySql库的话就导出mysql的库文件然后调用时改成mysql即可】

#!/usr/bin/env/python3
# -*- coding:utf-8 -*-
"""
@author: Liqx
@file: 2w条普通采购退货单数据.py
@desc:
"""
import psycopg2
from psycopg2 import OperationalError

# 数据库连接配置
config = {
    'host': 'xx.xx.xx.xx',      # 数据库服务器地址
    'user': 'xxxx',        # 数据库用户名
    'password': 'xxxx',    # 数据库密码
    'database': 'xxxx', # 数据库名称
}

# 连接到PostgreSQL数据库
try:
    conn = psycopg2.connect(**config)
    cursor = conn.cursor()
    print("数据库连接成功")
except OperationalError as e:
    print(f"连接数据库失败: {e}")
    exit(1)

# 基础的SQL插入语句模板
insert_sql = """
INSERT INTO stk_out_rtn (stk_out_rtn_id, bill_no, stk_out_rtn_type, rtn_org_id, rtn_org_code, rtn_org_name, 
                        rtn_wh_id, rtn_wh_code, rtn_wh_name, sup_org_id, sup_org_code, sup_org_name, 
                        pur_cnt_id, pur_cnt_code, main_org_id, main_org_code, main_org_name, dis_cnt_id, 
                        source_biil_no, rel_rtn_in_tax_sum, rel_rtn_num, rel_rtn_term_num, pur_rtn_in_tax_sum, 
                        rtn_term_num, rtn_num, rtn_reason, remark, crt_date, crt_user_id, crt_user_code, 
                        crt_user_name, crt_org_id, crt_org_code, crt_org_name, upd_date, upd_user_id, 
                        upd_user_code, upd_user_name, upd_org_id, upd_org_code, upd_org_name, acc_date, 
                        acc_user_id, acc_user_code, acc_user_name, acc_org_id, acc_org_code, acc_org_name, 
                        acc_status, bill_status, com_rtn_order_reason, ref_bill_no, ref_bill_type, 
                        ref_bill_type_dsc, business_date, drc_rtn_order_reason, pur_user_id, pur_user_code, 
                        pur_user_name, biz_bill_type, is_rebate, is_specified_price, print_num, print_date, 
                        dis_rtn_in_total, rel_dis_rtn_in_total, red_status, pur_rtn_ex_tax_sum, 
                        rel_rtn_ex_tax_sum, audit_date, audit_user_id, audit_user_code, audit_user_name, 
                        audit_status, audit_opinion, rtn_org_contact, rtn_org_contact_tel, rtn_addr, udp1, 
                        dep_code, dep_name, source_type, ord_bill_no, ord_biz_type, pre_bill_no, pre_biz_type, 
                        version, pur_rtn_tax_sum, rel_rtn_tax_sum, pur_cnt_name, dis_cnt_code, dis_cnt_name, 
                        rel_dis_rtn_ex_total, rel_dis_rtn_tax_total, dis_rtn_ex_total) 
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 
        %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 
        %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 
        %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s,%s,%s,%s);
"""

# 数据生成并批量插入
# 数据生成并批量插入
data_list = []
for i in range(20000):  # 生成2条数据，实际可以根据需要增加
    stk_out_rtn_id = 1368466666370893783 + i
    bill_no = f"THDJ202411190000{i:04}"
    data = (
        stk_out_rtn_id, bill_no, '0', 10005247, '30252002', 'AIP接口门店', 1349077876179207113, '01', '卖场仓库',
        1349176980402078173, '2020101601', 'AIP供应商', 1366635123738411317, '98760003', None, None, None, None,
        'THDD20210512000002', 25.20000000, 2.0000, 1, 25.20000000, 1.0000, 2.0000, None, None,
        '2021-05-12 09:43:42.000',
        1349104142051705801, '66666666', 'Eric', 10001902, '30252002', 'AIP接口门店', None, None, None, None, None,
        None, None, None, None, None, None, None, '2022-03-24 15:40:07.000', None, None, None, None, None, None, None,
        '2023-03-17', None, 1349104142051705801, '66666666', 'Eric', '30501', 1, None, 0, None, 0.00000000,
        0.00000000, None, 25.20000000, 25.20000000, None, None, None, None, '0', None, None, None, None, None,
        None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None
    )

    # 打印数据元组的长度以检查是否为78
    print(f"数据元组的长度: {len(data)}")

    data_list.append(data)

# 检查数据元组的长度
print(f"数据元组的总数量: {len(data_list)}")
for idx, item in enumerate(data_list):
    print(f"第 {idx + 1} 条数据元组长度: {len(item)}")

# 执行批量插入
try:
    cursor.executemany(insert_sql, data_list)
    conn.commit()
    print("数据插入成功")
except Exception as e:
    conn.rollback()
    print(f"数据插入失败: {e}")

# 关闭游标和连接
cursor.close()
conn.close()

方法3：存储过程调用方法

这个脚本里面handcashid字段是可变的，执行后会存储在数据库中，调用的时候直接在数据库执行CALL `InsertHandCashRecords`(1000);即可生成1000条数据、

也可以直接CREATE PROCEDURE `InsertHandCashRecords`(IN 1000 INT)

若是想其他字段也是随机生成可：

DECLARE orgcode VARCHAR(20) DEFAULT 'AAAA';  -- 定义一个新的变量来存储 orgcode
    DECLARE orgcode_suffix INT DEFAULT 1;  -- 用于递增orgcode的后缀

CREATE PROCEDURE `InsertHandCashRecords`(IN num_records INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE random_handcashid VARCHAR(20);
    DECLARE orgcode VARCHAR(20) DEFAULT 'AAAA';  -- 定义一个新的变量来存储 orgcode
    DECLARE orgcode_suffix INT DEFAULT 1;  -- 用于递增orgcode的后缀

    -- 循环插入数据
    WHILE i <= num_records DO
        -- 生成随机的 handcashid
        SET random_handcashid = CONCAT('AAAA01', DATE_FORMAT(NOW(), '%Y%m%d'), LPAD(FLOOR(RAND() * 10000000), 9, '0'));
        
        -- 插入数据
        INSERT INTO `tposhandcashrecord`(`handcashid`, `orgcode`, `posno`, `cshid`, `cshcode`, `cshname`, `handtime`, `orderno`, `bizdate`, `paycode`, `payname`, `total`, `incometype`, `handtype`, `exrefno`, `exrefdate`, `confirmbillno`, `comtag`, `combatchno`, `orgname`, `salechannel`)  
        VALUES (
            random_handcashid,
            CONCAT(orgcode, LPAD(orgcode_suffix, 3, '0')),  -- 生成顺序递增的 orgcode
            '01',
            UUID(),  -- 使用UUID()生成唯一的cshid
            '8888',
            CONCAT('王小明', i % 100),  -- 简单示例，通过i生成不同的名字
            '2024-11-01 18:47:55',
            CONCAT('AAAA0120241012', LPAD(i, 5, '0')),
            '2024-11-01',
            '0',
            '现金',
            ROUND(RAND() * 1000, 2),  -- 随机金额
            '0',
            '0',
            '',
            '',
            '',
            0,
            MD5(RANDOM_BYTES(16)),  -- 生成随机的combatchno
            '测试支付门店',
            '1'
        );

        -- 增加 orgcode_suffix 和 i
        SET orgcode_suffix = orgcode_suffix + 1;  -- orgcode后缀递增
        SET i = i + 1;
    END WHILE;
END