Python MySQL利用load data infile加载大文件入表

有时候我们需要将大量数据批量写入数据库，直接使用程序语言和Sql写入往往很耗时间，其中有一种方案就是使用MySql Load data
infile导入文件的形式导入数据，这样可大大缩短数据导入时间。

假如是从MySql客户端调用，将客户端的文件导入，则需要使用 load local data infile.

LOAD DATA INFILE 语句以很高的速度从一个文本文件中读取行到一个表中。文件名必须是一个文字字符串。

1，开启load local data infile.

假如是Linux下编译安装，

如果使用源码编译的MySQL，在configure的时候，需要添加参数：--enable-local-infile 客户端和服务器端都需要，否则不能使用local参数。
./configure --prefix=/usr/local/mysql --enable-local-infile

make install

若是其它系统，可在配置文件中配置：
在MySql 配置文件My.ini文件中下面项中加入local-infile=1：
add:

[mysqld]
local-infile=1 
[mysql]
local-infile=1

客户端和服务端度需要开启，对于客户端也可以在执行命中加上--local-infile=1 参数：
mysql --local-infile=1 -uroot -pyourpwd yourdbname

如：
/usr/local/mysql/bin/mysql -uroot -h192.168.0.2 -proot databaseName --local-infile=1 -e "LOAD DATA LOCAL INFILE 'data.txt' into table test(name,sex) "

2, 编码格式注意：

若包含中文，请保证导入文件、连接字符串、导入表都是UTF-8编码。

3. 应用：

• 文件格式：
  20201101T010000Z_Index.old
• 文件内容：

13310009675473|13310009675473002|113000000|113000001|2020-11-01 01:04:37.152|2020-11-01 01:04:37.259|2|cctv4sd|100.64.113.4|0.00|2018061044912|99|0|1
13310009675473|13310009675473002|113000000|113000001|2020-11-01 01:06:15.82|2020-11-01 01:06:15.942|2|cctv4sd|100.64.113.4|0.00|2018061044912|99|0|1
13310009675473|13310009675473002|113000000|113000001|2020-11-01 01:03:59.006|2020-11-01 01:03:59.122|2|cctv4sd|100.64.113.4|0.00|2018061044912|99|0|1
13310009675473|13310009675473002|113000000|113000001|2020-11-01 01:35:14.976|2020-11-01 01:35:15.067|2|cctv4sd|100.64.113.4|0.00|2018061044912|99|0|1

• 代码

#!/usr/local/ete/python/bin/python3
import os
import sys
import zipfile

from common.logModule import logClass
from common.sqlFunction import mysqlClass

parent_path = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.append(parent_path)


class CdnRateRecord(logClass, mysqlClass):
    """cdn收视记录数据入库"""

    def __init__(self):
        """初始化"""
        logClass.__init__(self, logName='cdnRateRecord')
        # 日志
        self.setTimedRotatingFileHandler(fileName='cdnRateRecord')
        # mysql
        mysqlClass.__init__(self, database='anhuimobile')
        # 表名
        self.tableName = "cdnUserRateRecord"
        # 文件路径
        self.processPath = '/data/zte/'
        # 历史路径
        self.historyPath = '/data/history/'

    def loadData(self, file_path):
        """加载文件入库"""
        self.logger.info("load %s data to %s" % (file_path, self.tableName))
        self.connectSql()
        try:
            # load data infile 文件路径， into table 表名 ，fields terminated by 分隔符“|”， lines terminated by 换行'\\n'
            loadDateSql = "load data infile '%s' " \
                          "into table %s " \
                          "fields terminated by '|' " \
                          "lines terminated by'\\n' " \
                          "(bossUserID,userID,citycode,areacode,@startTime,@stopTime,businessType,programName,terminalIP,@flow,cpid,productid,serviceTime,cdnmanufacturer) " \
                          "set startTime=unix_timestamp(@startTime),stopTime=unix_timestamp(@stopTime)" % (
                              file_path, self.tableName)

            self.executeSql(loadDateSql)
        except Exception as e:
            self.logger.error(e)
        finally:
            self.disConnectSql()
            os.system('mv %s %s' % (file_path, self.historyPath))

    def unzip_file(self):
        """解压文件"""
        try:
            files = os.listdir(self.processPath)
            for file in files:
                if file.endswith(".old"):
                    src_file_path = self.processPath + file
                    dst_file_name = self.processPath + str(file).split(".")[0] + ".zip"
                    os.rename(src_file_path, dst_file_name)  # 重命名

                    res = zipfile.is_zipfile(dst_file_name)
                    if res is False:
                        continue
                    fz = zipfile.ZipFile(dst_file_name, 'r')  # 解压
                    for file in fz.namelist():
                        fz.extract(file, self.processPath)
                    os.system('mv %s %s' % (dst_file_name, self.historyPath))
        except Exception as e:
            self.logger.error(e)

    def main(self):
        self.unzip_file()  # 解压文件
        files = os.listdir(self.processPath)
        for file in files:
            if file.endswith(".log"):
                log_file_path = self.processPath + str(file)
                self.loadData(log_file_path)
            else:
                continue


if __name__ == '__main__':
    c = CdnRateRecord()
    c.main()