python - FastApi sqlalchemy 连接在运行中被关闭

Question

我有一个带有异步 sqlalchemy 的异步 FastApi 应用程序，源代码(不会提供 schemas.py，因为它不是必需的):

数据库.py

from sqlalchemy import (
    Column,
    String,
)
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm.decl_api import DeclarativeMeta

from app.config import settings


engine = create_async_engine(settings.DATABASE_URL)
Base: DeclarativeMeta = declarative_base()
async_session = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)


class Titles(Base):
    __tablename__ = "titles"
    id = Column(String(100), primary_key=True)
    title = Column(String(100), unique=True)


async def get_session() -> AsyncSession:
    async with async_session() as session:
        yield session

路由器.py

import .database
from fastapi_utils.cbv import cbv
from fastapi_utils.inferring_router import InferringRouter


router = InferringRouter()


async def get_titles(session: AsyncSession):
    results = await session.execute(select(database.Titles)))
    return results.scalars().all()


@cbv(router)
class TitlesView:
    session: AsyncSession = Depends(database.get_session)

    @router.get("/titles", status_code=HTTP_200_OK)
    async def get(self) -> List[TitlesSchema]:
        results = await get_titles(self.session)
        return [TitlesSchema.from_orm(result) for result in results]

主.py

from fastapi import FastAPI

from app.routers import router 


def create_app() -> FastAPI:
    app = FastAPI()
    app .include_router(routers, prefix="/", tags=["Titles"])

    return printer_app


app = create_app()

它与 docker 一起运行:

CMD ["uvicorn", "main:app", "--reload", "--host", "0.0.0.0", "--port", "8000", "--limit-max-requests", "10000"]

它在 docker 中也有默认设置的 Postgres 数据库。这一切都在 docker-swarm 上运行。一开始工作正常，接受所有请求。但是如果你放15-30分钟(我没算)，然后再去request，就不行了:

<class 'asyncpg.exceptions.ConnectionDoesNotExistError'>: connection was closed in the middle of operation

紧接着我发送了下一个请求，它没有抛出错误。会是什么呢？如何摆脱 ConnectionDoesNotExistError？

Answer 1

我使用这样的 pool_pre_ping 设置解决了这个问题:

engine = create_engine(DB_URL, pool_pre_ping=True)

https://docs.sqlalchemy.org/en/14/core/pooling.html