【Flask 系统教程 7】数据库使用 SQLAlchemy

SQLAlchemy 是一个功能强大的 SQL 工具包和对象关系映射 (ORM) 库。使用 Flask 和 SQLAlchemy 可以方便地连接和操作数据库。

SQLAlchemy基本操作

连接数据库

环境准备

首先，确保你已经安装了SQLAlchemy 。可以使用以下命令安装这些库：

pip install sqlalchemy 

from sqlalchemy import create_engine, text

# 设置数据库配置
HOST = 'localhost'
PORT = 3306
DATABASE = 'flask_db'  # 需要在MySQL中事先创建好flask_db数据库
USER = 'root'
PASSWORD = 'password'

SQLALCHEMY_DATABASE_URI = f'mysql+pymysql://{USER}:{PASSWORD}@{HOST}:{PORT}/{DATABASE}'

engine = create_engine(url=SQLALCHEMY_DATABASE_URI)
conn = engine.connect()  # 创建连接对象

sql = text('SELECT 1;')  # 模拟查询
result = conn.execute(sql)
print(result.fetchone())

conn.close()  # 关闭连接

在以上案例中，实现了使用SQLAlchemy连接本地MySQL数据库并执行原生SQL语句。

通过类映射表结构

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

# 设置数据库配置
HOST = 'localhost'
PORT = 3306
DATABASE = 'flask_db'  # 需要在MySQL中事先创建好flask_db数据库
USER = 'root'
PASSWORD = 'password'

# 构建数据库连接URI
SQLALCHEMY_DATABASE_URI = f'mysql+pymysql://{USER}:{PASSWORD}@{HOST}:{PORT}/{DATABASE}'

# 创建数据库引擎
engine = create_engine(SQLALCHEMY_DATABASE_URI)

# 创建基类
Base = declarative_base()


# 定义模型类
class User(Base):
    __tablename__ = 'users'  # 设置表名

    id = Column(Integer, primary_key=True)
    name = Column(String(50))  # 设置name字段 指定长度为50
    age = Column(Integer)


# 使用模型类来创建表
Base.metadata.create_all(engine)

这段代码使用 SQLAlchemy 通过模型类创建了一个名为 “users” 的表，该表包含 id、name 和 age 列，并将其映射到了 MySQL 数据库中。

需要注意的是如果已经创建过来过了，则无法再次通过Base.metadata.create_all(engine)创建同名表

映射表常用数据类型

字段类型	描述
Integer	整数类型
String(length)	字符串类型，必须指定长度
Text	可变长度的文本类型，通常用于大文本字段
Float	浮点数类型
Boolean	布尔值类型
Date	日期类型（年-月-日）
Time	时间类型（时-分-秒）
DateTime	日期和时间类型
LargeBinary	二进制数据类型，通常用于存储文件或图片
Enum	枚举类型，用于存储一组有限的字符串或整数值
Numeric	精确数字类型，通常用于货币和需要高精度的数值
Interval	时间间隔类型
JSON	JSON 类型，用于存储 JSON 数据
PickleType	使用 Python pickle 序列化的任意 Python 对象

示例：

from sqlalchemy import create_engine, Column, Integer, String, Text, Float, Boolean, Date, Time, DateTime, LargeBinary, Enum, Numeric, Interval, JSON
from sqlalchemy.orm import declarative_base, sessionmaker
import enum
from datetime import datetime, date, time, timedelta

# 设置数据库配置
HOST = 'localhost'
PORT = 3306
DATABASE = 'flask_db'  # 需要在MySQL中事先创建好flask_db数据库
USER = 'root'
PASSWORD = 'password'

# 构建数据库连接URI
SQLALCHEMY_DATABASE_URI = f'mysql+pymysql://{USER}:{PASSWORD}@{HOST}:{PORT}/{DATABASE}'

# 创建数据库引擎
engine = create_engine(SQLALCHEMY_DATABASE_URI)

# 创建基类
Base = declarative_base()

# 定义枚举类型
class Status(enum.Enum):
    ACTIVE = 'active'
    INACTIVE = 'inactive'

# 定义模型类
class Example(Base):
    __tablename__ = 'example'

    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    description = Column(Text)
    price = Column(Float)
    is_active = Column(Boolean)
    created_date = Column(Date)
    created_time = Column(Time)
    created_datetime = Column(DateTime)
    data = Column(LargeBinary)
    status = Column(Enum(Status))
    precision_number = Column(Numeric(10, 2))
    duration = Column(Interval)
    json_data = Column(JSON)

