FastAPI系列（18）：ORM查询操作

 

本系列汇总，请查看这里：https://www.cnblogs.com/uncleyong/p/19503695

添加表数据

手动依次添加以下表数据：

clas

teacher

course，依赖teacher

student，依赖clas

student_course，多对多的表

 

main.py

import uvicorn
from fastapi import FastAPI
from tortoise.contrib.fastapi import register_tortoise

from test_orm.api.student import student_api
from test_orm.settings import TORTOISE_ORM

app = FastAPI()

app.include_router(student_api, prefix="/student", tags=["学生接口"])

# register_tortoise是注册函数，fastapi一旦运行，register_tortoise已经执行，通过传递进去的app对象，监听服务启动和终止事件
register_tortoise(
    app=app,
    config=TORTOISE_ORM,
)

if __name__ == '__main__':
    uvicorn.run('main:app', host='127.0.0.1', port=8001, reload=True, workers=1)

　　

补充：

# 该方法会在fastapi启动时触发，内部通过传递进去的app对象，监听服务启动和终止事件
# 当检测到启动事件时，会初始化Tortoise对象，如果generate_schemas为True则还会进行数据库迁移
# 当检测到终止事件时，会关闭连接
register_tortoise(
    app,
    config=TORTOISE_ORM,
    # generate_schemas=True,  # 如果数据库为空，则自动生成对应表单，生产环境不要开
    # add_exception_handlers=True,  # 生产环境不要开，会泄露调试信息
)

 

查询所有：all

from fastapi import APIRouter

from test_orm.models import Student

student_api = APIRouter()


@student_api.get("/")
def getAllStudent():
    # 查询所有 all方法
    students = Student.all()  # Queryset: [Student(),Student(),Student()]
    print(students)  # <tortoise.queryset.QuerySet object at 0x0000025B92F0FD00>

    return {"msg": "查询所有学生"}

　　

浏览器请求后：因为没有加await，所以结果是一个QuerySet，加了才是返回对象

 

from fastapi import APIRouter

from test_orm.models import Student

student_api = APIRouter()


@student_api.get("/")
def getAllStudent():
    # 查询所有all方法
    students = Student.all()  # Queryset: [Student(),Student(),Student()]
    print(students)  # <tortoise.queryset.QuerySet object at 0x0000025B92F0FD00>
    for student in students:
        print(student.name, student.sno)
    return {"msg": "查询所有学生"}

　　

浏览器请求后报错：tortoise.exceptions.ParamsError: QuerySet indices must be slices

 

 

tortoise是支持异步操作数据库的orm，必须异步，协程并发（支持大并发，请求来了放到一个请求队列），加async

from fastapi import APIRouter

from test_orm.models import Student

student_api = APIRouter()


@student_api.get("/")
async def getAllStudent():
    # 查询所有all方法
    students = await Student.all()  # Queryset: [Student(),Student(),Student()]
    print(students)
    for student in students:
        print(student.name, student.sno)
    return {"msg": "查询所有学生"}

　　

浏览器请求后

 

可以循环，也可以索引访问

from fastapi import APIRouter

from test_orm.models import Student

student_api = APIRouter()


@student_api.get("/")
async def getAllStudent():
    # (1) 查询所有 all方法
    students = await Student.all()  # Queryset: [Student(),Student(),Student()]
    print(students, type(students))

    for student in students:
        print(student.name, student.sno)

    print(students[0])
    print(students[1])
    print(students[0].name)
    return {"msg": "查询所有学生"}

　　

浏览器请求后

 

过滤查询：filter

返回模型类型对象list

from fastapi import APIRouter

from test_orm.models import Student

student_api = APIRouter()


@student_api.get("/")
async def getAllStudent():
    students = await Student.filter(name="ren")
    print(students, type(students))
    print(students[0].name)
    students = await Student.filter(clas_id=2)
    print(students)
    print(students[0].name)
    return {"msg": "ok"}

　　

浏览器请求后

 

过滤查询：get

返回模型类型对象

from fastapi import APIRouter

from test_orm.models import Student

student_api = APIRouter()


@student_api.get("/")
async def getAllStudent():
    students = await Student.get(name="ren")
    print(students, type(students))
    print(students.name)
    students = await Student.get(clas_id=2)
    print(students)
    print(students.name)
    return {"msg": "ok"}

　　

浏览器请求后

 

模糊查询

from fastapi import APIRouter

from test_orm.models import Student

student_api = APIRouter()


@student_api.get("/")
async def getAllStudent():
    students = await Student.filter(sno__gt=1001)  # __gt表示大于
    print(students, students[0], students[0].name)

    students = await Student.filter(sno__range=[1000, 9999])  # __range表示范围
    print(students, students[0], students[0].name)

    students = await Student.filter(sno__in=[1001, 2002])  # __in表示在哪些值中
    print(students, students[0], students[0].name)

    return {"msg": "ok"}

　　

浏览器请求后

 

values查询

from fastapi import APIRouter

from test_orm.models import Student

student_api = APIRouter()


@student_api.get("/")
async def getAllStudent():
    students = await Student.filter(sno__range=[1, 10000])  # [Student(),Student(),Student(),...]
    print(students, students[0], students[0].name)
    students = await Student.all().values("name", "sno")  # [{},{},{},...]
    print(students, type(students))

    return {"结果": students}

 

浏览器请求后

 

接口文档请求

 

 

 如果是返回students

return students

 

说明json数组也是json