Flask-SQLAlchemy的使用_flask sqlalchemy 模糊查询

SQLAlchemy 是目前python中最强大的 ORM框架, 功能全面, 使用简单。
Flask-SQLAlchemy 是 Flask 扩展之一，它封装了 SQLAlchemy 库，使得在 Flask 中与数据库交互变得更加方便。使用 Flask-SQLAlchemy 可以完成以下任务：

1. 配置数据库连接信息：可以在 Flask 应用程序中设置数据库连接字符串（比如 MySQL 或 PostgreSQL），从而方便地与数据库建立连接。
2. 定义模型类：通过定义类来表示数据库中的表格和表格之间的关系。模型类继承自 SQLAlchemy 提供的基类 Model。
3. 声明关系：可以使用特殊的属性（比如 relationship、backref）来声明模型类之间的关系，比如一对多、多对一、多对多等。
4. 数据库迁移：Flask-SQLAlchemy 可以支持数据库的迁移（migration）功能，它使用 Flask-Migrate 扩展实现，可以对数据库进行版本管理。
   使用 Flask-SQLAlchemy，我们可以快速地将 Flask 应用程序连接到数据库，并通过定义模型类来进行 CRUD 操作。具体的使用方法可以参考 Flask-SQLAlchemy 的官方文档。

ORM优点：

1. 有语法提示, 省去自己拼写SQL，保证SQL语法的正确性
2. orm提供方言功能(dialect, 可以转换为多种数据库的语法), 减少学习成本
3. 防止sql注入攻击
4. 搭配数据迁移, 更新数据库方便
5. 面向对象, 可读性强, 开发效率高

ORM缺点：

1. 需要语法转换, 效率比原生sql低
2. 复杂的查询往往语法比较复杂 (可以使用原生sql替换)

一、相关配置

安装

pip install flask-sqlalchemy
# flask-sqlalchemy 在安装/使用过程中, 如果出现 ModuleNotFoundError: No module named 'MySQLdb’错误, 则表示缺少mysql依赖包, 则执行下面命令
pip install mysqlclient # 如果还是失败，再尝试pip install pymysql
1
2
3

MySQL数据库相关配置

在app/config.py中

"""
项目应用配置
"""
from pathlib import Path
BASE_DIR = Path(__file__).resolve().parent.parent # 项目路径
DEBUG = True # 设置调试模式
SECRET_KEY = 'key' # 设置交互用的密钥，以防跨域攻击（ CSRF ）
SQLALCHEMY_DATABASE_URI = 'mysql://user:pass@127.0.0.1:3306/db_ccblog' # 数据库路径，用于设置数据库连接地址
SQLALCHEMY_COMMIT_ON_TEARDOWN = True # 设置每次连接结束后自动提交数据库中的变动。
SQLALCHEMY_TRACK_MODIFICATIONS = True # 设置追踪数据库变化（触发钩子函数），会额外消耗内存
1
2
3
4
5
6
7
8
9
10

1. 在项目目录（CCBlog/app）的__init__.py中引入SQLAlchemy

from flask_sqlalchemy import SQLAlchemy
# 实例化SQLAlchemy对象
db = SQLAlchemy()
1
2
3

2. 在工厂函数create_app中与app绑定关系

def create_app(test_config=None):
    # ...省略代码
    # 递归创建目录，确保项目文件存在
    try:
        os.makedirs(app.instance_path)
    except OSError:
        pass

    # 绑定数据库
    db.init_app(app)

    return app
1
2
3
4
5
6
7
8
9
10
11
12

3. 创建模型数据
   以auth的用户模型为例
   在app/models.py中创建模型如下

from app import db
from datetime import datetime

class BaseModel(db.Model):
    """ 基类模型
    """
    __abstract__ = True
    # 不过utc时间比北京时间要慢8个小时
    datetime_create = db.Column(db.DateTime, nullable=False, default=datetime.utcnow ) # 创建时间

class User(BaseModel):
    """用户模型
    """
    __tablename__ = "user" # 表名
    # 属性
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(128), unique=True, nullable=False)
    password = db.Column(db.String(320), nullable=False)
    is_admin = db.Column(db.Boolean, nullable=True, default=False) # 管理员标识

    def __repr__(self):
        return '<User %r>' % self.name
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

4. 注册数据模型
   在项目目录（CCBlog/app）的__init__.py中引入模型

from app.auth import models
1

---

扩展内容，不建议采用此方式
5. 连接并同步字段
做完以上步骤，就已经可以连接到数据库了
但是字段此时还并没有同步到数据库，同步字段到数据库

# 方法是在app所在的目录下进入Flask的shell环境，运行以下两个命令即可：
from app import db
db.create_all() # 创建所有表
db.drop_all() # 删除所有表
1
2
3
4

