那些年被我坑过的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")

engine

 #!/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) # 创建表结构

tables relationship

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

db handler

上一篇:c#匿名类 anonymous学习


下一篇:HTC辟谣: HTC Vive2不会在CES 2017上公布