FastAPI部署在DigitalOcean与Azure上的MySQL持久化连接问题求助

我在DigitalOcean云服务器与Azure App Service上分别部署了FastAPI应用，对接对应平台托管的MySQL数据库，但持续遭遇间歇性数据库连接问题，导致应用中断，报错信息如下：

2024-06-06T07:27:13.243707191Z: [ERROR] File "/usr/local/lib/python3.11/site-packages/pymysql/connections.py", line 759, in _write_bytes
2024-06-06T07:27:13.243710391Z: [ERROR] raise err.OperationalError(
2024-06-06T07:27:13.243713391Z: [ERROR] sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2006, "MySQL server has gone away (ConnectionResetError(104, 'Connection reset by peer'))")

2024-05-21T12:03:39.909286838Z: [ERROR] File "/usr/local/lib/python3.11/site-packages/pymysql/connections.py", line 692, in _read_packet
2024-05-21T12:03:39.909290038Z: [ERROR] packet_header = self._read_bytes(4)
2024-05-21T12:03:39.909293038Z: [ERROR] ^^^^^^^^^^^^^^^^^^^
2024-05-21T12:03:39.909296338Z: [ERROR] File "/usr/local/lib/python3.11/site-packages/pymysql/connections.py", line 738, in _read_bytes
2024-05-21T12:03:39.909299638Z: [ERROR] raise err.OperationalError(
2024-05-21T12:03:39.909303838Z: [ERROR] pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query ([Errno 110] Connection timed out)')

已尝试以下缓解措施，但问题仍未解决：

• 添加数据库连接中间件
• 将Azure数据库配置为“允许所有主机访问(*)”
• 在create_engine()中设置pool_pre_ping=True
• 在数据库URL后追加?connect_timeout=10&read_timeout=30&write_timeout=30
• 在finally块中实现db_session.close()确保连接正确关闭

此前在DigitalOcean部署Flask应用时遇到类似问题，通过在每个API路由末尾显式关闭数据库连接缓解，但该方法不适用于当前FastAPI架构。

版本详情 #

• FastAPI：0.90.0
• SQLAlchemy：2.0.2
• Python：3.11

代码结构 #

main.py #

from fastapi import FastAPI
from fastapi.middleware.cors import CORSMiddleware

from routers import *


app = FastAPI()
origins = ['*']


app.add_middleware(
    CORSMiddleware,
    allow_origins     = origins,
    allow_credentials = True,
    allow_methods     = ["*"],
    allow_headers     = ["*"],
)


app.include_router(user.router)

app/database.py #

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

SQLALCHEMY_DATABASE_URL = f"mysql+pymysql://{{user}}:{{pwrd}}@{{host}}:{{port}}/{{name}}"
SQLALCHEMY_DATABASE_URL = (
    f"mysql+pymysql://{{user}}:{{pwrd}}@{{host}}:{{port}}/{{name}}"
    "?connect_timeout=10&read_timeout=30&write_timeout=30"
)

engine = create_engine(SQLALCHEMY_DATABASE_URL, pool_pre_ping = True)

SessionLocal = sessionmaker(autocommit = False, autoflush = False, bind = engine)

Base = declarative_base()


def get_db():
    try:
        db_session = SessionLocal()
        yield db_session
    finally:
        db_session.close()

db_models/SQL_Models.py（包含所有数据表定义的SQLAlchemy类） #

import sqlalchemy as db

from app.database import Base


class Table_1(Base):

    __tablename__ = 'Table_1'

    row_id = db.Column(db.Integer, primary_key=True, index=True, autoincrement=True)
    ...

from app.database import engine; Base.metadata.create_all(engine)

routers/routes.py #

@router.get('/{{student_id}}')
async def get_student_details(
    student_id: int,
    db_session: Session = Depends(get_db), # 在此处理数据库连接
    token: Union[str, None] = Header(default=None)
):

    students = db_session \
        .query(Students) \
        .filter(Students.student_id == student_id) \
        .first()

    return students

注：使用的是MySQL关系型数据库。

恳请提供解决该连接问题的思路或建议，谢谢！

内容的提问来源于stack exchange，提问作者Amit Pathak