python将excel导入mysql_Python将Excel数据自动导入MySQL,python,实现,excel,到,中

废话不多说，下面附上代码。

# -*- coding: utf-8 -*-

"""

Created on Mon Apr 20 14:18:49 2020

@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

)