实战：利用Python脚本生成MySQL测试数据

实战：利用Python脚本生成MySQL测试数据

新建一个用户表

drop table if exists tb_user;

create table tb_user(
    id int primary key auto_increment comment '主键',
    name varchar(20) comment '姓名',
    phone varchar(11) comment '手机号',
    profession varchar(50) comment '专业',
    age int comment '年龄',
    status int comment '状态',
    email varchar(50) comment '邮箱'
);

利用Python脚本生成10万条测试数据

# Python >= 3.7.0
# 安装依赖 pip install faker records mysqlclient
from faker import Faker
import records

# 简体中文：zh_CN
faker = Faker(locale="zh_CN")
# 指定随机种子，确保每次生成的数据都是一致的
faker.seed(1)

# 生成数据插入 insert sql
def get_insert_sql(table_name, fields):
    keys = ', '.join([f'`{key}`'.format(key) for key in fields])
    values = ', '.join([f':{key}'.format(key) for key in fields])
    return f'INSERT INTO `{table_name}` ({keys}) VALUES ({values})'

# 获取数据
def get_row():
    return {
        'name': faker.name(),
        'phone': faker.phone_number(),
        'email': faker.email(),
        'age': faker.random_int(20, 30),
        'status': faker.random_int(0, 1),
        'profession': faker.job(),
    }


def main():
    db = records.Database('mysql://root:123456@localhost/data?charset=utf8')

    # 10 * 10000 = 10万条数据
    for i in range(10):
        data = [get_row() for _ in range(1000)]
        sql = get_insert_sql('tb_user', data[0].keys())
        db.bulk_query(sql, data)

if __name__ == '__main__':
    main()

查看生成的测试数据

mysql> select count(*) from tb_user;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+

select * from tb_user limit 10;
+----+-----------+-------------+---------------------------------+------+--------+---------------------+
| id | name      | phone       | profession                      | age  | status | email               |
+----+-----------+-------------+---------------------------------+------+--------+---------------------+
|  1 | 费阳      | 13777763170 | 法务经理                        |   27 |      1 | wyao@gmail.com      |
|  2 | 祁海燕    | 13400806360 | 日式厨师                        |   23 |      0 | jwan@jin.cn         |
|  3 | 姬秀英    | 18281241586 | 食品/饮料研发                   |   29 |      0 | li97@wang.cn        |
|  4 | 官桂芳    | 15625851781 | 前台接待/总机/接待生            |   20 |      1 | fpeng@chang.cn      |
|  5 | 应秀珍    | 13030388368 | 酒店前台                        |   20 |      1 | qiang48@hotmail.com |
|  6 | 亢婷      | 18207598386 | 药品市场推广主管/专员           |   28 |      1 | ping50@hotmail.com  |
|  7 | 仰俊      | 13192184011 | 机场代表                        |   24 |      0 | wcai@liang.net      |
|  8 | 匡洁      | 13622482447 | 汽车电工                        |   24 |      1 | htang@gmail.com     |
|  9 | 程建华    | 13748396030 | 市场通路经理/主管               |   28 |      1 | fangguo@yahoo.com   |
| 10 | 岳荣      | 15080695604 | 培训督导                        |   24 |      1 | fanglong@ding.com   |
+----+-----------+-------------+---------------------------------+------+--------+---------------------+
10 rows in set (0.00 sec)