轻量快速处理txt文本数据到sql

文件信息如下:

25.741429	25.666774	25.502521	25.598025	25.433203	25.722867	25.528502	25.233629
25.678774	25.638237	25.402524	25.607435	25.361613	25.710895	25.510187	25.209288
25.770144	25.709364	25.369660	25.556364	25.317734	25.734124	25.503375	25.208610
25.860532	25.770554	25.404431	25.522391	25.305215	25.747192	25.504935	25.239305
25.823904	25.728357	25.455887	25.534948	25.319969	25.716611	25.506053	25.259907

1. mysql load data infile

需要打开mysql客户端,并执行mysql命令(同时需要设置mysql的my.cnf文件的secure-file-priv字段设置上传的文件夹,赋予权限

# __author__:"Ye Zhaoliang"
# __version__:V1.0
# 技术要求: 1. 文件名代表时间戳 年月日小时分钟秒.txt
#           2. 要求输出文件的每行记录追加一行时间戳,该时间戳每0.02s一个记录
#           3. 要求最好输出到一个文件,并且最后转化为sql语句
# 技术点:   1. 文件处理 包括遍历文件(glob模块)、文件名处理(os.path)
#           2. 时间处理 包括datetime.datetime模块, time.time  datetime.datetime.strptime(组装为时间结构1, 该结构可以执行时间相加减,然后再用strftime拆装出来)  datetime.datetime.strftime(拆装时间结构1)
# 研究内容: 1. 采用python工具,批量合并txt文件,形成一个merge.txt
#           2. 通过mysql工具,批量载入merge.txt
#                 load data infile "e:/uploads/merge.txt" into table qiuxu fields terminated by "\t" lines terminated by "\n";
#           3. 导出sql
# https://blog.csdn.net/weixin_42819452/article/details/110228102
# 创建日期:  2022-03-02 14:55

import glob     ## 遍历文件
import os       ## 处理文件
import datetime ## 处理时间

## 文件常量
dirname="e:/uploads"
outputname=f'{dirname}/modified/merge.txt'

## 时间常量
timeDelta=datetime.timedelta(seconds=0.02)  ## 每隔0.02s一个数据
format = '%Y-%m-%d %H:%M:%S %f' ### 日期格式
formatOut = '%Y-%m-%d %H:%M:%S.%f' ### 日期格式  format和format2类似,只是为了显示好看


with open(outputname, 'a') as writer:
    for filenameOri in glob.glob(f'{dirname}/*.txt'):

## 不是逐行读取?
        with open(filenameOri, 'r') as reader:
            line="fine"
            filename=os.path.splitext(os.path.basename(filenameOri))[0]

            year=filename[0:4]
            month=filename[4:6]
            day=filename[6:8]
            hour=filename[8:10]
            minute=filename[10:12]
            second=filename[12:14]
            microsecond=0  ## 微秒
            #millsecond=0  ##毫秒
            # print(f'{os.path.basename(filename)}')
            # print(f'{year}-{month}-{day} {hour}:{minute}:{second} {microsecond}')
            currentDateTime=datetime.datetime.strptime(f'{year}-{month}-{day} {hour}:{minute}:{second} {microsecond}',format)  ###起始时间
            while(line):
                line=reader.readline().strip()
                if not line:
                    break
                currentDateTime=currentDateTime+timeDelta ## 每一行累加0.02s
                # print(f'{newDateTime}    {type(newDateTime) }')
                print(f'{currentDateTime.strftime(formatOut)[0:23]}\t{line}\n')
                writer.write(f'{currentDateTime.strftime(formatOut)[0:23]}\t{line}\n')
            # with open(filename,'r'):


# format = '%Y-%m-%d %H:%M:%S %f'
# a = datetime.datetime.strptime("2017-03-09 08:51:51 615", format)
# b = datetime.datetime.strptime("2017-03-09 08:51:51 617", format)
# b.strftime('%Y-%m-%d %H:%M:%S.%f')[0:23]
# Out[72]: '2017-03-09 08:51:51.617'
# Out[73]: datetime.datetime(2017, 3, 9, 8, 51, 51, 617000)
# b+datetime.timedelta(seconds=0.02)
# Out[74]: datetime.datetime(2017, 3, 9, 8, 51, 51, 637000)
# b+datetime.timedelta(seconds=0.02)
# Out[75]: datetime.datetime(2017, 3, 9, 8, 51, 51, 637000)

2. python直接字段导入

方法1需要断开mysql服务,设置my.cnf,启动mysql服务,然后编程形成merge.txt,最后打开mysql客户端,导入merge.txt,较为繁杂!

如何更轻量化执行? 直接建立各个列的字段,python和mysql数据字段直接挂接,逐行导入即可。据测试速度还可以

#!_*_coding:utf-8_*_
# __author__:"Ye Zhaoliang"
# __version__:V1.0
# 技术要求: 1. 文件名代表时间戳 年月日小时分钟秒.txt
#           2. 要求输出文件的每行记录追加一行时间戳,该时间戳每0.02s一个记录
#           3. 要求最好输出到一个文件,并且最后转化为sql语句
#  逐条写进数据库
# 创建日期:  2022-03-02 14:55

import glob     ## 遍历文件
import os       ## 处理文件  清理文件
import datetime ## 处理时间
import pymysql  ## 连接数据库

dirname="e:/uploads"
## 时间常量
timeDelta=datetime.timedelta(seconds=0.02)  ## 每隔0.02s一个数据
format = '%Y-%m-%d %H:%M:%S %f' ### 日期格式
formatOut = '%Y-%m-%d %H:%M:%S.%f' ### 日期格式  format和format2类似,只是为了显示好看

##https://www.zhihu.com/question/323688843  链接数据库 ##连接数据库配置
config = {
    'host':'localhost',
    'port':3306,
    'user':'root',
    'passwd':'457866',
    'database':'testinsert',
    'charset':'utf8mb4',
    'local_infile':1
}
table_name="qiuxu"
conn=pymysql.connect(**config)  ## 载入config字典
cur=conn.cursor()
## 使用数据库
# cur.execute(f'use {database}')
cur.execute(f'use {config["database"]}')
##设置编码格式
cur.execute('SET NAMES utf8;')

## 执行数据导入程序
for filenameOri in glob.glob(f'{dirname}/*.txt'):

## 不是逐行读取?
    with open(filenameOri, 'r') as reader:
        line="fine"
        filename=os.path.splitext(os.path.basename(filenameOri))[0]

        year=filename[0:4]
        month=filename[4:6]
        day=filename[6:8]
        hour=filename[8:10]
        minute=filename[10:12]
        second=filename[12:14]
        microsecond=0  ## 微秒
        #millsecond=0  ##毫秒
        currentDateTime=datetime.datetime.strptime(f'{year}-{month}-{day} {hour}:{minute}:{second} {microsecond}',format)  ###起始时间
        while(line):
            line=reader.readline().strip()
            dataman=line.split("\t")
            ## filename col1 col2 col3...col8
            # https://codeantenna.com/a/ETyf7JZGYe
            if not line:
                break
            currentDateTime=currentDateTime+timeDelta ## 每一行累加0.02s
            row_count=cur.execute(data_sql,[currentDateTime,dataman[0],dataman[1],dataman[2],dataman[3],dataman[4],dataman[5],dataman[6],dataman[7]])

## 提交数据
conn.commit()
## 关闭连接
conn.close()
cur.close()
Related
叶昭良
叶昭良
Engineer of offshore wind turbine technique research

My research interests include distributed energy, wind turbine power generation technique , Computational fluid dynamic and programmable matter.