SQLAlchemy - 数据库的连接、创建会话与模型

SQLAlchemy 是一个强大的 Python 库，它让你可以用一种面向对象的方式来操作数据库（ORM 技术）。

在学习 SQLAlchemy 的过程中，需要一些基础知识的沉淀：Python基础、Python面向对象、MySQL数据库的诸多知识点……

在此之前，你可能需要了解传统执行SQL语句和使用ORM的一些区别以及他们的概念。

什么是传统执行 SQL？

传统执行 SQL 是指直接使用 SQL 语句与数据库进行交互。这通常包括连接数据库、编写 SQL 查询、执行查询以及处理结果。

特点

1. 直接编写 SQL 语句：你需要手动编写 SQL 查询来操作数据库。
2. 灵活性高：可以使用所有的 SQL 功能，精确控制查询和操作。
3. 低级别控制：你需要管理数据库连接、事务处理等。

示例

import pymysql

# 连接数据库
connection = pymysql.connect(
    host='localhost',
    user='root',
    password='0908',
    database='db_flask_demo_school',
    charset='utf8mb4'
)

try:
    with connection.cursor() as cursor:
        # 执行 SQL 查询
        sql = "SELECT * FROM tb_student"
        cursor.execute(sql)
        result = cursor.fetchall()
        for row in result:
            print(row)
finally:
    connection.close()

什么是 ORM（对象关系映射）？

ORM 是一种通过面向对象的方式来操作数据库的技术。ORM 将数据库表映射为类，将表中的记录映射为类的实例，使得你可以用面向对象的方式来进行数据库操作。

特点

1. 面向对象：使用类和对象来表示数据库表和记录。
2. 自动生成 SQL：ORM 框架会根据你的操作自动生成相应的 SQL 语句。
3. 简化代码：简化了数据库操作的代码，使得代码更易读、更易维护。

示例

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

# 创建数据库引擎
engine = create_engine('mysql+pymysql://root:0908@localhost:3306/db_flask_demo_school?charset=utf8mb4')
Base = declarative_base()

# 定义模型
class Student(Base):
    __tablename__ = 'tb_student'
    id = Column(Integer, primary_key=True)
    name = Column(String(20))
    sex = Column(Boolean, default=True)
    age = Column(Integer)

# 创建会话
SessionLocal = sessionmaker(bind=engine)
session = SessionLocal()

# 查询数据
students = session.query(Student).all()
for student in students:
    print(student.name)

比较

特性	传统执行 SQL	ORM
编写 SQL 语句	手动编写	自动生成
操作方式	直接操作数据库	面向对象操作
灵活性	高，完全控制 SQL 语句	中等，受限于 ORM 框架
代码简洁性	代码较冗长	代码简洁易读
学习曲线	需要掌握 SQL 语法	需要学习 ORM 框架的用法
性能	可能更高效，具体取决于 SQL 语句	可能稍慢，但差异通常不明显
维护性	低，SQL 语句分散在代码中	高，结构清晰，易于维护

适用场景

• 传统执行 SQL：适用于需要精细控制 SQL 查询的场景，或者需要使用复杂的 SQL 特性的场景。
• ORM：适用于需要快速开发、代码可读性和维护性要求高的场景。ORM 可以大大简化常见的数据库操作。

接下来进入 SQLAlchemy 的快速学习

1. 创建数据库驱动引擎

首先，我们需要创建一个数据库驱动引擎。当你连接数据库时，需要提前创建这个数据库。

CREATE DATABASE db_flask_demo_school CHARSET=utf8mb4

这个引擎就是你和数据库之间的桥梁。

from sqlalchemy import create_engine

engine = create_engine(
    # url = '驱动://账户:密码@地址:端口/数据库名?charset=编码'
    url='mysql+pymysql://root:0908@localhost:3306/db_flask_demo_school?charset=utf8mb4',
    echo=True,  # 在控制台输出SQL语句，方便调试
    pool_size=8,  # 连接池的数据库连接数量
    max_overflow=30,  # 连接池的数据库连接最大数量
    pool_recycle=60 * 30,  # 设置秒数限制数据库多久没连接自动断开
)

