一、创建表
用于第一次创建
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()
