- 计划一：应用 executemany 一次 + commit 一次
- 计划二：应用 execute 10000 次 + commit 一次
- 计划三：应用 （execute + commit） 都 10000 次
生成伪数据
fake: Faker = Faker(locale='zh_CN')start = time.time()tables = []for i in range(10000): row = (fake.name(), fake.text()) tables.append(row) # print(row)end = time.time()print(f'生成数据实现！ pay time is {end - start} s')connection: Connection = pymysql.connect( user='root', password='yourpassword', host='192.168.31.203', port=3306, database='test_001')计划一
print('开始计划一......')with connection: # 创立数据表 with connection.cursor() as cursor: cursor: Cursor sql = """ create table if not exists `tweet`( `id` bigint NOT NULL AUTO_INCREMENT, `username` varchar(255) NOT NULL, `content` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) """ cursor.execute(sql) start = time.time() # 插入数据 with connection.cursor() as cursor: cursor: Cursor sql = 'insert into tweet (`username`,`content`) values (%s,%s) ' cursor.executemany(sql, tables) connection.commit() end = time.time() print(f'计划一：插入数据实现！ pay time is {end - start} s') # 删除数据表 with connection.cursor() as cursor: cursor: Cursor sql = 'drop table tweet;' cursor.execute(sql)计划二
print('开始计划二......')connection: Connection = pymysql.connect( user='root', password='yourpassword', host='192.168.31.203', port=3306, database='test_001')with connection: # 创立数据表 with connection.cursor() as cursor: cursor: Cursor sql = """ create table if not exists `tweet`( `id` bigint NOT NULL AUTO_INCREMENT, `username` varchar(255) NOT NULL, `content` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) """ cursor.execute(sql) start = time.time() # 插入数据 with connection.cursor() as cursor: cursor: Cursor for row in tables: sql = 'insert into tweet (`username`,`content`) values (%s,%s) ' cursor.execute(sql, row) connection.commit() end = time.time() print(f'计划二：插入数据实现！ pay time is {end - start} s') # 删除数据表 with connection.cursor() as cursor: cursor: Cursor sql = 'drop table tweet;' cursor.execute(sql)计划三
print('开始计划三......')connection: Connection = pymysql.connect( user='root', password='yourpassword', host='192.168.31.203', port=3306, database='test_001')with connection: # 创立数据表 with connection.cursor() as cursor: cursor: Cursor sql = """ create table if not exists `tweet`( `id` bigint NOT NULL AUTO_INCREMENT, `username` varchar(255) NOT NULL, `content` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) """ cursor.execute(sql) start = time.time() # 插入数据 with connection.cursor() as cursor: cursor: Cursor for row in tables: sql = 'insert into tweet (`username`,`content`) values (%s,%s) ' cursor.execute(sql, row) connection.commit() end = time.time() print(f'计划三：插入数据实现！ pay time is {end - start} s') # 删除数据表 with connection.cursor() as cursor: cursor: Cursor sql = 'drop table tweet;' cursor.execute(sql)运行后果
生成数据实现！ pay time is 1.2480072975158691 s开始计划一......计划一：插入数据实现！ pay time is 2.1816399097442627 s开始计划二......计划二：插入数据实现！ pay time is 27.0793399810791 s开始计划三......计划三：插入数据实现！ pay time is 247.8990249633789 s能够看到
速度：计划一 > 计划二 > 计划三
残缺代码
import pymysqlfrom pymysql.connections import Connectionfrom pymysql.cursors import Cursorfrom faker import Fakerimport timefake: Faker = Faker(locale='zh_CN')start = time.time()tables = []for i in range(10000): row = (fake.name(), fake.text()) tables.append(row) # print(row)end = time.time()print(f'生成数据实现！ pay time is {end - start} s')connection: Connection = pymysql.connect( user='root', password='yourpassword', host='192.168.31.203', port=3306, database='test_001')print('开始计划一......')with connection: # 创立数据表 with connection.cursor() as cursor: cursor: Cursor sql = """ create table if not exists `tweet`( `id` bigint NOT NULL AUTO_INCREMENT, `username` varchar(255) NOT NULL, `content` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) """ cursor.execute(sql) start = time.time() # 插入数据 with connection.cursor() as cursor: cursor: Cursor sql = 'insert into tweet (`username`,`content`) values (%s,%s) ' cursor.executemany(sql, tables) connection.commit() end = time.time() print(f'计划一：插入数据实现！ pay time is {end - start} s') # 删除数据表 with connection.cursor() as cursor: cursor: Cursor sql = 'drop table tweet;' cursor.execute(sql)# --------------------------------------------------------------------print('开始计划二......')connection: Connection = pymysql.connect( user='root', password='yourpassword', host='192.168.31.203', port=3306, database='test_001')with connection: # 创立数据表 with connection.cursor() as cursor: cursor: Cursor sql = """ create table if not exists `tweet`( `id` bigint NOT NULL AUTO_INCREMENT, `username` varchar(255) NOT NULL, `content` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) """ cursor.execute(sql) start = time.time() # 插入数据 with connection.cursor() as cursor: cursor: Cursor for row in tables: sql = 'insert into tweet (`username`,`content`) values (%s,%s) ' cursor.execute(sql, row) connection.commit() end = time.time() print(f'计划二：插入数据实现！ pay time is {end - start} s') # 删除数据表 with connection.cursor() as cursor: cursor: Cursor sql = 'drop table tweet;' cursor.execute(sql)# --------------------------------------------------------------------print('开始计划三......')connection: Connection = pymysql.connect( user='root', password='yourpassword', host='192.168.31.203', port=3306, database='test_001')with connection: # 创立数据表 with connection.cursor() as cursor: cursor: Cursor sql = """ create table if not exists `tweet`( `id` bigint NOT NULL AUTO_INCREMENT, `username` varchar(255) NOT NULL, `content` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) """ cursor.execute(sql) start = time.time() # 插入数据 with connection.cursor() as cursor: cursor: Cursor for row in tables: sql = 'insert into tweet (`username`,`content`) values (%s,%s) ' cursor.execute(sql, row) connection.commit() end = time.time() print(f'计划三：插入数据实现！ pay time is {end - start} s') # 删除数据表 with connection.cursor() as cursor: cursor: Cursor sql = 'drop table tweet;' cursor.execute(sql)