3个Python SQLAlchemy数据库操作功能详解

Python 是一种多功能且富有表现力的编程语言，由于其简单性和可读性，已成为软件开发的强大动力。本文学习 Python 数据库的处理库。

Python 是一个强大且多功能的 Python SQL 工具包和对象关系映射 (ORM) 系统，提供了一整套众所周知的企业级持久性模式，专为高效和高性能的数据库访问而设计。它可以处理从小型简单查询到高负载场景中复杂事务的所有事务。它为开发人员提供了一个高级的 Pythonic 接口来与关系数据库交互，允许使用 Python 类和对象来处理数据库，而不是编写复杂的 SQL 查询，并以这种方式抽象代码，删除所有支持的数据库脚本语句，例如 PostgreSQL、MySQL、SQLite 和 Oracle 等等。许多流行的 Python 框架都使用 SQLAlchemy，例如 Django、Flask 和 Pyramid。

本文一起来学习库的三个有用功能，在大部分的项目开发中都可能用得上的功能。

下面先来了解一些基础知识。

基础知识

从这里开始介绍一些基本的知识，对于大多数人来说基本都是熟悉的知识。

数据模型

为示例定义的基本用户模型如下：

from pydantic import BaseModel
from typing import Optional
class UserSignUp(BaseModel):
    name: str
    surname: Optional[str] = None
    birth_year: Optional[int] = None
    notes: Optional[str] = None

在上面的代码片段中，定义了一个 Pydantic 模型，它是想要在数据库中保存记录的数据的模型。Pydantic 是 Python 中最新且最有前途的库，用于处理数据验证、结构和建模。如果不熟悉它，其实跟 Mongoose 、Prisma 实现原理相似。

存储 Storage

出于存储目的，将使用最简单的 SQL 数据库系统之一，即 SQLite。为此，需要定义一个引擎来与文件系统中实际的 SQLite 数据库文件进行通信，并创建一个会话对象，以便可以与数据库进行交互以进行操作。

from datetime import datetime
from pydantic import BaseModel
import os
import json
from sqlalchemy import create_engine
from sqlalchemy.orm import declarative_base
from sqlalchemy.sql import func
from sqlalchemy import Column, DateTime, Integer, String
project_dir = os.path.dirname(os.path.abspath(__file__))
engine = create_engine("sqlite:///{}".format(os.path.join(project_dir, "./storage.db")),
                       connect_args={"check_same_thread": False})
Base = declarative_base()
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=True)
    surname = Column(String, nullable=True)
    birth_year = Column(Integer, nullable=True)
    notes = Column(String, nullable=True)
    register_date = Column(DateTime, default=func.now())
    def __repr__(self):
        dict_repr = {c.name: getattr(self, c.name) for c in self.__table__.columns}
        for key, value in dict_repr.items():
            if isinstance(value, datetime):
                dict_repr[key] = datetime.isoformat(value)
        return json.dumps(dict_repr, indent=2)
Base.metadata.create_all(bind=engine)

在上面的代码片段中，项目文件夹路径用于指向数据库路径。数据库名称定义为 storage.db，如果不存在，则会自动创建。

此外，使用 SQLAlchemy ORM 模块的声明函数来构造一个基类，然后由 User 类来使用，User 类表示数据库中保存用户记录的表。

表用户行被定义为包含比我们为用户注册引入的数据模型额外的两个字段。

User 表包含注册引入的数据模型额外的两个字段。

一个自动增量整数，作为已索引并用作表主键的行的 ID。
注册日期字段，它将当前时间戳作为默认参数，这意味着正在数据库中写入的条目的时间戳。

数据库表定义类还包含一个额外的魔术方法，__repr__ 方法在此处定义以实现自定义序列化器。这样做的目的是可以直接打印数据库记录，即数据记录行，因为它们是使用 SQLAlchemy 库从表中获取的。

最后，通过调用模块 create_all 的方法，Base.metadata 在创建的数据库中实例化定义的表结构。因此，列及其数据类型和主键定义现在都存在。

写入数据库表

定义并创建了数据库表，现在来尝试写入一条记录。首先使用之前创建的引擎对象来创建一个到数据库的会话。

from sqlalchemy import create_engine
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import sessionmaker
session_local = sessionmaker(autocommit=False, autoflush=False, bind=engine)
db_session = session_local()

然后，使用声明的数据模型创建一个要注册的新用户。

user_sign_up = UserSignUp(
    name="Quintion",
    surname="Tang",
    notes="一个工程师"
)

最后，使用 Users 的数据库表类创建一个条目，以使用建立的会话写入数据库并提交更改。

try:
    user_to_store = User(
        name=user_sign_up.name,
        surname=user_sign_up.surname,
        birth_year=user_sign_up.birth_year,
        notes=user_sign_up.notes
    )
    db_session.add(user_to_store)
    db_session.commit()
    print(user_to_store)
