返回顶部

收藏

Python3 读取文本文件插入到Mysql数据库

更多
# -*- coding:utf8 -*-
#读取冰箱数据并插入到数据库
import time,os,glob,mysql.connector

def formatTime(millSecond):
    originTime = time.localtime(int(millSecond)/1000)
    return time.strftime('%Y-%m-%d %H:%M:%S',originTime)

def get_file_list(dir_path, extension_list):
    '''
    fuction: get_file_list(dir_path,extension_list)
    parms:
    dir_path - a string of directory full path. eg. 'D:user'
    extension_list - a list of file extension. eg. ['zip']
    return: a list of file full path. eg. ['D:user1.zip', 'D:user2.zip']
    '''
    os.chdir(dir_path)
    file_list = []
    for extension in extension_list:
        extension = '*.' + extension
        file_list += [ os.path.realpath(e) for e in glob.glob(extension) ]
    return file_list

def getConn():
    # 连接数据库
    conn = mysql.connector.connect(user='root', password='mysql', host='localhost',database='hjtmapp')
    return conn

file_list = get_file_list(u'C:/Users/12/Desktop/冰箱数据/冰箱原始数据',['txt']);
conn = getConn()
cursor = conn.cursor()
insertCount = 0
for fileName in file_list:
    index = 0
    with open(fileName, 'r') as f:
        for line in f.readlines():
            index += 1
            if index == 1:
                continue
            line = line.strip() # 把末尾的'\n'删掉
            record = line.split(',')
            if len(record)==4 and record[3]!='' and record[3]!='null':
                # 插入数据库
                operateTime = formatTime(record[1])
                cursor.execute('insert into fridge(tp,ts,mac_id,operate_code) values(%s,%s,%s,%s)',[record[0],operateTime,record[2],record[3]])
                insertCount += 1
                conn.commit()

cursor.close()
conn.close()

标签:python

收藏

0人收藏

支持

0

反对

0

发表评论