mysql - 学生管理系统

要求：

　　ORM 版的学生管理系统

功能介绍：

* 讲师视图：
　　1：查看班级
　　2：创建班级
　　3：添加学员
　　4：创建课程
　　5：修改成绩
　　6：签到
　　7：返回

* 学员视图：
　　1：查看成绩(学员全部成绩并排行)

 

* 数据关系图

 

 

* 系统流程图

 * table模型

#!/usr/bin/env python
# -*- coding: utf-8 -*-

__author__ = 'Fade Zhao'
from sqlalchemy import String,Column,Integer,ForeignKey,DATE,Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from config.setting import engine

Base = declarative_base()
# 班级对学生
grades_m2m_students = Table('grades_m2m_students',Base.metadata,
                            Column('grade_id',Integer,ForeignKey('grades.grade_id')),
                            Column('student_id',Integer,ForeignKey('students.student_id')))

# 班级对老师
grades_m2m_teachers = Table('grades_m2m_teachers',Base.metadata,
                            Column('grade_id',Integer,ForeignKey('grades.grade_id')),
                            Column('teacher_id',Integer,ForeignKey('teachers.teacher_id'))
                            )



# 班级对课程
class grades_m2m_lessons(Base):
    __tablename__ = 'grades_m2m_lessons'
    id = Column(Integer,primary_key=True)
    grade_id = Column(Integer,ForeignKey('grades.grade_id'))
    lesson_id = Column(Integer,ForeignKey('lessons.lesson_id'))

    grade = relationship('grades',backref = 'grades_m2m_lesson')
    lesson = relationship('lessons',backref = 'grades_m2m_lesson')

    def __repr__(self):
        return '\033[33;1m%d grade_id=%d ,lesson_id%d\033[0m'%(self.id,self.grade_id,self.lesson_id)
class study_records(Base):
    '''学习记录'''
    __tablename__ = 'study_records'
    id = Column(Integer,primary_key=True)
    grades_lesson_id = Column(Integer,ForeignKey('grades_m2m_lessons.id'))
    student_id = Column(Integer,ForeignKey('students.student_id'))
    studied = Column(String(32),nullable=False,default='NO')
    score = Column(Integer,nullable=False,default=0)

    grades_m2m_lesson = relationship('grades_m2m_lessons',backref = 'study_record')
    student = relationship('students',backref = 'study_record')
    def __repr__(self):
        return '\033[33;1mID：{0} grades_lesson_id：{1} student_id-{2} 是否学完：{3} 成绩：{4}\033[0m'.format(self.id,
                                self.grades_lesson_id,
                                self.student_id,
                                self.studied,
                                self.score)

class students(Base):
    '''学生'''
    __tablename__ = 'students'
    student_id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False,unique=True)
    QQ = Column(String(32),nullable=False,unique=True)

    def __repr__(self):
        return '\033[33;1mID：%d 姓名：%s QQ：%s\033[0m'%(self.student_id,self.name,self.QQ)
class teachers(Base):
    '''老师'''
    __tablename__ = 'teachers'
    teacher_id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False,unique=True)

    def __repr__(self):
        return '\033[33;1m姓名:%s\033[0m' % (self.name)
class lessons(Base):
    '''课程'''
    __tablename__ = 'lessons'
    lesson_id = Column(Integer,primary_key=True)
    lesson_name = Column(String(32),nullable=False)

class grades(Base):
    '''班级'''
    __tablename__ = 'grades'
    grade_id = Column(Integer,primary_key=True)
    grade_name = Column(String(32),nullable=False,unique=True)
    grade_course = Column(String(32),nullable=False)

    students = relationship('students',secondary = grades_m2m_students,backref = 'grades')
    teachers = relationship('teachers',secondary = grades_m2m_teachers,backref = 'grades')

    def __repr__(self):
        return '\033[33;1m%d 名称 %s  科目 %s\033[0m'%(self.grade_id,self.grade_name,self.grade_course)



def init_database():
    Base.metadata.create_all(engine)