（二十四）基于数据库表的 CRUD 核心逻辑

鸿泥雪瓜

于 2025-09-09 23:18:01 发布

阅读量445

点赞数 8

CC 4.0 BY-SA版权

分类专栏： python进阶精通计划（2025持续更新，订阅不吃亏哦！）文章标签：数据库 python

本文链接：https://blog.csdn.net/qq_43599657/article/details/151373964

python进阶精通计划（2025持续更新，订阅不吃亏哦！）专栏收录该内容

24 篇文章

订阅专栏

#VibeCoding·九月创作之星挑战赛#

经过配置抽离、异常封装和 Alembic 数据库迁移后，我们接下来聚焦 “CRUD 核心逻辑实现与工程化落地”—— 基于已创建的users表，封装完整的异步 CRUD 操作（新增 / 查询 / 更新 / 删除用户），同时结合 Pydantic 参数校验、Pytest 测试和 Pdoc 文档，让代码兼具 “功能性” 和 “可维护性”。

一、核心目标：构建健壮的异步 CRUD 模块

我们将在src/db_tool/core.py中实现 CRUD 函数，需满足以下要求：

参数安全：用 Pydantic 校验输入（如邮箱格式、年龄范围），拒绝无效数据；
异常清晰：抛出自定义数据库异常（DBQueryError/DBConnectionError），方便调用方定位问题；
类型明确：全函数带类型注解，支持 IDE 自动提示和 mypy 静态检查；
异步适配：基于 asyncpg 实现异步操作，不阻塞主线程；
文档自生成：规范 docstring，让 Pdoc 自动生成可查阅的 API 文档。

1. 第一步：定义 Pydantic 校验模型（输入过滤）

先创建参数校验模型，统一规范 “创建用户” 和 “更新用户” 的输入格式，避免非法数据进入数据库：

# src/db_tool/core.py
from pydantic import BaseModel, Field, validate_arguments
from typing import Dict, Optional, List
from datetime import datetime
import asyncpg

# 导入项目内部模块
from src/db_tool.config import DB_CONFIG
from src/db_tool.exceptions import DBConnectionError, DBQueryError


# ------------------------------
# Pydantic 模型：参数校验与数据结构定义
# ------------------------------
class CreateUserRequest(BaseModel):
    """创建用户的请求参数模型（自动校验合法性）"""
    name: str = Field(
        ...,  # 必传字段
        min_length=1, 
        max_length=50, 
        description="用户名（1-50个字符，不允许为空）"
    )
    age: Optional[int] = Field(
        None,  # 可选字段
        ge=0,  # 大于等于0
        le=150,  # 小于等于150
        description="年龄（0-150岁，超出范围会报错）"
    )
    email: str = Field(
        ..., 
        pattern=r"^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$", 
        description="邮箱（需符合标准格式，如 user@example.com）"
    )


class UpdateUserRequest(BaseModel):
    """更新用户的请求参数模型（支持部分字段更新）"""
    name: Optional[str] = Field(None, min_length=1, max_length=50, description="用户名（可选更新）")
    age: Optional[int] = Field(None, ge=0, le=150, description="年龄（可选更新）")
    email: Optional[str] = Field(None, pattern=r"^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$", description="邮箱（可选更新）")

2. 第二步：实现 CRUD 核心函数（异步操作）

基于 asyncpg 编写异步 CRUD 函数，每个函数都包含 “SQL 执行 + 异常捕获 + 结果处理”，确保稳定性和可追溯性。

（1）数据库连接工具函数（复用连接）

# ------------------------------
# 工具函数：获取数据库连接（复用逻辑）
# ------------------------------
async def get_db_connection() -> asyncpg.Connection:
    """
    异步获取PostgreSQL数据库连接（自动处理连接异常）

    Returns:
        asyncpg.Connection: 数据库异步连接对象

    Raises:
        DBConnectionError: 连接失败时抛出（如账号密码错误、数据库未启动、网络问题）
    """
    try:
        # 从配置文件读取连接信息，创建连接
        conn = await asyncpg.connect(**DB_CONFIG)
        return conn
    except asyncpg.PostgresError as e:
        # 捕获原生异常，包装成自定义异常（附带排查提示）
        raise DBConnectionError(f"连接失败：{str(e)}（检查.env配置或数据库服务状态）") from e

