关于python:pymysql-三种插入方式的速度对比

9次阅读

共计 5160 个字符，预计需要花费 13 分钟才能阅读完成。

  • 计划一：应用 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)
正文完
python
发表至： python
2021-07-13
 0
关于python:Docker-又出了个新玩意儿
关于python:Python-里最具代表性的符号竟如此强大
关于python:PyCharm中文教程-13关闭烦人的灯泡提示
关于python:Python元祖介绍

站内搜索

-「」