【进阶】数据库高级操作：使用SQLAlchemy

# 1.1 数据库高级操作概述

数据库高级操作是指在基础数据库操作（如增删改查）的基础上，进行更复杂、更高效的数据处理操作。这些操作通常涉及到复杂查询、关联查询、数据修改、数据验证和约束等方面。

在现代软件开发中，数据库高级操作对于处理海量数据、构建复杂业务逻辑和确保数据完整性至关重要。通过掌握数据库高级操作技术，开发者可以显著提升应用程序的性能、可靠性和可维护性。

# 2. SQLAlchemy基础

### 2.1 SQLAlchemy的安装和配置

#### 安装

使用pip安装SQLAlchemy：

pip install sqlalchemy

#### 配置

在Python脚本中导入SQLAlchemy：

from sqlalchemy import create_engine, Column, Integer, String

创建引擎对象，指定数据库类型和连接信息：

engine = create_engine("postgresql://user:password@host:port/database")

### 2.2 SQLAlchemy的模型定义和映射

#### 模型定义

使用SQLAlchemy的`declarative_base`创建模型基类：

Base = declarative_base()

定义模型类，继承自`Base`并声明属性：

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    email = Column(String(100), unique=True)

#### 映射

使用`metadata`属性将模型映射到数据库：

Base.metadata.create_all(engine)

### 2.3 SQLAlchemy的会话和事务管理

#### 会话

会话对象用于与数据库交互：

session = sessionmaker(bind=engine)()

#### 事务

事务用于确保数据库操作的原子性：

try:
    # 执行数据库操作
    session.commit()
except Exception as e:
    # 发生异常时回滚事务
    session.rollback()
    raise e

# 3. SQLAlchemy高级查询

### 3.1 复杂查询的构建

#### 3.1.1 过滤器和排序

SQLAlchemy提供了丰富的过滤和排序功能，可以构建复杂的查询。

**过滤器**

过滤器用于限制查询结果集，通过指定条件来筛选出符合条件的数据。SQLAlchemy支持多种过滤操作，包括：

from sqlalchemy import and_, or_

# 等于
session.query(User).filter(User.name == 'John')

# 不等于
session.query(User).filter(User.name != 'John')

# 大于
session.query(User).filter(User.age > 30)

# 小于
session.query(User).filter(User.age < 30)

# 范围
session.query(User).filter(User.age.between(20, 30))

# 多个条件
session.query(User).filter(and_(User.name == 'John', User.age > 30))
session.query(User).filter(or_(User.name == 'John', User.age > 30))

**排序**

排序用于对查询结果集进行排序，可以通过指定排序字段和排序方式来实现。

# 升序
session.query(User).order_by(User.name)

# 降序
session.query(User).order_by(User.name.desc())

# 多个字段排序
session.query(User).order_by(User.name, User.age)

#### 3.1.2 分组和聚合

SQLAlchemy支持分组和聚合操作，可以对查询结果集进行分组和聚合计算。

**分组**

分组用于将查询结果集按指定的字段分组，可以统计每个分组中的数据。

# 按姓名分组
session.query(User.name, func.count(User.id)).group_by(User.name)

**聚合**

聚合用于对分组后的数据进行聚合计算，常见的聚合函数包括：