# 在powershell终端， 运行如下命令，即可进入shell环境
# FLASK_ENV 变量用于指定 Flask 应用程序的运行环境，例如开发环境、测试环境或生产环境。当设置为 "development" 时，Flask 将启用调试模式和自动重新加载，这使得开发应用程序更加容易。
> $env:FLASK_APP = "app"
> $env:FLASK_ENV = "development" # 
> flask shell
1
2
3
4
5

二、ORM模型的创建与调整

1、示例

from app import db
class User(db.Model): # 必须继承db.Model
    """用户模型
    """
    __tablename__ = "user" # 表名
    # 字段, 由创造db.Column()
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(128), unique=True, nullable=False)
    password = db.Column(db.String(320), nullable=False)
    is_admin = db.Column(db.Boolean, nullable=True, default=False)
    datetime_create = db.Column(db.DateTime, nullable=False, default=datetime.utcnow )

    def __repr__(self):
        return '<User %r>' % self.name
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2、常用字段类型以及字段选项

• 字段类型

• 字段选项

3、外键

# 用db.ForeignKey设置外键关系
id_user = db.Column(db.Integer, db.ForeignKey('user.id', ondelete='CASCADE', onupdate='CASCADE'), nullable=False) 
1
2

4、多对多关系帮助器表

# 收藏表，帮助器表
star = db.Table('star',
    db.Column('id_user', db.Integer, db.ForeignKey('user.id', ondelete='CASCADE', onupdate='CASCADE'), primary_key=True),
    db.Column('id_blog', db.Integer, db.ForeignKey('blog.id', ondelete='CASCADE', onupdate='CASCADE'), primary_key=True)
)
# 对帮助器表的增删查改要利用的关系引用，在ORM模型的使用中会说明
1
2
3
4
5
6

三、ORM模型的使用

# 原生sql语句操作
sql = 'select * from user'
result = db.session.execute(sql)
1
2
3

1、类创建的表（增删查改操作）

1. 增添记录

# 1.创建模型对象
user = User(id, username, password)
# 2.将模型对象添加到会话中 
db.session.add(user)
# 一次性添加多条记录：db.session.add_all([user1, user2, user3])
# 3.提交会话 (会提交事务)
db.session.commit()
# 扩展说明
# db.session.flush() # 主动执行flush操作, 立即执行SQL操作(数据库同步)
# User.query.count()  # 查询操作会自动执行flush操作
# db.session.commit()  # 提交会话会自动执行flush操作

1
2
3
4
5
6
7
8
9
10
11
12

2. 删除记录

# 方法1
User.query.filter_by(User.username='name').delete()
# 方法2
# db.session.delete(user)
# 两个方法均需提交会话
db.session.commit()
1
2
3
4
5
6

3. 修改记录

# 方法1
User.query.filter_by(User.username='name').update({'password':'newdata'})
# 方法2
# user.password = 'newdata'
# 两个方法均需提交会话
db.session.commit()
1
2
3
4
5
6

4. 查询记录

User.query.all() # 查询所有用户数据，返回列表, 元素为模型对象
User.query.count() # 获取查询所得的记录数
User.query.first() # 获取查询所得的第一条记录
# 查询id为userid的用户[3种方式]
# 方式1: 根据id查询  返回模型对象/None
User.query.get(userid)  
# 方式2: 等值过滤器 关键字实参设置字段值  返回BaseQuery对象
# BaseQuery对象可以续接其他过滤器/执行器  如 all/count/first等
User.query.filter_by(id=userid).all()  
# 方式3: 复杂过滤器  参数为比较运算/函数引用等  返回BaseQuery对象
User.query.filter(User.id == userid).first()  
# 查询名字结尾字符为g的所有用户[开始 / 包含]
User.query.filter(User.name.endswith("g")).all()
User.query.filter(User.name.startswith("w")).all()
User.query.filter(User.name.contains("n")).all()
User.query.filter(User.name.like("w%n%g")).all()  # 模糊查询

from sqlalchemy import and_, or_, not_, in_
# 查询名字和邮箱都以li开头的所有用户[2种方式]
User.query.filter(User.name.startswith('li'), User.email.startswith('li')).all()
User.query.filter(and_(User.name.startswith('li'), User.email.startswith('li'))).all()

# 查询age是25 或者 `email`以`itheima.com`结尾的所有用户
User.query.filter(or_(User.age==25, User.email.endswith("itheima.com"))).all()

# 查询名字不等于wang的所有用户[2种方式]
User.query.filter(not_(User.name == 'wang')).all()
User.query.filter(User.name != 'wang').all()

# 查询id为[1, 3, 5, 7, 9]的用户
User.query.filter(User.id.in_([1, 3, 5, 7, 9])).all()

