用python读取excel表格 并写进数据库

安装xlrd模块：pip install xlrd
安装mysql模块：pip install pymysql

import xlrd
import pymysql

book = xlrd.open_workbook(r'D:\测试\test.xlsx')
sheet = book.sheet_by_name('students')  # 获取到工作表
num = sheet.nrows  # 获取到有效行数
# 读取excel表格
for i in range(0, num):
    info = sheet.row_values(i)
    print(info)

# 连接mysql
db = pymysql.connect('127.0.0.1', 'root', '123456', 'student', 3306)
db.set_charset('utf8')
c = db.cursor()
# 执行sql语句
sql = "insert into students(sname,gender,age,education,place,phone,remark,email) values(%s,%s,%s,%s,%s,%s,%s,%s)"
for i in range(1, sheet.nrows):
    sname = sheet.cell(i, 0).value
    gender = sheet.cell(i, 1).value
    age = sheet.cell(i, 2).value
    education = sheet.cell(i, 3).value
    place = sheet.cell(i, 4).value
    phone = sheet.cell(i, 5).value
    remark = sheet.cell(i, 6).value
    email = sheet.cell(i, 7).value
    values = (sname, gender, age, education, place, phone, remark, email)
    c.execute(sql, values)
db.commit()  # 提交
c.close()  # 关闭
db.close()  # 关闭