flask_sqlalchemy
1. model.py
# pip install flask-sqlalchemy
from flask_sqlalchemy import SQLAlchemy
from flask import Flask
app = Flask(__name__)
# app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:123456@127.0.0.1:3306/test'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + "/home/lmp/test.db"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SECRET_KEY'] = 'xxx'
db = SQLAlchemy(app)
# 学生表
class Student(db.Model):
__tablename__ = "student"
id = db.Column(db.Integer, primary_key=True) # id号(独一无二的)
name = db.Column(db.String(64), nullable=False) # 学生姓名
gender = db.Column(db.Enum("男", "女"), nullable=False) # 学生性别
phone = db.Column(db.String(11), unique=True, nullable=False) # 学生手机号
courses = db.relationship("Course", secondary="student_course", backref="students") # 关系关联
grades = db.relationship("Grade", backref="student") # 成绩关系关联
# 学生-课程中间表
class StudentCourse(db.Model):
__tablename__ = "student_course"
id = db.Column(db.Integer, primary_key=True) # id号(独一无二的)
students_id = db.Column(db.Integer, db.ForeignKey("student.id")) # 学生的id
courses_id = db.Column(db.Integer, db.ForeignKey("course.id")) # 课程的id
# 课程表
class Course(db.Model):
__tablename__ = "course"
id = db.Column(db.Integer, primary_key=True) # id号(独一无二的)
name = db.Column(db.String(32), unique=True) # 课程名字
teacher_id = db.Column(db.Integer, db.ForeignKey("teacher.id")) # 所属老师的id
grades = db.relationship("Grade", backref="course") # 成绩关系关联
# 教师表
class Teacher(db.Model):
__tablename__ = "teacher"
id = db.Column(db.Integer, primary_key=True) # id号(独一无二的)
name = db.Column(db.String(32), unique=True) # 姓名
phone = db.Column(db.String(11), unique=True, nullable=False) # 手机号
gender = db.Column(db.Enum("男", "女"), nullable=False) # 性别
course = db.relationship("Course", backref="teacher") # 所教课程
# 成绩表
class Grade(db.Model):
__tablename__ = "grade"
id = db.Column(db.Integer, primary_key=True) # id号(独一无二的)
my_grade = db.Column(db.String(32), unique=True) # 分数
course_id = db.Column(db.Integer, db.ForeignKey("course.id")) # 所属课程
students_id = db.Column(db.Integer, db.ForeignKey("student.id")) # 所属学生
if __name__ == "__main__":
db.create_all()
# db.drop_all()
实操作
from learn_sql import db, Student, Grade, Teacher, Course
# 增
# s = Student(name="张三", gender="男", phone="12345678900")
# s1 = Student(name="kk", gender="女")
# s2 = Student(name="张蛋", gender="女")
# s3 = Student(name="李四", gender="男")
# s4 = Student(name="李鬼", gender="男", phone="12345678900")
# 语句 第一种
# db.session.add(s)
# db.session.commit()
#
# db.session.add_all([s1, s2, s3, s4])
# db.session.commit()
# 查
# get(id) 查 单一个
# stu = Student.query.get(1)
# print(stu)
# print(stu.name)
# print(stu.gender)
# print(stu.phone)
# all() 查全部
# stu = Student.query.all()
# print(stu)
# for i in stu:
# print(i.name, i.gender, i.phone)
# filter() 条件查询
# stu = Student.query.filter(Student.gender == "女")
# print(stu)
# for i in stu:
# print(i.name, i.id,i.gender)
# filter_by() 比较类似SQL的查询 first() 查询到的第一个
# stu = Student.query.filter_by(name="张三").first()
# print(stu)
# print(stu.name, stu.id, stu.gender)
# stu = Student.query.filter_by(name="张三").filter(Student.id <= 2)
# print(stu)
# for i in stu:
# print(i.name, i.id)
# 改
# 第一种
# stu = Student.query.filter(Student.id == 1).update({"name": "张毅1"})# 返回动了多少条数据
# db.session.commit()
# print(stu)
# stu = Student.query.filter(Student.gender == "男").update({"gender": "女"}) # 返回动了多少条数据
# print(stu)
# db.session.commit()
# 第二种
# 匹配一个出来，修改
# stu = Student.query.get(1)
# print(stu.name,stu.gender)
# stu.gender = "男"
# db.session.add(stu)
# db.session.commit()
# print(stu.name,stu.gender)
# 匹配第一个修改
# stu = Student.query.filter(Student.gender == "男").first()
# print(stu.name,stu.gender)
# stu.gender = "女"
# db.session.add(stu)
# db.session.commit()
# print(stu.name,stu.gender)
# 修改全部
# stu = Student.query.filter(Student.gender == "女").all()
# for i in stu:
# i.gender = "男"
# db.session.add(i)
# db.session.commit()
# 删
#
# stu = Student.query.filter(Student.id >= 5).delete() # 返回动了多少条数据
# print(stu)
# db.session.commit()
# stu = Student.query.all()
# for i in stu:
# print(i.gender,i.name)
# print("--------------------------")
# grade1 = Grade(grade=100, student_id=1)
# grade2 = Grade(grade=95, student_id=1)
#
# db.session.add(grade1)
# db.session.add(grade2)
# db.session.commit()
# stu = Student.query.all()
# for i in stu:
# print(i.gender, i.name)
# grade = Grade.query.filter(Grade.student_id == 1).all()
# for i in grade:
# print(i.student_id, i.student, i.student.name)
# 通过 一 访问 多
stu = Student.query.get(1)
for i in stu.grades:
print(stu.name,i.grade)
# 通过 多 访问 一
# grade = Grade.query.filter(Grade.grade == "100").all()
# for i in grade:
# print(i.student.name,i.student.gender)
# 多对多
# student0 = Student(name="李玲", gender="女", phone="12345678900")
# student1 = Student(name="李依", gender="女", phone="12345678901")
# student2 = Student(name="李贰", gender="男", phone="12345678902")
# student3 = Student(name="李叁", gender="男", phone="12345678903")
# student4 = Student(name="李斯", gender="男", phone="12345678904")
# student5 = Student(name="李舞", gender="女", phone="12345678905")
# student6 = Student(name="李榴", gender="男", phone="12345678906")
# student7 = Student(name="李淇", gender="女", phone="12345678907")
# student8 = Student(name="李巴", gender="男", phone="12345678908")
# student9 = Student(name="李玖", gender="男", phone="12345678909")
#
# teacher0 = Teacher(name="老数", gender="男", phone="12345678910")
# teacher1 = Teacher(name="老语", gender="女", phone="12345678911")
# teacher2 = Teacher(name="老英", gender="女", phone="12345678912")
# teacher3 = Teacher(name="老物", gender="男", phone="12345678913")
# teacher4 = Teacher(name="老化", gender="男", phone="12345678914")
# teacher5 = Teacher(name="老生", gender="男", phone="12345678915")
#
# course0 = Course(name="数学")
# course1 = Course(name="语文")
# course2 = Course(name="英语")
# course3 = Course(name="物理")
# course4 = Course(name="化学")
# course5 = Course(name="生物")
#
# db.session.add_all(
# [student0, student1, student2, student3, student4, student5, student6, student7, student8, student9, teacher0,
# teacher1, teacher2, teacher3, teacher4, teacher5, course0, course1, course2, course3, course4, course5])
# db.session.commit()
# for i in range(1, 7):
# c = Course.query.filter(Course.id == i).update({"teacher_id": i})
# db.session.commit()
# 查询课程表
# cs = Course.query.filter(Course.id >= 2).all()
# print(cs)
# 查询学生
# stu = Student.query.filter(Student.id >= 2).all()
# for s in stu:
# s.courses = cs
# db.session.add(s)
# db.session.commit()
# 学生查询课程
# stu = Student.query.get(1)
# for s in stu.courses:
# print(s.name)
# print(stu.courses)
#
# print("===================")
# 课程查询学生
# c = Course.query.get(2)
# for s in c.students:
# print(s.name)
[Haima的博客]
http://www.cnblogs.com/haima/
