批量插入大量数据

最近有个任务需要给一个MySQL数据库的一个表插入 1亿条数据。之前并没有处理过这么大的数据量，所以用之前的 for 循环一条条插入实在是太慢了。
解决办法：批量插入。

思路：每次生成1w条数据，然后批量插入,重复循环1w次。

直接上代码

# -*- coding: utf-8 -*-
"""
@Time: 1/5/2019 7:30 PM
@Author: hejing
@Email: 906099915@qq.com
"""
import pymysql
import random
import time

# 批量插的次数
loop_count = 10000
# 每次批量查的数据量
batch_size = 10000
success_count = 0
fails_count = 0

conn = pymysql.connect(host='localhost', user='root', password='123456', database='test', charset='utf8')


def random_generate_datetime():
    a1 = (2018, 1, 1, 0, 0, 0, 0, 0, 0)  # 设置开始日期时间元组（1976-01-01 00：00：00）
    a2 = (2018, 12, 31, 23, 59, 59, 0, 0, 0)  # 设置结束日期时间元组（1990-12-31 23：59：59）

    start = time.mktime(a1)  # 生成开始时间戳
    end = time.mktime(a2)  # 生成结束时间戳

    t = random.randint(start, end)  # 在开始和结束时间戳中随机取出一个
    date_touple = time.localtime(t)  # 将时间戳生成时间元组
    datetime = time.strftime("%Y-%m-%d %H:%M:%S", date_touple)  # 将时间元组转成格式化字符串（1976-05-21）
    # print(datetime)
    return datetime

def random_generate_search_controller_data(base_cid, max_cid, base_light_pole_id):
    def _random_generate_search_controller_data():
        # get LightPole_id -- Controller_id
        Controller_id = random.randint(base_cid, max_cid)
        LightPole_id = Controller_id - base_cid + base_light_pole_id
        # Read_time
        Read_time = random_generate_datetime()
        # Electric_quantity 1--43
        Electric_quantity = random.randint(1, 43)
        # Electric_voltage 220~ 245
        Electric_voltage = random.randint(220, 245)
        # Electric_current 0 ~ 0.189
        Electric_current = random.randint(0, 189) / 1000
        # Power 0 ~ 46
        Power = random.randint(0, 46)
        return (
            LightPole_id,
            Controller_id,
            Read_time,
            '测试服务器',
            'T8-501',
            Electric_quantity,
            Electric_voltage,
            Electric_current,
            Power,
            '1',
            '0',
            '0',
            '1',
            '0',
            '100'
        )

    return _random_generate_search_controller_data


def execute_many(insert_sql, batch_data):
    global success_count, fails_count
    cursor = conn.cursor()
    try:
        cursor.executemany(insert_sql, batch_data)
    except Exception as e:
        conn.rollback()
        fails_count = fails_count + len(batch_data)
        print(e)
        raise
    else:
        conn.commit()
        success_count = success_count + len(batch_data)
        print(str(success_count) + " commit")
    finally:
        cursor.close()


def start(base_cid, max_cid, base_light_pole_id):
    try:
        insert_sql = "INSERT INTO `search_controller` (`s_Controller_id`, `LightPole_id`, `Controller_id`, `Read_time`, `Concentrator_name`, `Concentrator_address`, `Electric_quantity`, `Electric_voltage`, `Electric_current`, `Power`, `Power_factor`, `OneSwitch_status`, `TwoSwitch_status`, `Fault_status`, `LightOn_duration`, `brightness`) VALUES (NULL, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"

        # batch_count = 0
        begin_time = time.time()
        batch_data = []
        for x in range(loop_count):
            gen_fun = random_generate_search_controller_data(base_cid, max_cid, base_light_pole_id)
            batch_data = [gen_fun() for x in range(batch_size)]
            execute_many(insert_sql, batch_data)

        end_time = time.time()
        total_sec = end_time - begin_time
        qps = success_count / total_sec
        print("总共生成数据： " + str(success_count))
        print("总共耗时(s): " + str(total_sec))
        print("QPS: " + str(qps))

    except Exception as e:
        print(e)
        raise
    else:
        pass
    finally:
        pass


if __name__ == '__main__':
    
    base_cid = 1000
    max_cid = 10000 + base_cid - 1
    base_light_pole_id = 53

    start(base_cid, max_cid, base_light_pole_id)

测试结果：

...
99980000 commit
10000
99990000 commit
10000
100000000 commit
总共生成数据： 100000000
总共耗时(s): 12926.742953062057
QPS: 7735.900710883419

本机测试平均每秒插入7735条记录，耗时约3.5个小时。

参考资料：https://www.kissfree.cn/2235.html