第130集_数据库综合项目

1. 项目概述

1.1 项目目标

本集将创建一个学生管理系统，作为数据库知识的综合应用项目。通过这个项目，我们将学习如何：

  • 设计合理的数据库结构
  • 使用SQLAlchemy进行数据库操作
  • 实现完整的CRUD功能
  • 处理数据关系和事务
  • 编写可维护的数据库应用

1.2 技术栈

  • 后端框架：Python
  • ORM工具：SQLAlchemy
  • 数据库：SQLite（开发环境）/ MySQL/PostgreSQL（生产环境）
  • 项目结构：模块化设计，清晰的代码分层

2. 数据库设计

2.1 需求分析

学生管理系统需要包含以下核心功能：

  • 学生信息管理（增删改查）
  • 课程信息管理
  • 成绩管理
  • 教师信息管理
  • 班级信息管理

2.2 ER图设计

+-----------+        +-----------+        +-----------+
|   Student |        |   Course  |        |  Teacher  |
+-----------+        +-----------+        +-----------+
| id        |        | id        |        | id        |
| name      |        | name      |        | name      |
| gender    |        | code      |        | email     |
| birthday  |        | credit    |        | phone     |
| class_id  |<------>|           |        |           |
+-----------+        +-----------+        +-----------+
        |                      ^                ^
        |                      |                |
        v                      |                |
+-----------+        +------------------+       |
|   Class   |        |   Score          |       |
+-----------+        +------------------+       |
| id        |        | id               |       |
| name      |        | student_id       |       |
| teacher_id|<-------| course_id        |-------+
+-----------+        | score            |
                     | exam_date        |
                     +------------------+

2.3 表结构设计

1. students表

字段名 数据类型 约束 描述
id INTEGER PRIMARY KEY 学生ID
name VARCHAR(50) NOT NULL 学生姓名
gender VARCHAR(10) NOT NULL 性别
birthday DATE 出生日期
class_id INTEGER FOREIGN KEY 所属班级ID

2. classes表

字段名 数据类型 约束 描述
id INTEGER PRIMARY KEY 班级ID
name VARCHAR(50) NOT NULL 班级名称
teacher_id INTEGER FOREIGN KEY 班主任ID

3. teachers表

字段名 数据类型 约束 描述
id INTEGER PRIMARY KEY 教师ID
name VARCHAR(50) NOT NULL 教师姓名
email VARCHAR(100) UNIQUE 邮箱
phone VARCHAR(20) 电话

4. courses表

字段名 数据类型 约束 描述
id INTEGER PRIMARY KEY 课程ID
name VARCHAR(100) NOT NULL 课程名称
code VARCHAR(20) UNIQUE 课程代码
credit INTEGER NOT NULL 学分

5. scores表

字段名 数据类型 约束 描述
id INTEGER PRIMARY KEY 成绩ID
student_id INTEGER FOREIGN KEY 学生ID
course_id INTEGER FOREIGN KEY 课程ID
score FLOAT 分数
exam_date DATE NOT NULL 考试日期

3. 项目架构设计

3.1 目录结构

student_management_system/
├── app/
│   ├── __init__.py           # 应用初始化
│   ├── models/               # 数据模型
│   │   ├── __init__.py
│   │   ├── student.py        # 学生模型
│   │   ├── class_.py         # 班级模型
│   │   ├── teacher.py        # 教师模型
│   │   ├── course.py         # 课程模型
│   │   └── score.py          # 成绩模型
│   ├── services/             # 业务逻辑
│   │   ├── __init__.py
│   │   ├── student_service.py
│   │   ├── class_service.py
│   │   ├── teacher_service.py
│   │   ├── course_service.py
│   │   └── score_service.py
│   ├── utils/                # 工具函数
│   │   ├── __init__.py
│   │   └── db.py             # 数据库连接
│   └── main.py               # 主程序入口
├── config.py                 # 配置文件
├── requirements.txt          # 依赖列表
└── README.md                 # 项目说明

3.2 模块划分

  1. models层：定义数据库模型和关系
  2. services层：实现业务逻辑和CRUD操作
  3. utils层：提供数据库连接、配置管理等工具
  4. main层：程序入口，提供用户交互界面

4. 核心功能实现

4.1 数据库连接配置

# app/utils/db.py
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import config

# 创建数据库引擎
engine = create_engine(config.DATABASE_URL)

# 创建会话工厂
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# 创建基类
Base = declarative_base()

# 获取数据库会话
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

4.2 数据模型定义

学生模型

# app/models/student.py
from sqlalchemy import Column, Integer, String, Date, ForeignKey
from sqlalchemy.orm import relationship
from app.utils.db import Base

class Student(Base):
    __tablename__ = "students"
    
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(50), nullable=False)
    gender = Column(String(10), nullable=False)
    birthday = Column(Date)
    class_id = Column(Integer, ForeignKey("classes.id"))
    
    # 关系
    class_ = relationship("Class", back_populates="students")
    scores = relationship("Score", back_populates="student")

班级模型

# app/models/class_.py
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
from app.utils.db import Base

class Class(Base):
    __tablename__ = "classes"
    
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(50), nullable=False)
    teacher_id = Column(Integer, ForeignKey("teachers.id"))
    
    # 关系
    teacher = relationship("Teacher", back_populates="classes")
    students = relationship("Student", back_populates="class_")

4.3 业务逻辑实现

学生服务

# app/services/student_service.py
from sqlalchemy.orm import Session
from app.models.student import Student
from app.models.class_ import Class