（2）新增用户（Create）

# ------------------------------
# CRUD函数1：新增用户（插入users表）
# ------------------------------
@validate_arguments(config=dict(arbitrary_types_allowed=True))
async def create_user(
    conn: asyncpg.Connection, 
    user_data: CreateUserRequest
) -> Dict[str, int | str | datetime]:
    """
    异步新增用户，插入一条记录到users表，并返回完整用户信息

    Args:
        conn: 数据库连接对象（需通过get_db_connection()获取）
        user_data: 创建用户的请求参数（含name/age/email，已通过Pydantic校验）

    Returns:
        Dict[str, int | str | datetime]: 新增用户的完整信息（含自动生成的id和created_at）

    Raises:
        DBQueryError: 插入失败时抛出（如邮箱重复、字段长度超限等）
    """
    # PostgreSQL支持RETURNING *，插入后直接返回新增记录
    sql = """
        INSERT INTO users (name, age, email)
        VALUES ($1, $2, $3)
        RETURNING id, name, age, email, created_at
    """
    try:
        # 执行SQL，将Pydantic模型转成具体参数
        user_record = await conn.fetchrow(
            sql,
            user_data.name,
            user_data.age,
            user_data.email
        )
        # 将asyncpg.Record转成普通字典（方便调用方使用）
        return dict(user_record) if user_record else {}
    except asyncpg.PostgresError as e:
        # 抛出带SQL语句的自定义异常，方便排查错误
        raise DBQueryError(sql=sql, message=f"新增用户失败：{str(e)}") from e

（3）查询用户（Read）

支持 “按 ID 查单个用户” 和 “按条件查多个用户” 两种场景：

# ------------------------------
# CRUD函数2：查询用户（按ID查单个/按条件查多个）
# ------------------------------
@validate_arguments
async def get_user_by_id(
    conn: asyncpg.Connection, 
    user_id: int
) -> Optional[Dict[str, int | str | datetime]]:
    """
    按用户ID异步查询单个用户信息

    Args:
        conn: 数据库连接对象
        user_id: 目标用户ID（必须为正整数，Pydantic自动校验）

    Returns:
        Optional[Dict]: 用户信息（存在则返回字典，不存在则返回None）

    Raises:
        DBQueryError: 查询失败时抛出（如SQL语法错误、表不存在）
        ValueError: user_id为非正整数时抛出（Pydantic自动触发）
    """
    sql = "SELECT id, name, age, email, created_at FROM users WHERE id = $1"
    try:
        user_record = await conn.fetchrow(sql, user_id)
        return dict(user_record) if user_record else None
    except asyncpg.PostgresError as e:
        raise DBQueryError(sql=sql, message=f"查询用户失败：{str(e)}") from e


@validate_arguments
async def get_users_by_condition(
    conn: asyncpg.Connection,
    min_age: Optional[int] = None,
    max_age: Optional[int] = None,
    name_contains: Optional[str] = None
) -> List[Dict[str, int | str | datetime]]:
    """
    按条件异步查询多个用户（支持年龄范围、用户名模糊匹配）

    Args:
        conn: 数据库连接对象
        min_age: 最小年龄（可选，如传20则查询20岁及以上用户）
        max_age: 最大年龄（可选，如传30则查询30岁及以下用户）
        name_contains: 用户名包含的字符（可选，如传"张"则查询名字含"张"的用户）

    Returns:
        List[Dict]: 符合条件的用户列表（无结果则返回空列表）

    Raises:
        DBQueryError: 查询失败时抛出
    """
    # 动态拼接SQL条件（避免硬编码，支持任意条件组合）
    conditions = []
    params = []
    param_index = 1

    if min_age is not None:
        conditions.append(f"age >= ${param_index}")
        params.append(min_age)
        param_index += 1
    if max_age is not None:
        conditions.append(f"age <= ${param_index}")
        params.append(max_age)
        param_index += 1
    if name_contains is not None:
        conditions.append(f"name LIKE ${param_index}")
        params.append(f"%{name_contains}%")  # LIKE模糊匹配，前后加%
        param_index += 1

    # 组装完整SQL（无条件则查所有）
    where_clause = f"WHERE {', '.join(conditions)}" if conditions else ""
    sql = f"SELECT id, name, age, email, created_at FROM users {where_clause} ORDER BY created_at DESC"

    try:
        user_records = await conn.fetch(sql, *params)
        # 将Record列表转成字典列表
        return [dict(record) for record in user_records]
    except asyncpg.PostgresError as e:
        raise DBQueryError(sql=sql, message=f"批量查询用户失败：{str(e)}") from e