# 使用模型类来创建表
Base.metadata.create_all(engine)

# 创建会话工厂
Session = sessionmaker(bind=engine)
session = Session()

# 插入示例数据
example = Example(
    name='Example Name',
    description='This is a text description.',
    price=19.99,
    is_active=True,
    created_date=date.today(),
    created_time=datetime.now().time(),
    created_datetime=datetime.now(),
    data=b'binary data',
    status=Status.ACTIVE,
    precision_number=1234.56,
    duration=timedelta(days=1, hours=2),
    json_data={'key': 'value'}
)

with Session() as session:
    session.add(example)
    session.commit()

# 查询示例数据
with Session() as session:
    example = session.query(Example).first()
    print(example.name, example.description, example.price, example.is_active)

这个示例代码展示了如何在 SQLAlchemy 模型类中使用各种常见的字段类型，并插入和查询数据。

column常用参数

好的，以下是 SQLAlchemy 中 Column 的常见参数及其描述的表格形式：

参数名	描述
name	列的名称，如果未提供则使用作为关键字参数传递的属性名
type	列的数据类型，例如 Integer、String、Boolean 等
primary_key	是否为主键，默认值为 False
nullable	是否允许 NULL 值，默认值为 True
default	列的默认值，可以是一个值或一个函数
unique	是否在该列上创建唯一索引，默认值为 False
index	是否在该列上创建常规索引，默认值为 False
server_default	在服务器端设置的默认值，例如 func.now() 用于时间戳
server_onupdate	在服务器端更新时的默认值，例如 func.now() 用于时间戳更新
autoincrement	是否自动递增，只对 Integer 类型有效，默认值为 auto（如果是主键则自动递增）
comment	列的注释，可以是任意字符串
onupdate	在行更新时自动应用的值或函数
foreign_key	外键约束，引用另一个表的列
primaryjoin	使用复杂的条件来定义外键关系
doc	列的文档字符串，可以用于生成自动文档
info	任意元数据字典，可以用于存储额外的信息

下面是一个示例，展示如何在 SQLAlchemy 模型类中使用这些参数：

from sqlalchemy import create_engine, Column, Integer, String, Boolean, DateTime, func
from sqlalchemy.orm import declarative_base, sessionmaker
# 设置数据库配置
HOST = 'localhost'
PORT = 3306
DATABASE = 'flask_db'  # 需要在MySQL中事先创建好flask_db数据库
USER = 'root'
PASSWORD = 'password'

# 构建数据库连接URI
SQLALCHEMY_DATABASE_URI = f'mysql+pymysql://{USER}:{PASSWORD}@{HOST}:{PORT}/{DATABASE}'

# 创建数据库引擎
engine = create_engine(SQLALCHEMY_DATABASE_URI)

# 创建基类
Base = declarative_base()


# 定义模型类
class User(Base):
    __tablename__ = 'worker'
    # 主键 自增 注释
    id = Column(Integer, primary_key=True, autoincrement=True, comment="User ID")
    # 不可为空 不可重复 设为索引 注释
    username = Column(String(50), nullable=False, unique=True, index=True, comment="Username")
    # 不可为空 不可重复 注释
    email = Column(String(100), nullable=False, unique=True, comment="Email Address")
    # 默认为true 注释
    is_active = Column(Boolean, default=True, comment="Is Active")
    # 默认为现在时间 注释
    created_at = Column(DateTime, default=func.now(), comment="Created At")
    # 默认为现在时间 更新时间设置为当前时间 注释
    updated_at = Column(DateTime, default=func.now(), onupdate=func.now(), comment="Updated At")


# 使用模型类来创建表
Base.metadata.create_all(engine)

# 创建会话工厂
Session = sessionmaker(bind=engine)
session = Session()

# 插入示例数据
new_user = User(username='johndoe', email='johndoe@example.com')

with Session() as session:
    session.add(new_user)
    session.commit()

# 查询示例数据
with Session() as session:
    user = session.query(User).filter_by(username='johndoe').first()
    print(user.username, user.email, user.is_active, user.created_at, user.updated_at)

这个示例展示了如何使用 Column 的各种参数来定义 SQLAlchemy 模型中的字段，包括设置默认值、唯一约束、索引、注释等。

增删改查CURD

综合常用

创建一个 SQLAlchemy 的 session 对象，并使用它执行增、删、改、查操作：

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, sessionmaker

