使用Faker生成测试数据并写入数据库_faker生成大数据,写入数据库

'''
@Version: 1.0
@Autor: ace
@Date: 2020-04-23 12:30:39
@LastEditors: ace
@LastEditTime: 2020-04-26 13:24:25
@Description:  使用Faker生成数据并写入数据库
'''
import pymysql
import threading
from faker import Faker
import time
 
threads_maximum = 100  # 表数量: 和执行线程数绑定,建议小于150
commit_maximum = 500  # 每次提交数据量
table_name = "stu"  # 表名
data_size = 100000  # 每张表插入数据量
 
 
fake = Faker("zh-CN")
 
 
def creat_data(num):
    conn = pymysql.connect(host="127.0.0.1", port=3306,
                           user="root", password="root", db="test", charset="utf8")
    cursor = conn.cursor()
    # 这里给出表结构，如果使用已存在的表，可以不创建表。
    sql = """
        create table %s%s (
            id int unsigned auto_increment primary key COMMENT '自增id',
            company varchar(40) not null comment '公司名称',
            city_name varchar(64) comment '城市',
            street_name varchar(64) comment '街道',
            street_address varchar(64) comment '街道地址',
            postcode varchar(64) comment '邮政编码',
            ipv4 varchar(20) not null comment 'ipv4',
            password varchar(64) comment '密码'
        ) engine=InnoDB default charset=utf8;
        """ % (table_name, num)
    cursor.execute(sql)
    for i in range(1, 10001):
        sql = """insert into information%s(company, city_name, street_name, street_address, postcode, ipv4, password)
        values('%s', '%s', '%s', '%s', '%s', '%s', '%s')""" % (table_name, num, fake.company(), fake.city_name(), fake.street_name(), fake.street_address(), fake.postcode(), fake.ipv4(), fake.md5(raw_output=False))
        cursor.execute(sql)
        if(i % commit_maximum == 0):
            conn.commit()
    conn.commit()
    cursor.close()
    conn.close()
 
 
if __name__ == "__main__":
    start_time = time.time()
    threads = []
 
    for i in range(threads_maximum):
        threads.append(threading.Thread(target=creat_data, args=(i,)))
    for t in threads:
        t.setDaemon(True)
        t.start()
    for t in threads:
        t.join()
    end_time = time.time()
 
    print(f"程序执行完成,耗时{end_time-start_time}秒")