#!/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