Flask SQLAlchemy

李恩泽的博客
75 阅读14分钟

Flask-SQLAlchemy

SQLAlchemy是一个ORM框架，可以不局限于框架或位置使用

而Flask-SQLAlchemy是一个Flask扩展，简化了在Flask程序中使用SQLAlchemy的操作，并且可以支持多种数据库后台

扩展安装

pip3 install Flask-SQLAlchemy
pip3 install flask-migrate
pip3 install flask_script
pip3 install mysqlclient

迁移集成

默认的Flask-SQLAlchemy并不能像django的orm一样，进行动态迁移，改变数据库状态，只能使用对象db的.create_all等方法进行数据库的管理

因此我们使用flask-migrate就解决这个问题，flask-migrate可以直接将修改的东西映射到数据库中，这一小节首先配置flask-migrate

首先我们创建一个最基本的含有蓝图的项目结构用来实现一个简单实用Flask-SQLAlchemy结合flask-migrate使用的例子

并且采用工厂模式创建flask应用对象，下面是目录结构

flaskProject/
	ext.py
	manage.py
	settings.py
	app.py
	user/
		__init__.py
		models.py
		views.py

  • ext.py

    扩展插件，数据库配置文件，单独写入该文件下，避免循环引入

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

  • manage.py

    命令行管理文件，在这个文件进行app的manager初始化，migrate命令的支持添加等等

from flask_migrate import Migrate, MigrateCommand
from ext import db
from flask_script import Manager
from app import create_app

app = create_app() # 实例化flask的app对象
manager = Manager(app)  # 实例化一个manager对象
Migrate(app, db)  # 迁移命令管理与app,建立关系

manager.add_command('db', MigrateCommand)  # 添加迁移命令集 到脚本命令

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

  • app.py

    代码入口文件，进行配置选择，蓝图注册，服务启动等功能

from flask import Flask
from ext import db


def create_app(config="settings.py"):
    app = Flask(__name__)
    app.config.from_pyfile(config)

    from user.views import user_blueprint
    app.register_blueprint(user_blueprint)

    db.init_app(app)
    return app


if __name__ == '__main__':
    app = create_app()
    app.run()

  • settings.py

    配置文件，用来存放项目配置、数据库连接等有关数据

其余配置项参考: flask-sqlalchemy.palletsprojects.com/en/2.x/conf…

# 设置连接数据库的URL
SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://root:mysql@127.0.0.1:3306/flasktest'
# 设置每次请求结束后会自动提交数据库中的改动
SQLALCHEMY_COMMIT_ON_TEARDOWN = True
# 如果设置为True，Flask-SQLAlchemy将跟踪对象的修改并发出信号。默认值为None
SQLALCHEMY_TRACK_MODIFICATIONS = True
# 查询时会显示原始SQL语句
SQLALCHEMY_ECHO = True

数据库连接URL配置参考

dialect+driver://username:password@host:port/database
参数名解释
SQLALCHEMY_DATABASE_URI用于连接的数据库 URI 。例如:sqlite:////tmp/test.dbmysql://username:password@server/db
SQLALCHEMY_BINDS一个映射 binds 到连接 URI 的字典。更多 binds 的信息见用 Binds 操作多个数据库。
SQLALCHEMY_ECHO如果设置为Ture， SQLAlchemy 会记录所有 发给 stderr 的语句，这对调试有用。(打印sql语句)
SQLALCHEMY_RECORD_QUERIES可以用于显式地禁用或启用查询记录。查询记录 在调试或测试模式自动启用。更多信息见get_debug_queries()。
SQLALCHEMY_NATIVE_UNICODE可以用于显式禁用原生 unicode 支持。当使用 不合适的指定无编码的数据库默认值时，这对于 一些数据库适配器是必须的（比如 Ubuntu 上 某些版本的 PostgreSQL ）。
SQLALCHEMY_POOL_SIZE数据库连接池的大小。默认是引擎默认值（通常 是 5 ）
SQLALCHEMY_POOL_TIMEOUT设定连接池的连接超时时间。默认是 10 。
SQLALCHEMY_POOL_RECYCLE多少秒后自动回收连接。这对 MySQL 是必要的， 它默认移除闲置多于 8 小时的连接。注意如果 使用了 MySQL ， Flask-SQLALchemy 自动设定 这个值为 2 小时。

