NAVICAT导出的SQL文件,分割为多个批量加载语句的SQL文件

NAVICAT导出的SQL文件,分割为多个批量加载语句的SQL文件

# -*- coding: utf-8 -*-
"""
@Time    : 2021/7/15 16:04
@Author  : Little Duo
@File    : tools.py
"""

from tqdm import tqdm
import os


def gererateFixedLengthString(number, length=16, start='0'):
    """
    生成固定长度的串

    :param number: 源数字
    :param length: 生成数据的长度,默认16
    :param start: 开头数字或字符,默认0
    :return:
    """

    return str(start) + str(number).zfill(length)


def getFileLineCounts(filePath, endoding='utf8'):
    """
    获取文件总行数

    @param filePath:
    @param endoding:
    @return:
    """
    count = 0
    file = open(filePath, encoding=endoding)
    while True:
        buffer = file.read(10485760)
        if not buffer:
            break
        count += buffer.count('\n')
    file.close()
    return count


def osMkdirs(path):
    path = path.strip()
    path = path.rstrip("\\")
    isExists = os.path.exists(path)
    if not isExists:
        os.makedirs(path)
        return True


def splitSQLFile(filePath, pageSize: int = 10000):
    """

    @param filePath: 要分割的SQL文件路径(文件)
    @param pageSize: 以pageSize条SQL行进分割
    """

    fileTotalCount = getFileLineCounts(filePath)  # 文件实际的总条数
    tmpf = open(filePath, encoding='utf8')
    lineOne = tmpf.readline()  # sql文件第一行
    insertSQL = lineOne[:lineOne.index('VALUES') + 7]  # 获取insert 语句
    tmpf.close()

    fileName = os.path.basename(filePath).split('.')[0]  # 获取sql文件名
    newSavePath = os.path.dirname(filePath) + '\\' + fileName  # 分割后文件存放路径
    osMkdirs(newSavePath)

    wf = open(newSavePath + '\\' + gererateFixedLengthString(start='part', number=pageSize) + '.sql', 'w',
              encoding='utf-8')
    wf.write(insertSQL + '\n')
    f = open(filePath, encoding='utf8')

    totalCount = 0
    lineCount = 0
    processList = tqdm(range(fileTotalCount))  # 进度条
    for i in processList:
        processList.set_description_str('文件分割进度:')
        totalCount = totalCount + 1
        lineCount = lineCount + 1
        line = f.readline()
        line = line.replace(insertSQL, '')
        if lineCount != pageSize and totalCount != fileTotalCount:
            position = line.rindex(';')  # 找到最后一个分号的位置
            line = line[:position] + ',\n'
        wf.write(line)
        if (lineCount >= pageSize):
            lineCount = 0
            wf.close()
            wf = open(
                newSavePath + '\\' + gererateFixedLengthString(start='part', number=totalCount + pageSize) + '.sql', 'w',
                encoding='utf-8')
            wf.write(insertSQL + '\n')
    f.close()


if __name__ == '__main__':
    splitSQLFile(filePath=r'C:\Users\Admin\Desktop\trt_serv_b.sql')