except IntegrityError as e:
    db_session.rollback()
    print(f"用户注册时出错：{str(e)}")

如果没有因任何完整性错误引发异常，则应写入该记录并将其提交到数据库。

{
  "id": 1,
  "name": "Quintion",
  "surname": "Tang",
  "birth_year": null,
  "notes": "一个工程师",
  "register_date": "2023-09-10T14:29:50"
}

请注意，birth_year 字段为空，因为首先没有为其提供任何整数，并且在写入操作期间会自动为 register_date 生成时间戳。

数据验证和预处理

在实际用例中，在存储之前，首先都需要预先验证或处理数据。

来看看将如何在这里做到这一点。例如，假设想要将每个字符串字段大写。如果需要在数据库中对数据进行标准化，这样的过程将对于后期数据处理分析非常有用。统一字符串字段大写的好处是在后续相关功能（如检索）不必担心区分大小写的问题。

那么在 SQLAlchemy 中是如何使用其 ORM validates 装饰器来完成此操作。

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=True)
    surname = Column(String, nullable=True)
    birth_year = Column(Integer, nullable=True)
    notes = Column(String, nullable=True)
    register_date = Column(DateTime, default=func.now())
    # 这里对数据进行预处理
    @validates('name', 'surname', 'notes')
    def normalize(self, _, value):
        if type(value) == str:
            return value.upper()
    def __repr__(self):
        dict_repr = {c.name: getattr(self, c.name) for c in self.__table__.columns}
        for key, value in dict_repr.items():
            if isinstance(value, datetime):
                dict_repr[key] = datetime.isoformat(value)
        return json.dumps(dict_repr, indent=2)

请注意，验证装饰器接受定义为字符串数据类型列的所有三列的参数 @validates('name', 'surname', 'notes')。当然，这不是强制性的，可以使用任意数量的列并选择不同的操作进行预处理。

函数 normalize 将应用于所有选定的三列，传入数据将通过此功能将要写入数据库的数据进行相应处理以返回所期望的大写字符串。

再次运行的写入操作现在将打印以下内容：

{
  "id": 1,
  "name": "QUINTION",
  "surname": "TANG",
  "birth_year": null,
  "notes": "一个工程师",
  "register_date": "2023-09-10T15:29:50"
}

用于动态查询数据的列属性

在 Python SQLAlchemy 中，column_property 函数用于在 SQLAlchemy 类上创建计算属性或别名属性，这个属性通常源自数据库表中的一列或多列。column_property 函数允许在 SQLAlchemy 模型上定义其他属性，这些属性不直接映射到数据库中的单个列，而是从现有列数据通过一定的计算或派生。

在上面用到的 User 数据表中，通常存储的是具体的年月日，而要获取用户的年龄就需要经过一定的计算。

from sqlalchemy import Column, DateTime, Integer, String
from sqlalchemy.sql import func, extract
from sqlalchemy.orm import column_property, validates
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=True)
    surname = Column(String, nullable=True)
    birth_year = Column(Integer, nullable=True)
    # 计算获取年龄列数据，此数据在数据库中并非真实存在
    age = column_property(extract('year', func.now()) - birth_year)
    notes = Column(String, nullable=True)
    register_date = Column(DateTime, default=func.now())
    @validates('name', 'surname', 'notes')
    def normalize(self, _, value):
        if type(value) == str:
            return value.upper()
    def __repr__(self):
        dict_repr = {c.name: getattr(self, c.name) for c in self.__table__.columns}
        for key, value in dict_repr.items():
            if isinstance(value, datetime):
                dict_repr[key] = datetime.isoformat(value)
        dict_repr['age'] = self.age
        return json.dumps(dict_repr, indent=2)

上面代码定义了新字段 age，它不是列类型，而是包含一个短表达式。使用来自 SQLAlchemy 的 sql 模块的 extract 方法，由 func.now() 派生的时间戳中提取年份。以前见过这个，每当查询表并动态派生时间戳时，该函数就会运行。通过提取当前时间戳的年份字段，减去用户的出生年份，就可以计算出用户的年龄。这个值不会存储在数据表行中的任何位置，但会在查询数据表的时候返回，是动态计算的。

由于字段 age 不是实际的列，因此它的值不会包含在 self.__table__.columns 字典中。当然还有其他的方法来达到这一目的。

下面来看看写入数据库和查询数据的结果。

user_sign_up = UserSignUp(
    name="Quintion",
    surname="Tang",
    notes="一个工程师",
    birth_year=1988
)
try:
    user_to_store = User(
        name=user_sign_up.name,
        surname=user_sign_up.surname,
        birth_year=user_sign_up.birth_year,
        notes=user_sign_up.notes
    )
    db_session.add(user_to_store)
    db_session.commit()
    print(user_to_store)
except IntegrityError as e:
    db_session.rollback()
    print(f"用户注册时出错：{str(e)}")

这次，提供出生年份，以便执行年龄计算。

