flask框架数据库增删改查

from  flask import Flask
import  os
from flask_sqlalchemy import SQLAlchemy
import   pymysql
app=Flask(__name__)#__name__当前文件
BASE_DIR=os.path.abspath(os.path.dirname(__file__))#路径abspath绝对路径abspath;dirname目录名dirname,dirname
#BASE_DIR=当前文件的绝对路径 #base基础base;dir目录dir;base_dir基础目录base_dir;base_dir基础目录base_dir
app.config["SQLALCHEMY_DATABASE_URI"]="mysql+pymysql://root:root@127.0.0.1:3306/student"
#app.config['SQLALCHEMY_DATABASE_URI']="sqlite:///"+os.path.join(BASE_DIR,"Demo.sqlite")#demo演示demo;数据库指向自带的sqlite数据库
#config配置，布局;config,config配置config#sqlite,django和flask自带的数据库sqlite一个轻量级的数据库sqlite
#databases数据库databases;sqlalchemy功能，关系，外键关联，中进行关联查询sqlalchemy,sqlalchemy
#URI统一资源匹配符；配置数据连接的参数#app.config返回类字典对象，里面用来存放当前app的配置
app.config["SQLALCHEMY_COMMIT_TEARDOWN"]=True#teardown拆卸，待拆房屋,teardown,teardown
#commit把...交托给commit把...交托给commit commit
#sqlalchemy  commit  teardown请求结束后自动提交数据库修改
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"]=True#track轨道，追踪track,track,track，track,track；
#modifications#修改修饰modifications,modifications;
#如果设置成True(默认情况),flask_SQLALchemy 将会追踪对象的修改并且发送信号
models=SQLAlchemy(app)#关联sqlcchemhy和flask应用
session=models.session()
class  BaseModel(models.Model):#学员表
    __abstract__=True#抽象表为True,代表当前类为抽象类，不会被实例化
    id=models.Column(models.Integer,primary_key=True,autoincrement=True)
    def  save(self):
        session.add(self)
        session.commit()
    def  delete_obj(self):#魔术方法
        session.delete(self)
        session.commit()
class  Students(BaseModel):#学员表
    __tablename="students"
    name=models.Column(models.String(32))
    age=models.Column(models.Integer)
    gender=models.Column(models.Integer)#0,男；1女；2其他
class  Course(BaseModel):#课程表
    __tablename = "course"
    label = models.Column(models.String(32))
    description=models.Column(models.Text)
    #双向映射
    # teachers=models.relationship(
    #     'course',#映射类的名称#双向映射
    #     backref='teachers'#映射到教师表
    # )
class  Stu_Cou(BaseModel):#课程表
    __tablename = "stu_cou"
    course_id = models.Column(models.Integer, models.ForeignKey("course.id"))
    student_id = models.Column(models.Integer, models.ForeignKey("students.id"))
class  Grade(BaseModel):#成绩表，课程、学员关联此表
    __tablename = "grade"
    id = models.Column(models.Integer, primary_key=True, autoincrement=True)
    grade=models.Column(models.Float)
    course_id=models.Column(models.Integer,models.ForeignKey("course.id"))
    student_id = models.Column(models.Integer, models.ForeignKey("students.id"))
class  Attendance(BaseModel):#考勤表 关联学员
    __tablename = "attendance"
    id = models.Column(models.Integer, primary_key=True, autoincrement=True)
    student_id = models.Column(models.Integer, models.ForeignKey("students.id"))
    att_time=models.Column(models.Date)
    status = models.Column(models.Integer,default=1)#0迟到；1正常出勤；2早退；3请假；4旷课
class  Teachers(BaseModel):#教师表
    __tablename = "teachers"
    id = models.Column(models.Integer, primary_key=True, autoincrement=True)
    name = models.Column(models.String(32))
    age = models.Column(models.Integer)
    gender = models.Column(models.Integer)  # 0,男；1女；2其他
    course_id = models.Column(models.Integer, models.ForeignKey("course.id"))
# session=models.session()#创建一个数据库修改实例，增删改
# course=Course(label="python",description="python是世界上最好的语言")
# session.add(course)
# session.commit()#提交保存
#添加数据
# t=Teachers(name="老边",age=24,gender=0,course_id=1)
# t.save()
# t1=Teachers()
# t1.name="老王"
# t1.gender=0
# t1.age=30
# t1.save()
# t=Teachers(name="laoli",age=36,gender=1,course_id=1)
# t.save()
# t1=Teachers()
# t1.name="老王"
# t1.gender=1
# t1.age=45
# t1.save()

#修改数据
# t=Teachers.query.get(1)
# t.name="松哥帅"
# t.gender=0
# t.age=18
# t.save()
t=Teachers.query.get(7)
t.name="松哥帅"
t.gender=0
t.age=18
# #删除数据
# t=Teachers.query.get(1)
# t.delete_obj()
# Teachers.query.get(2).delete_obj()
# t=Teachers.query.get(3)
# t.delete_obj()
# Teachers.query.get(4).delete_obj()
# t=Teachers.query.get(5)
# t.delete_obj()
# Teachers.query.get(6).delete_obj()
# #查询数据
# print(Teachers.query.all())
# print(Teachers.query.first())
# print(Teachers.query.filter_by(age=18).all())
# print(Teachers.query.all())
# print(Teachers.query.first())
# print(Teachers.query.filter_by(age=18).all())
# print(Teachers.query.order_by("age").all())#升序
# print(Teachers.query.order_by(models.desc("age")).all())#降序
# print(Teachers.query.get(3))#只能以主键查询
# print(Teachers.query.offset(1).limit(2).all())
print(Teachers.query.offset(1).limit(2).all())
print(Teachers.query.get(3))#只能以主键查询
print(Teachers.query.order_by(models.desc("age")).all())#降序
print(Teachers.query.order_by("age").all())#升序
# #offset偏移量，就是从哪里开始；limit限制条数，就是单次返回条数
# page=2
# page_size=4
# start=(page-1)*page_size
# teachers=Teachers.query.offset(start).limit(page_size).all()
# print(teachers)#模拟分页显示
page=2
page_size=4
start=(page-1)*page_size
teachers=Teachers.query.offset(start).limit(page_size).all()

# models.drop_all()
# models.create_all()
# course=Course(label="python",description="世界上最好的语言")
# course.save()
# course=Course(label="php",description="世界上将out的语言")
# course.save()
# course=Course(label="java",description="世界上快out的语言")
# course.save()