1-MySQL - my_idb.sql

目录

• about
• 代码示例

• 返回MySQL目录

about

本篇主要使用pymysql批量生成个人信息数据。

代码示例

import time
import faker  # pip install faker
import pymysql
from pymysql.connections import CLIENT

fk = faker.Faker(locale='zh_CN')

conn = pymysql.Connect(
    host='10.0.0.200', user='root', password='123',
    database='', charset='utf8', client_flag=CLIENT.MULTI_STATEMENTS)
cursor = conn.cursor()


def timmer(func):
    def wrapper(*args, **kwargs):
        start = time.time()
        res = func(*args, **kwargs)
        print('{} running: {}'.format(func.__name__, time.time() - start))
        return res

    return wrapper


def create_table(databaseName, tableName):
    """ 创建表 """
    sql = """
    DROP DATABASE IF EXISTS {databaseName};
    CREATE DATABASE {databaseName} CHARSET utf8;
    USE {databaseName};
    DROP TABLE IF EXISTS {tableName};
    CREATE TABLE {tableName}(
      id int not null primary key unique auto_increment,
      name varchar(32) not null default "张开",
      addr varchar(128) not null default "",
      phone varchar(32) not null,
      email varchar(64) not null
    ) ENGINE=INNODB CHARSET=utf8;
    """.format(databaseName=databaseName, tableName=tableName)
    # 注意，一次性执行多行sql，必须在连接时，指定client_flag=CLIENT.MULTI_STATEMENTS
    cursor.execute(sql)
    conn.commit()


@timmer
def insert_many(num, tableName):
    """ 批量插入 """
    gen = ((i, fk.name(), fk.address(), fk.phone_number(), fk.email()) for i in range(1, num + 1))
    # print(gen)  # <generator object insert_many.<locals>.<genexpr> at 0x000001A68843C360>
    sql = "insert into {tableName}(id, name, addr, phone, email) values(%s, %s, %s, %s, %s);".format(
        tableName=tableName)
    cursor.executemany(sql, gen)
    conn.commit()


if __name__ == '__main__':
    num = 1000000
    database = "my_idb"
    table = 't1'
    create_table(database, table)
    insert_many(num, table)  

    cursor.close()
    conn.close()

---

that's all