建表

找到app的models文件夹下，使用ext目录下初始化好的db对象进行orm表的编写

class Users(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(32), index=True, nullable=False)
    age = db.Column(db.String(2), index=True, nullable=False)

    def __init__(self, name, age):
        self.name = name
        self.age = age

引入模型

建立好的表，为了能够让flask-migrate捕捉到模型类上下文变化，需要在manage.py文件中引入app下的models文件

from user import models  # 导入你想要迁移的数据表的ORM类

执行迁移

  • 初始化，只执行一次

python3 manage.py db init

  • 生成迁移文件，等同于django的makemigartions

python3 manage.py db migrate

  • 迁移

python3 manage.py db upgrade

  • 返回之前数据库版本

python3 manage.py db history

输出格式：<base> ->  版本号 (head), initial migration
  • 回滚到指定版本

python3 manage.py db downgrade 版本号

常用字段

类型名python中类型说明
Integerint普通整数，一般是32位
SmallIntegerint取值范围小的整数，一般是16位
BigIntegerint或long不限制精度的整数
Floatfloat浮点数
Numericdecimal.Decimal普通整数，一般是32位
Stringstr变长字符串
Textstr变长字符串，对较长或不限长度的字符串做了优化
Unicodeunicode变长Unicode字符串
UnicodeTextunicode变长Unicode字符串，对较长或不限长度的字符串做了优化
Booleanbool布尔值
Datedatetime.date时间
Timedatetime.datetime日期和时间
LargeBinarystr二进制文件

常用字段选项

选项名说明
primary_key如果为True，代表表的主键
unique如果为True，代表这列不允许出现重复的值
index如果为True，为这列创建索引，提高查询效率
nullable如果为True，允许有空值，如果为False，不允许有空值
default为这列定义默认值

常用关系选项

选项名说明
backref在关系的另一模型中添加反向引用
primary join明确指定两个模型之间使用的联结条件
uselist如果为False，不使用列表，而使用标量值
order_by指定关系中记录的排序方式
secondary指定多对多关系中关系表的名字
secondary join在SQLAlchemy中无法自行决定时，指定多对多关系中的二级联结条件

增删改查

增加数据

object = Users(name="test1", age="16")
db.session.add(object)
db.session.commit()

添加多条

user2 = User(name="admin", age="17")
user3 = User(name="root", age="18")
db.session.add(user2)
db.session.add(user3)
db.session.commit()

user_list = [
  	User(name="admin1", age="19"),
  	User(name="admin2", age="20"),
]
db.session.add_all(user_list)
db.session.commit()

查询数据

全部查询
Users.query.all()

select * from users;

查询user表中的所有数据

数量查询
Users.query.count()
Users.query.filter(...).count()
首个查询
Users.query.first()
User.query.filter_by(...).first()
first_or_404() # 返回查询的第一个结果，如果未查到，返回404
通过主键查询
Users.query.get(1)
get_or_404() # 返回指定主键对应的行，如不存在，返回404

select * from users where users.id= %(param_1)s;

通过id在users表中查询

filter过滤

以下语句使用时，需加.all()等方法实获取数据，否则只是一条创建好的SQL语句

EQUALS

query.filter(User.id==1)

NOT EQUALS

query.filter(User.id!=1)

LIKE

query.filter(User.name.like('%test%'))

IN

query.filter(User.id.in_([1, 2, 3]))

NOT IN

query.filter(~User.id.in_([1, 2, 3]))

IS NULL

query.filter(User.name == None)
query.filter(User.name.is_(None))

IS NOT NULL

query.filter(User.name != None)
query.filter(User.name.isnot(None))

AND

query.filter(and_(User.name=='test', User.age == '17'))

OR

query.filter(or_(User.id==1, User.id==2))

MATCH AGAINST

使用全文索引FULLTEXT INDEX进行查询

query.filter(Users.name.match("te"))

OFFSET LIMIT

跳过前两条拿取，只拿三条

