企业生产实践--生产环境批量插入百万数据到数据库几种实现方式(一)

1、实现思路

• 1、设置数据库连接的配置文件；
• 2、使用mysqlclient库进行数据库连接对象及游标对象的创建
• 3、根据待插入数据表的各个列字段的数据类型约束以及取值范围约束进行各个列字段随机生成数据的方法设计；
• 4、分批进行数据生成和数据插入；
• 5、待所有数据全部插入完成之后，一次提交。

2、实现方式和主要依赖包

python版本：3.7.7
Python自带的模块
ConfigParser
time
os
random
hashlib

需要安装的模块MySQLdb
MySQLdb

3、脚本设计

import os
import time
from hashlib import sha1
from random import randint
from configparser import ConfigParser
import MySQLdb


cfg = ConfigParser()


def make_passwd():
    """[summary]
    使用sha1进行用户表的passwd字段的密码加密
    [description]
    
    Returns:
        [type] -- [description]
    """
    return sha1('123456'.encode('utf-8')).hexdigest()


class PyMySQLHelper(object):

    sqli = "INSERT INTO d_users(uid,nickname,passwd,roleId,gender,status,createtime) VALUES(%s,%s,%s,%s,%s,%s,%s)"
    conf_path = 'config.ini'
    

    @classmethod
    def get_params_dict(cls, conf_path):
        """[summary]
        [description]
            解析配置文件中mysql数据库参数列表
        Returns:
            [type] -- [description]
            返回值为一个字典
        """
        conn_params = dict()
        cfg.read(conf_path)
        conn_params['host'] = cfg.get('mysql', 'host')
        conn_params['user'] = cfg.get('mysql', 'user')
        conn_params['passwd'] = cfg.get('mysql', 'password')
        conn_params['port'] = cfg.get('mysql', 'port')
        conn_params['db'] = cfg.get('mysql', 'db')
        conn_params['charset'] = cfg.get('mysql', 'charset')
        return conn_params
    
    @classmethod
    def get_conn_object(cls, conn_params):
        """[summary]
        [description]
            获取数据库连接对象，同时创建游标对象
        Returns:
            [type] -- [description]
            返回值为一个元组
        """
        conn = MySQLdb.Connect(
            host=conn_params['host'], 
            port=int(conn_params['port']),
            user=conn_params['user'],
            passwd=conn_params['passwd'],
            db=conn_params['db'])
        cur = conn.cursor()
        return conn, cur
    
    @classmethod
    def get_insert_values(cls, start, end):
        """[summary]
        [description]
            生成待插入数据表的批量数据，
            对应每一个列字段生成随机数据，保证生成的每条数据记录都不重复
        Returns:
            [type] -- [description]
            返回值为一个列表
        """
        return [tuple([i, 
                "test0"+str(i), 
                str(make_passwd()),
                int(randint(1, 7)),
                str(randint(0, 1)),
                str(randint(0, 1)),
                time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
            ]) for i in range(start, end + 1)]
    
    @classmethod
    def insert_data_to_db(cls, counts=10, list_len = 1000):
        """[summary]
        [description]
            确定要插入的数据总数，分批进行插入，比如要插入100万行数据，每次生成10000条，循环100次就OK了
            list_len: 一次插入多少条数据， 默认为1000条，也可以是10000条，自己可以随意设置
            counts: 需要执行多少次， 如果要插入100000条，那就设置10次，插入1000000条，就设置100次即可
        Returns:
            [type] -- [description]
            返回值为一个列表
        """
        start_list =[count * list_len + 1 for count in range(counts)] 
        end_list = [count * list_len + list_len for count in range(counts)]
        conn_params = cls.get_params_dict(cls.conf_path)
        conn, cur = cls.get_conn_object(conn_params)
        for index in range(len(start_list)):
            datas = cls.get_insert_values(start_list[index], end_list[index])
            cur.executemany(cls.sqli, datas)
        cur.close()
        conn.commit()
        conn.close()


if __name__ == "__main__":
    PyMySQLHelper.insert_data_to_db()

config.ini配置大致如下：

[mysql]
host=192.168.x.xxx
port=3306
user=tom
password=HelloTom
db=test
charset=utf8

4、最终实现效果

4、脚本缺陷

1、只能操作特定的表，通用性不强（毕竟我当时做也是针对特定的场景写的），如果想直接使用当前脚本在别的数据表上面插入数据，无法直接使用，还需要修改一下几点配置：
1、配置文件里面的数据库链接信息；
2、需要修改数据表的insert模板语句：
要往哪张表插入数据，就把表名改成那张表，把需要插入的字段名也修改成要要插入的表的列字段名，有多少个字段名就用多少个%s占位符就行了
3、当模板sqli被修改了之后，PyMysqLHelper中的get_insert_values()方法也需要相应修改，其实也没有多复杂， 就是随机生成各种类型的数据罢了，改一改就能用， 没有多难的。

其他的就没有什么可以需要改的了，直接调 PyMySQLHelper.insert_data_to_db（counts=10, list_len = 1000） ,上面解释过了，这里不啰嗦了。

注意：
这个脚本有一个小坑，就是你批量输入的数据表如果有外键，建议你先把外键删了，在执行脚本，等到数据都插入完成之后再把表的外键加回来就行了，不这样做就会出现下面这个错误，尝试了很多方法都没有解决 。

5、下一步计划

等有时间了，我再优化优化，争取把这个脚本写的更加通用， 方便自己使用。

后面还会分享Python + shell 脚本的方式 、使用shell脚+存储过程的方式的大批量插入数据到数据库的脚本实现,很快更新哦，敬请期待！