flask-数据库crud

一、创建表

用于第一次创建
db.create_all()
删除数据库中的所有的表
db.drop_all()

二、数据库数据crud

• 创建

stu = Students()
stu.s_name = '夏明'
db.session.add(stu)
db.session.commit()
在models.py文件中定义方法save()也可以写成
stu = Students()
stu.s_name = '小谢'
stu.save()

• 批量创建

names = ['xxx'.'xxx'.'xx']
stu_list = []
for name = names:
stu = Students()
stu.s_name = name
stu_liat.append(stu)
db.session.add_all(stu_list)
db.session.commit()

• 查询

name='小王8'
    stu = Students.query.filter(Students.s_name==name).first()
    stu = Students.query.filter_by(s_name=name)
    # all(),first()
    stu = Students.query.all()
    stu = Students.query.filter(Students.s_age==19).first()
    # 执行sql
    sql = 'select * from students;'
    stud = db.session.execute(sql)

    # 模糊查询姓名包含王的信息
    # select * from students where s)name like '%王%'
    # select * from students where s)name like '王%'
    # select * from students where s)name like '_王%'
    stu = Students.query.filter(Students.s_name.contains('王'))
    stu = Students.query.filter(Students.s_name.startswith('库'))

    # 查询id在某个范围之类的学生信息
    # select * from students where id in (2,3,4,5,6)
    stu1 = Students.query.filter(Students.id.in_([9,10,11]))

    # 查询id大于10的学生信息
    stu1 = Students.query.filter(Students.id > 10)
    stu1 = Students.query.filter(Students.id.__gt__(10))

    # 查询id为4的学号是学生信息
    # get()获取主键对应的行数据
    stu1 = Students.query.get(4)

    # offset+limit
    stu1 = Students.query.limit(3)
    stu1 = Students.query.offset(0).limit(3)

    # order_by
    stu1 = Students.query.order_by('id')

    # 查询姓名中包含王的，并且年龄等于23
    stus = Students.query.filter(Students.s_name.contains('王'),
                                 Students.s_age == 19)
    # 查询姓名中包含王的，并且年龄等于23
    # django中，filter(Q(A)|Q(B))
    # flask中，filter(or_(A,B))
    from sqlalchemy import or_,not_
    stus = Students.query.filter(or_(Students.s_name.contains('王'),
                                 Students.s_age == 19))
    # 查询姓名中不包含王，且年龄等于19
    stus = Students.query.filter(not_(Students.s_name.contains('王')),
                                 Students.s_age == 19)

• 删除

stu = Students.query.filter(Students.id == id).first()
db.session.delete(stu)
db.session.commit()

• 更新

stu = Students.query.filter_by(id=id).first()
stu.s_name = '库里'
stu.save()