Flask的sqlalchemy SQL练习

models.py

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, Enum, UniqueConstraint
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship


# 数据库配置信息
HOST = "127.0.0.1"
PORT = "3306"
DATABASE = "数据库名字"
USERNAME = "用户名"
PASSWORD = "密码"


# 数据库的固定格式
DB_URL = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset-utf8".format(
username=USERNAME,
password=PASSWORD,
host=HOST,
port=PORT,
db=DATABASE
)

# 创建一个引擎
engine = create_engine(DB_URL)

# 用declarative_base根据engine创建一个ORM基类
Base = declarative_base(engine)


# 创建一个ORM模型，这个模型继承sqlalchemy给我们提供基类，即上面的Base
class Depart(Base):
"""班级表"""
__tablename__ = "depart"
id = Column(Integer, primary_key=True, autoincrement=True)
caption = Column(String(64), nullable=False)

def __str__(self):
return self.caption

def __repr__(self):
return self.__str__()


class Student(Base):
"""学生表"""
__tablename__ = "student"
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(32), nullable=False)
gender = Column(Enum("男", "女"))
# 外键关联
depart_id = Column(Integer, ForeignKey("depart.id"))
# 与生成表结构无关,仅用于查询方便, backref指定反向查询字段名(起一个关联当前表的名字)
departs = relationship("Depart", backref="student_list")

def __str__(self):
return self.name

def __repr__(self):
return self.__str__()


class Teacher2Student(Base):
"""老师学生多对多关联表"""
__tablename__ = "teacher2student"
id = Column(Integer, primary_key=True, autoincrement=True)
teacher_id = Column(Integer, ForeignKey("teacher.id"))
student_id = Column(Integer, ForeignKey("student.id"))


class Teacher2Depart(Base):
"""老师班级多对多关联表"""
__tablename__ = "teacher2depart"
id = Column(Integer, primary_key=True, autoincrement=True)
teacher_id = Column(Integer, ForeignKey("teacher.id"))
depart_id = Column(Integer, ForeignKey("depart.id"))

__table_args__ = (
# 联合唯一索引,加速查找、不重复
UniqueConstraint('teacher_id', 'depart_id', name='teacher_depart_id'),
# Index('st_id_name', 'name', 'extra'), # 联合索引,加速查找
)


class Teacher(Base):
"""老师表"""
__tablename__ = "teacher"
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(64), nullable=False)
# secondary指定多对多中记录的排序方式，前面的代表关联类名字，后面是反向查询本表的字段名
students = relationship("Student", secondary="teacher2student", backref="teacher_list")
departs = relationship("Depart", secondary="teacher2depart", backref="teacher_list")

def __str__(self):
return self.name

def __repr__(self):
return self.__str__()


class Course(Base):
"""课程表"""
__tablename__ = "course"
id = Column(Integer, primary_key=True, autoincrement=True)
title = Column(String(64), nullable=False)
teacher_id = Column(Integer, ForeignKey("teacher.id"))
scores = relationship("Score", backref="course_list")
teachers = relationship("Teacher", backref="course_list")

def __str__(self):
return self.title

def __repr__(self):
return self.__str__()


class Score(Base):
"""成绩表"""
__tablename__ = "score"
id = Column(Integer, primary_key=True, autoincrement=True)
number = Column(Integer, nullable=False)
student_id = Column(Integer, ForeignKey("student.id"))
course_id = Column(Integer, ForeignKey("course.id"))
students = relationship("Student", backref="score_list")

def __str__(self):
return self.number

def __repr__(self):
return self.__str__()


# 将创建好的ORM模型，映射到数据库中
Base.metadata.create_all()
# ORM对数据库操作需要构建一个session对象
session = sessionmaker(engine)()

learn_sql.py

from learn.models import *
from learn.models import session


# 增(需要什么字段添加什么字段)
obj = Depart(caption="Go全栈")
session.add(obj)
session.commit()


session.add_all([Course(title="Go基础"), Course(title="Java三大器")])
session.commit()


session.add_all([
Student(name="小五"),
Depart(caption="间谍"),
Teacher(name="海哥"),
Score(number=90),
Course(title="翻盘一号"),
])
session.commit()
# 创建一个新老师，并给他指定两个新学生
obj = Teacher(name="二筒")
obj.students = [Student(name="小五", gender="男", class_id=2), Student(name="二胖", gender="男", class_id=3)]
session.add(obj)
session.commit()


# 查询Python班级有多少学生
val = session.query(ClassTable.caption, Student.name).filter(ClassTable.id==1).all()
查询课程id大于2的所有课程
val = session.query(Course).filter(Course.id > 2).all()
# 找到钢蛋的老师
val = session.query(Student).filter(Student.name=="钢蛋").first()
for item in val.teacher_list:
print(item)
# 查看小王老师教的课程
val = session.query(Teacher).filter(Teacher.name=="小王").first()
for item in val.courses:
print(item)

# 删
session.query(Teacher2Student).filter(Teacher2Student.id > 4).delete()
session.commit()
session.close()

# 修改学生属性和关联课程
session.query(Student).filter(Student.id == 8).update({Student.gender: "女"})
session.query(Student).filter(Student.id == 9).update({Student.gender: "女"})
session.query(Student).filter(Student.id == 10).update({Student.class_id: 2})
session.query(Student).filter(Student.id == 11).update({Student.class_id: 3})
session.commit()