Sqlalchemy连接超时：执行营地查询语句触发连接错误

我手动执行以下SQL可正常获取结果：

SELECT 
  backend.campsites.campsite_id AS backend_campsites_campsite_id, 
  backend.campsites.campground_id AS backend_campsites_campground_id, 
  backend.campsites.available_from AS backend_campsites_available_from, 
  backend.campsites.available_to AS backend_campsites_available_to, 
  backend.campsites.site_number AS backend_campsites_site_number, 
  backend.campsites.size AS backend_campsites_size, 
  backend.campsites.amenities AS backend_campsites_amenities, 
  backend.campsites.availability AS backend_campsites_availability
FROM backend.campsites
WHERE backend.campsites.size = 'Medium' 
  AND backend.campsites.campground_id = 2

但调用assign_campsites函数时出现SQLAlchemy连接超时错误：

def assign_campsites(Campsites):
    campsite_session = SessionLocal()
    retrieve_all_bookings = bookings.find()
    retrieve_available_campsites = campsite_session.query(Campsites).filter(Campsites.availability == True).all()
    for booking in retrieve_all_bookings:
            for campsite in retrieve_available_campsites:
                if campsite.availability == True:
                    departure_date = datetime.strptime(booking['arrival_date'], "%Y-%m-%d") + timedelta(days=7)
                    existing_booking = campsites.find_one({
                        "booking_id": booking['booking_id'],
                    })
                    if not existing_booking:
                        if booking['campsite_size'] <= campsite.size and booking['campground_id'] == campsite.campground_id:
                            # 创建MongoDB文档
                            submit_mongo_doc(booking, campsite, departure_date)
                            update_campsites_table(campsite, Campsites, campsite_session)
                            
                        else:
                            current_booking = booking['campground_id']
                            current_size = campsite.size
                            assign = campsite_session.query(Campsites).filter(Campsites.size == current_size, Campsites.campground_id == current_booking).first()
                            if assign:
                                submit_mongo_doc(booking, assign, departure_date)
                                update_campsites_table(campsite, Campsites, campsite_session)
                    else:
                        continue

经排查，错误由以下代码片段触发：

assign = (
    campsite_session
        .query(Campsites)
        .filter(
            Campsites.size == current_size, 
            Campsites.campground_id == current_booking
        )
        .first()
)

排查与解决建议 #

• 会话资源未回收：当前代码未主动关闭campsite_session，循环中持续占用连接导致池耗尽。改用上下文管理器自动管理会话：

  def assign_campsites(Campsites):
      with SessionLocal() as campsite_session:
          # 原有业务逻辑...
  

• 重复查询优化：内层循环中反复发起数据库查询，加剧连接压力。提前将可用营地按(营地ID, 尺寸)分组缓存，避免重复查询：

  def assign_campsites(Campsites):
      with SessionLocal() as campsite_session:
          retrieve_all_bookings = bookings.find()
          available_campsites = campsite_session.query(Campsites).filter(Campsites.availability == True).all()
          # 构建营地映射字典
          campsite_map = {(cs.campground_id, cs.size): cs for cs in available_campsites}
          
          for booking in retrieve_all_bookings:
              # 直接从缓存取对应营地
              target_key = (booking['campground_id'], booking['campsite_size'])
              assign = campsite_map.get(target_key)
              if assign:
                  # 后续业务逻辑...
  

• 连接池参数调整：检查SQLAlchemy连接池配置，适当调大pool_size、max_overflow，设置pool_recycle避免连接失效。
• 数据库索引优化：在campsites表的campground_id和size字段上创建联合索引，提升查询效率：

  CREATE INDEX idx_campsite_campground_size ON backend.campsites (campground_id, size);
  

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