廢話不多說，下面附上代碼。

# -*- 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

)

總結

以上是生活随笔為你收集整理的python将excel导入mysql_Python将Excel数据自动导入MySQL,python,实现,excel,到,中的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。