（4）更新用户（Update）

支持 “部分字段更新”（如只更用户名，不更年龄）：

# ------------------------------
# CRUD函数3：更新用户（支持部分字段）
# ------------------------------
@validate_arguments(config=dict(arbitrary_types_allowed=True))
async def update_user(
    conn: asyncpg.Connection,
    user_id: int,
    update_data: UpdateUserRequest
) -> Optional[Dict[str, int | str | datetime]]:
    """
    按用户ID异步更新用户信息（支持部分字段，未传字段不更新）

    Args:
        conn: 数据库连接对象
        user_id: 目标用户ID（正整数）
        update_data: 更新参数（可选字段：name/age/email，已校验合法性）

    Returns:
        Optional[Dict]: 更新后的用户信息（用户不存在则返回None）

    Raises:
        DBQueryError: 更新失败时抛出（如邮箱重复）
        ValueError: 更新数据为空时抛出（未传任何需更新的字段）
    """
    # 过滤掉None值（只保留用户实际要更新的字段）
    update_fields = update_data.model_dump(exclude_unset=True)
    if not update_fields:
        raise ValueError("更新失败：需至少指定一个字段（name/age/email）")

    # 动态生成SET子句（如update_fields={"name":"Alice","age":25} → "name=$1, age=$2"）
    set_clause = ", ".join([f"{field} = ${index+1}" for index, field in enumerate(update_fields.keys())])
    # 拼接完整SQL（RETURNING * 返回更新后的记录）
    sql = f"UPDATE users SET {set_clause} WHERE id = ${len(update_fields)+1} RETURNING *"
    # 准备参数：更新字段值 + 用户ID
    params = list(update_fields.values()) + [user_id]

    try:
        updated_record = await conn.fetchrow(sql, *params)
        return dict(updated_record) if updated_record else None
    except asyncpg.PostgresError as e:
        raise DBQueryError(sql=sql, message=f"更新用户失败：{str(e)}") from e

（5）删除用户（Delete）

# ------------------------------
# CRUD函数4：删除用户（按ID）
# ------------------------------
@validate_arguments
async def delete_user(
    conn: asyncpg.Connection, 
    user_id: int
) -> bool:
    """
    按用户ID异步删除用户

    Args:
        conn: 数据库连接对象
        user_id: 目标用户ID（正整数）

    Returns:
        bool: 删除结果（成功删除返回True，用户不存在返回False）

    Raises:
        DBQueryError: 删除失败时抛出（如存在外键关联）
    """
    sql = "DELETE FROM users WHERE id = $1 RETURNING id"
    try:
        # 执行删除后，若有返回值说明删除成功（用户存在）
        deleted_record = await conn.fetchrow(sql, user_id)
        return bool(deleted_record)
    except asyncpg.PostgresError as e:
        raise DBQueryError(sql=sql, message=f"删除用户失败：{str(e)}") from e

3. 第三步：编写 Pytest 测试用例（验证功能）

在tests/test_core.py中编写异步测试用例，覆盖 “正常场景” 和 “异常场景”，确保 CRUD 函数稳定可靠。

（1）测试前准备：安装依赖（若未安装）

# 确保已安装异步测试依赖
poetry add --dev pytest pytest-asyncio

（2）编写测试代码（含测试夹具复用连接）

# tests/test_core.py
"""
测试users表CRUD函数（异步测试）
注意：使用测试环境数据库（执行前设置 ENV=test），避免污染开发数据
"""
import pytest
import asyncio
from typing import Dict, List
from src.db_tool.core import (
    get_db_connection,
    create_user,
    get_user_by_id,
    get_users_by_condition,
    update_user,
    delete_user,
    CreateUserRequest,
    UpdateUserRequest
)
from src.db_tool.exceptions import DBQueryError