# 所有用户先按年龄从小到大, 再按id从大到小排序, 取前5个
User.query.order_by(User.age, User.id.desc()).limit(5).all()

# 查询年龄从小到大第2-5位的数据   2 3 4 5
User.query.order_by(User.age).offset(1).limit(4).all()

# 分页查询, 每页10个, 查询第2页的数据  paginate(页码, 每页条数)
pn = User.query.paginate(page=2, per_page=10, error_out=False)
# pn.pages 总页数  pn.page 当前页码 pn.items 当前页的数据  pn.total 总条数

# 比较查询
User.query.filter(User.id.__lt__(5)) # 小于5
User.query.filter(User.id.__le__(5)) # 小于等于5
User.query.filter(User.id.__gt__(5)) # 大于5
User.query.filter(User.id.__ge__(5)) # 大于等于5

# ......
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

2、帮助器表创建的表（增删查改操作）

# 收藏表，帮助器表
star = db.Table('star',
    db.Column('id_user', db.Integer, db.ForeignKey('user.id', ondelete='CASCADE', onupdate='CASCADE'), primary_key=True),
    db.Column('id_blog', db.Integer, db.ForeignKey('blog.id', ondelete='CASCADE', onupdate='CASCADE'), primary_key=True)
)
class User(db.Model): 
    """用户模型
    """
    __tablename__ = "user" # 表名
    # 字段
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(128), unique=True, nullable=False)
    password = db.Column(db.String(320), nullable=False)
    is_admin = db.Column(db.Boolean, nullable=True, default=False)
    datetime_create = db.Column(db.DateTime, nullable=False, default=datetime.utcnow )
    # 关系引用：正向引用与反向引用
    # 一对多关系
    blogs = db.relationship('Blog', backref=db.backref('blog_user'), lazy=True)
    # 多对多关系引用
    stars = db.relationship('Blog', secondary=star, backref=db.backref('star_user'), lazy='select')

    def __repr__(self):
        return '<User %r>' % self.name

class Blog(db.Model):
    """博客模型
    """
    __tablename__ = "blog"
    # 字段
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(128), nullable=False)
    intro = db.Column(db.String(200), nullable=True)
    # 外键
    id_user = db.Column(db.Integer, db.ForeignKey('user.id', ondelete='CASCADE', onupdate='CASCADE'), nullable=False)
    # 关系引用：正向引用与反向引用
    # 一对一关系引用
    author = db.relationship("User", backref=db.backref('user_blog'), viewonly=True)

    def __repr__(self):
        return f'<Blog {self.title}>'

# 扩展
# 当同一个表有多个外键时，建立关系引用需设置foreign_keys
# messages = db.relationship('Message', backref=db.backref('message_user'), lazy='select', foreign_keys='[Message.id_receive]')
# 当多个外键指向同一表下同一字段时，建立关系引用需设置primaryjoin、secondaryjoin
# follows = db.relationship('User', secondary=follow, backref=db.backref('follow_user'), lazy='select', primaryjoin = (follow.c.id_user == id),secondaryjoin = (follow.c.id_other_user == id))
# 当建立关系引用，报出指向不清时，可考虑设置viewonly
# blogs = db.relationship('Blog', secondary=blogtag, backref=db.backref('blog_tag'), lazy='select',viewonly=True)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

1. 增添记录

# 方法1（仅在定义了关系引用时可用）
# user = User()
# blog = Blog()
user.blogs.append(blog)
db.session.add(user)
db.session.commit()
# 方法2（利用SQL语句）
# 1.定义SQL语句
sql = """
INSERT INTO star (id_user, id_blog)
VALUES (:id_user1, :id_blog1), (:id_user1, :id_blog2)
"""
# 2.执行SQL语句，传递参数
db.session.execute(sql, {
    'id_user1': 1,
    'id_blog1': 1,
    'id_blog2': 2
})
# 提交更改到数据库
db.session.commit()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

2. 删除记录

user.stars.remove(blog)
db.session.commit()
1
2

3. 查询记录

# 查询指定用户收藏的所有博客
user_stars = user.stars
for blog in user_stars:
    print(blog.title)

# 查询指定博客被哪些用户收藏
blog_stars = blog.star_user
for user in blog_stars:
    print(user.name)

# 在查询条件中使用 in 时，需要使用 Blog.tags.any()
# 获取携带了该标签的项目
    pagination = Blog.query.filter(
        Blog.is_release==True,
        Blog.tags.any(Tag.id == tag.id)
    ).paginate(page=page, per_page=4, error_out=False)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

四、参考资料

flask-sqlalchemy官方文档
flask插件系列之SQLAlchemy基础使用
Flask-SQLAlchemy详解
Flask-SQLAlchemy
ChatGPT