# 需要sqlalchemy, pandas,mysql-connector
from sqlalchemy import create_engine
import pandas as pd
import os
import datetime
starttime = datetime.datetime.now()
path = r'H:\京津冀暴雨\数据分批下载'
files = os.listdir(path)
unreadable_files = []
for i in files:
try:
print(i)
path1 = path + '\\' + i
print(path1)
data = pd.read_excel(path1,sheet_name='全部')
# data = pd.read_excel(path1, sheet_name='Sheet1')
engine = create_engine('mysql+pymysql://root:111111@127.0.0.1:3306/socialriskdb?charset=utf8mb4')
data.to_sql(name='jjjdy', con=engine.connect(), if_exists="append", index=False, chunksize=100)
print('导入' + i + '成功')
except Exception as e:
print(f'{i} 无法打开或读取，错误信息：{str(e)}')
unreadable_files.append(f'{i} 无法打开或读取，错误信息：{str(e)}')
endtime = datetime.datetime.now()
x = endtime - starttime
print(x.seconds)
with open('unreadable_files.txt', 'w') as f:
for filename in unreadable_files:
f.write(f"{filename}\n")
