首页 > 数据库 > MySQL

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

来源：SegmentFault

时间：2023-01-23 16:02:05 450浏览收藏

亲爱的编程学习爱好者，如果你点开了这篇文章，说明你对《[ pymysql ] 三种插入方式的速度对比》很感兴趣。本篇文章就来给大家详细解析一下，主要介绍一下MySQL、python，希望所有认真读完的童鞋们，都有实质性的提高。

方案一：使用 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

可以看到

速度：方案一 > 方案二 > 方案三

完整代码

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

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)

今天关于《[ pymysql ] 三种插入方式的速度对比》的内容就介绍到这里了，是不是学起来一目了然！想要了解更多关于mysql的内容请关注golang学习网公众号！

mysql MySQL python

声明：本文转载于：SegmentFault 如有侵犯，请联系study_golang@163.com删除