query.filter(Users.name.match("te")).offset(2).limit(3)
filter_by过滤

filter_by用于简单的查询列名，不支持比较运算符

User.query.filter_by(name='test').first()
order_by排序

默认是升序

query.order_by(-Users.id) # 降序
group_by分组

比如User用户表与Role角色表，用户可以外键关联一个角色，那么通过角色进行分组那么就是

User.query.group_by(User.role_id).all()
limit限制
query.limit(1)
query.limit(1).offset(2)
paginate分页查询
def paginate(self, page=None, per_page=None, error_out=True, max_per_page=None):
   	'''
    page: 第几页
    per_page: 一页几条
    '''

比如分页一个表中的数据

paginate = History.query.paginate(5, 3)
# 第五页，每页三条

分页对象paginate不光包含一页的数据，还包含其余属性

'items', 'iter_pages', 
'has_next', 'next', 'next_num', 
'page', 'pages', 'per_page', 
'has_prev', 'prev', 'prev_num', 
'query', 'total'
  • pages: 总共能生成多少页
  • page: 当前页码
  • has_next、has_prev: 是否有下、上一页
  • next_num、prev_num: 下、上一页页码
  • next、prev: 下、上一页的分页对象
  • items: 当前页数据
  • total: 记录总数
  • per_page: 每页显示的记录数量
查询优化

默认的查询方式都是讲所有表的行都作为结果查询，那么数据量很大，其次无法高效利用索引

所以我们使用load_only来采用查询特定字段作为结果

from sqlalchemy.orm import load_only
User.query.options(load_only(User.name, User.age)).all()

或者采用原生语法查询时定义查询结果

db.session.query(User.id).filter()

db.session查询

这是sqlAlchemy的原生语法

db.session.query(User).all()
db.session.query(User).first()
db.session.query(User).get(2)

更新数据

修改单一数据 先执行查，再执行修改

user = User.query.get(1)
user.name = "new"
db.session.commit()

批量修改

Users.query.filter_by(name='zxc').update({'name': 'aaabbb'})
db.session.commit()

db.session.query(Users).filter_by(name="bbb").update({"name": "zxc"})
db.session.commit()

删除数据

删除数据，也是要先获取，在删除哦

  • 批量删除
Users.query.filter_by(name='aaabbb').delete()
Users.query.filter(User.id.in_(1,2,3)).delete(synchronize_session=False)
db.session.commit()
  • 单独删除
object = Users.query.filter_by(name='test').first()
db.session.delete(object)
db.session.commit()
  • 全量删除

一整个表都没了

Users.query.delete()

批量更新或删除

sqlalchemy 利用 session 执行 delete 时有一个 synchronize_session 参数用来说明 session 删除对象时需要执行的策略，共三个选项

session.filter(Tag).delete(synchronize_session='evaluate')

synchronize_session用于query在进行批量的delete或者update操作时，对session的同步策略

  • False: 不对session进行同步，直接进行delete或update操作，如果被删除的在session中存在，那么在session commit或者expire_all之前，这些被删除的对象都会存在session中
  • 'fetch': 在delete或update操作之前，先发一条sql到数据库获取符合条件的记录，符合条件的将会从session中删掉或更新
  • 'evaluate': 默认值。如果 session 中原本就没有这些被删除的 objects，扫描不会发生匹配，相当于匹配未正确执行，抛出错误

表索引

主键索引

id = db.Column(db.Integer, primary_key=True)

普通索引

phone = db.Column(db.String(11), index=True)

唯一约束

phone = db.Column(db.String(11), unique=True)

联合唯一索引

idol = db.Column(db.Integer, db.ForeignKey("users.id")) # 偶像
fans = db.Column(db.Integer, db.ForeignKey("users.id")) # 粉丝
db.UniqueConstraint('idol','fans',name="follow_relation")

表关系

多对一

一对多关系创建表通过db.ForeignKey关联

建立多对一的关系，比如一个老师有多个学生，一个学生只能有一个老师

  • 老师表
class Teacher(db.Model):
    __tablename__ = "teachers"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(32))
    students = db.relationship("Student", backref="teachers", lazy="dynamic")
  • 学生表
