废话不多说，下面附上代码。
# -*- coding: utf-8 -*-
"""
Created on Mon Apr 20 14:18:49
@author: admin
"""
import os
import pandas as pd
#import cx_Oracle as cx
from sqlalchemy import create_engine
import pymysql
file_name=[]
#获得文件的路径
def get_file(file_dir):
for parent, dirnames, filenames in os.walk(file_dir):
for filename in filenames:
a = os.path.join(parent, filename)
file_name.append(a)
#return file_name
#调用函数
get_file(r'C:\Users\admin\Desktop\tech\table')
for i in file_name:
df=pd.DataFrame(pd.read_excel(i))
#转为字符串
df1=df.astype(str)
b = df.columns.size
#重命名列名
col_name = []
for j in range(b):
a ='col_'+ str(j)
col_name.append(a)
#行数
m = df.shape[0]
#列数
n = df.shape[1]
Matrix = [[0 for x in range(n)] for y in range(m)]
#去除单元格前后的空格
for k in range(m):
for j in range(n):
Matrix[k][j]=df1.iloc[k][j].strip()
df2 = pd.DataFrame(Matrix,columns=col_name)
df2.columns = col_name
df2 = df2.applymap(lambda x: x if str(x) != 'nan' else '')
#截取表名
table_name=i.split('\\')[-1].split('.')[0]
#调用sqlalchemy包自动生成表插入数据
try:
engine=create_engine('mysql+pymysql://root:123@192.168.3.274:3336/test?charset=utf8',encoding='utf8')
df2.to_sql(table_name,con=engine,if_exists='replace',index=False)
except UnicodeEncodeError:
print(table_name+'报错了')
continue
生成的表结构：如下所示：
CREATE TABLE pub_newhigh_02_jc (
XH bigint(20) NULL,
ENTNAME text NULL,
UNISCID text NULL,
ZCDZ text NULL,
QYFZR text NULL,
FZRDH text NULL,
RZID text NULL,
RDND bigint(20) NULL,
YYSRZE double NULL,
XSSR double NULL,
GXJSCPSR double NULL,
JCKZE double NULL,
YJKAJF double NULL,
LRZE double NULL,
JLR double NULL,
SJSJSF double NULL,
JNZZSE double NULL,
JNSDSE double NULL,
JMSZE double NULL,
CYRYSL bigint(20) NULL,
WJZJSL bigint(20) NULL,
DNZLSQSL bigint(20) NULL,
DNSQFMZLSL bigint(20) NULL,
QMYYYYZLSL bigint(20) NULL,
QMYYFMZLYXSL bigint(20) NULL,
QMYYRJZZQ bigint(20) NULL,
DNHDRJZZQ bigint(20) NULL,
QMJCDLSJZYQ bigint(20) NULL,
QZDNJCDLBTSJZYQ bigint(20) NULL,
QMZWXPZSL bigint(20) NULL,
QZDNZWXPZSL bigint(20) NULL,
QMGJXYSL bigint(20) NULL,
QZDNGJXYSL bigint(20) NULL,
QMGJYJZYBHPZSL bigint(20) NULL,
QZDNGJYJZYBHPZSLbigint(20) NULL,
SSQY bigint(20) NULL,
GXJSLX text NULL,
YXQQ double NULL,
YXQZ double NULL,
BSC double NULL,
SSDS double NULL,
SFSCRD double NULL,
JFSJRQ double NULL,
ZJZ double NULL,
RCTDSL double NULL,
QMYYSYXXZLYXSL double NULL,
DNSQSYXZLSL double NULL,
QMYYWGZLYXSL double NULL,
DNSQWGZLSL double NULL,
QMGJJNZWPZSL double NULL,
QZDNGJJNZWPZSL double NULL,
QMYYQTZLYXSL double NULL,
DNSQQTZLSL double NULL,
YFTRBL double NULL,
YFTRZZL double NULL,
GNYFTRBL double NULL,
LRZZL double NULL,
ZSRZZL double NULL,
ZCFZL double NULL
)
如果觉得《python将excel导入mysql_Python将Excel数据自动导入MySQL python 实现 excel 到 中》对你有帮助，请点赞、收藏，并留下你的观点哦！