2. 创建数据库会话

有了引擎之后，我们需要创建一个会话，这样才能和数据库进行交互。会话就像是你和数据库之间的对话窗口。

from sqlalchemy.orm import sessionmaker

SessionLocal = sessionmaker(bind=engine)  # 工厂函数，创建新的会话类
session = SessionLocal()  # 实例化。用于与数据库进行交互

另一种创建会话的方式

from sqlalchemy.orm import Session

# 目前官方文档上的案例使用的这种
# 大多数机构教学或者旧的学习资料中可能是上述方式
session = Session(bind=engine)

3. 定义模型基类

在 SQLAlchemy 中，模型是与数据库表对应的类。我们需要定义一个基类，所有的模型都将继承这个基类。

from sqlalchemy.orm import declarative_base

Model = declarative_base()

另一种创建基类的方式

from sqlalchemy.orm import DeclarativeBase

class Model(DeclarativeBase):
    pass

该方式也是来源于目前官方文档的示例。

4. 创建模型

现在我们基于上方的模型基类，创建一个学生模型，这个模型对应数据库中的 tb_student 表。

每个模型类对应的其实就是数据库中的表，其中表名、字段都对应了类属性的设置，而每个类的实例对象也就是一条记录。

import db
import datetime

class Student(db.Model):
    __tablename__ = 'tb_student'  #  数据表的名字
    # db.Column 是一个字段对象，对应表字段，接受很多很多的参数（没啥好记的，浪费大脑为数不多的空间，参见末尾的表），常用字段属性是字段类型、是否为主键、备注描述、默认值指定等等
    # 整型 + 主键 + 告知这是学生编号（默认自增）
    id = db.Column(db.Integer, primary_key=True, comment='学生编号')
    # 20位字符串 + 告知这是学生姓名
    name = db.Column(db.String(20), comment='学生姓名')
    # 布尔型（实际上存储是0或1），默认是 True（存储是1）
    sex = db.Column(db.Boolean, default=True, comment='学生性别')
    # 精度更小的整型
    age = db.Column(db.SmallInteger, comment='学生年龄')
    # 'class' 这里不是类型，而是别名，因为 class = xxx，在python中是不行的（关键字）
    class_ = db.Column('class', db.SMALLINT, comment='学生班级')
    # 文本类型
    description = db.Column(db.Text, comment='个性签名')
    # 布尔型，默认值也可以写成 1
    status = db.Column(db.Boolean, default=1, comment='登录状态')
    # 日期时间类型，默认值需要特别注意，引用了 datetime 库，使用的是 now函数的地址，而不是它的调用结果。如果使用调用结果，将以项目执行启动的时间为准
    addtime = db.Column(db.DateTime, default=datetime.datetime.now, comment='入学时间')
    orders = db.Column(db.SMALLINT, default=1, comment='学生排序')

    def __repr__(self):
        # 便于打印列表时，显示 [ <Student: 老王(12)>,...]
        return f'<{self.__class__.__name__}: {self.name}({self.id})>'

    def to_dict(self):
        # 便于对象直接转成字符串，实现方式很多，比如 __dict__ 拷贝一份，然后筛选非'_'字符开头的键也行。
        return {
            'id': self.id,
            'name': self.name,
            'sex': self.sex,
            'age': self.age,
            'class': self.class_,
            'description': self.description,
            'status': self.status,
            'addtime': self.addtime.strftime('%Y-%m-%d %H:%M:%S'),  # 需要注意获取到的是 DateTime 对象，参见 datetime 库
            'orders': self.orders,
        }

