利用 Python FastAPI 框架 以及 openpyxl 模块 实现 将Excel表格数据 批量导入至 Mysql 数据库

实验环境：

PyCharm 2018.1

CentOS Linux 7

实验前提：

1.已完成虚拟环境的创建以及pycharm连接linux操作

2.在linux上已经安装mysql数据库（mariadb也可以）

3.已经创建 test_excel数据库（执行sql语句：create database test_excel charset=utf8;）

新建 test.xlsx 文件，并写入数据：

实验步骤：

1.安装软件包

pip install sqlalchemy -i https://pypi.tuna.tsinghua.edu.cn/simple
pip install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple

pip install fastapi -i https://pypi.tuna.tsinghua.edu.cn/simple
pip install uvicorn -i https://pypi.tuna.tsinghua.edu.cn/simple

pip install openpyxl -i https://pypi.tuna.tsinghua.edu.cn/simple

2.编写代码

# 导入FastAPI模块
from fastapi import FastAPI
# 创建app实例
app = FastAPI()

# 导入load_workbook模块，用于操作excel表格
from openpyxl import load_workbook

## 连接数据库

# 连接mysql数据库需要导入pymysql模块
import pymysql
pymysql.install_as_MySQLdb()

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# 配置数据库地址：数据库类型+数据库驱动名称://用户名:密码@机器地址:端口号/数据库名
engine = create_engine("mysql+pymysql://root:westos@localhost:3306/test_excel", encoding='utf-8')
# 把当前的引擎绑定给这个会话；
# autocommit：是否自动提交  autoflush：是否自动刷新并加载数据库  bind：绑定数据库引擎
Session = sessionmaker(autocommit=False, autoflush=False, bind=engine)
# 实例化
session = Session()


# declarative_base类维持了一个从类到表的关系，通常一个应用使用一个Base实例，所有实体类都应该继承此类对象
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

from sqlalchemy import Column, String, Integer

# 创建数据库模型（定义表结构:表名称，字段名称以及字段类型）
class Student(Base):
    # 定义表名
    __tablename__ = 'student_message'
    # 定义字段
    # primary_key=True 设置为主键
    # 默认Integer为Integer(11)
    num = Column(Integer, primary_key=True, nullable=False)
    name = Column(String(200), nullable=False)
    age = Column(Integer, nullable=False)
    classes = Column(String(200), nullable=False)
    score = Column(Integer, nullable=False)

    # 构造函数
    def __init__(self, num, name, age, classes, score):
        self.num = num
        self.name = name
        self.age = age
        self.classes = classes
        self.score = score

    # 打印形式
    def __str__(self):
        return "num:%s, name:%s, age:%s, classes:%s, score:%s" % (str(self.num), self.name, str(self.age), self.classes, str(self.score))

# 在数据库中生成表
# 注意：需要先生成数据库表以后再注释掉
# Base.metadata.create_all(bind=engine)



## 批量添加（将excel表格中的数据导入到mysql数据库中）
@app.get("/user/addStudents")
async def InserUser():
    try:
        # 打开工作薄与工作表
        wb = load_workbook('test.xlsx')
        sheet = wb.get_sheet_by_name('Sheet1')

        # 计算表格数据的有效行数rows
        num = 1
        while 1:
            cell = sheet.cell(row=num, column=1).value
            if cell:
                num = num + 1
            else:
                # print(num)
                break
        rows = num - 1
        # print(rows)

        # for循环迭代读取xls文件中的每行数据, 从第二行开始因为需要跳过标题行
        # 注意：openpyxl方式表格列标与行标都是从1开始计算的
        for r in range(2, rows + 1):
            numData = sheet.cell(row=r, column=1).value
            nameData = sheet.cell(row=r, column=2).value
            ageData = sheet.cell(row=r, column=3).value
            classesData = sheet.cell(row=r, column=4).value
            scoreData = sheet.cell(row=r, column=5).value
            # values = (numData, nameData, ageData, classesData, scoreData)
            # print(values)
            # 添加数据
            dataStudent = Student(num=numData, name=nameData, age=ageData, classes=classesData, score=scoreData)
            session.add(dataStudent)
        session.commit()
        session.close()
    except ArithmeticError:
        return {"code":"0002","message":"数据库异常"}
    return {"code":"0000","message":"添加成功"}

3.测试

# 启动应用程序
(venv) [root@localhost fast]# uvicorn budget_manage:app --host '192.168.56.20' --port 8080 --reload

MariaDB [test_excel]> desc student_message;

MariaDB [test_excel]> select * from student_message;

注意：需要先生成student_message数据库表，之后再导入数据；