Flask项目使用Oracle数据库踩坑指南——数据库连接池配置

概述

为了尝试快速开发，使用Flask框架构建了web项目，但因需要适配Oracle数据库，从而折腾出一堆问题。
flask项目一般使用 flask-sqlalchemy 基于 SQLAlchemy中间件实现多种类型数据库的适配，Oracle也在其中。
本文为踩坑指南的第二篇。

配置连接池创建连接的一般过程

在Flask项目的配置文件中，可通过设置常量的方式来告知SQLAlchemy连接数据库的配置参数。
配置文件示例：

import os
from sqlalchemy.pool import QueuePool

class Config:
    SECRET_KEY = os.environ.get('SECRET_KEY') or 'some complicate strings'
    SQLALCHEMY_COMMIT_ON_TEARDOWN = True
    SQLALCHEMY_TRACK_MODIFICATIONS = False
    SQLALCHEMY_ENGINE_OPTIONS = {
        'poolclass': QueuePool,
        'max_overflow': 5,   # 超过连接池大小外最多可创建的连接
        'pool_size': 20,  # 连接池大小
        'pool_timeout': 5,  # 池满后，线程的最多等待连接的时间，否则报错
        'pool_recycle': 1200,  # 多久之后对线程池中的线程进行一次连接的回收（重置）—— -1 永不回收
        'pool_pre_ping': True
    }
    
    @staticmethod
    def init_app(app):
        pass

class developmentConfig(Config):
    """开发环境配置"""
    SQLALCHEMY_DATABASE_URI = ""  # 数据库连接字符串

class ProductionConfig(Config):
    """生产环境配置"""
    SQLALCHEMY_DATABASE_URI = ""  # 数据库连接字符串

config = {
    'development': DevelopmentConfig,
    'production': ProductionConfig
}

然后，在app中加载配置信息：

from flask_sqlalchemy import SQLAlchemy


app = Flask(__name__)
conf = config['development']
app.config.from_object(conf)

db = SQLAlchemy()
db.init_app(app)

之后，即可在功能模块中通过conn = db.session()建立连接并执行增删改查操作。

一般情况下，对于MySQL、SQLite等数据库，可以通过SQLALCHEMY_ENGINE_OPTIONS 来配置数据库连接池。默认情况下，flask_sqlalchemy通过调用sqlalchemy.create_engine方法创建连接。

查阅flask_sqlalchemy源码和SQLAlchemy 官方文档，可以发现，flask_sqlalchemy 在create_engine时，有如下注释：

# flask_sqlalchemy.__init__.py 1091
    def create_engine(self, sa_url, engine_opts):
        """
            Override this method to have final say over how the SQLAlchemy engine
            is created.

            In most cases, you will want to use ``'SQLALCHEMY_ENGINE_OPTIONS'``
            config variable or set ``engine_options`` for :func:`SQLAlchemy`.
        """
        return sqlalchemy.create_engine(sa_url, **engine_opts)

注释中提到：create_engine这个方法可以被覆盖，这取决于最终如何创建SQLAlchemy engine。大部分情况下，可以通过配置变量SQLALCHEMY_ENGINE_OPTIONS来实现。其中，sa_url即连接字符串。sqlalchemy根据连接字符串，解析出数据库类型，并调用不同的方法实现数据库连接。

通过engine创建数据库连接及使用示例：

with engine.connect() as connection:
    connection.execute(text("insert into table values ('foo')"))
    connection.commit()

连接Oracle数据库时发生的问题

但是假如使用Oracle数据库及cx_Oracle方式（数据库连接字符串以oracle+cx_oracle://开头），则以上配置方法往往无法使得数据库连接池生效，导致经过一段时间后，session失效，项目请求后台无响应。报错信息如下：

ERROR 2022-01-11 21:04:03 base.py[240]: Exception closing connection <cx_Oracle.Connection to ...db_connect_string>
Traceback (most recent call last):
  File "/.../flask_admin/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 238, in _close_connection
    self._dialect.do_close(connection)
  File "/.../flask_admin/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 673, in do_close
    dbapi_connection.close()
cx_Oracle.DatabaseError: DPI-1080: connection was closed by ORA-3113

报错的代码：

    def _close_connection(self, connection):
        self.logger.debug("Closing connection %r", connection)

        try:
            self._dialect.do_close(connection)  # 报错代码行，在关闭连接时抛出异常
        except Exception:
            self.logger.error(
                "Exception closing connection %r", connection, exc_info=True
            )

推测为Oracle数据库连接已中断，但sqlalchemy在超过pool_recycle配置的时间时试图关闭连接，从而发生异常。
无论如何调整 SQLALCHEMY_ENGINE_OPTIONS配置项，都无法解决此问题。

改用cx_oracle.SessionPool自有连接池

然后，阅读sqlalchemy源码，并从中找到了如下注释：

# sqlalchemy/dialects/oracle/cx_oracle.py 98
"""
Using cx_Oracle SessionPool
---------------------------

The cx_Oracle library provides its own connectivity services that may be
used in place of SQLAlchemy's pooling functionality.    This can be achieved
by using the :paramref:`_sa.create_engine.creator` parameter to provide a
function that returns a new connection, along with setting
:paramref:`_sa.create_engine.pool_class` to ``NullPool`` to disable
SQLAlchemy's pooling::

    import cx_Oracle
    from sqlalchemy import create_engine
    from sqlalchemy.pool import NullPool

    pool = cx_Oracle.SessionPool(
        user="scott", password="tiger", dsn="oracle1120",
        min=2, max=5, increment=1, threaded=True
    )

    engine = create_engine("oracle://", creator=pool.acquire, poolclass=NullPool)

The above engine may then be used normally where cx_Oracle's pool handles
connection pooling::

    with engine.connect() as conn:
        print(conn.scalar("select 1 FROM dual"))

"""

其中提到了cx_oracle 自有连接池功能，并给出了示例代码。同时，若使用其自有连接池功能，应设置sqlalchemy的poolclass为NullPool，即无连接池模式。简单来说，就是不使用sqlalchemy的连接池，转而使用cx_oracle.SessionPool连接池。

因此，通过覆盖 create_engine方法，可实现此功能：

class OracleSqlAlchemy(SQLAlchemy):
    def create_engine(self, sa_url, engine_opts):
        import cx_Oracle
        from sqlalchemy import create_engine
        from sqlalchemy.pool import NullPool
        from sqlalchemy.engine import url as _url

        # create url.URL object
        u = _url.make_url(sa_url)
        pool = cx_Oracle.SessionPool(
            user=u.username, password=u.password, dsn=f'{u.host}:{u.port}/{u.database}',
            min=2, max=5, increment=1,
            timeout=1200,  # 闲置会话关闭前的等待时间
            getmode=cx_Oracle.SPOOL_ATTRVAL_TIMEDWAIT,
            wait_timeout=5,  # 池满后创建新会话的等待时间
            threaded=True
        )

        engine = create_engine("oracle://", creator=pool.acquire, poolclass=NullPool)
        return engine

然后，在app初始化时，用如下方式初始化db连接：

db = OracleSqlAlchemy()
db.init_app(app)

经过实际测试，flask项目不再发生后台无响应的情况。