if __name__ == '__main__':
    # 建表操作，如果如果你所在的公司或者团队或者你自身负责数据库的设计（DBA），那么建表无需执行 create_all
    # 当然，也有一些坑13，如果没有为你建表甚至没有设计，emmmm……，那就自己来吧！
    db.Model.metadata.create_all(db.engine)

附表：常用 Column 字段

下面是一个详细的 SQLAlchemy Column 类型及其参数的表格，包括字段的用途、存储到数据库的类型、Python 中表示的数据类型等信息。

字段类型	参数示例	用途	数据库类型	Python 类型
Integer	Column(Integer, primary_key=True)	存储整数	INTEGER	int
String	Column(String(50), nullable=False)	存储字符串	VARCHAR	str
Text	Column(Text)	存储大文本数据	TEXT	str
Boolean	Column(Boolean, default=True)	存储布尔值	BOOLEAN	bool
DateTime	Column(DateTime, default=datetime.datetime.now)	存储日期和时间	DATETIME	datetime.datetime
Float	Column(Float)	存储浮点数	FLOAT	float
SmallInteger	Column(SmallInteger)	存储小范围整数	SMALLINT	int
LargeBinary	Column(LargeBinary)	存储二进制数据	BLOB	bytes
Numeric	Column(Numeric(10, 2))	存储精确的小数	NUMERIC	decimal.Decimal
Date	Column(Date)	存储日期	DATE	datetime.date
Time	Column(Time)	存储时间	TIME	datetime.time
Enum	Column(Enum('value1', 'value2'))	存储枚举值	ENUM	enum.Enum
Interval	Column(Interval)	存储时间间隔	INTERVAL	datetime.timedelta
JSON	Column(JSON)	存储 JSON 数据	JSON	dict
UUID	Column(UUID(as_uuid=True))	存储 UUID	UUID	uuid.UUID
ARRAY	Column(ARRAY(String))	存储数组	ARRAY	list
JSONB	Column(JSONB)	存储 JSONB 数据（PostgreSQL）	JSONB	dict
HSTORE	Column(HSTORE)	存储键值对（PostgreSQL）	HSTORE	dict
INET	Column(INET)	存储 IP 地址（PostgreSQL）	INET	str
CIDR	Column(CIDR)	存储 IP 地址范围（PostgreSQL）	CIDR	str
MACADDR	Column(MACADDR)	存储 MAC 地址（PostgreSQL）	MACADDR	str

常用 Column 参数

• primary_key：是否为主键。
  • 类型：bool
  • 默认值：False
  • 示例：Column(Integer, primary_key=True)
• nullable：是否允许为空。
  • 类型：bool
  • 默认值：True
  • 示例：Column(String, nullable=False)
• default：默认值。
  • 类型：any
  • 默认值：None
  • 示例：Column(Boolean, default=True)
• unique：是否唯一。
  • 类型：bool
  • 默认值：False
  • 示例：Column(String, unique=True)
• index：是否创建索引。
  • 类型：bool
  • 默认值：False
  • 示例：Column(String, index=True)
• comment：字段注释。
  • 类型：str
  • 默认值：None
  • 示例：Column(String, comment='用户名')
• autoincrement：是否自动递增（通常用于主键）。
  • 类型：bool 或 str（好像是auto表示自增吧，忘记了，可查文档）
  • 默认值：True（在主键列上）
  • 示例：Column(Integer, primary_key=True, autoincrement=True)
• server_default：数据库服务器端的默认值。
  • 类型：DefaultClause 或 str
  • 默认值：None
  • 示例：Column(String, server_default='default_value')
• server_onupdate：数据库服务器端的更新值。
  • 类型：DefaultClause 或 str
  • 默认值：None
  • 示例：Column(DateTime, server_onupdate=func.now())
• onupdate：更新时的默认值。
  • 类型：any
  • 默认值：None
  • 示例：Column(DateTime, onupdate=datetime.datetime.now)
• foreign_key：外键约束。
  • 类型：ForeignKey
  • 默认值：None
  • 示例：Column(Integer, ForeignKey('other_table.id'))