python脚本批量创建数据

背景

	批量创建测试数据，利用python自带的库如faker库，节省大量的人工。

准备工作

1、安装python，参考地址
https://www.runoob.com/python3/python3-install.html

2、设置环境变量(不同操作系统)
PATH=“$PATH:/usr/local/bin/python”

3、查看python版本，说明python环境变量设置成功

代码示例

from datetime import datetime
import random

import mysql.connector
import uuid
from faker import Faker

# 简体中文：zh_CN
faker = Faker(locale="zh_CN")

# 创建数据库连接
db = mysql.connector.connect(
    host="127.0.0.1",  # 数据库主机地址（根据您的配置进行修改）
    port=3306,
    user="root",  # 用户名（根据您的配置进行修改）
    password="123456",  # 密码（根据您的配置进行修改）
    database="test"  # 数据库名称（根据您的配置进行修改）
)

# 测试办案区depart_code(江苏省下的)
baqCodes = ['098751123','xxhbaq','xxhbaq2','stbaq1','hhjdbaq']
xbdm = [0,1,2,3]
# 处理结果字典
dcResultCodeMap = {}
# 处理结果代码
dcResultCodes = ['01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','99']
dcResultCodeMap['01'] = '刑事拘留'
dcResultCodeMap['02'] = '逮捕'
dcResultCodeMap['03'] = '取保候审'
dcResultCodeMap['04'] = '监视居住'
dcResultCodeMap['05'] = '结束传唤'
dcResultCodeMap['06'] = '移交'
dcResultCodeMap['07'] = '强制隔离戒毒'
dcResultCodeMap['08'] = '社区戒毒'
dcResultCodeMap['09'] = '行政拘留'
dcResultCodeMap['10'] = '罚款'
dcResultCodeMap['11'] = '警告'
dcResultCodeMap['12'] = '批评教育'
dcResultCodeMap['13'] = '不予处罚'
dcResultCodeMap['14'] = '在逃抓获后送押'
dcResultCodeMap['15'] = '结束取保传讯'
dcResultCodeMap['16'] = '排除违法犯罪嫌疑'
dcResultCodeMap['99'] = '其他'

if __name__ == '__main__':
    # 创建游标对象
    cursor = db.cursor()
    cursor.execute("select ASJBH from `test`.`ajjbxx`")
    # 获取所有查询结果
    results = cursor.fetchall()
    # 打印查询结果
    AJBHS = []
    for row in results:
        AJBHS.append(row[0])
    # 获取办案区的父级code
    parentDepartCodeSql = "select d2.depart_code from `sys_depart` d1 left join `sys_depart` d2 on d1.parent_id=d2.id where d1.depart_code=%s"
    # 编写SQL插入语句
    sql = "INSERT INTO `test`.`rcbaqdjxx` (`S_ID`,`PARENT_DEPART_CODE`,`BAZXBH`,`RQRYBH`,`RYLX`,`XYRBH`,`XM`,`XBDM`,`XBMC`,`ZJZLDM`,`ZJZLMC`,`ZJHM`,`MZDM`,`MZMC`,`CSRQ`,`LXDH`,`RYBJ`,`STRQMJXM`,`STRQMJSFZHM`,`RQSJ`,`RQYYDM`,`RQYYMC`,`RQBZ`,`RQSXLXDM`,`RQSXLXMC`,`LQSJ`,`DCCLJGDM`,`DCCLJG`,`CQQXDM`,`CQQXMC`,`LQBZ`,`BJAJBM`,`ASJBH`,`BJJQBM`,`JJDBH`,`S_DJBWJCKDZ`,`S_BLWJCKDZ`,`S_RQJCJLWJCKDZ`,`DT_CREATE_TIME`,`DT_UPDATE_TIME`) 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); "
    for _ in range(10):
        name = faker.name()
        shenfenzheng = faker.ssn()
        phone = faker.phone_number()
        birth = faker.ssn()[6:14]
        num = faker.random_int(min=0, max=4, step=1)
        resultCode = random.choice(dcResultCodes)
        resultCodeName = dcResultCodeMap[resultCode]
        # 获取办公区及父级code
        baqCode = baqCodes[num]
        cursor.execute(parentDepartCodeSql, (baqCode,))
        results = cursor.fetchone()
        parentDepartCode = results[0]

        # 定义插入的值
        values = (
            str(uuid.uuid4()).replace("-", ""),
            parentDepartCode,
            baqCode,
            'P0' + str(uuid.uuid4()).replace("-", ""),
            '01',
            'P' + str(uuid.uuid4()).replace("-", ""),
            name,
            1,
            '男',
            222,
            '居民身份证',
            shenfenzheng,
            '01',
            '汉族',
            birth,
            phone,
            '08',
            '',
            '',
            faker.past_datetime(),
            '01',
            '随访',
            '',
            '',
            '',
            faker.past_datetime(),
            resultCode,
            resultCodeName,
            '',
            '',
            '',
            random.choice(AJBHS),
            random.choice(AJBHS),
            'JQ' + str(uuid.uuid4()).replace("-", ""),
            'JQ' + str(uuid.uuid4()).replace("-", ""),
            '',
            '',
            '',
            datetime.now(),
            datetime.now()
        )
        print(values)
        # 执行插入操作
        cursor.execute(sql, values)


    # 提交事务
    db.commit()
    # 关闭游标和数据库连接
    cursor.close()
    db.close()

代码说明

python操作数据库还需要安装数据库操作类，其他的用到了比如random,faker,uuid等类，可以简化批量创建数据的烦扰。这里也只是简单的用到了一些，python类库很强大，以后的工作当中会继续学习。

执行脚本,生成业务数据