Azure Consumption计划下FastAPI+SQLAlchemy登录超时问题排查

我在Azure Consumption计划上运行FastAPI应用，通过SQLAlchemy连接Azure SQL数据库。长时间闲置后首次请求API时，总会触发OperationError: Login TimeOut Expired错误，但后续请求能正常获取数据。

已将get_db作为FastAPI的依赖项使用，且尝试了重试机制，但问题仍未解决，恳请提供解决方案。

RETRIES = 3
DELAY = 5  # delay in seconds

DATABASE_URL = ("mssql+pyodbc://username:MyPassword@sql-servername.database.windows.net"
                 "/sqldb-mydatabase"
                 "?driver=ODBC+Driver+18+for+SQL+Server"
                 "&Encrypt=yes"
                 "&TrustServerCertificate=no"
                 "&ConnectionTimeout=30")

engine = create_engine(
    DATABASE_URL,
    pool_size=10,    # Number of connections to maintain in the pool
    max_overflow=20,  # Additional connections that can be created beyond the pool_size
    pool_timeout=30,  # Maximum time in seconds to wait for a connection
    pool_recycle=1800,  # Recycle connections after this many seconds
    pool_pre_ping=True,  # Ensures connections are tested before use
    # Additional Azure-specific connection parameters
    connect_args={
        'connect_timeout': 30,# Max time in seconds to establish a connection 
        'login_timeout': 30,
    }
)

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

# base class for all ORM models in SQLAlchemy which is used to set up metadata
# for mapping models to database tables.
Base = declarative_base()


def get_db():
    '''Get's a database instance for the session & ensure it's properly closed'''

    for attempt in range(RETRIES):
        try:
            db = SessionLocal()
            # Test the connection by executing a simple query
            data = db.execute(text('SELECT 1'))
            logging.info("Able to fetch a user after making a connection")
            yield db
            break
        except (OperationalError, DBAPIError) as oe:
            logging.error("Database connection failed: %s", oe)

            if attempt < RETRIES - 1:

                logging.info("Retry attempt (%d) for connecting to the database...",
                             (attempt + 1)/RETRIES)

                time.sleep(DELAY)

            else:
                logging.critical(
                    "Database connection failed after (%d) attempts.", RETRIES)
                raise HTTPException(status_code=status.HTTP_503_SERVICE_UNAVAILABLE,
                                    detail="Database connection failed, please try again later.") from e
        finally:
            if db:
                
                db.close()

已解决 #

移除db.execute前的条件判断后重新部署代码，问题现已解决。推测此前函数未重置或部署未生效。

长时间闲置后访问API仍有延迟，但不再报错，可返回正常响应，后续调用也运行正常。

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