【举个例子】
需要在学生表里插入10w条不同的数据，包括学生的编号（唯一）、姓名、性别、年龄、入学时间、班级、邮箱、手机号。
目录
方法一（执行语句耗时13s）：
1、通过python生成包含insert语句的txt文件
可以先尝试生成10条（把里面的100000改成10即可），成功后再生成10w条。
import random
import string
import time
#生成name
def create_name():
first_name = ["柳", "令狐", "慕容","文","西门","秦","向","迪丽","虞","白","诸葛","古","老","方","房","成"]
dict = ["鹤", "冲", "棣","钱","纤","露","璐","热","森","子","汤","杏","魅","格","凉","凌","牧","暮","神","贤"]
last_name = ''
for i in range(random.randint(1,2)):
last_name += random.choice(dict)
return random.choice(first_name)+last_name
#生成date
def date():
a1=(2013,1,1,0,0,0,0,0,0) #设置开始日期时间元组（2013-01-01 00：00：00）
a2=(2017,12,31,23,59,59,0,0,0) #设置结束日期时间元组（2017-12-31 23：59：59）
start=time.mktime(a1) #生成开始时间戳
end=time.mktime(a2) #生成结束时间戳
t = random.randint(start, end) #在开始和结束时间戳中随机取出一个
date_touple = time.localtime(t) #将时间戳生成时间元组
return time.strftime("%Y-%m-%d %H:%M:%S", date_touple)
#生成email
def create_email():
# 首先生成用户名
# 规定用户名由6~8位的随机大小写字母加数字组成
chars = string.ascii_letters + string.digits
n1 = random.choice((6,8))
user_name = ''
for i in range(n1):
c = random.choice(chars)
s = str(c)
user_name += s
# 再生成域名
# 规定域名由3~5位的随机大小写字母加数字组成
n2 = random.choice((3,5))
domain_name = ''
for i in range(n2):
c = random.choice(chars)
s = str(c)
domain_name += s
# 随机选取后缀名
list2 = ['.com','.org','.net','.cn']
last_name = random.choice(list2)
return user_name+'@'+domain_name+last_name
insert_sql = "INSERT INTO `wyy_test`.`t_student` VALUES "
with open("b.txt", "a") as fp:
fp.write(insert_sql+"\n")
for id in range(100000):
a = "('%s', '%s', '%s','%s','%s','%s班','%s','%s'),"%(str(id+1),create_name(),random.choice('男女'),random.randint(18,24),date(),random.randint(1,10),create_email(),str(id+18600000000))
with open("b.txt", "a", encoding="utf-8") as fp:
fp.write(a+"\n")2、复制txt文件里的insert语句
注意将最后的“，”改成“；”
3、在mysql执行该insert语句
如果字符串超过限制，会抛出异常并提示执行语句的长度，将max_allowed_packet改成大于执行语句的长度，然后重新执行语句即可。
show global variables like 'max_allowed_packet';?——?查看最大允许包
set global max_allowed_packet=xxx; ——?设置最大允许包
方法二（执行语句耗时3s）：
1、直接python连接mysql，执行生成的语句
import pymysql
import random
import string
import time
dbinfo = {
"host": "xx.xx.xx.xxx",
"user": "xxxx",
"password": "xxxxxxxx",
"port": xxxx}
class DbConnect():
def __init__(self, db_cof, database=""):
self.db_cof = db_cof
self.db = pymysql.connect(database=database,
cursorclass=pymysql.cursors.DictCursor,
**db_cof)
self.cursor = self.db.cursor()
def execute(self, sql):
try:
self.cursor.execute(sql)
self.db.commit()
except:
self.db.rollback()
def close(self):
self.db.close()
if __name__ == '__main__':
#生成name
def create_name():
first_name = ["柳", "令狐", "慕容","文","西门","秦","向","迪丽","虞","白","诸葛","古","老","方","房","成"]
dict = ["鹤", "冲", "棣","钱","纤","露","璐","热","森","子","汤","杏","魅","格","凉","凌","牧","暮","神","贤"]
last_name = ''
for i in range(random.randint(1,2)):
last_name += random.choice(dict)
return random.choice(first_name)+last_name
#生成date
def date():
a1=(2013,1,1,0,0,0,0,0,0) #设置开始日期时间元组（2013-01-01 00：00：00）
a2=(2017,12,31,23,59,59,0,0,0) #设置结束日期时间元组（2017-12-31 23：59：59）
start=time.mktime(a1) #生成开始时间戳
end=time.mktime(a2) #生成结束时间戳
t = random.randint(start, end) #在开始和结束时间戳中随机取出一个
date_touple = time.localtime(t) #将时间戳生成时间元组
return time.strftime("%Y-%m-%d %H:%M:%S", date_touple)
#生成email
def create_email():
# 首先生成用户名
# 规定用户名由6~8位的随机大小写字母加数字组成
chars = string.ascii_letters + string.digits
n1 = random.choice((6,8))
user_name = ''
for i in range(n1):
c = random.choice(chars)
s = str(c)
user_name += s
# 再生成域名
# 规定域名由3~5位的随机大小写字母加数字组成
n2 = random.choice((3,5))
domain_name = ''
for i in range(n2):
c = random.choice(chars)
s = str(c)
domain_name += s
# 随机选取后缀名
list2 = ['.com','.org','.net','.cn']
last_name = random.choice(list2)
return user_name+'@'+domain_name+last_name
insert_sql = "INSERT INTO `wyy_test`.`t_student` VALUES "
insert_values = "".join(["('%s', '%s', '%s','%s','%s','%s班','%s','%s'),"
%(str(id+1),create_name(),random.choice('男女'),random.randint(18,24),date(),random.randint(1,10),create_email(),str(id+18600000000))
for id in range(100000)])
sql = insert_sql + insert_values[:-1] + ";"
time1 = time.time()
db = DbConnect(dbinfo, database="wyy_test")
db.execute(sql)
db.close()
time2 = time.time()
print("总过耗时：%s秒" % (time2 - time1))