(airflow) branch main updated: Cache DbApiHook.inspector to avoid creating N engines (#62594)

[kaxilnaik]

This is an automated email from the ASF dual-hosted git repository.

kaxilnaik pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/airflow.git


The following commit(s) were added to refs/heads/main by this push:
     new 442a9a83aa1 Cache DbApiHook.inspector to avoid creating N engines 
(#62594)
442a9a83aa1 is described below

commit 442a9a83aa1deaf84dd7653ea0e6513893a91974
Author: Kaxil Naik <kaxiln...@apache.org>
AuthorDate: Fri Feb 27 22:44:27 2026 +0000

    Cache DbApiHook.inspector to avoid creating N engines (#62594)
    
    DbApiHook.inspector was a @property that called
    get_sqlalchemy_engine() on every access. Each call creates a new
    SQLAlchemy engine with its own connection pool. When inspecting
    multiple tables (e.g., GenericTransfer, SQLColumnCheckOperator),
    this created N engines instead of reusing one.
    
    Change @property to @cached_property so the inspector (and its
    underlying engine) is created once per hook instance. The hook is
    scoped to a single task execution, so the cache lifetime is
    appropriate. Update the .pyi stub to match.
---
 .../sql/src/airflow/providers/common/sql/hooks/sql.py       |  2 +-
 .../sql/src/airflow/providers/common/sql/hooks/sql.pyi      |  2 +-
 .../common/sql/tests/unit/common/sql/hooks/test_sql.py      | 13 ++++++++++++-
 3 files changed, 14 insertions(+), 3 deletions(-)

diff --git a/providers/common/sql/src/airflow/providers/common/sql/hooks/sql.py 
b/providers/common/sql/src/airflow/providers/common/sql/hooks/sql.py
index db62736c78a..b890f63ed79 100644
--- a/providers/common/sql/src/airflow/providers/common/sql/hooks/sql.py
+++ b/providers/common/sql/src/airflow/providers/common/sql/hooks/sql.py
@@ -336,7 +336,7 @@ class DbApiHook(BaseHook):
         self.log.debug("engine_kwargs: %s", engine_kwargs)
         return create_engine(url=url, **engine_kwargs)
 
-    @property
+    @cached_property
     def inspector(self) -> Inspector:
         if inspect is None:
             raise AirflowOptionalProviderFeatureException(
diff --git 
a/providers/common/sql/src/airflow/providers/common/sql/hooks/sql.pyi 
b/providers/common/sql/src/airflow/providers/common/sql/hooks/sql.pyi
index 931ccd1c780..ebdcd58cf66 100644
--- a/providers/common/sql/src/airflow/providers/common/sql/hooks/sql.pyi
+++ b/providers/common/sql/src/airflow/providers/common/sql/hooks/sql.pyi
@@ -95,7 +95,7 @@ class DbApiHook(BaseHook):
     @property
     def sqlalchemy_url(self) -> URL: ...
     def get_sqlalchemy_engine(self, engine_kwargs: Incomplete | None = None) 
-> Engine: ...
-    @property
+    @cached_property
     def inspector(self) -> Inspector: ...
     @cached_property
     def dialect_name(self) -> str: ...
diff --git a/providers/common/sql/tests/unit/common/sql/hooks/test_sql.py 
b/providers/common/sql/tests/unit/common/sql/hooks/test_sql.py
index a52901fec67..e0c9c854a09 100644
--- a/providers/common/sql/tests/unit/common/sql/hooks/test_sql.py
+++ b/providers/common/sql/tests/unit/common/sql/hooks/test_sql.py
@@ -20,7 +20,7 @@ from __future__ import annotations
 
 import inspect
 import logging
-from unittest.mock import MagicMock
+from unittest.mock import MagicMock, patch
 
 import pandas as pd
 import polars as pl
@@ -332,3 +332,14 @@ class TestDbApiHook:
         else:
             df = dbapi_hook.get_df("SQL", df_type=df_type)
             assert isinstance(df, expected_type)
+
+
+def test_inspector_is_cached():
+    """inspector should return the same object on repeated access (not create 
N engines)."""
+    hook = DBApiHookForTests(conn_id=DEFAULT_CONN_ID)
+    mock_engine = MagicMock()
+    with patch.object(hook, "get_sqlalchemy_engine", return_value=mock_engine) 
as mock_get_engine:
+        inspector1 = hook.inspector
+        inspector2 = hook.inspector
+        assert inspector1 is inspector2
+        mock_get_engine.assert_called_once()