class Student(db.Model):
    __tablename__ = "students"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(32))
    teacher_id = db.Column(db.Integer, db.ForeignKey("teachers.id", ondelete="SET NULL"))

db.Column(db.Integer, db.ForeignKey("teachers.id", ondelete="SET NULL"))

构建外键: 通过db.ForeignKey进行设置，括号内参数为对应表的id属性，ondelete属性代表当前外键约束效果

  1. RESTRICT

    若子表中有父表对应的关联数据，删除父表对应数据，会阻止删除，也是默认项

  2. NO ACTION

    在MySQL中，同RESTRICT

  3. CASCADE

    级联删除

  4. SET NULL

    父表对应数据被删除，子表对应数据项会设置为NULL

db.relationship("Teacher", backref="teachers", lazy="dynamic")

构建关联关系: 类似手动声明了django的orm中_set字段，一般关联外键是为了方便进行关联表直接的orm正反查询，常建议写在一的一方

lazy: 指定sqlalchemy数据库什么时候加载数据

  1. select: 就是访问到属性的时候，就会全部加载该属性的数据
  2. joined: 对关联的两个表使用联接
  3. subquery: 与joined类似，但使用子子查询
  4. dynamic: 不加载记录，但提供加载记录的查询，也就是生成query对象，在这个对象基础上还支持继续进行filter、all等操作
    • dynamic属性只能用在多对一中为一的一方，比如学生老师中的老师一方，这样lazy属性对应的就是多的一方了

一对一关系:在relationship里面lazy变量去掉,换成uselist=False

多对一添加

创建或者查询老师，将老师数据对应的id赋值到学生数据中

t1 = Teacher.query.first() # 查询获得
#-------------------------
t1 = Teacher(name="t1")
db.session.add(t1)
db.session.commit()

s1 = Student(name="s1")
s1.teacher_id = t1.id
db.session.add(s1)
db.session.commit()

使用关联关系db.relationship进行添加，同样也可以添加一个已的老师作为外键，或者在添加过程中创建一个新的老师

这里使用时，通过db.relationship("Teacher", backref="teachers", lazy="dynamic")关系中，在学生表指明的teachers属性进行赋值

s = Student(name="s", teachers=Teacher(name="t")) # 创建个新老师
# ----------------------------------------------
t = Teacher.query.first() # 查询已有老师
s = Student(name="s", teachers=t) # 用已有的老师

db.session.add(s)
db.session.commit()

除了在建立学生时，正向赋值老师外键，关系型外键还支持反向创建，比如在建立老师过程中，将关联这个老师的学生们创建出来

注意关联这个老师的学生们是一个集合，并不是单独一条数据，所以字段赋值时采用序列形式

t = Teacher(name="t") # 创建老师对象
t.students = [Student(name="s1"), Student(name="s2")] # 老师外键关联对象赋值关联他的学生们
db.session.add(t)
db.session.commit()

同理可得，其实也可以在创建老师时，赋值外键关联对象时，使用已经查找到的学生们，比如

t = Teacher(name="t") # 创建老师对象
t.students = Student.query.all() # 老师外键关联对象赋值关联他的学生们
db.session.add(t)
db.session.commit()
多对一查询

普通的查询，比如通过一个学生，查询这个学生的老师名

s = Student.query.first()
t = Teacher.query.get(s.teacher_id)

非常的麻烦，需要两步，但是现在如果通过关联关系db.relationship就比较简单了

数据查询

首先是正向查询，查询一个学生的老师

s = Student.query.first()
s.teachers.name # 通过关联关系就可以直接拿到对应老师，不需要再用id获取

如果是反向查询，查询一个老师对应的学生们

t = Teacher.query.get(4)
t.students.all() # 由于此时设置的是dynamic属性，返回的数据结果为一个AppenderBaseQuery对象，继续对其进行查询结果即可拿到真正数据
表查询

查询哪些学生的老师叫t，如果在表结构层面正向查询，通过原始的id查询就像下面这样

s = Student.query.filter(
  	Student.teacher_id.in_(db.session.query(Teacher.id).filter(Teacher.name == "t"))
).all()

如果在关联关系上进行正向查询，那么可以