# 设置数据库配置
HOST = 'localhost'
PORT = 3306
DATABASE = 'flask_db'  # 需要在MySQL中事先创建好flask_db数据库
USER = 'root'
PASSWORD = 'password'

# 构建数据库连接URI
SQLALCHEMY_DATABASE_URI = f'mysql+pymysql://{USER}:{PASSWORD}@{HOST}:{PORT}/{DATABASE}'

# 创建数据库引擎
engine = create_engine(SQLALCHEMY_DATABASE_URI)

# 创建基类
Base = declarative_base()


# 定义模型类
class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    age = Column(Integer)


# 使用模型类来创建表
Base.metadata.create_all(engine)

# 创建会话工厂
Session = sessionmaker(bind=engine)


def add_user(name, age):
    with Session() as session:
        new_user = User(name=name, age=age)
        session.add(new_user)
        session.commit()


def get_user_by_name(name):
    with Session() as session:
        user = session.query(User).filter_by(name=name).first()
  		# user = session.query(User).filter(User.name == name).first() 这种用法等价

        return user


def add_user_many(name_age_list):
    with Session() as session:
        users = [User(name=name, age=age) for name, age in name_age_list]
        session.add_all(users)
        session.commit()


def get_all_users():
    with Session() as session:
        users = session.query(User).all()
        return users


def update_user(name, new_age):
    with Session() as session:
        user_to_update = session.query(User).filter_by(name=name).first()
        if user_to_update:
            user_to_update.age = new_age
            session.commit()


def delete_user(name):
    with Session() as session:
        user_to_delete = session.query(User).filter_by(name=name).first()
        # 实际上是先查找再删除对象
        if user_to_delete:
            session.delete(user_to_delete)
            session.commit()


# 测试函数
if __name__ == '__main__':
    # 增加单个用户
    add_user('Alice', 30)

    # 增加多个用户
    add_user_many([('Bob', 25), ('Charlie', 35)])

    # 查询所有用户并打印结果
    print("======查询所有用户并打印结果======")
    users = get_all_users()
    for user in users:
        print(user.id, user.name, user.age)

    # 通过姓名查询用户
    print("======通过姓名查询用户======")
    user = get_user_by_name('Alice')
    if user:
        print(f"Found user: id:{user.id}, name:{user.name}, age:{user.age}")

    # 修改用户信息
    update_user('Alice', 31)

    # 查询更新后的用户并打印结果
    print("======查询更新后的用户并打印结果======")
    users = get_all_users()
    for user in users:
        print(user.id, user.name, user.age)

    # 删除用户
    delete_user('Alice')

    # 查询所有用户以确认删除操作
    print("======查询所有用户以确认删除操作======")
    users = get_all_users()
    if not any(user.name == 'Alice' for user in users):
        print("User 'Alice' has been deleted.")

以上代码提供了一些增删改查函数作为案例，通过这些案例的学习相信大家能够了解session的使用方法，并在此基础上改进应用。

query进阶用法

from sqlalchemy import create_engine, Column, Integer, String, Boolean, DateTime, func, ForeignKey, Text
from sqlalchemy.orm import declarative_base, sessionmaker

# 设置数据库配置
HOST = 'localhost'
PORT = 3306
DATABASE = 'flask_db'  # 需要在MySQL中事先创建好flask_db数据库
USER = 'root'
PASSWORD = 'password'

# 构建数据库连接URI
SQLALCHEMY_DATABASE_URI = f'mysql+pymysql://{USER}:{PASSWORD}@{HOST}:{PORT}/{DATABASE}'

# 创建数据库引擎
engine = create_engine(SQLALCHEMY_DATABASE_URI)

# 创建基类
Base = declarative_base()


# 定义模型类
class Teacher(Base):
    __tablename__ = 'teachers'

    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50), nullable=False, unique=True, index=True)
    email = Column(String(100), nullable=False, unique=True)
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=func.now())
    updated_at = Column(DateTime, default=func.now(), onupdate=func.now())


# 使用模型类来创建表
Base.metadata.create_all(engine)

# 创建会话工厂
Session = sessionmaker(bind=engine)
session = Session()


# 插入示例数据
def add_sample_data():
    teacher1 = Teacher(username='johndoe', email='johndoe@example.com')
    teacher2 = Teacher(username='janedoe', email='janedoe@example.com', is_active=False)
    teacher3 = Teacher(username='alice', email='alice@example.com')
    with Session() as session:
        session.add_all([teacher1, teacher2, teacher3])
        session.commit()


