在Cherrypy中安全使用SQLAlchemy作用域会话注册Oracle输出参数

我追踪这个问题已经数月，找到最相关的社区讨论线索，但目前仍难以明确核心问题，尽力避免陷入“XY问题”。

环境配置 #

• 前端通过AJAX调用基于CherryPy的REST API
• API使用SQLAlchemy连接池对接Oracle（cx_Oracle），采用社区常见的CherryPy/SQLAlchemy连接池集成方案
• 后端部署通过Apache反向代理转发请求到CherryPy服务

预期结果 #

API端点能稳定返回用户数据，无CherryPy超时或502错误。

实际问题 #

• 使用Promise.all发送批量请求（如10个）时，平均9个正常返回，总有1个或多个请求挂起，直至代理10秒超时返回502
• 收到502后立即重试相同请求，可成功返回
• CherryPy服务器重启后初期运行正常
• 怀疑调用存储过程/函数时，作用域会话中的游标/连接未正确关闭

核心业务代码 #

raw_conn = None
#print('units', data['units'], dir(data['units']))
#print(data['units'])
try:
    # Give it some user id, this is just example code
    data["name"] = cherrypy.request.db.query(func.user_package.get_users_function(data['uid'], 'US')).one()[0]
    raw_conn = cherrypy.request.db.connection().engine.raw_connection()
    cur = None
    data["metadata"] = []
    try:
        cur = raw_conn.cursor()
        # I tried this below, same results as the above line
        #data["units"] = cur.callfunc('user_package.get_users_function', str, [data['uid'], 'US'])
        result = cur.var(cx_Oracle.CURSOR)
        #cur.callfunc('cwms_ts.retrieve_ts', None, [result, data['ts'], data["units"], data["start_time"].strftime('%d-%b-%Y %H%M'), data["end_time"].strftime('%d-%b-%Y %H%M')])
        cur.execute('''begin
            users_metadata.getUserInfo(
            :1,
            :2,
            :3,
            to_date(:4, 'dd-mon-yyyy hh24mi'),
            to_date(:5, 'dd-mon-yyyy hh24mi'),
            'CDT');
        end;''', (result, data['uid'], data["name"], data["start_time"].strftime(
            '%d-%b-%Y %H%M'), data["end_time"].strftime('%d-%b-%Y %H%M')))
        # Data is returned as a 2d array with [datetime, int, int]
        data['values'] = [[x[0].isoformat(), x[1] if not isinstance(
            x[1], float) else round(x[1], 2), x[2]] for x in result.values[0].fetchall()]
    finally:
        if cur:
            cur.close()
        #return data
    data["end_time"] = data["end_time"].isoformat()
    data["start_time"] = data["start_time"].isoformat()
    return data
except Exception as err:
    # Don't log this error
    return {"title": "Failed to Query User Date", "msg": str(err), "err": "User Error"}
finally:
    if raw_conn: raw_conn.close()

CherryPy配置 #

[/]
cors.expose.on = True
tools.sessions.on = True
tools.gzip.on = True
tools.gzip.mime_types = ["text/plain", "text/html", "application/json"]
tools.sessions.timeout = 300
tools.db.on = True
tools.secureheaders.on = True
log.access_file = './logs/access.log'
log.error_file = './logs/application.log'
tools.staticdir.root: os.path.abspath(os.getcwd())
tools.staticdir.on = True
tools.staticdir.dir = '.'
tools.proxy.on = True

[/static]
tools.staticdir.on = True
tools.staticdir.dir = "./public"

[/favicon.ico]
tools.staticfile.on = True
tools.staticfile.filename = os.path.abspath(os.getcwd()) + "/public/terminal.ico"

SQLAlchemy引擎配置 #

def start(self):
    if not self.sa_engine: 
        self.sa_engine = create_engine(
            self.dburi, echo=False, pool_recycle=7199,
            pool_size=300, max_overflow=100, pool_timeout=9)  # , pool_pre_ping=True)
        cherrypy.log("Connected to Oracle")

Apache反向代理配置 #

<Location /myapp>
  Order allow,deny
  allow from all
  ProxyPass http://127.0.0.1:8080
  ProxyPassReverse http://127.0.0.1:8080
</Location>

排查思路与可能原因 #

1. 连接池资源耗尽

   • 确认Oracle端的最大连接数限制，若Oracle侧连接数不足，会导致新请求等待连接释放
   • 启用pool_pre_ping=True，检测连接有效性，避免使用已失效的连接
   • 检查pool_recycle=7199是否匹配Oracle的IDLE_TIME设置，防止连接被Oracle主动回收后仍留在SQLAlchemy池中

2. 手动获取的连接未正确归还

   • 代码中直接获取底层连接后，需确认raw_conn.close()是否会将连接正确归还到SQLAlchemy连接池，而非直接关闭
   • 建议使用SQLAlchemy的上下文管理器（with语句）管理连接和游标，确保资源自动释放

3. 游标与存储过程的资源泄漏

   • 调用存储过程返回的游标result.values[0]未显式关闭，可能占用连接资源，尝试在获取数据后添加result.values[0].close()

4. CherryPy线程池容量不足

   • 检查CherryPy默认线程池大小（通常为10），若批量请求数超过线程池容量，会导致请求排队超时，可在配置中添加server.thread_pool = 50（根据负载调整）

5. 代理超时与请求耗时不匹配

   • 当前代理超时为10秒，可适当延长（如ProxyTimeout 30），同时排查个别请求的业务逻辑执行耗时是否过长

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