Flask学习笔记（5）

SQLAlchemy是一个关系型数据库框架，提供了ORM（Object-Relation Mapping）和底层的原生数据库的操作。Flask-SQLAlchemy是一个简化了SQLAlchemy操作的flask扩展。

安装 flask-sqlalchemy

pip install flask-sqlalchemy

如果连接MySql数据库，需要安装PyMySQL

pip install PyMySQL

常用的SQLALchemy字段类型

模型字段类型	python中的类型	说明
Integer	int	普通整数
SmallInteger	int	取值范围小的整数，一般16位
BigInteger	int或long	不限制精度的整数
Float	float	浮点数
Numeric	decimal, Decimal	普通数值，一般32位
String	str	变长字符串
Text	str	变长字符串，对较长字符串做了优化
Unicode	unicode	变长Unicode字符串
Boolean	bool	布尔值
Date	datetime.date	日期
Time	datetime.time	时间
DateTime	datetime.datetime	日期和时间
LargeBinary	str	二进制文件内容

常用的SQLALchemy约束选项

选项	说明
primary_key	如果为True，代表主键
unique	如果为True，代表该列不允许出现重复的值
index	如果为True，为该列创建索引
nullable	如果为True，允许有空值，如果设置False，则不允许有空值
default	为该列定义默认值

入门案例

配置数据库连接:

class Config:
    # 数据库连接配置
    # mysql+pymysql://user:password@hostip:port/database
    SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://root:123456@127.0.0.1:3306/flask'
    # 动态追踪修改配置，如未设置只会提示警告
    SQLALCHEMY_TRACK_MODIFICATIONS = False
    # 查询时会显示原始SQL语句
    SQLALCHEMY_ECHO = True

加载配置，新建model:

app.config.from_object(Config)
db = SQLAlchemy()
db.init_app(app)

class User(db.Model):
    __tablename__ = 'tb_user'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='主键ID')
    name = db.Column(db.String(15), nullable=False)
    password = db.Column(db.String(12), nullable=False)
    sex = db.Column(db.Boolean, default=False, comment='性别')
    phone_number = db.Column(db.String(11), unique=True)
    register_time = db.Column(db.DateTime, default=datetime.now())

    def __repr__(self):
        return self.username

根据模型创建表:

if __name__ == '__main__':
    with app.app_context():
        db.create_all()  # 根据模型创建对应表
        # db.drop_all()  # 删除模型对应的表
    app.run()

数据表基础操作

添加一条数据:

@app.route('/')
def index():
    """添加一条数据"""
    # user = User(name='xiaoming', password='123', sex=True, phone_number='12345678911')
    # db.session.add(user)
    # db.session.commit()
    return 'OK!'

添加多条数据:

"""添加多条数据"""
user_list = [
    User(name='xiaoming', password='1234', sex=True, phone_number='12347249111'),
    User(name='xiaohong', password='4561', sex=False, phone_number='21431234421'),
    User(name='xiaolan', password='2341', sex=False, phone_number='3412453462'),
    User(name='xiaohuang', password='5674', sex=True, phone_number='73734731322')
]
db.session.add_all(user_list)
db.session.commit()

查询数据:

# 根据主键ID查询一条数据
user = User.query.get(1)
if user is None:
    print('当前用户不存在!')
else:
    print(user)
    print(user.name, user.phone_number)

根据查询条件查询一条数据:

# 根据查询条件获取一条数据
# 模型.query.filter(模型.字段==条件值).first()
user = User.query.filter(User.id == 1).first()
print(user)
print(user.name)

根据查询条件查询多条数据:

# 根据查询条件获取多条数据
# 模型.query.filter(模型.字段==条件值).all()
user_list = User.query.filter(User.id < 10).all()
print(user_list)
for user in user_list:
    print(user.name, user.sex, user.password)

更新数据:

# 更新数据，先查询后修改
user = User.query.filter(User.name == 'xiaoming').first()
user.name = 'zhangsan'
db.session.commit()

直接根据条件修改:

# 直接根据条件修改
User.query.filter(User.name == 'zhangsan').update({User.name: 'lisi', User.password: 'qbcdefg'})
db.session.commit()

删除数据:

# 删除数据
user = User.query.filter(User.name == 'lisi').first()
db.session.delete(user)
db.session.commit()

直接根据条件删除数据:

# 直接根据条件删除操作
User.query.filter(User.name == 'lisi').delete()
db.session.commit()

进阶操作

新建Student表

class Student(db.Model):
    __tablename__ = 'tb_student'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='主键ID')
    name = db.Column(db.String(100), nullable=False, comment='姓名')
    age = db.Column(db.Integer, nullable=False, comment='年龄')
    sex = db.Column(db.Boolean, default=False, comment='性别')
    money = db.Column(db.DECIMAL(8, 2), nullable=False, comment='钱包')

    def __repr__(self):
        return self.name

