[ pymysql ] 三种插入方式的速度对比

• 方案一：使用 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 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:
    # 创建数据表
    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)