SQLAlchemy pre-ping 機制

Connection Pool 是資料庫連線管理的機制，用來節省資料庫連線的成本，達到較高的資源利用效益。 Connection Pool 會事先與資料庫一次建立多個連線(connection)，並維持住這些連線，每當應用程式(Application)需要操作資料庫時，就會從 Connection Pool 中提供(checkout)連線給應用程式使用。

但是資料庫連線也有斷線的可能，那麼要如何確保從 Connection Pool 取得的連線是可用的呢？

其中一種確定連線可用的機制就是 Pre-ping 。

本文就來談談 Pre-ping 與 SQLAlchemy 的 Pre-ping 如何設定。

本文環境 #

• MySQL 5.7
• Python 3.6.5
• SQLAlchemy 1.3.3

$ pip install SQLAlchemy

Pre-ping #

Pre-ping 說來簡單，就是每次從 Connection Pool 取得連線時，就試著執行一次相當於 SELECT 1 的 SQL ，如果有問題，就可以重新建立新的連線取代失效的連線。

值得注意的是雖然 Pre-ping 可以確保從 Connection Pool 取得的連線可用，但是 Pre-ping 並無法解決執行資料庫操作到一半連線中斷的問題。

SQLAlchemy #

SQLAlchemy 的 pre-ping 在 create_engine() 時設定，預設是沒有開啟 pre-ping 的，得加上參數 pool_pre_ping=True 才行：

engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/db", pool_pre_ping=True)
session = sessionmaker(bind=engine)()
session.execute('SELECT 1 + 1')
session.commit()

順便一提的是， pre-ping 的 log 並不會顯示在 SQLAlchemy 的 log 紀錄中。

the “SELECT 1” emitted by “pre-ping” is invoked within the scope of the connection pool / dialect, using a very short codepath for minimal Python latency. As such, this statement is not logged in the SQL echo output, and will not show up in SQLAlchemy’s engine logging.

Flask-SQLAlchemy #

同場加映，如果是利用 Flask 開發 Web 應用的開發者們，應該都會使用 Flask-SQLAlchemy 套件進行開發。

如果想要針對該套件開啟 pre-ping 功能的話，就得自行將該套件提供的 SQLAlchemy 類別再包裝一層：

from flask_sqlalchemy import SQLAlchemy as _BaseSQLAlchemy


class SQLAlchemy(_BaseSQLAlchemy):
    def apply_pool_defaults(self, app, options):
        super(SQLAlchemy, self).apply_pool_defaults(app, options)
        options['pool_pre_ping'] = True


db = SQLAlchemy()

以上！

Happy Coding!

References #

https://docs.sqlalchemy.org/en/13/core/engines.html#sqlalchemy.create_engine.params.pool_pre_ping