from flask import Flask,render_template,request
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
db = SQLAlchemy(app)
app.config['SQLALCHEMY_DATABASE_URI']= 'mysql+pymysql://root:liu@176.215.155.241:3306/flask' # 创建连接实例
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True #自动提交
class Course(db.Model):
__tablename__='course'
id = db.Column(db.Integer,primary_key=True)
cname = db.Column(db.String(30),nullable=True)
#增加关联属性和反向引用关系
# 关联属性在course对象中通过哪个属性能够得到对应的所有的teacher
#反向引用关系：在teacher对象中通过哪个属性能找到它对应的course
teachers = db.relationship('Teacher',backref ='course',lazy ='dynamic')
def __init__(self,name):
self.cname=name
def __repr__(self):
return '<Coure:%r>'%self.cname
class Teacher(db.Model):
__tablename__='teacher'
id = db.Column(db.Integer,primary_key=True)
tname = db.Column(db.String(30),nullable=True)
tage = db.Column(db.Integer)
course_id = db.Column(db.Integer,db.ForeignKey('course.id'))
# 增加关联属性以及反向引用
student = db.relationship('Student',backref = 'teacher',lazy='subquery')
wife = db.relationship('Wife',backref='teacher',uselist=False)
def __init__(self,name,age,course_id):
self.tname=name
self.tage=age
self.course_id=course_id
def __repr__(self):
return '<Teacher %r>'%self.name
class Wife(db.Model):
__tablename__='wife'
id=db.Column(db.Integer,primary_key=True)
wname =db.Column(db.String(30))
wage = db.Column(db.Integer)
#增加一个列（外键）：表示引用自Teacher表的主键
teacher_id = db.Column(db.Integer,db.ForeignKey('teacher.id'))
def __init__(self,wname,wage):
self.wname = wname
self.wage = wage
def __repr__(self):
return '<Wife %r>'%self.wname
class Student(db.Model):
__tablename__='student'
id = db.Column(db.Integer,primary_key=True)
sname = db.Column(db.String(10))
sage = db.Column(db.Integer)
steacher = db.Column(db.Integer,db.ForeignKey('teacher.id'))
courses = db.relationship('Course',
secondary='student_course',
lazy = 'dynamic',
backref=db.backref('students', lazy='dynamic'))
teachers = db.relationship('Teacher',secondary ='student_teacher',
lazy = 'dynamic',
backref = db.backref('teachers',lazy ='dynamic'))
def __repr__(self):
return '<student %s>'%self.sname
student_course = db.Table(
'student_course',
db.Column('id',db.Integer,primary_key=True),
db.Column('student_id',db.Integer,db.ForeignKey('student.id')),
db.Column('course_id',db.Integer,db.ForeignKey('course.id'))
)
student_teacher = db.Table(
'student_teacher',
db.Column('id',db.Integer,primary_key=True),
db.Column('student_id',db.Integer,db.ForeignKey('student.id')),
db.Column('teacher_id',db.Integer,db.ForeignKey('teacher.id'))
)
db.create_all()
@app.route('/')
def hello_world():
return 'Hello World!'
@app.route('/01-addcourse')
def add_course():
course1 = Course('python基础')
course2 = Course('python高级')
course3 = Course('数据基础')
db.session.add(course1)
db.session.add(course2)
db.session.add(course3)
return '1'
@app.route('/02-register')
def register_teacher():
teacher = Teacher()
teacher.tname='吕老师'
teacher.tage = 28
course = Course.query.filter_by(id=3).first()
# teacher.course = course #通过关联属性course对象赋值
teacher.course_id = 1
db.session.add(teacher)
return '1'
@app.route('/03-query-teacher')
def query_teacher():
# 通过 course 查找对应的所有的老师们
# 查找course_id 为1的course对象
# course = Course.query.filter_by(id=1).first()
# print(course.cname)
# for tea in course.teachers.all():
# print(tea.tname)
#通过teacher 查找对应的course
teacher = Teacher.query.filter_by(id=1).first()
print('老师姓名',teacher.tname)
course = teacher.course
print(course.cname)
pass
@app.route('/04-regTeacher',methods=['GET','POST'])
def reg_Teacher():
if request.method=='GET':
a = Course.query.all()
return render_template('regTeacher.html',a=a)
elif request.method=='POST':
name = request.form['name']
age = request.form['age']
select = request.form['select1']
db.session.add(Teacher(name,age,select))
return '1'
@app.route('/05-showdata')
def showdata_05():
a = Teacher.query.all()
return render_template('showtea.html',a=a)
@app.route('/06-regwife')
def regwife():
# wife = Wife('王dd夫人',18)
# wife.teacher_id=1
# db.session.add(wife)
wife=Wife('老夫人',15)
teacher = Teacher.query.filter_by(tname='刘杰').first()
wife.teacher = teacher
db.session.add(wife)
return 'OK'
@app.route('/07-querywife')
def querywife():
#通过teacher 找wife
# teacher = Teacher.query.filter_by(id=1).first()
# wife = teacher.wife
# 通过wife找 teacher
wife = Wife.query.filter_by(id=2).first()
teacher = wife.teacher
return '%s--%s'%(teacher.tname,wife.wname)
@app.route('/07-get')
def get_07():
a= Teacher.query.filter_by(id =4).first()
b = a.student
c = a.wife
print(b,c)
return render_template('07-get.html',a=b)
@app.route('/08')
def add_student_course():
# 获取id为1 的学员的信息
student =Student.query.filter_by(id=1).first()
# 获取 id为1 的课程信息
course = Course.query.filter_by(id=1).first()
# 将student 与 course 关联到一起
student.courses.append(course)
db.session.add(student)
for i in student.courses:
print(i)
return '1'
@app.route('/09')
def getM2M():#这里是多对多存储关系
courses = Course.query.filter_by(id=1).first()
students = courses.students.all()
print(students)
return '1'
@app.route('/10',methods=['GET','POST'])
def register_student():
if request.method=="GET":
s = Student.query.all()
t = Teacher.query.all()
return render_template('register_student.html',s=s,t=t)
elif request.method=='POST':
studen = request.form['select1']
teacher = request.form.getlist('select2')
print(teacher)
s = Student.query.filter_by(id=int(studen)).first()
t = Teacher.query.filter(Teacher.id.in_(teacher)).all()
for i in t :
s.teachers.append(i)
db.session.add(s)
return 'OK'
@app.route('/011')
def show_011():
s = Student.query.all()
return render_template('show_11.html',s=s)
if __name__ == '__main__':
app.run(debug=True)
