flask day04

ORM概念: object relationship mapping，对象关系映射

views.py

"""__author__ = song"""

from flask import Blueprint
from sqlalchemy import and_, not_, or_

from app.models import db, Student

blue = Blueprint('app',__name__)

@blue.route('/')
def hello():
    return 'hello world'

@blue.route('/creat_db/')
def create_db():
    # 第一次迁移模型时才有用
    db.create_all()
    # db.drop_all()
    return '创建表成功'

@blue.route('/add_stu/')
def add_stu():
    stu = Student()
    stu.s_name = 'zzz'
    stu.age = 20
    db.session.add(stu)
    db.session.commit()
    return '创建数据成功'

@blue.route('/add_stus/')
def add_stus():
    stu_list = []
    for i in range(10):
        stu = Student()
        stu.s_name = 'zqm' + str(i)
        stu.age = int(i)
        stu_list.append(stu)
        # db.session.add(stu)
    db.session.add_all(stu_list)
    db.session.commit()
    return '批量插入数据'

@blue.route('/sel_stu/')
def sel_stu():
    # stu: <flask_sqlalchemy.BaseQuery object at 0x0000016D7C6EED68>
    stu = Student.query.filter_by(s_name = 'zqm0').first()
    print(stu.s_name)
    print(stu.age)
    return '查询成功'

@blue.route('/del_stu/')
def del_stu():
    stus = Student.query.filter_by(age=0).all()
    for stu in stus:
        db.session.delete(stu)
    db.session.commit()
    return '删除成功'

@blue.route('/update_stu/')
def update_stu():
    # 修改，先获取需要修改的对象
    stu = Student.query.filter_by(s_name='zzz').first()
    stu.age = 10
    stu.save()
    return '修改成功'

@blue.route('/sel_stus/')
def sel_stus():
    # stu = Student.query.filter_by(s_name='zzz').first()
    stu = Student.query.filter(Student.s_name =='zzz').first()
    print(stu)

    # all()结果为列表，列表中的元素是查询学生的对象
    stus = Student.query.all()
    print(stus)

    stu = Student.query.filter(Student.id == 1).first()
    # get查询主键所在行的信息
    stu = Student.query.get(1)
    print(stu)

    # order by
    stus = Student.query.order_by(-Student.id).all()
    print(stus)
    stus = Student.query.limit(3).all()
    print(stus)
    page = 1
    stus = Student.query.offset((page-1)*3).limit(3).all()
    print(stus)

    # 模糊查询
    stus = Student.query.filter(Student.s_name.contains('z')).all()
    print(stus)
    stus = Student.query.filter(Student.s_name.like('z%')).all()

    stus = Student.query.filter(Student.s_name.startswith('z')).all()
    stus = Student.query.filter(Student.s_name.endswith('m')).all()

    # 大于gt 小于lt 大于等于ge 小于等于le
    stus = Student.query.filter(Student.age.__ge__(10)).all()
    stus = Student.query.filter(Student.age >= 10).all()

    # where id in [1,2,3,4,5,6,7]
    stus = Student.query.filter(Student.id.in_([1,2,3,4,5,6,7])).all()
    stus = Student.query.filter(Student.id.notin_([1, 2, 3, 4, 5, 6, 7])).all()
    print(stus)

    stus = Student.query.filter(Student.age == 2).filter(Student.s_name.like("z%")).all()

    # and or
    stus = Student.query.filter(Student.age == 2, Student.s_name.like("z%")).all()
    stus = Student.query.filter(Student.age == 2 or Student.s_name.like("z%")).all()
    print(stus)
    # and_, or_, not_
    stus = Student.query.filter(and_(Student.age == 2, Student.s_name.like("z%"))).all()
    stus = Student.query.filter(or_(Student.age == 2,Student.s_name.like("z%"))).all()
    stus = Student.query.filter(not_(Student.s_name.like("z%"))).all()
    
    return '查询学生信息'

models.py

"""__author__ = song"""

from flask_sqlalchemy import SQLAlchemy

# 生成数据库访问对象db
db = SQLAlchemy()

class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    s_name = db.Column(db.String(10), unique=True, nullable=False)
    age = db.Column(db.Integer, default=20)

    __tablename__ = 'stu'

    def save(self):
        db.session.add(self)
        db.session.commit()

    def delete(self):
        db.session.delete(self)
        db.session.commit()

manage.py

"""__author__ = song"""
from flask import Flask
from flask_script import Manager

from app.models import db
from app.views import blue

app = Flask(__name__)
app.register_blueprint(blueprint=blue)

# 配置数据库连接信息

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:你的本地数据库密码@127.0.0.1:3306/flask9'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db.init_app(app)

manage = Manager(app)

if __name__ == '__main__':
    manage.run()