# -*- coding: utf-8 -*-
# Author:benjamin
 
import pymysql
 
# 创建连接
conn = pymysql.connect(host='192.168.214.128', port=3306, user='root', passwd='ben123', db='db2')
 
# 创建游标
cursor = conn.cursor()
 
def createTable():
 '''
 创建数据库表
 :return:
 '''
  
 try:
  sql = '''
   create table mytable (
   nid int not null auto_increment primary key,
   name varchar(255) not null,
   email varchar(255) not null,
   extra text
   )engine=innodb default charset=utf8
  '''
  cursor.execute(sql)
  conn.commit()
  print('create table ok!')
 except Exception as e:
  print(e)
 
 
def myList(value):
 '''
 生成一个列表，[('admin1', 'admin1qq.com', 'hahaadmin1'),...]
 :param value: 自定义的数据量
 :return: new_list
 '''
 new_list = [] # 新建一个空列表用来存储元组数据
 
 for i in range(1, value + 1):
 
  name = 'admin'+ str(i)
  email = name + '@qq.com'
  extra = 'I am '+ name
 
  tup = (name,email,extra) # 构造元组
  new_list.append(tup) # [(),(),()...]
 
 print("*"*5+"generate list ok"+"*"*5)
 return new_list
 
 
def myInsert(newList):
 '''
 数据库插入
 :param newList: 传入的列表数据
 :return:
 '''
 
 try:
  sql = "insert into mytable(name,email,extra) values(%s,%s,%s)" # 要插入的数据
  cursor.executemany(sql,newList) # 执行插入数据
 
  conn.commit()
  cursor.close()
  conn.close()
  print('insert ok')
 except Exception as e:
  print(e)
 
 
if __name__ == '__main__':
 
 # 创建数据表
 createTable()
 # 选择要插入的数据量
 value = 1000000 # 定义数据量
 newList = myList(value)
 myInsert(newList)