filter模糊查询

"""filter模糊查询"""
# 模糊查询
# 包含
# student_list = Student.query.filter(Student.name.like("%xiaoming2%")).all()
# 以xxx开头
student_list = Student.query.filter(Student.name.startswith("xiao")).all()
# 以xxx结尾
student_list = Student.query.filter(Student.name.endswith("hong")).all()
print(student_list)

filter_by设置精确条件查找数据

"""filter_by 只支持一个等号作为判断条件，而且字段左边不需要声明模型类名"""
student = Student.query.filter_by(name='xiaoming').first()

filter多条件查询

and:

"""filter多条件查询"""
"""and_ 且"""
from sqlalchemy import and_
# 方式1:
student_list1 = Student.query.filter(Student.money == 20000, Student.sex == False).all()
print(student_list1)
# 方式2:
student_list2 = Student.query.filter(and_(Student.money == 20000, Student.sex == False)).all()
print(student_list2)

or:

"""or_ 或"""
from sqlalchemy import or_
student_list = Student.query.filter(or_(Student.age > 20, Student.age < 19)).all()
print(student_list)

not:

"""not_ 非"""
from sqlalchemy import not_
student_list = Student.query.filter(not_(Student.age > 18)).all()
print(student_list)

filter值范围查询

"""filter值范围查询"""
# 年龄=17或18或19
student_list = Student.query.filter(Student.age.in_([17, 18, 19])).all()
print(student_list)

排序

order_by:

"""order_by"""
# 方式1:
student_list1 = Student.query.order_by(db.desc(Student.money)).all()
print(student_list1)
# 方式2:
student_list2 = Student.query.order_by(Student.money.desc()).all()
print(student_list2)

统计数量

count:

"""统计结果数量"""
student_count = Student.query.filter(Student.age > 20).count()
print(student_count)

限制查询结果

"""限制查询结果"""
student_list = Student.query.order_by(Student.money.desc()).offset(2).limit(3).all()
print(student_list)

分页

paginate分页器

"""paginate分页器"""
# paginate(page=当前页码, per_page=每一页数据量, max_per_page=每一页最大数据量 )
# 当前页码: 从request.args['page']获取，如果当前参数没有，则为1
pagination = Student.query.filter(Student.sex == True).paginate(per_page=1)
print(pagination.items)
print(pagination.has_next)  # 是否有下一页
print(pagination.has_prev)  # 是否有上一页
print(pagination.total)  # 本次查询结果总数量

分组查询

查询男生和女生的最大年龄

from sqlalchemy import func
# 查询男生和女生的最大年龄
result = db.session.query(Student.name, func.max(Student.age)).group_by(Student.sex).all()
print(result)

原生sql语句

"""执行原生sql语句"""
# 查询
result = db.session.execute("select * from tb_student").fetchall()
print(result)

# 修改
result = db.session.execute("UPDATE tb_student SET money=(tb_student.money + %s) where tb_student.age = %s" % (200, 22))
db.session.commit()

关联查询

一对一

新建StudentInfo表

class StudentInfo(db.Model):
    __tablename__ = 'tb_student_info'
    id = db.Column(db.Integer, primary_key=True, comment='主键ID')
    sid = db.Column(db.Integer, db.ForeignKey(Student.id), comment='学生')
    address = db.Column(db.String(255), nullable=True, comment='家庭住址')
    mobile = db.Column(db.String(15), unique=True, comment='紧急联系电话')

    def __repr__(self):
        return self.own.name

Student 表需要添加关联

# 关联属性 是SQLAlchemy提供给开发者快速引用外键模型的一个对象属性
# backref 反向引用，通过外键模型查询主模型数据时的关联属性
info = db.relationship('StudentInfo', backref='own', uselist=False)

添加数据

"""添加数据"""
student = Student(
    name='zhangsan',
    age=13,
    sex=True,
    money=1000,
    info=StudentInfo(
        mobile='18812345678',
        address='地球'
    )
)
db.session.add(student)
db.session.commit()

修改更新

"""修改更新"""
student = Student.query.get(1)
student.age = 18
student.info.address = '火星'
db.session.commit()

删除

"""删除"""
student = Student.query.get(1)
db.session.delete(student.info)  # 先删除外键模型，再删除住模型
db.session.delete(student)
db.session.commit()

一对多

新建Course和Teacher表

class Teacher(db.Model):
    __tablename__ = 'tb_teacher'
    id = db.Column(db.Integer, primary_key=True, comment='主键ID')
    name = db.Column(db.String(100), comment='姓名')
    option = db.Column(db.Enum('教授', '讲师', '班主任', '助教'), default='讲师', comment='教职')
    course_list = db.relationship('Course', uselist=True, backref='teacher', lazy='subquery')

    def __repr__(self):
        return self.name