class StudentService:
    @staticmethod
    def create_student(db: Session, name: str, gender: str, birthday: str, class_id: int):
        """创建学生"""
        db_student = Student(name=name, gender=gender, birthday=birthday, class_id=class_id)
        db.add(db_student)
        db.commit()
        db.refresh(db_student)
        return db_student
    
    @staticmethod
    def get_student(db: Session, student_id: int):
        """获取学生信息"""
        return db.query(Student).filter(Student.id == student_id).first()
    
    @staticmethod
    def get_students(db: Session, skip: int = 0, limit: int = 100):
        """获取学生列表"""
        return db.query(Student).offset(skip).limit(limit).all()
    
    @staticmethod
    def update_student(db: Session, student_id: int, **kwargs):
        """更新学生信息"""
        db_student = db.query(Student).filter(Student.id == student_id).first()
        if db_student:
            for key, value in kwargs.items():
                setattr(db_student, key, value)
            db.commit()
            db.refresh(db_student)
        return db_student
    
    @staticmethod
    def delete_student(db: Session, student_id: int):
        """删除学生"""
        db_student = db.query(Student).filter(Student.id == student_id).first()
        if db_student:
            db.delete(db_student)
            db.commit()
        return db_student
    
    @staticmethod
    def get_students_by_class(db: Session, class_id: int):
        """获取班级所有学生"""
        return db.query(Student).filter(Student.class_id == class_id).all()

4.4 主程序实现

# app/main.py
from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy.orm import Session
from app.utils.db import get_db, engine, Base
from app.services import student_service, class_service, teacher_service, course_service, score_service
from app.models import student, class_, teacher, course, score

# 创建数据库表
Base.metadata.create_all(bind=engine)

# 创建FastAPI应用
app = FastAPI(title="学生管理系统", description="一个基于FastAPI和SQLAlchemy的学生管理系统")

# 学生相关接口
@app.post("/students/")
def create_student(name: str, gender: str, birthday: str, class_id: int, db: Session = Depends(get_db)):
    return student_service.StudentService.create_student(db, name, gender, birthday, class_id)

@app.get("/students/{student_id}")
def get_student(student_id: int, db: Session = Depends(get_db)):
    student = student_service.StudentService.get_student(db, student_id)
    if not student:
        raise HTTPException(status_code=404, detail="学生不存在")
    return student

@app.get("/students/")
def get_students(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
    return student_service.StudentService.get_students(db, skip, limit)

# 其他接口省略...

5. 项目运行与测试

5.1 安装依赖

pip install -r requirements.txt

5.2 配置文件

# config.py
# 数据库连接配置
DATABASE_URL = "sqlite:///./student_management.db"
# 生产环境可以使用MySQL或PostgreSQL
# DATABASE_URL = "mysql+pymysql://root:password@localhost/student_management"
# DATABASE_URL = "postgresql+psycopg2://user:password@localhost/student_management"

5.3 启动项目

uvicorn app.main:app --reload

5.4 API测试

使用FastAPI自动生成的文档测试API：

6. 高级功能与优化

6.1 数据验证

使用Pydantic进行数据验证：

# app/schemas/student.py
from pydantic import BaseModel, Field
from datetime import date

class StudentBase(BaseModel):
    name: str = Field(..., min_length=1, max_length=50)
    gender: str = Field(..., pattern="^(男|女)$")
    birthday: date
    class_id: int

class StudentCreate(StudentBase):
    pass

class StudentUpdate(StudentBase):
    pass

class StudentInDB(StudentBase):
    id: int
    
    class Config:
        orm_mode = True

6.2 分页功能

# app/services/student_service.py
@staticmethod
def get_students(db: Session, skip: int = 0, limit: int = 10):
    """获取分页学生列表"""
    total = db.query(Student).count()
    students = db.query(Student).offset(skip).limit(limit).all()
    return {
        "total": total,
        "skip": skip,
        "limit": limit,
        "students": students
    }

6.3 事务处理

# app/services/score_service.py
@staticmethod
def add_score_with_transaction(db: Session, student_id: int, course_id: int, score_value: float):
    """使用事务添加成绩"""
    try:
        # 开启事务
        with db.begin():
            # 检查学生和课程是否存在
            student = db.query(Student).filter(Student.id == student_id).first()
            course = db.query(Course).filter(Course.id == course_id).first()
            
            if not student or not course:
                raise ValueError("学生或课程不存在")
            
            # 添加成绩
            new_score = Score(
                student_id=student_id,
                course_id=course_id,
                score=score_value,
                exam_date=date.today()
            )
            db.add(new_score)
            
        return new_score
    except Exception as e:
        # 事务会自动回滚
        raise e

7. 总结与扩展

7.1 项目总结

通过这个学生管理系统项目，我们学习了：

  • 如何设计合理的数据库结构
  • 如何使用SQLAlchemy进行ORM操作
  • 如何实现完整的CRUD功能
  • 如何处理数据关系和事务
  • 如何构建一个模块化的Python应用

7.2 扩展功能

可以进一步扩展的功能：

  1. 用户认证和授权
  2. 数据导入导出（Excel、CSV）
  3. 数据统计和报表生成
  4. 邮件通知功能
  5. 前端界面开发（使用Vue.js或React）

7.3 最佳实践

  1. 数据库设计：遵循范式原则，避免数据冗余
  2. 代码分层：清晰的模块划分，降低耦合度
  3. 错误处理：完善的异常处理机制
  4. 数据验证：严格的数据输入验证
  5. 事务管理：确保数据一致性
  6. 测试覆盖：编写单元测试和集成测试

8. 下一步学习

完成这个综合项目后，我们将进入Web开发阶段，学习如何：

  • 使用Flask/FastAPI构建Web应用
  • 实现前后端分离架构
  • 部署和维护Web应用

数据库是Web开发的基础，掌握好数据库知识将为后续的Web开发学习打下坚实的基础。

« 上一篇 SQLAlchemy高级用法 下一篇 » Web开发概念
↑