add_sample_data()

# 基本查询：查询所有教师
with Session() as session:
    teachers = session.query(Teacher).all()
    for teacher in teachers:
        print(teacher.username, teacher.email)

# 过滤查询：查询活跃教师
with Session() as session:
    active_teachers = session.query(Teacher).filter(Teacher.is_active == True).all()
    for teacher in active_teachers:
        print(teacher.username, teacher.email, teacher.is_active)

# 排序查询：按创建时间排序
with Session() as session:
    sorted_teachers = session.query(Teacher).order_by(Teacher.created_at).all()
    for teacher in sorted_teachers:
        print(teacher.username, teacher.created_at)


# 升序排序
from sqlalchemy import asc

with Session() as session:
    sorted_teachers_asc = session.query(Teacher).order_by(Teacher.created_at.asc()).all()
    for teacher in sorted_teachers_asc:
        print(teacher.username, teacher.created_at)


from sqlalchemy import desc

with Session() as session:
    sorted_teachers_desc = session.query(Teacher).order_by(Teacher.created_at.desc()).all()
    for teacher in sorted_teachers_desc:
        print(teacher.username, teacher.created_at)


# 限制查询：限制返回的记录数
with Session() as session:
    limited_teachers = session.query(Teacher).limit(2).all()
    for teacher in limited_teachers:
        print(teacher.username, teacher.email)

# 聚合查询：统计教师总数
with Session() as session:
    teacher_count = session.query(func.count(Teacher.id)).scalar()
    print(f'Total teachers: {teacher_count}')


# 连接查询：假设有另一个模型 Course，查询教师及其课程数量
class Course(Base):
    __tablename__ = 'courses'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(100))
    content = Column(Text)
    teacher_id = Column(Integer, ForeignKey('teachers.id'))


Base.metadata.create_all(engine)


def add_courses():
    with Session() as session:
        teacher = session.query(Teacher).filter_by(username='johndoe').first()
        course1 = Course(title='Math 101', content='This is the first course', teacher_id=teacher.id)
        course2 = Course(title='Physics 101', content='This is the second course', teacher_id=teacher.id)
        session.add_all([course1, course2])
        session.commit()


add_courses()

with Session() as session:
    teacher_courses = session.query(Teacher.username, func.count(Course.id).label('course_count')).join(Course, Teacher.id == Course.teacher_id).group_by(Teacher.username).all()
    for username, course_count in teacher_courses:
        print(username, course_count)

# 返回指定字段：仅返回用户名和邮箱
with Session() as session:
    teacher_details = session.query(Teacher.username, Teacher.email).all()
    for username, email in teacher_details:
        print(username, email)

# 子查询：查询有课程的教师
with Session() as session:
    subquery = session.query(Course.teacher_id).distinct().subquery()
    teachers_with_courses = session.query(Teacher).filter(Teacher.id.in_(subquery)).all()
    for teacher in teachers_with_courses:
        print(teacher.username, teacher.email)

主要方法说明

• query(Model)：查询指定模型的所有记录。
• filter(condition)：根据给定条件过滤结果。
• filter_by(**kwargs)：根据关键字参数过滤结果。
• order_by(*columns)：对结果进行排序。
• limit(n)：限制返回的结果数量。
• offset(n)：跳过指定数量的结果。
• count()：统计记录数量。
• sum(column)求和、avg(column)平均值、min(column)最小值、max(column)最大值：对字段进行聚合计算。
• join(target, onclause)：执行 SQL JOIN 操作。
• with_entities(*entities)：仅返回指定的字段。
• subquery()：构建子查询。

filter函数用法

filter函数是SQLAlchemy查询中常用的方法之一，用于构建 子句来筛选查询结果。以下是filter函数的一些常见用法：

1. 等于（Equal）

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)

# 创建数据库连接和会话
engine = create_engine('sqlite:///example.db')
Session = sessionmaker(bind=engine)
session = Session()

# 查询name等于'John'的记录
result = session.query(User).filter(User.name == 'John').all()

2. 不等于（Not Equal）

# 查询name不等于'John'的记录
result = session.query(User).filter(User.name != 'John').all()

3. 模糊匹配（Like）

# 查询name以'Jo'开头的记录
result = session.query(User).filter(User.name.like('Jo%')).all()

4. 模糊匹配（不区分大小写）（Ilike）

# 查询name以'jo'开头（不区分大小写）的记录
result = session.query(User).filter(User.name.ilike('jo%')).all()