{
  "id": 1,
  "name": "QUINTION",
  "surname": "TANG",
  "birth_year": 1988,
  "notes": "一个工程师",
  "register_date": "2023-09-10T15:58:31",
  "age": 35
}

现在，字段 age 会动态填充并在每次查询时返回。

主键之上多列的唯一约束

熟悉 SQL 数据库系统的人从一开始就知道每个表都需要包含一个主键。主键是唯一标识表中每条记录（行）的基本概念。它确保指定列中不存在重复值或空值，从而保证数据的完整性和唯一性。

然而，有时，需要对记录条目进行更多限制。在某些场景中，主键并不能满足所有需求。

举一个例子，假设注册用户与现有数据表中的用户具有完全相同的 name 和 surname ，在表主键只是一个增量数字的时候，是允许插入的。为了说明问题，验证相同用户名信息是否是同一个人，可以通过使用字段 name 、 surname 和 birth_year 来标识两个不同的记录，也就是说，如果现有用户的所有的这三个字段都相同，则应拒绝插入，这种规则称为唯一性索引。

下面来看看如何在表中强制执行这样的规则：

import json
from sqlalchemy import UniqueConstraint
from sqlalchemy import Column, DateTime, Integer, String
from sqlalchemy.sql import func, extract
from sqlalchemy.orm import column_property, validates
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=True)
    surname = Column(String, nullable=True)
    birth_year = Column(Integer, nullable=True)
    age = column_property(extract('year', func.now()) - birth_year)
    notes = Column(String, nullable=True)
    register_date = Column(DateTime, default=func.now())
    # 增加多字段唯一性规则
    __table_args__ = (UniqueConstraint('name', 'surname', 'birth_year', name='unique_fullname_per_birth_year'),)
    @validates('name', 'surname', 'notes')
    def normalize(self, _, value):
        if type(value) == str:
            return value.upper()
    def __repr__(self):
        dict_repr = {c.name: getattr(self, c.name) for c in self.__table__.columns}
        for key, value in dict_repr.items():
            if isinstance(value, datetime):
                dict_repr[key] = datetime.isoformat(value)
        dict_repr['age'] = self.age
        return json.dumps(dict_repr, indent=2)

对象 UniqueConstraint 提供的表参数，对象采用不同的列名作为参数，这些列名称形成每个记录的唯一性，还可以为规则提供名称。

这一特性就不再演示其效果了。下面是完整代码：

from datetime import datetime
from pydantic import BaseModel
from typing import Optional
import os
import json
from sqlalchemy import create_engine, UniqueConstraint
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import sessionmaker, declarative_base
from sqlalchemy import Column, DateTime, Integer, String
from sqlalchemy.sql import func, extract
from sqlalchemy.orm import column_property, validates
project_dir = os.path.dirname(os.path.abspath(__file__))
engine = create_engine("sqlite:///{}".format(os.path.join(project_dir, "./storage.db")),
                       connect_args={"check_same_thread": False})
Base = declarative_base()
class UserSignUp(BaseModel):
    name: str
    surname: Optional[str] = None
    birth_year: Optional[int] = None
    notes: Optional[str] = None
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=True)
    surname = Column(String, nullable=True)
    birth_year = Column(Integer, nullable=True)
    age = column_property(extract('year', func.now()) - birth_year)
    notes = Column(String, nullable=True)
    register_date = Column(DateTime, default=func.now())
    __table_args__ = (UniqueConstraint('name', 'surname', 'birth_year', name='unique_fullname_per_birth_year'),)
    @validates('name', 'surname', 'notes')
    def normalize(self, _, value):
        if type(value) == str:
            return value.upper()
    def __repr__(self):
        dict_repr = {c.name: getattr(self, c.name) for c in self.__table__.columns}
        for key, value in dict_repr.items():
            if isinstance(value, datetime):
                dict_repr[key] = datetime.isoformat(value)
        dict_repr['age'] = self.age
        return json.dumps(dict_repr, indent=2)
Base.metadata.create_all(bind=engine)
session_local = sessionmaker(autocommit=False, autoflush=False, bind=engine)
db_session = session_local()
user_sign_up = UserSignUp(
    name="chris",
    surname="karvouniaris",
    notes="some notes about me",
    birth_year=1992
)
try:
    user_to_store = User(
        name=user_sign_up.name,
        surname=user_sign_up.surname,
        birth_year=user_sign_up.birth_year,
        notes=user_sign_up.notes
    )
    db_session.add(user_to_store)
    db_session.commit()
    print(user_to_store)
except IntegrityError as e:
    db_session.rollback()
    print(f"用户注册时出错：{str(e)}")

总结

在处理任何规模的 Python 项目中的数据库时，SQLAlchemy 是一个必须了解的工具，本文只是探索的一些非常基本的功能，基本上也用得上。对于从事过后台开发的朋友来说，这些都是很熟悉的。