那些年被我坑过的Python——牵一发动全身 第十一章MySQL、ORM

#!/usr/bin/env python
# -*- coding:utf-8 -*-
__Author__ = "Zhang Xuyao"

from sqlalchemy import create_engine

# engine = create_engine("mysql+pymysql://root:123123@localhost/attendance?charset=utf8", echo=True)
engine = create_engine("mysql+pymysql://root:123123@localhost/attendance?charset=utf8")

#!/usr/bin/env python
# -*- coding:utf-8 -*-
__Author__ = "Zhang Xuyao"

from sqlalchemy.ext.declarative import declarative_base
from settings import db_settings
from sqlalchemy import Table, Column, Integer, String, Enum, DATE, ForeignKey, PrimaryKeyConstraint
from sqlalchemy.orm import relationship

Base = declarative_base()

teacherBatch_t = Table('tbt', Base.metadata,
                       Column('batch_name', String(64), ForeignKey('batch.name')),
                       Column('teacher_id', String(64), ForeignKey('teacher.id'))
                       )

studentBatch_t = Table('sbt', Base.metadata,
                       Column('batch_name', String(64), ForeignKey('batch.name')),
                       Column('student_qq', String(64), ForeignKey('student.qq'))
                       )


class Teacher(Base):
    __tablename__ = 'teacher'
    id = Column(String(64), primary_key=True)
    name = Column(String(64), nullable=False)
    password = Column(String(64), nullable=False, default='321cba')

    # tstudents = relationship('Student', secondary=task_t, backref='teachers')
    tbatchs = relationship('Batch', secondary=teacherBatch_t, backref='teachers')

    def __repr__(self):
        return self.name


class Student(Base):
    __tablename__ = 'student'
    qq = Column(String(64), primary_key=True)
    name = Column(String(64), nullable=False)
    password = Column(String(64), nullable=False, default='321cba')

    # steachers = relationship('Teacher', secondary=task_t, backref='students')
    sbatchs = relationship('Batch', secondary=studentBatch_t, backref='students')

    def __repr__(self):
        return self.name


# 课程批次（班级）name为批次如python14期，courseType为类型如python
class Batch(Base):
    __tablename__ = 'batch'
    name = Column(String(64), primary_key=True)
    courseType = Column(String(64), nullable=False)

    def __repr__(self):
        return self.name + self.courseType


class Task(Base):
    __tablename__ = 'task'
    batch_name = Column(String(64), ForeignKey('batch.name'))
    date = Column(DATE, nullable=False)
    student_qq = Column(String(64), ForeignKey('student.qq'))

    status = Column(Enum('at', 'ab', 'va', 'ot'),
                    default='ab', nullable=False)
    score = Column(Integer,
                   default=0, nullable=False)
    teacher_id = Column(String(64), ForeignKey('teacher.id'))

    comment = Column(String(1024))
    task_pk = PrimaryKeyConstraint(batch_name, student_qq, date)

    student = relationship('Student', backref='tasks')
    teacher = relationship('Teacher', backref='tasks')


# 父类调用所有的子类实现表结构的创建#####


# Base.metadata.drop_all(db_settings.engine)  # 删除表结构
Base.metadata.create_all(db_settings.engine)  # 创建表结构

#!/usr/bin/env python
# -*- coding:utf-8 -*-
__Author__ = "Zhang Xuyao"

from settings import db_tables as dbt
from settings import db_settings
from sqlalchemy.orm import sessionmaker
from sqlalchemy import desc,func
import datetime

Session_class = sessionmaker(bind=db_settings.engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
db = Session_class()  # 生成session实例


def teacherAuth(id, password):
    obj = db.query(dbt.Teacher).filter(dbt.Teacher.id == id).first()
    if not obj:
        return False
    if password == obj.password:
        # print("got it")
        return obj
    else:
        return False

def studentAuth(qq, password):
    obj = db.query(dbt.Student).filter(dbt.Student.qq == qq).first()
    if not obj:
        return False
    if password == obj.password:
        # print("got it")
        return obj
    else:
        return False


def addTeacher(id, name, password='abc123'):
    obj = dbt.Teacher(id=id, name=name, password=password)
    db.add(obj)
    db.commit()
    return obj


def batchBindTeacher(id, name, password):
    pass


def addStudent(qq, name, password='abc123'):
    obj = dbt.Student(qq=qq, name=name, password=password)
    db.add(obj)
    db.commit()
    return obj


def batchBindStudent(id, name, password):
    pass


def addBatch(name, courseType):
    obj = dbt.Batch(name=name, courseType=courseType)
    db.add(obj)
    db.commit()
    return obj


def addTask(date, score='N/A', status='absent'):
    pass


if __name__ == '__main__':
    t1 = addTeacher('t001', "Alex", "Alex123")
    t2 = addTeacher('t002', "Jack", "Jack123")
    t3 = addTeacher('t003', "Rain", "Rain123")
    #
    s1 = addStudent('s001', "chenronghua", "abc123")
    s2 = addStudent('s002', "niuhanyang", "abc123")
    s3 = addStudent('s003', "wangsen", "abc123")
    #
    b1 = addBatch("python14", "python")
    b2 = addBatch("pythonS2", "python")
    b3 = addBatch("Ops10", "Linux")

    b1.students = [s1, s2, s3]
    b1.teachers = [t1, t2]

    for i in b1.students:
        obj = dbt.Task(date='2016-10-26', teacher_id=t1.id, student_qq=i.qq, batch_name=b1.name)
        db.add(obj)
        db.commit()
    #
    # obj = db.query(dbt.Teacher).filter(dbt.Teacher.id == "t001").first()
    # for i in obj.tbatchs:
    #     if i.name == 'python14':
    #         for j in i.students:
    #             if j.qq == 's001':
    #                 print(j.name, j.password)

    pass