5. 包含（In）

# 查询name在给定列表中的记录
names = ['John', 'Jane', 'Doe']
result = session.query(User).filter(User.name.in_(names)).all()

6. 不包含（Not In）

# 查询name不在给定列表中的记录
names = ['John', 'Jane', 'Doe']
result = session.query(User).filter(~User.name.in_(names)).all()

7. IS NULL

# 查询name为NULL的记录
result = session.query(User).filter(User.name == None).all()

8. IS NOT NULL

# 查询name不为NULL的记录
result = session.query(User).filter(User.name != None).all()

9. AND查询

from sqlalchemy import and_

# 查询name为'John'且id大于5的记录
result = session.query(User).filter(and_(User.name == 'John', User.id > 5)).all()

10. OR查询

from sqlalchemy import or_

# 查询name为'John'或id大于5的记录
result = session.query(User).filter(or_(User.name == 'John', User.id > 5)).all()

这些是SQLAlchemy中使用filter函数的一些常见方法。通过组合使用这些条件，您可以构建出复杂的查询来满足各种需求。

表关系

外键的使用

在SQLAlchemy中，外键（Foreign Key）用于建立表之间的关系。定义外键时，可以指定不同的删除策略（如RESTRICT、NO ACTION、CASCADE、SET NULL），以控制当父表中的数据被删除时，子表中的数据应该如何处理。

基本用法

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Parent(Base):
    __tablename__ = 'parents'
    id = Column(Integer, primary_key=True)
    name = Column(String)

class Child(Base):
    __tablename__ = 'children'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    parent_id = Column(Integer, ForeignKey('parents.id', ondelete='CASCADE'))

# 创建数据库连接和会话
engine = create_engine('sqlite:///example.db')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

删除策略

RESTRICT
当父表中的数据被删除时，如果子表中有对应的关联数据，则删除操作会被阻止。RESTRICT是默认的删除策略。

parent_id = Column(Integer, ForeignKey('parents.id', ondelete='RESTRICT'))

NO ACTION
在MySQL中，NO ACTION与RESTRICT相同，当父表中的数据被删除时，若子表中有对应的关联数据，则删除操作会被阻止。

parent_id = Column(Integer, ForeignKey('parents.id', ondelete='NO ACTION'))

CASCADE
当父表中的数据被删除时，子表中所有对应的关联数据也会被删除。

parent_id = Column(Integer, ForeignKey('parents.id', ondelete='CASCADE'))

SET NULL
当父表中的数据被删除时，子表中所有对应的关联数据项会被设置为NULL。

parent_id = Column(Integer, ForeignKey('parents.id', ondelete='SET NULL'))

Flask-sqlalchemy的使用

下面以案例的形式讲解。

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)

    def __repr__(self):
        return f'<User {self.username}>'

with app.app_context():
    db.create_all()

@app.route('/add_user/<username>')
def add_user(username):
    new_user = User(username=username)
    db.session.add(new_user)
    db.session.commit()
    return f'User {username} added.'

@app.route('/users')
def get_users():
    users = User.query.all()
    return '<br>'.join([user.username for user in users])

if __name__ == '__main__':
    app.run(debug=True)

和常规的sqlalchemy不同的是

• Flask-sqlalchemy不需要手动创建SQLAlchemy引擎和会话。
• Flask-sqlalchemy不需要手动绑定和管理Flask的应用上下文。
• Flask-sqlalchemy不需要手动管理会话的创建和销毁。

下面是sqlalchemy的常规使用

from flask import Flask
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session

app = Flask(__name__)

# 配置数据库连接
DATABASE_URI = 'sqlite:///test.db'
engine = create_engine(DATABASE_URI)

# 创建会话
Session = scoped_session(sessionmaker(bind=engine))

# 基类
Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    username = Column(String(80), unique=True, nullable=False)

    def __repr__(self):
        return f'<User {self.username}>'

with app.app_context():
    Base.metadata.create_all(engine)

@app.route('/add_user/<username>')
def add_user(username):
    session = Session()
    try:
        new_user = User(username=username)
        session.add(new_user)
        session.commit()
        return f'User {username} added.'
    except Exception as e:
        session.rollback()
        return str(e)
    finally:
        session.close()

@app.route('/users')
def get_users():
    session = Session()
    try:
        users = session.query(User).all()
        return '<br>'.join([user.username for user in users])
    finally:
        session.close()

if __name__ == '__main__':
    app.run(debug=True)