# -*- coding: utf-8 -*-
# Created by Y.W. on 2017/7/31 17:46.
import pymysql
import xlrd
# 获取xlsx文件,获取sheet文件
try:
book = xlrd.open_workbook('D:/test.xls')
sheet = book.sheet_by_name(u'Sheet1')
except Exception as e:
print(str(e))
# 打开数据库连接,并指明用utf-8方式连接数据库
db = pymysql.connect(host='localhost', user='yangshaoping', password='yangshaoping', database='test', charset='utf8')
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 使用 execute() 方法执行 SQL,如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS TEST1")
# 使用预处理语句创建表
createSql = """CREATE TABLE TEST1 (
product VARCHAR(20) NOT NULL,
num VARCHAR(5))"""
cursor.execute(createSql)
# 创建插入SQL语句
query = """INSERT INTO TEST1 (product, num) VALUES(%s,%s)"""
# 创建一个for循环迭代读取xlsx文件每行数据的, 从第二行开始是要跳过标题
for r in range(1, 4):
product = sheet.cell(r, 0).value
num = sheet.cell(r, 1).value
values = (product, num)
cursor.execute(query, values)
# 确认提交
db.commit()
# 关闭游标
cursor.close()
# 关闭数据库连接
db.close()