python3 excel导入mysql_Python3动态导入Excel到Mysql数据库

```java

import xlrd

import pymysql

import datetime

filePath = input("文件地址:")

tabName = input("表名:")

isCreateTabe = input("是否建表(Y/N):")

file = xlrd.open_workbook(filePath) #打开Excel文件

sheet_1 = file.sheet_by_index(0) #根据sheet页的排序选取sheet

row_content = sheet_1.row_values(1) #获取指定行的数据，返回列表，排序自0开始

row_number = sheet_1.nrows #获取有数据的最大行数

col_number = sheet_1.ncols #获取有数据的最大列数

titles = sheet_1.row_values(0)

if(isCreateTabe == 'Y'):

sql0 = 'DROP TABLE IF EXISTS '+tabName+';'

sql1 = 'create table '+tabName+' ('

for c in range(col_number):

sql1 +=  str(titles[c])

ctype = sheet_1.cell(1,c).ctype

if(ctype == 3):

sql1 += ' datetime'

elif(ctype == 2):

sql1 += ' double'

else:

sql1 += ' varchar(255)'

if(c < col_number-1): sql1+=',\n'

sql1 += ');'

print('建表语句:')

print(sql1)

sql2 = 'insert into '+tabName+' values'

for r in range(1,row_number):

sql2 += '('

row = sheet_1.row_values(r)

for c in range(col_number):

val = str(row[c])

if(sheet_1.cell(r,c).ctype == 3):

date = xlrd.xldate_as_tuple(sheet_1.cell(r,c).value,0)

val = datetime.datetime(*date)

val = str(val)

sql2 += '\''+val+'\''

if(c < col_number-1): sql2+=','

sql2 += ')'

if(r < row_number-1): sql2+=',\n'

sql2 += ';'

print('数据语句:')

print(sql2)

db = pymysql.connect('127.0.0.1','root','12345678','dbgirl') #建立数据库连接

cusor = db.cursor() # 使用 cursor() 方法创建一个游标对象 cursor

try:

# 执行sql语句

if(isCreateTabe == 'Y'):

cusor.execute(sql0);

cusor.execute(sql1);

cusor.execute(sql2);

# 提交到数据库执行

db.commit()

except:

# 发生错误时回滚

print('repr(e):\t', repr(e))

db.rollback()

# 关闭数据库连接

db.close()

print('导入成功!')

```