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()
