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

以下代码由PHP站长网 52php.cn收集自互联网

现在PHP站长网小编把它分享给大家，仅供参考

# -*- 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)',[record[0],operateTime,record[2],record[3]])
				insertCount += 1
				conn.commit()

cursor.close()
conn.close()

以上内容由PHP站长网【52php.cn】收集整理供大家参考研究

如果以上内容对您有帮助，欢迎收藏、点赞、推荐、分享。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!