class Course(db.Model):
    __tablename__ = 'tb_course'
    id = db.Column(db.Integer, primary_key=True, comment='主键ID')
    name = db.Column(db.String(100), comment='姓名')
    price = db.Column(db.Numeric(6, 2), default=False, comment='价格')
    teacher_id = db.Column(db.Integer, db.ForeignKey(Teacher.id), comment='老师')

• lazy='subquery'，查询当前数据模型时，采用子查询（subquery），把外键模型的属性也查询出来。
• lazy=True或lazy='select'，查询当前数据模型时，不会把外键模型的数据查询出来，只有操作到外键关联属性时，才进行连表查询。
• lazy='dynamic'，查询当前数据模型时，不会把外键模型的数据查询出来，只有操作到外键关联属性并操作外键模型具体属性时，才进行连表查询数据。

添加数据

"""添加数据"""
# 添加主模型数据，同时添加外键模型
teacher = Teacher(
    name='abc',
    option='班主任',
    course_list=[
        Course(name='语文', price='99.0'),
        Course(name='数学', price='80.0'),
        Course(name='英语', price='60.0'),
    ])
db.session.add(teacher)
db.session.commit()

查询数据

"""查询数据"""
teacher = Teacher.query.filter(Teacher.name == 'abc').first()
print(teacher.name, teacher.option)
for course in teacher.course_list:
    print(course.name)

course = Course.query.filter(Course.name == '语文').first()
print("%s在教%s" % (course.teacher.name, course.name))

更新数据

"""更新数据"""
teacher = Teacher.query.filter(Teacher.name == 'abc').first()
teacher.course_list[0].name = 'English'
db.session.commit()

删除数据

teacher = Teacher.query.filter(Teacher.name == 'abc').first()
for course in teacher.course_list:
	db.session.delete(course)
db.session.delete(teacher)
db.session.commit()

多对多

方式1：使用db.Table的方式

"""
    db.Table(
        表名,
        db.Column("字段名", 字段类型, 外键声明),
        ...
    )
"""
# 以db.Table关系表来确定模型之间的多对多关联
achievement = db.Table(
    'tb_achievement',
    db.Column('student_id', db.Integer, db.ForeignKey('tb_student.id')),
    db.Column('course_id', db.Integer, db.ForeignKey('tb_course.id'))
)

class Course(db.Model):
	...
    student_list = db.relationship('Student', secondary=achievement, backref='course_list', lazy='dynamic')

class Student(db.Model):
    ...

多对多，拆分为3个模型，其中tb_achievement作为单独模型存在

添加

course1 = Course(name='语文', price='9.9', teacher=Teacher(name='aaa', option='讲师'))
course3 = Course(name='英语', price='7.9', teacher=Teacher(name='bbb', option='教授'))

student = Student(
    name='xiaoming',
    age=5,
    sex=False,
    money=1000,
    info=StudentInfo(
        mobile='13211111111',
        address='beijing'
    ),
    course_list=[
        course1,
        course2,
        course3
    ]
)
db.session.add(student)
db.session.commit()

方式2：创建中间模型类的方式

class Achievement(db.Model):
__tablename__ = 'tb_achievement'
id = db.Column(db.Integer, primary_key=True, comment='主键ID')
student_id = db.Column(db.Integer, db.ForeignKey('tb_student.id'), comment='学生')
course_id = db.Column(db.Integer, db.ForeignKey('tb_course.id'), comment='课程')

数据库迁移

在Flask中可以使用Flask-Migrate扩展，来实现数据迁移，并且集成到Flask-Script中，所有操作通过命令都能完成。

为了导出数据库迁移命令，Flask-Migrate提供了一个MigrateCommand类，可以附加到flask-script的manager对象上。

安装Flask-Migrate

pip install flask-migrate

app.py

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_script import Manager
from flask_migrate import Migrate, MigrateCommand
app = Flask(__name__)
class Config:
    # 数据库连接配置
    # mysql+pymysql://user:password@hostip:port/database
    SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://root:123456@127.0.0.1:3306/flask'
    # 动态追踪修改配置，如未设置只会提示警告
    SQLALCHEMY_TRACK_MODIFICATIONS = False
    # 查询时会显示原始SQL语句
    SQLALCHEMY_ECHO = True
    DEBUG = True

app.config.from_object(Config)

manager = Manager()
manager.app = app

db = SQLAlchemy()
db.init_app(app)

# 数据迁移，第一个参数是app实例，第二个参数是SqlAlchemy数据库实例
migrate = Migrate(app, db)

# manager时Flask-Script的实例，下面这条语句在flask-script中添加一个db命令
manager.add_command('db', MigrateCommand)

...

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

创建迁移版本仓库:

python app.py db init

会生成migrations文件夹。

创建迁移脚本:

python app.py db migrate -m 'table'

更新数据库:

python app.py db upgrade