# -*- coding: utf-8 -*-
# @Author : kai xu
# @Contact: xukai93@foxmail.com
# @File : Main.py
# @Time : 2019/6/28 23:19
# @Desc :不支持CSV格式,仅支持xlsm,xls,xlsx格式
import xlrd
import pymysql
import os
import datetime
from time import sleep
def listdir(path): #传入根目录
file_list = []
for file in os.listdir(path):
file_path = os.path.join(path, file)
# print(file_path)#获取绝对路径
file_list.append(file_path)
# print(file_list)
return file_list#返回Excel文件路径列表
for x in listdir("D:\Temp\Temp"):
data = xlrd.open_workbook(x)
table = data.sheets()[0]
t = table.col_values(1)
nrows = table.nrows
ops = []
for i in range(1,nrows):
r1 = table.row_values(i)
ops.append((x[13:],r1[0],r1[1],r1[2]))
conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='pachong', charset='utf8mb4')
try:
cur = conn.cursor()
sql='insert into `doudouche` values("%s","%s","%s","%s")' #原来SQL是这么tm写的啊!
# sql="insert into `doudouche` values('%s','%s','%s','%s')" #错误示范
# conn.ping(reconnect=True)
cur.executemany(sql, ops)
conn.commit()
print(str(x)+":数据已成功读取并上传至数据库--"+str(datetime.datetime.now())[0:22])
cur.close()
conn.close()
except Exception as e:
print(e)
Python批量读取文件夹中Excel文件并上传至MySQL
最新推荐文章于 2022-06-20 11:54:36 发布