python小脚本导入数据到本地mysql

代码

import xlrd
import pymysql


# filename = eval(input("输入Excel表所在位置："))
filename = input("输入Excel表所在位置：")
# 打开excel表
major = xlrd.open_workbook(filename)
# 表中表属于是，麻了
sheetName = input("输入Excel表的sheet：")
sheet = major.sheet_by_name(sheetName)

# 建立一个Mysql连接
username = input("输入数据库账号：")
password = input("输入数据库密码：")
dbName = input("输入数据库名称：")
tableName = input("输入表名：")
connection = pymysql.connect(
    host='localhost',
    user=username,
    password=password,
    # 我们数据库
    db=dbName,
    port=3306,
    charset='utf8'
)
# 获取游标
cur = connection.cursor()
# 创建插入SQL数据
col = input("输入表字段(和excel数据对应)通过逗号(英文)隔开: ")
colList = col.split(",",-1)
colNum = len(colList)
tempList = []
for i in range(0,colNum):
    tempList.append("%s")
query = \
    'insert into '+tableName+' ' \
    '('+col+')' \
    'values ('+(",".join(tempList))+')'
# 循环读取每行数据，从第二行开始，跳过标题
for obj in range(1,sheet.nrows):
    values = []
    for i in range(0,colNum):
        values.append(sheet.cell(obj,i).value)
    #执行插入语句
    try:
        cur.execute(query,tuple(values))
    except Exception as e:
        print(f'异常数据展示：{values}')
cur.close()
connection.commit()
connection.close()
columns = str(sheet.ncols)
rows = str(sheet.nrows)
print(f'导入{columns}列，{rows}行数据到{dbName}数据库!')

展示