# ------------------------------
# 测试夹具（Fixture）：复用数据库连接
# ------------------------------
@pytest.fixture(scope="module")  # 整个测试模块复用一个连接
async def db_conn():
    """创建数据库连接，测试结束后自动关闭"""
    conn = None
    try:
        conn = await get_db_connection()
        yield conn  # 测试函数通过参数获取连接
    finally:
        # 测试结束后关闭连接（避免资源泄漏）
        if conn and not conn.is_closed():
            await conn.close()


# ------------------------------
# 测试用例：覆盖CRUD所有场景
# ------------------------------
@pytest.mark.asyncio  # 标记为异步测试用例
async def test_create_user_normal(db_conn):
    """测试正常新增用户（成功场景）"""
    # 1. 准备测试数据（用时间戳确保邮箱唯一，避免重复）
    timestamp = int(asyncio.get_running_loop().time())
    test_data = CreateUserRequest(
        name=f"TestUser_{timestamp}",
        age=25,
        email=f"test_{timestamp}@example.com"
    )

    # 2. 执行新增操作
    result = await create_user(db_conn, test_data)

    # 3. 断言结果（检查返回字段和值是否正确）
    assert isinstance(result, Dict), "新增用户应返回字典"
    assert result["name"] == test_data.name
    assert result["age"] == test_data.age
    assert result["email"] == test_data.email
    assert "id" in result, "新增用户应返回自动生成的id"
    assert "created_at" in result, "新增用户应返回自动生成的created_at"

    # 4. 清理测试数据（避免污染数据库）
    await delete_user(db_conn, result["id"])


@pytest.mark.asyncio
async def test_create_user_duplicate_email(db_conn):
    """测试新增用户时邮箱重复（异常场景，应抛出DBQueryError）"""
    # 1. 先新增一个用户（占用邮箱）
    test_data = CreateUserRequest(name="DuplicateTest", age=30, email="duplicate@example.com")
    created = await create_user(db_conn, test_data)

    # 2. 再次用相同邮箱新增（预期抛出异常）
    with pytest.raises(DBQueryError) as exc_info:
        await create_user(db_conn, test_data)

    # 3. 断言异常信息（确认是唯一约束错误）
    assert "unique constraint" in str(exc_info.value).lower(), "邮箱重复应触发唯一约束错误"

    # 4. 清理测试数据
    await delete_user(db_conn, created["id"])


@pytest.mark.asyncio
async def test_get_user_by_id(db_conn):
    """测试按ID查询用户（正常场景+用户不存在场景）"""
    # 1. 先新增测试用户
    test_data = CreateUserRequest(name="GetTest", age=35, email="get_test@example.com")
    created = await create_user(db_conn, test_data)
    user_id = created["id"]

    # 2. 查询存在的用户（正常场景）
    exist_user = await get_user_by_id(db_conn, user_id)
    assert exist_user is not None, "应查询到新增的用户"
    assert exist_user["id"] == user_id
    assert exist_user["name"] == test_data.name

    # 3. 查询不存在的用户（返回None）
    non_exist_user = await get_user_by_id(db_conn, 999999)
    assert non_exist_user is None, "查询不存在的用户应返回None"

    # 4. 清理数据
    await delete_user(db_conn, user_id)


@pytest.mark.asyncio
async def test_get_users_by_condition(db_conn):
    """测试按条件查询用户（年龄范围+用户名模糊匹配）"""
    # 1. 先新增3个测试用户
    test_users = [
        CreateUserRequest(name="Zhang San", age=20, email="zhang@example.com"),
        CreateUserRequest(name="Li Si", age=25, email="li@example.com"),
        CreateUserRequest(name="Zhang Wei", age=30, email="wei@example.com")
    ]
    created_ids = []
    for user in test_users:
        result = await create_user(db_conn, user)
        created_ids.append(result["id"])

    # 2. 条件1：查询年龄20-25岁的用户（预期2个：Zhang San、Li Si）
    users_by_age = await get_users_by_condition(db_conn, min_age=20, max_age=25)
    assert len(users_by_age) == 2, "按年龄20-25查询应返回2个用户"
    assert {u["name"] for u in users_by_age} == {"Zhang San", "Li Si"}

    # 3. 条件2：查询用户名含"Zhang"的用户（预期2个：Zhang San、Zhang Wei）
    users_by_name = await get_users_by_condition(db_conn, name_contains="Zhang")
    assert len(users_by_name) == 2, "用户名含Zhang查询应返回2个用户"
    assert {u["name"] for u in users_by_name} == {"Zhang San", "Zhang Wei"}

    # 4. 清理数据
    for user_id in created_ids:
        await delete_user(db_conn, user_id)


