• Option 1: Use ExecuteAny once + Commit once
  • Plan 2: Execute 10000 times + Commit once
  • Solution 3: Execute + Commit each 10000 times

Generating pseudo data

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' )

Plan a

Print (' start scheme 1...... ') WITH CONNECTION: # CREATE TABLE 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 () Cursor sql = 'insert into tweet (`username`,`content`) values (%s,%s) ' cursor.executemany(sql, Tables) connection.mit () end = time.time() print(f' Pay time is {end-start} s') # with connection. CURSOR () AS CURSOR: CURSOR: CURSOR ' cursor.execute(sql)

Scheme 2

Print (' start scheme 2...... ') connection: Connection = pymysql.connect(user='root', password='yourpassword', host='192.168.31.203', port=3306, DATABASE ='test_001') WITH CONNECTION: # CREATE DATABASE WITH CONNECTION. CURSOR () AS 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 () Cursor for row in tables: sql = 'insert into tweet (`username`,`content`) values (%s,%s) ' cursor.execute(sql, Row) connection.mit () end = time.time() print(f' Pay time is {end-start} s') # with connection. CURSOR () AS CURSOR: CURSOR: CURSOR ' cursor.execute(sql)

Plan 3

Print (' Start scheme three...... ') connection: Connection = pymysql.connect(user='root', password='yourpassword', host='192.168.31.203', port=3306, DATABASE ='test_001') WITH CONNECTION: # CREATE DATABASE WITH CONNECTION. CURSOR () AS 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 () Cursor for row in tables: sql = 'insert into tweet (`username`,`content`) values (%s,%s) ' cursor.execute(sql, Row) connection.mit () end = time.time() print(f') Pay time is {end-start} s') # with connection. CURSOR () AS CURSOR: CURSOR: CURSOR ' cursor.execute(sql)

The results

Data generation complete! Pay time is 1.2480072975158691 S start scheme 1...... Solution 1: Data insertion is complete! Pay time is 2.1816399097442627 s start scheme 2...... Solution 2: Data insertion is complete! Pay time is 27.0793399810791 s start scheme three...... Solution 3: Data insertion is complete! Pay time is 247.8990249633789 s

You can see

Speed: scheme one > scheme two > scheme three

The complete code

import pymysql from pymysql.connections import Connection from pymysql.cursors import Cursor from faker import Faker import time 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: # CREATE TABLE 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 () Cursor sql = 'insert into tweet (`username`,`content`) values (%s,%s) ' cursor.executemany(sql, Tables) connection.mit () end = time.time() print(f' Pay time is {end-start} s') # with connection. CURSOR () AS CURSOR: CURSOR: CURSOR 'cursor. The execute (SQL) # -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- print (' began to plan two... ') connection: Connection = pymysql.connect(user='root', password='yourpassword', host='192.168.31.203', port=3306, DATABASE ='test_001') WITH CONNECTION: # CREATE DATABASE WITH CONNECTION. CURSOR () AS 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 () Cursor for row in tables: sql = 'insert into tweet (`username`,`content`) values (%s,%s) ' cursor.execute(sql, Row) connection.mit () end = time.time() print(f' Pay time is {end-start} s') # with connection. CURSOR () AS CURSOR: CURSOR: CURSOR 'the cursor. The execute (SQL) # -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- print (' began to plan 3... ') connection: Connection = pymysql.connect(user='root', password='yourpassword', host='192.168.31.203', port=3306, DATABASE ='test_001') WITH CONNECTION: # CREATE DATABASE WITH CONNECTION. CURSOR () AS 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 () Cursor for row in tables: sql = 'insert into tweet (`username`,`content`) values (%s,%s) ' cursor.execute(sql, Row) connection.mit () end = time.time() print(f') Pay time is {end-start} s') # with connection. CURSOR () AS CURSOR: CURSOR: CURSOR ' cursor.execute(sql)