Python读取excel文件数据并插入数据库

例子： 将excel文件StudentInfo.xls的学生信息插入到student表中

注: 使用的版本：Python3.7，MySQL5.5

一、连接mysql数据库

1. 安装第三方库pymysql：pip install pymysql（Python2中则使用mysqldb）
2. 调用pymysql.connect()方法连接数据库，代码如下

import pymysql

# 打开数据库连接
conn = pymysql.connect(
    host='localhost',  # MySQL服务器地址
    user='root',  # MySQL服务器端口号
    password='root',  # 用户名
    charset='utf8',  # 密码
    port=3308,  # 端口
    db='test',  # 数据库名称
)

# 使用cursor()方法获取操作游标
c = conn.cursor()
sql = "show databases"
# 使用execute方法执行SQL语句
c.execute(sql)
# 使用 fetchone() 方法获取一条数据
res = c.fetchone()
print(res)
# 关闭数据库连接
conn.close()

3.运行程序，如果控制台没有报错，且能正常执行sql语句，则代表连接数据库成功；

4. 连接数据库成功后，先插入一条数据看看效果 👇

# 使用cursor()方法获取操作游标
c = conn.cursor()
sql = "insert into student(Sno,Sname,Ssex,Sage,Sdept) value ('2012151','刚子','男','29','CS')"
# 使用execute方法执行SQL语句
c.execute(sql)
# 插入数据,需执行conn.commit()
conn.commit()
# 关闭数据库连接
conn.close()

注：使用pymysql操作数据库时，增删改与查询是有区别的，在增删改操作时一定要记得conn.commit()，提交当前事务。

二、读取excel文件

1. 读取excel文件需要用到xlrd库，安装方法：pip install xlrd
2. 对excel文件中的数据进行读取 👇

import xlrd

FilePath = 'E:/PDBC/StudentInfo.xls'

# 1.打开excel文件
wkb = xlrd.open_workbook(FilePath)
# 2.获取sheet
sheet = wkb.sheet_by_index(0)  # 获取第一个sheet表['学生信息']
# 3.获取总行数
rows_number = sheet.nrows
# 4.遍历sheet表中所有行的数据，并保存至一个空列表cap[]
cap = []
for i in range(rows_number):
    x = sheet.row_values(i)  # 获取第i行的值（从0开始算起）
    cap.append(x)
print(cap)

3. 上面读取到的结果为列表类型，每个小列表代表一个学生的信息。因为student表中有五个字段，分别是：Sno、Sname、Ssex、Sage、Sdept，所以我们要拿到每个学生的这五个属性值 👇

for Stu in cap:
    Sno = int(Stu[0])
    Sname = Stu[1]
    Ssex = Stu[2]
    Sage = Stu[3]
    Sdept = Stu[4]
    print(Sno, Sname, Ssex, Sage, Sdept)

三、批量插入数据库

获取到每个学生的属性值后，就可以逐个插入到数据中了，总代码如下

import pymysql
import xlrd

"""
一、连接mysql数据库
"""
# 打开数据库连接
conn = pymysql.connect(
    host='localhost',  # MySQL服务器地址
    user='root',  # MySQL服务器端口号
    password='root',  # 用户名
    charset='utf8',  # 密码
    port=3308,  # 端口
    db='test',  # 数据库名称
)

# 使用cursor()方法获取操作游标
c = conn.cursor()

"""
二、读取excel文件
"""
FilePath = 'E:/PDBC/StudentInfo.xls'

# 1.打开excel文件
wkb = xlrd.open_workbook(FilePath)
# 2.获取sheet
sheet = wkb.sheet_by_index(0)  # 获取第一个sheet表['学生信息']
# 3.获取总行数
rows_number = sheet.nrows
# 4.遍历sheet表中所有行的数据，并保存至一个空列表cap[]
cap = []
for i in range(rows_number):
    x = sheet.row_values(i)  # 获取第i行的值（从0开始算起）
    cap.append(x)
print(cap)  # [['9022478', '郭赛', '男', 34.0, 'CS'], ['9022472', '林伟', '男', 36.0, 'MA'], ···]

"""
三、将读取到的数据批量插入数据库
"""
for Stu in cap:
    Sno = int(Stu[0])
    Sname = Stu[1]
    Ssex = Stu[2]
    Sage = Stu[3]
    Sdept = Stu[4]
    # 使用f-string格式化字符串，对sql进行赋值
    c.execute(f"insert into student(Sno,Sname,Ssex,Sage,Sdept) value ('{Sno}','{Sname}','{Ssex}','{Sage}','{Sdept}')")   
conn.commit()
conn.close()
print("插入数据完成！")