s = Student.query.filter(
  Student.teachers == Teacher.query.filter_by(name="t").first()).all()

s = Student.query.filter(Student.teachers.has(Teacher.name == "t")).all()
# 通过has语句，在多的一方使用，因为Student.teachers只能是一

反向查询比如查询哪些老师带的学生名字叫s，那么可以这么查

t = Teacher.query.filter(Teacher.students.any(Student.name == "s")).all()
# 使用any语句，在一的一方使用，因为Teacher.students是多

或者使用query.jion连接来实现外键的查询

t = Teacher.query.join(Student).filter(Student.name.like("%s%")).all()
t = Teacher.query.join(Student).filter(Student.name.contains("s")).all()
多对一修改

更新某个学生的外键为另外一个，使用关联关系直接赋值即可

s = Student.query.first()
s.teachers = Teacher.query.first()
db.session.commit()

修改张姓老师下的所有学生外键为另一个，所有姓张的老师都辞职了，不干了，他的学生统统转移到另外一个老师下

Student.query.filter(
  Student.teachers.has(Teacher.name.startswith("张"))
).update({"teacher_id": Teacher.query.get(4).id}, synchronize_session=False)
db.session.commit()
多对一删除

删除姓名为张开头老师的所有学生

Student.query.filter(
Student.teachers.has(Teacher.name.startswith("张"))).delete(synchronize_session="fetch")
db.session.commit()

删除老师的话，那么对应动作取决于外键设置的ondelete属性

多对多

sqlalchemy的多对多使用第三张表完成关联关系

比如关注功能，用户可以被多个用户所关注，那么就可以采用多对多来完成，注意为了方便使用orm

  • 用户表
class User(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(32))
    
    fans = db.relationship("User", secondary="follows", backref=db.backref("idols", lazy="dynamic"),
                           lazy="dynamic", # 由于是多对多，所以生成query对象可以继续查询
                           primaryjoin=(Follow.idol == id), # 左侧，用于获取 我的粉丝
                           secondaryjoin=(Follow.fans == id) # 右侧，用于获取 我的偶像
                          )
    
    def __repr__(self): return self.name

backref

​ 替换了原来的参数，变为了一个属性参数，并使反向关系的结果也成为一个query对象，支持继续查询过滤

secondary

​ 用来指明中间的关系表，构建额外多对多关系的表

primaryjoin

​ 多对多中用于从子对象查询其父对象的 condition（child.parents），默认只考虑外键

secondaryjoin

​ 多对多中用于从父对象查询其所有子对象的 condition（parent.children），同样的，默认情况下只考虑外键

  • 实现关注的第三张表
class Follow(db.Model):
    __tablename__ = 'follows'

    id = db.Column(db.Integer, primary_key=True)
    idol = db.Column(db.Integer, db.ForeignKey("users.id"))  # 偶像
    fans = db.Column(db.Integer, db.ForeignKey("users.id"))  # 粉丝
    db.UniqueConstraint('idol', 'fans', name="follow_relation")

    def __repr__(self): return self.name
多对多添加

比如添加关注关系，那么由于是多对多关系，所以会采用列表的形式进行外键的orm赋值，如下所示

fans1 = Users(name="fans.no1")
fans2 = Users(name="fans.no2")
# 为user添加俩粉丝
db.session.add_all([fans1,fans2])
db.session.commit()

user.fans = [fans1,fans2]
db.session.commit()
多对多查询

查询某个用户的粉丝

fans = user.fans.all()

查询某个用户的偶像

idols = user.idols.all()
多对多修改

多对多外键的修改和列表操作类似，可以使用remove将某个关联从多对多关系列表中删除掉，注意移除不存在对象会抛出错误

user = Users.query.get(1) # 获取到偶像
fans1 = Users.query.filter_by(name="fans.no1").first() # 查到他的某个粉丝
user.fans.remove(fans1) # 从偶像的粉丝列表中移除，脱粉

也可以通过append操作额外添加多对多关系

fans1 = Users.query.filter_by(name="fans.no1").first()
user.fans.append(fans1)
db.session.commit()
avatar
文章
阅读
粉丝