@pytest.mark.asyncio
async def test_update_user(db_conn):
    """测试更新用户（部分字段更新+全字段更新）"""
    # 1. 先新增测试用户
    original_data = CreateUserRequest(name="UpdateTest", age=40, email="update_original@example.com")
    created = await create_user(db_conn, original_data)
    user_id = created["id"]

    # 2. 场景1：部分字段更新（只更新年龄和邮箱）
    partial_update = UpdateUserRequest(age=45, email="update_new@example.com")
    updated_partial = await update_user(db_conn, user_id, partial_update)
    assert updated_partial["age"] == 45, "年龄应更新为45"
    assert updated_partial["email"] == "update_new@example.com", "邮箱应更新为新值"
    assert updated_partial["name"] == original_data.name, "未更新的用户名应保持原样"

    # 3. 场景2：全字段更新（更新所有字段）
    full_update = UpdateUserRequest(name="UpdateFull", age=50, email="update_full@example.com")
    updated_full = await update_user(db_conn, user_id, full_update)
    assert updated_full["name"] == "UpdateFull"
    assert updated_full["age"] == 50
    assert updated_full["email"] == "update_full@example.com"

    # 4. 场景3：更新不存在的用户（返回None）
    non_exist_update = UpdateUserRequest(age=60)
    updated_non_exist = await update_user(db_conn, 999999, non_exist_update)
    assert updated_non_exist is None, "更新不存在的用户应返回None"

    # 5. 清理数据
    await delete_user(db_conn, user_id)


@pytest.mark.asyncio
async def test_delete_user(db_conn):
    """测试删除用户（存在+不存在场景）"""
    # 1. 新增测试用户
    test_data = CreateUserRequest(name="DeleteTest", age=55, email="delete@example.com")
    created = await create_user(db_conn, test_data)
    user_id = created["id"]

    # 2. 删除存在的用户（返回True）
    delete_exist = await delete_user(db_conn, user_id)
    assert delete_exist is True, "删除存在的用户应返回True"

    # 3. 再次查询已删除的用户（返回None）
    deleted_user = await get_user_by_id(db_conn, user_id)
    assert deleted_user is None, "删除后的用户应查询不到"

    # 4. 删除不存在的用户（返回False）
    delete_non_exist = await delete_user(db_conn, 999999)
    assert delete_non_exist is False, "删除不存在的用户应返回False"

执行测试用例（关键：切换到测试环境）

测试前必须切换到 测试环境（避免污染开发环境数据），执行命令如下：

Windows（CMD）：

# 1. 设置环境变量为测试环境
set ENV=test

# 2. 执行所有测试用例（显示详细日志）
poetry run pytest tests/test_core.py -v

Mac/Linux（终端）：

# 1. 设置环境变量为测试环境并执行测试
ENV=test poetry run pytest tests/test_core.py -v

执行成功后，会看到类似日志（所有用例均通过）：

collected 6 items

tests/test_core.py::test_create_user_normal PASSED
tests/test_core.py::test_create_user_duplicate_email PASSED
tests/test_core.py::test_get_user_by_id PASSED
tests/test_core.py::test_get_users_by_condition PASSED
tests/test_core.py::test_update_user PASSED
tests/test_core.py::test_delete_user PASSED

补充：测试注意事项

环境隔离：必须用 ENV=test 切换到测试环境，测试数据库（test_db）需提前通过 Alembic 创建表结构（执行 ENV=test poetry run alembic upgrade head）；
数据清理：每个测试用例执行后都会删除测试数据（delete_user），避免测试残留影响后续用例；
异常覆盖：测试用例包含 “邮箱重复”“查询不存在用户” 等异常场景，确保函数在错误输入下能正确抛出自定义异常。

到这里，我们就完成了 “CRUD 核心逻辑实现 + 全场景测试” 的闭环。接下来可以基于这个模块扩展功能（比如分页查询、批量操作），或者集成到 Web 框架（如 FastAPI）中提供 API 服务～