Fastapi0.115.6之Tortoise ORM0.23.0、Redis7.2.1基本增删改查大全【亲测可用，仅供参考】

自己在有限的时间里写了一个Fastapi之Tortoise ORM增删改查大全，如有写错的地方还请多多指点。接口功能有可能不全，我会迭代的。主要是方便查找。先放出来一下浏览器的接口文档截图

一、models或ORM数据表文件

from tortoise.models import Model
from tortoise import fields

class P_user(Model):
    p_id = fields.IntField(pk=True)
    p_user = fields.CharField(null=False, unique=True, max_length=12, index=True, description='用户账号')
    p_pass = fields.CharField(null=False, unique=True, max_length=100, description='用户密码')
    role = fields.IntField(null=False, description="角色")
    permissions = fields.IntField(null=False, description="权限")
    created_at = fields.DatetimeField(auto_now_add=True, description="创建日期")
    is_deleted = fields.BooleanField(default=False, description="软删除")
    OAuth2client_id = fields.CharField(null=True, max_length=255, description="OAuth2client_id")
    OAuth2client_secret = fields.CharField(null=True, max_length=255, description="OAuth2client_secret")

    class Meta:
        db_table = 'p_user'
        verbose_name = '用户信息表'
        verbose_name_plural = '用户信息表'


class P_info(Model):
    p_id = fields.IntField(pk=True)
    infoa = fields.CharField(null=False, unique=True, max_length=12, index=True, description='第一种信息')
    infob = fields.CharField(null=False, unique=True, max_length=12, index=True, description='第二种信息')
    infoc = fields.JSONField(null=True, description='第三种信息')

    class Meta:
        db_table = 'p_info'
        verbose_name = '批量信息表'
        verbose_name_plural = '批量信息表'


class P_Login(Model):
    p_id = fields.IntField(pk=True)
    p_user = fields.CharField(null=False, unique=True, max_length=18, description="用户账号")
    p_pass = fields.CharField(null=False, unique=True, max_length=100, description='用户密码')
    p_pic = fields.CharField(null=False, max_length=48, description="用户头像")
    is_deleted = fields.BooleanField(default=False, description="软删除")

    class Meta:
        db_table = 'p_login'
        verbose_name = '用户登录表'
        verbose_name_plural = '用户登录表'

# 一对一关系
class IDCard(Model):
    id = fields.IntField(pk=True)
    content = fields.CharField(max_length=50, unique=True)

    class Meta:
        table = "id_card"

# 一对一关系
class Person(Model):
    id = fields.IntField(pk=True)
    name = fields.CharField(max_length=50)
    id_card = fields.OneToOneField("models.IDCard", related_name="person", on_delete=fields.OnDelete.CASCADE
)

    class Meta:
        table = "person"


# 一对多关系
class Users(Model):
    id = fields.IntField(pk=True)
    username = fields.CharField(max_length=255, unique=True)
    posts = fields.ReverseRelation["Post"]

    class Meta:
        table = "users"

# 一对多关系
class Post(Model):
    id = fields.IntField(pk=True)
    title = fields.CharField(max_length=255)
    content = fields.TextField()
    user = fields.ForeignKeyField("models.Users", related_name="posts", on_delete=fields.OnDelete.CASCADE)

    class Meta:
        table = "posts"

链接数据库代码如下：

TORTOISE_ORM = {
    "connections": {
        'default': {
            'engine': 'tortoise.backends.mysql',
            'credentials': {
                'host': '192.168.80.133',
                'port': '3306',
                'user': 'root',
                'password': 'Newmysql55..',
                'database': 'waerhouse',
                'minsize': 1,
                'maxsize': 5,
                'charset': 'utf8mb4',
                "echo": True
            }
        }
    },
    'apps': {
        'models': {
            # 如果你的models在文件夹里，那就在前面把文件夹的名称写在.models的前面
            'models': ['study_ORM.models', "aerich.models"],
            'default_connection': 'default',
        }
    },
    'use_tz': False,
    'timezone': 'Asia/Shanghai'
}

二、基础接口案例

详细代码如下：

import logging
from math import ceil
from typing import Optional, List

import bcrypt
from fastapi import APIRouter, HTTPException, Query, Body, Depends, Request
from fastapi.templating import Jinja2Templates
from pydantic import BaseModel, field_validator
from starlette import status
from study_ORM.models import P_user

# 设置日志记录
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

p_user_api = APIRouter()


# 查询所有用户信息
@p_user_api.get("/A", summary="查询所有信息接口(不带分页功能)")
async def get_all_users():
    users = await P_user.all()
    logger.info("全部信息查询: %s", users)
    return {"操作结果": users}


# 查询单个字段信息
@p_user_api.get("/B", summary="查询单个字段里的信息接口(精准搜索)")
async def get_user_by_username(p_user: str):
    user = await P_user.filter(p_user=p_user)
    logger.info("过滤查询: %s", user)
    return {"操作结果": user}


# 根据ID查询用户信息
@p_user_api.get("/C", summary="按ID号进行查询")
async def get_user_by_id(p_id: int):
    user = await P_user.get(p_id=p_id)
    logger.info("get查询: %s", user.p_user)
    return {"操作结果": user}


# 模糊查询
@p_user_api.get("/D", summary="按某个字段进行模糊查询")
async def get_user_by_partial_username(p_user: str):
    users = await P_user.filter(p_user__icontains=p_user)
    logger.info("模糊查询: %s", users)
    return {"操作结果": users}


# 查询两个字段信息
@p_user_api.get("/E", summary="只查询其中两个字段里的所有信息")
async def get_user_values():
    users = await P_user.all().values("permissions", "p_user")
    logger.info("values查询: %s", users)
    return {"操作结果": users}


# 分页查询
class UserOut(BaseModel):
    total: int
    total_pages: int
    current_page: int
    has_previous: bool
    has_next: bool
    items: List[dict]


@p_user_api.get("/p_users/page", response_model=UserOut, summary="分页接口")
async def get_users(
        page: int = Query(1, ge=1, description="当前页码"),
        limit: int = Query(10, ge=1, le=100, description="每页数量"),
):
    try:
        skip = (page - 1) * limit
        users = await P_user.filter(is_deleted=False).offset(skip).limit(limit)
        total = await P_user.filter(is_deleted=False).count()
        total_pages = ceil(total / limit)

        user_list = [
            {
                "p_id": user.p_id,
                "p_user": user.p_user,
                "p_pass": user.p_pass,
                "role": user.role,
                "permissions": user.permissions,
                "created_at": user.created_at,
                "is_deleted": user.is_deleted,
            }
            for user in users
        ]

        return {
            "total": total,
            "total_pages": total_pages,
            "current_page": page,
            "has_previous": page > 1,
            "has_next": page < total_pages,
            "items": user_list,
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


# 前后端不分离查询
@p_user_api.get("/index", summary="前后端不分离查询")
async def get_all_users_html(request: Request):
    templates = Jinja2Templates(directory="templates")
    users = await P_user.all()
    return templates.TemplateResponse("index.html", {"request": request, "userinfo": users})


# 用户输入校验模型
class P_userIn(BaseModel):
    p_user: str = Body(..., descr