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 randomimport 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])# 获取办案区的父级codeparentDepartCodeSql = "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]# 获取办公区及父级codebaqCode = 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()