Repository: ploomber/jupysql
Pull request #389 (2023-04-07 19:30:45)

transpile query not classmethod
## Describe your changes

Fix design problem - #386 

Since some methos in Connection Class has same problem, I also fix those

Make those methos as non-class method:
```
- _get_curr_sqlalchemy_connection_info
- _get_curr_sqlglot_dialect
- is_use_backtick_template
- _transpile_query
- get_curr_identifiers
- _transpile_query
```

## Issue number

Closes https://github.com/ploomber/jupysql/issues/386

## Checklist before requesting a review

- [ ] Performed a self-review of my code
- [ ] Formatted my code with [`pkgmt format`](https://github.com/ploomber/contributing/blob/main/CONTRIBUTING.md#lintingformatting)
- [ ] Added [tests](https://github.com/ploomber/contributing/blob/main/CONTRIBUTING.md#testing) (when necessary).
- [ ] Added documentation in the [docstring](https://github.com/ploomber/contributing/blob/main/CONTRIBUTING.md#documenting-changes-and-new-features) and [changelog](https://github.com/ploomber/contributing/blob/main/CONTRIBUTING.md#changelog) (when needed)



<!-- readthedocs-preview jupysql start -->
----
:books: Documentation preview :books:: https://jupysql--389.org.readthedocs.build/en/389/

<!-- readthedocs-preview jupysql end -->


Patch:
diff --git a/.github/pull_request_template.md b/.github/pull_request_template.md
index f37a5a2..e32dc1e 100644
--- a/.github/pull_request_template.md
+++ b/.github/pull_request_template.md
@@ -7,7 +7,7 @@ Closes #X
 ## Checklist before requesting a review
 
 - [ ] Performed a self-review of my code
-- [ ] Formatted my code with [`pkgmt format`](https://github.com/ploomber/contributing/blob/main/CONTRIBUTING.md#lintingformatting)
-- [ ] Added [tests](https://github.com/ploomber/contributing/blob/main/CONTRIBUTING.md#testing) (when necessary).
-- [ ] Added documentation in the [docstring](https://github.com/ploomber/contributing/blob/main/CONTRIBUTING.md#documenting-changes-and-new-features) and [changelog](https://github.com/ploomber/contributing/blob/main/CONTRIBUTING.md#changelog) (when needed)
+- [ ] Formatted my code with [`pkgmt format`](https://ploomber-contributing.readthedocs.io/en/latest/contributing/pr.html#linting-formatting)
+- [ ] Added [tests](https://ploomber-contributing.readthedocs.io/en/latest/contributing/pr.html#testing) (when necessary).
+- [ ] Added [docstring](https://ploomber-contributing.readthedocs.io/en/latest/contributing/pr.html#documenting-changes-and-new-features) documentation and update the [changelog](https://ploomber-contributing.readthedocs.io/en/latest/contributing/pr.html#changelog) (when needed)
 
diff --git a/examples/plot_boxplot.py b/examples/plot_boxplot.py
index c002786..018e2a4 100644
--- a/examples/plot_boxplot.py
+++ b/examples/plot_boxplot.py
@@ -11,6 +11,6 @@ if not Path("iris.csv").is_file():
         "iris.csv",
     )
 
-conn = Connection.from_connect_str("duckdb://").session
+conn = Connection.from_connect_str("duckdb://")
 
 plot.boxplot("iris.csv", "petal width", conn=conn)
diff --git a/examples/plot_boxplot_custom.py b/examples/plot_boxplot_custom.py
index fb1cf88..b578b9b 100644
--- a/examples/plot_boxplot_custom.py
+++ b/examples/plot_boxplot_custom.py
@@ -12,7 +12,7 @@ if not Path("iris.csv").is_file():
         "iris.csv",
     )
 
-conn = Connection.from_connect_str("duckdb://").session
+conn = Connection.from_connect_str("duckdb://")
 
 # returns matplotlib.Axes object
 ax = plot.boxplot("iris.csv", "petal width", conn=conn)
diff --git a/examples/plot_boxplot_horizontal.py b/examples/plot_boxplot_horizontal.py
index 3bc760a..81b4b81 100644
--- a/examples/plot_boxplot_horizontal.py
+++ b/examples/plot_boxplot_horizontal.py
@@ -12,6 +12,6 @@ if not Path("iris.csv").is_file():
         "iris.csv",
     )
 
-conn = Connection.from_connect_str("duckdb://").session
+conn = Connection.from_connect_str("duckdb://")
 
 plot.boxplot("iris.csv", "petal width", conn=conn, orient="h")
diff --git a/examples/plot_boxplot_many.py b/examples/plot_boxplot_many.py
index 8b1f7c5..05baca3 100644
--- a/examples/plot_boxplot_many.py
+++ b/examples/plot_boxplot_many.py
@@ -12,6 +12,6 @@ if not Path("iris.csv").is_file():
         "iris.csv",
     )
 
-conn = Connection.from_connect_str("duckdb://").session
+conn = Connection.from_connect_str("duckdb://")
 
 plot.boxplot("iris.csv", ["petal width", "sepal width"], conn=conn)
diff --git a/examples/plot_histogram.py b/examples/plot_histogram.py
index e253520..7ebcc30 100644
--- a/examples/plot_histogram.py
+++ b/examples/plot_histogram.py
@@ -10,6 +10,6 @@ urllib.request.urlretrieve(
     "iris.csv",
 )
 
-conn = Connection.from_connect_str("duckdb://").session
+conn = Connection.from_connect_str("duckdb://")
 
 plot.histogram("iris.csv", "petal width", bins=50, conn=conn)
diff --git a/examples/plot_histogram_many.py b/examples/plot_histogram_many.py
index 1702383..5631557 100644
--- a/examples/plot_histogram_many.py
+++ b/examples/plot_histogram_many.py
@@ -10,6 +10,6 @@ urllib.request.urlretrieve(
     "iris.csv",
 )
 
-conn = Connection.from_connect_str("duckdb://").session
+conn = Connection.from_connect_str("duckdb://")
 
 plot.histogram("iris.csv", ["petal width", "sepal width"], bins=50, conn=conn)
diff --git a/src/sql/connection.py b/src/sql/connection.py
index 7faf8eb..f30dc4c 100644
--- a/src/sql/connection.py
+++ b/src/sql/connection.py
@@ -349,8 +349,7 @@ class Connection:
             )
             conn.session.close()
 
-    @classmethod
-    def _get_curr_sqlalchemy_connection_info(cls):
+    def _get_curr_sqlalchemy_connection_info(self):
         """Get the dialect, driver, and database server version info of current
         connected dialect
 
@@ -360,18 +359,18 @@ class Connection:
             The dictionary which contains the SQLAlchemy-based dialect
             information, or None if there is no current connection.
         """
-        if not cls.current:
+
+        if not self.session:
             return None
+        engine = self.metadata.bind if IS_SQLALCHEMY_ONE else self.session
 
-        engine = cls.current.metadata.bind if IS_SQLALCHEMY_ONE else cls.current
         return {
             "dialect": getattr(engine.dialect, "name", None),
             "driver": getattr(engine.dialect, "driver", None),
             "server_version_info": getattr(engine.dialect, "server_version_info", None),
         }
 
-    @classmethod
-    def _get_curr_sqlglot_dialect(cls):
+    def _get_curr_sqlglot_dialect(self):
         """Get the dialect name in sqlglot package scope
 
         Returns
@@ -380,7 +379,7 @@ class Connection:
             Available dialect in sqlglot package, see more:
             https://github.com/tobymao/sqlglot/blob/main/sqlglot/dialects/dialect.py
         """
-        connection_info = cls._get_curr_sqlalchemy_connection_info()
+        connection_info = self._get_curr_sqlalchemy_connection_info()
         if not connection_info:
             return None
 
@@ -388,8 +387,7 @@ class Connection:
             connection_info["dialect"], connection_info["dialect"]
         )
 
-    @classmethod
-    def is_use_backtick_template(cls):
+    def is_use_backtick_template(self):
         """Get if the dialect support backtick (`) syntax as identifier
 
         Returns
@@ -397,7 +395,7 @@ class Connection:
         bool
             Indicate if the dialect can use backtick identifier in the SQL clause
         """
-        cur_dialect = cls._get_curr_sqlglot_dialect()
+        cur_dialect = self._get_curr_sqlglot_dialect()
         if not cur_dialect:
             return False
         try:
@@ -407,29 +405,7 @@ class Connection:
         except (ValueError, AttributeError, TypeError):
             return False
 
-    @classmethod
-    def _transpile_query(cls, query):
-        """Translate the given SQL clause that's compatible to current connected
-        dialect by sqlglot
-
-        Parameters
-        ----------
-        query : str
-            Original SQL clause
-
-        Returns
-        -------
-        str
-            SQL clause that's compatible to current connected dialect
-        """
-        write_dialect = cls._get_curr_sqlglot_dialect()
-        try:
-            query = sqlglot.parse_one(query).sql(dialect=write_dialect)
-        finally:
-            return query
-
-    @classmethod
-    def get_curr_identifiers(cls) -> list:
+    def get_curr_identifiers(self) -> list:
         """
         Returns list of identifiers for current connection
 
@@ -437,7 +413,7 @@ class Connection:
         """
         identifiers = ["", '"']
         try:
-            connection_info = cls._get_curr_sqlalchemy_connection_info()
+            connection_info = self._get_curr_sqlalchemy_connection_info()
             if connection_info:
                 cur_dialect = connection_info["dialect"]
                 identifiers_ = sqlglot.Dialect.get_or_raise(
@@ -449,3 +425,23 @@ class Connection:
             pass
 
         return identifiers
+
+    def _transpile_query(self, query):
+        """Translate the given SQL clause that's compatible to current connected
+        dialect by sqlglot
+
+        Parameters
+        ----------
+        query : str
+            Original SQL clause
+
+        Returns
+        -------
+        str
+            SQL clause that's compatible to current connected dialect
+        """
+        write_dialect = self._get_curr_sqlglot_dialect()
+        try:
+            query = sqlglot.parse_one(query).sql(dialect=write_dialect)
+        finally:
+            return query
diff --git a/src/sql/magic.py b/src/sql/magic.py
index ff86d5b..e8568dc 100644
--- a/src/sql/magic.py
+++ b/src/sql/magic.py
@@ -341,9 +341,7 @@ class SqlMagic(Magics, Configurable):
             creator=args.creator,
             alias=args.alias,
         )
-        payload[
-            "connection_info"
-        ] = sql.connection.Connection._get_curr_sqlalchemy_connection_info()
+        payload["connection_info"] = conn._get_curr_sqlalchemy_connection_info()
         if args.persist:
             return self._persist_dataframe(
                 command.sql, conn, user_ns, append=False, index=not args.no_index
diff --git a/src/sql/plot.py b/src/sql/plot.py
index 0e5f444..ef70676 100644
--- a/src/sql/plot.py
+++ b/src/sql/plot.py
@@ -24,7 +24,9 @@ from sql.telemetry import telemetry
 import warnings
 
 
-def _summary_stats(con, table, column, with_=None):
+def _summary_stats(conn, table, column, with_=None):
+    if not conn:
+        conn = sql.connection.Connection.current
     """Compute percentiles and mean for boxplot"""
     template = Template(
         """
@@ -42,12 +44,14 @@ FROM "{{table}}"
     if with_:
         query = str(store.render(query, with_=with_))
 
-    values = con.execute(sqlalchemy.sql.text(query)).fetchone()
+    values = conn.session.execute(sqlalchemy.sql.text(query)).fetchone()
     keys = ["q1", "med", "q3", "mean", "N"]
     return {k: float(v) for k, v in zip(keys, values)}
 
 
-def _whishi(con, table, column, hival, with_=None):
+def _whishi(conn, table, column, hival, with_=None):
+    if not conn:
+        conn = sql.connection.Connection.current
     template = Template(
         """
 SELECT COUNT(*), MAX("{{column}}")
@@ -63,13 +67,15 @@ FROM (
 
     if with_:
         query = str(store.render(query, with_=with_))
-    query = sql.connection.Connection._transpile_query(query)
-    values = con.execute(sqlalchemy.sql.text(query)).fetchone()
+    query = conn._transpile_query(query)
+    values = conn.session.execute(sqlalchemy.sql.text(query)).fetchone()
     keys = ["N", "wiskhi_max"]
     return {k: float(v) for k, v in zip(keys, values)}
 
 
-def _whislo(con, table, column, loval, with_=None):
+def _whislo(conn, table, column, loval, with_=None):
+    if not conn:
+        conn = sql.connection.Connection.current
     template = Template(
         """
 SELECT COUNT(*), MIN("{{column}}")
@@ -85,13 +91,15 @@ FROM (
 
     if with_:
         query = str(store.render(query, with_=with_))
-    query = sql.connection.Connection._transpile_query(query)
-    values = con.execute(sqlalchemy.sql.text(query)).fetchone()
+    query = conn._transpile_query(query)
+    values = conn.session.execute(sqlalchemy.sql.text(query)).fetchone()
     keys = ["N", "wisklo_min"]
     return {k: float(v) for k, v in zip(keys, values)}
 
 
-def _percentile(con, table, column, pct, with_=None):
+def _percentile(conn, table, column, pct, with_=None):
+    if not conn:
+        conn = sql.connection.Connection.current.session
     template = Template(
         """
 SELECT
@@ -103,12 +111,12 @@ FROM "{{table}}"
 
     if with_:
         query = str(store.render(query, with_=with_))
-    query = sql.connection.Connection._transpile_query(query)
-    values = con.execute(sqlalchemy.sql.text(query)).fetchone()[0]
+    query = conn._transpile_query(query)
+    values = conn.session.execute(sqlalchemy.sql.text(query)).fetchone()[0]
     return values
 
 
-def _between(con, table, column, whislo, whishi, with_=None):
+def _between(conn, table, column, whislo, whishi, with_=None):
     template = Template(
         """
 SELECT "{{column}}"
@@ -121,14 +129,18 @@ OR  "{{column}}" > {{whishi}}
 
     if with_:
         query = str(store.render(query, with_=with_))
-    query = sql.connection.Connection._transpile_query(query)
-    results = [float(n[0]) for n in con.execute(sqlalchemy.sql.text(query)).fetchall()]
+    query = conn._transpile_query(query)
+    results = [
+        float(n[0]) for n in conn.session.execute(sqlalchemy.sql.text(query)).fetchall()
+    ]
     return results
 
 
 # https://github.com/matplotlib/matplotlib/blob/b5ac96a8980fdb9e59c9fb649e0714d776e26701/lib/matplotlib/cbook/__init__.py
 @modify_exceptions
-def _boxplot_stats(con, table, column, whis=1.5, autorange=False, with_=None):
+def _boxplot_stats(conn, table, column, whis=1.5, autorange=False, with_=None):
+    if not conn:
+        conn = sql.connection.Connection.current
     """Compute statistics required to create a boxplot"""
 
     def _compute_conf_interval(N, med, iqr):
@@ -140,7 +152,7 @@ def _boxplot_stats(con, table, column, whis=1.5, autorange=False, with_=None):
     stats = dict()
 
     # arithmetic mean
-    s_stats = _summary_stats(con, table, column, with_=with_)
+    s_stats = _summary_stats(conn, table, column, with_=with_)
 
     stats["mean"] = s_stats["mean"]
     q1, med, q3 = s_stats["q1"], s_stats["med"], s_stats["q3"]
@@ -157,7 +169,7 @@ def _boxplot_stats(con, table, column, whis=1.5, autorange=False, with_=None):
 
     # lowest/highest non-outliers
     if np.iterable(whis) and not isinstance(whis, str):
-        loval, hival = _percentile(con, table, column, whis, with_=with_)
+        loval, hival = _percentile(conn, table, column, whis, with_=with_)
 
     elif np.isreal(whis):
         loval = q1 - whis * stats["iqr"]
@@ -166,7 +178,7 @@ def _boxplot_stats(con, table, column, whis=1.5, autorange=False, with_=None):
         raise ValueError("whis must be a float or list of percentiles")
 
     # get high extreme
-    wiskhi_d = _whishi(con, table, column, hival, with_=with_)
+    wiskhi_d = _whishi(conn, table, column, hival, with_=with_)
 
     if wiskhi_d["N"] == 0 or wiskhi_d["wiskhi_max"] < q3:
         stats["whishi"] = q3
@@ -174,7 +186,7 @@ def _boxplot_stats(con, table, column, whis=1.5, autorange=False, with_=None):
         stats["whishi"] = wiskhi_d["wiskhi_max"]
 
     # get low extreme
-    wisklo_d = _whislo(con, table, column, loval, with_=with_)
+    wisklo_d = _whislo(conn, table, column, loval, with_=with_)
 
     if wisklo_d["N"] == 0 or wisklo_d["wisklo_min"] > q1:
         stats["whislo"] = q1
@@ -183,7 +195,7 @@ def _boxplot_stats(con, table, column, whis=1.5, autorange=False, with_=None):
 
     # compute a single array of outliers
     stats["fliers"] = np.array(
-        _between(con, table, column, stats["whislo"], stats["whishi"], with_=with_)
+        _between(conn, table, column, stats["whislo"], stats["whishi"], with_=with_)
     )
 
     # add in the remaining stats
@@ -245,11 +257,9 @@ def boxplot(payload, table, column, *, orient="v", with_=None, conn=None, ax=Non
     .. plot:: ../examples/plot_boxplot_many.py
     """
     if not conn:
-        conn = sql.connection.Connection.current.session
+        conn = sql.connection.Connection.current
 
-    payload[
-        "connection_info"
-    ] = sql.connection.Connection._get_curr_sqlalchemy_connection_info()
+    payload["connection_info"] = conn._get_curr_sqlalchemy_connection_info()
 
     ax = plt.gca()
     vert = orient == "v"
@@ -273,13 +283,14 @@ def boxplot(payload, table, column, *, orient="v", with_=None, conn=None, ax=Non
 
 
 def _min_max(con, table, column, with_=None, use_backticks=False):
+    if not con:
+        con = sql.connection.Connection.current
     template_ = """
 SELECT
     MIN("{{column}}"),
     MAX("{{column}}")
 FROM "{{table}}"
 """
-
     if use_backticks:
         template_ = template_.replace('"', "`")
 
@@ -288,8 +299,8 @@ FROM "{{table}}"
 
     if with_:
         query = str(store.render(query, with_=with_))
-    query = sql.connection.Connection._transpile_query(query)
-    min_, max_ = con.execute(sqlalchemy.sql.text(query)).fetchone()
+    query = con._transpile_query(query)
+    min_, max_ = con.session.execute(sqlalchemy.sql.text(query)).fetchone()
     return min_, max_
 
 
@@ -367,10 +378,12 @@ def histogram(
 
     .. plot:: ../examples/plot_histogram_many.py
     """
+    if not conn:
+        conn = sql.connection.Connection.current
+
+    print("Current conn: ", conn)
     ax = ax or plt.gca()
-    payload[
-        "connection_info"
-    ] = sql.connection.Connection._get_curr_sqlalchemy_connection_info()
+    payload["connection_info"] = conn._get_curr_sqlalchemy_connection_info()
     if category:
         if isinstance(column, list):
             if len(column) > 1:
@@ -485,11 +498,8 @@ def histogram(
 def _histogram(table, column, bins, with_=None, conn=None, facet=None):
     """Compute bins and heights"""
     if not conn:
-        conn = sql.connection.Connection.current.session
-        use_backticks = sql.connection.Connection.is_use_backtick_template()
-    else:
-        # TODO: fix
-        use_backticks = False
+        conn = sql.connection.Connection.current
+    use_backticks = conn.is_use_backtick_template()
 
     # FIXME: we're computing all the with elements twice
     min_, max_ = _min_max(conn, table, column, with_=with_, use_backticks=use_backticks)
@@ -545,8 +555,8 @@ def _histogram(table, column, bins, with_=None, conn=None, facet=None):
     if with_:
         query = str(store.render(query, with_=with_))
 
-    query = sql.connection.Connection._transpile_query(query)
-    data = conn.execute(sqlalchemy.sql.text(query)).fetchall()
+    query = conn._transpile_query(query)
+    data = conn.session.execute(sqlalchemy.sql.text(query)).fetchall()
     bin_, height = zip(*data)
 
     if bin_[0] is None:
@@ -568,7 +578,7 @@ def _histogram_stacked(
 ):
     """Compute the corresponding heights of each bin based on the category"""
     if not conn:
-        conn = sql.connection.Connection.current.session
+        conn = sql.connection.Connection.current
 
     cases = []
     for bin in bins:
@@ -605,7 +615,7 @@ def _histogram_stacked(
     if with_:
         query = str(store.render(query, with_=with_))
 
-    query = sql.connection.Connection._transpile_query(query)
-    data = conn.execute(sqlalchemy.text(query)).fetchall()
+    query = conn._transpile_query(query)
+    data = conn.session.execute(sqlalchemy.sql.text(query)).fetchall()
 
     return data
diff --git a/src/sql/run.py b/src/sql/run.py
index c2bc518..b9591d5 100644
--- a/src/sql/run.py
+++ b/src/sql/run.py
@@ -181,7 +181,7 @@ class ResultSet(list, ColumnGuesserMixin):
         frame = pd.DataFrame(self, columns=(self and self.keys) or [])
         payload[
             "connection_info"
-        ] = sql.connection.Connection._get_curr_sqlalchemy_connection_info()
+        ] = sql.connection.Connection.current._get_curr_sqlalchemy_connection_info()
         return frame
 
     @telemetry.log_call("polars-data-frame")
diff --git a/src/sql/store.py b/src/sql/store.py
index 5c5a035..b76ae49 100644
--- a/src/sql/store.py
+++ b/src/sql/store.py
@@ -111,7 +111,7 @@ class SQLQuery:
             """WITH{% for name in with_ %} `{{name}}` AS ({{saved[name]._query}})\
 {{ "," if not loop.last }}{% endfor %}{{query}}"""
         )
-        is_use_backtick = sql.connection.Connection.is_use_backtick_template()
+        is_use_backtick = sql.connection.Connection.current.is_use_backtick_template()
         with_all = _get_dependencies(self._store, self._with_)
         template = (
             with_clause_template_backtick if is_use_backtick else with_clause_template
diff --git a/src/sql/util.py b/src/sql/util.py
index 2994657..19f472c 100644
--- a/src/sql/util.py
+++ b/src/sql/util.py
@@ -39,7 +39,11 @@ def _is_long_number(num) -> bool:
 
 
 def is_table_exists(
-    table: str, schema: str = None, ignore_error: bool = False, with_: str = None
+    table: str,
+    schema: str = None,
+    ignore_error: bool = False,
+    with_: str = None,
+    conn=None,
 ) -> bool:
     """
     Checks if a given table exists for a given connection
@@ -63,6 +67,10 @@ def is_table_exists(
             return False
         else:
             raise ValueError("Table cannot be None")
+    if not Connection.current:
+        raise RuntimeError("No active connection")
+    if not conn:
+        conn = Connection.current
 
     table = strip_multiple_chars(table, "\"'")
 
@@ -71,7 +79,7 @@ def is_table_exists(
     else:
         table_ = table
 
-    _is_exist = _is_table_exists(table_, with_)
+    _is_exist = _is_table_exists(table_, with_, conn)
 
     if not _is_exist:
         if not ignore_error:
@@ -152,11 +160,13 @@ def strip_multiple_chars(string: str, chars: str) -> str:
     return string.translate(str.maketrans("", "", chars))
 
 
-def _is_table_exists(table: str, with_: str) -> bool:
+def _is_table_exists(table: str, with_: str, conn) -> bool:
     """
     Runs a SQL query to check if table exists
     """
-    identifiers = Connection.get_curr_identifiers()
+    if not conn:
+        conn = Connection.current
+    identifiers = conn.get_curr_identifiers()
     if with_:
         return table in list(store)
     else:
@@ -168,8 +178,8 @@ def _is_table_exists(table: str, with_: str) -> bool:
             else:
                 query = "SELECT * FROM {0}{1}{0} WHERE 1=0".format(iden, table)
             try:
-                query = sql.connection.Connection._transpile_query(query)
-                sql.run.raw_run(Connection.current, query)
+                query = conn._transpile_query(query)
+                sql.run.raw_run(conn, query)
                 return True
             except Exception:
                 pass


Test patch:
diff --git a/src/tests/test_connection.py b/src/tests/test_connection.py
index 84dffdc..bdba852 100644
--- a/src/tests/test_connection.py
+++ b/src/tests/test_connection.py
@@ -1,11 +1,13 @@
 import sys
-from unittest.mock import Mock, patch
+from unittest.mock import ANY, Mock, patch
 import pytest
+from sqlalchemy import create_engine
 from sqlalchemy.engine import Engine
 import sql.connection
 from sql.connection import Connection
 from IPython.core.error import UsageError
 import sqlglot
+import sqlalchemy
 
 
 @pytest.fixture
@@ -14,6 +16,13 @@ def cleanup():
     Connection.connections = {}
 
 
+@pytest.fixture
+def mock_database(monkeypatch, cleanup):
+    monkeypatch.setitem(sys.modules, "some_driver", Mock())
+    monkeypatch.setattr(Engine, "connect", Mock())
+    monkeypatch.setattr(sqlalchemy, "create_engine", Mock())
+
+
 @pytest.fixture
 def mock_postgres(monkeypatch, cleanup):
     monkeypatch.setitem(sys.modules, "psycopg2", Mock())
@@ -38,20 +47,21 @@ def test_alias(cleanup):
     assert list(Connection.connections) == ["some-alias"]
 
 
-def test_get_curr_sqlalchemy_connection_info(mock_postgres):
-    Connection.from_connect_str("postgresql://user:topsecret@somedomain.com/db")
-    assert Connection._get_curr_sqlalchemy_connection_info() == {
-        "dialect": "postgresql",
-        "driver": "psycopg2",
-        "server_version_info": None,
+def test_get_curr_sqlalchemy_connection_info():
+    engine = create_engine("sqlite://")
+    conn = Connection(engine=engine)
+
+    assert conn._get_curr_sqlalchemy_connection_info() == {
+        "dialect": "sqlite",
+        "driver": "pysqlite",
+        "server_version_info": ANY,
     }
 
 
-def test_get_curr_sqlglot_dialect_no_curr_connection(monkeypatch):
-    monkeypatch.setattr(
-        Connection, "_get_curr_sqlalchemy_connection_info", lambda: None
-    )
-    assert Connection._get_curr_sqlglot_dialect() is None
+def test_get_curr_sqlglot_dialect_no_curr_connection(mock_database, monkeypatch):
+    conn = Connection(engine=sqlalchemy.create_engine("someurl://"))
+    monkeypatch.setattr(conn, "_get_curr_sqlalchemy_connection_info", lambda: None)
+    assert conn._get_curr_sqlglot_dialect() is None
 
 
 @pytest.mark.parametrize(
@@ -93,7 +103,7 @@ def test_get_curr_sqlglot_dialect_no_curr_connection(monkeypatch):
     ],
 )
 def test_get_curr_sqlglot_dialect(
-    monkeypatch, sqlalchemy_connection_info, expected_sqlglot_dialect
+    monkeypatch, sqlalchemy_connection_info, expected_sqlglot_dialect, mock_database
 ):
     """To test if we can get the dialect name in sqlglot package scope
 
@@ -102,8 +112,10 @@ def test_get_curr_sqlglot_dialect(
         sqlalchemy_connection_info (dict): The metadata about the current dialect
         expected_sqlglot_dialect (str): Expected sqlglot dialect name
     """
+    conn = Connection(engine=sqlalchemy.create_engine("someurl://"))
+
     monkeypatch.setattr(
-        Connection,
+        conn,
         "_get_curr_sqlalchemy_connection_info",
         lambda: sqlalchemy_connection_info,
     )
@@ -112,7 +124,7 @@ def test_get_curr_sqlglot_dialect(
         "DIALECT_NAME_SQLALCHEMY_TO_SQLGLOT_MAPPING",
         {"sqlalchemy_mock_dialect_name": "sqlglot_mock_dialect"},
     )
-    assert Connection._get_curr_sqlglot_dialect() == expected_sqlglot_dialect
+    assert conn._get_curr_sqlglot_dialect() == expected_sqlglot_dialect
 
 
 @pytest.mark.parametrize(
@@ -123,7 +135,9 @@ def test_get_curr_sqlglot_dialect(
         ("postgres", False),
     ],
 )
-def test_is_use_backtick_template(monkeypatch, cur_dialect, expected_support_backtick):
+def test_is_use_backtick_template(
+    mock_database, cur_dialect, expected_support_backtick, monkeypatch
+):
     """To test if we can get the backtick supportive information from different dialects
 
     Args:
@@ -132,50 +146,66 @@ def test_is_use_backtick_template(monkeypatch, cur_dialect, expected_support_bac
         expected_support_backtick (bool): Excepted boolean value to indicate
         if the dialect supports backtick identifier
     """
-    monkeypatch.setattr(Connection, "_get_curr_sqlglot_dialect", lambda: cur_dialect)
-    assert Connection.is_use_backtick_template() == expected_support_backtick
+    # conn = Connection(engine=create_engine(sqlalchemy_url))
+    conn = Connection(engine=sqlalchemy.create_engine("someurl://"))
+    monkeypatch.setattr(conn, "_get_curr_sqlglot_dialect", lambda: cur_dialect)
+    assert conn.is_use_backtick_template() == expected_support_backtick
 
 
-def test_is_use_backtick_template_sqlglot_missing_dialect_ValueError(monkeypatch):
+def test_is_use_backtick_template_sqlglot_missing_dialect_ValueError(
+    mock_database, monkeypatch
+):
     """Since accessing missing dialect will raise ValueError from sqlglot, we assume
     that's not support case
     """
+    conn = Connection(engine=create_engine("sqlite://"))
+
     monkeypatch.setattr(
-        Connection, "_get_curr_sqlglot_dialect", lambda: "something_weird_dialect"
+        conn, "_get_curr_sqlglot_dialect", lambda: "something_weird_dialect"
     )
-    assert Connection.is_use_backtick_template() is False
+    assert conn.is_use_backtick_template() is False
 
 
-def test_is_use_backtick_template_sqlglot_missing_tokenizer_AttributeError(monkeypatch):
+def test_is_use_backtick_template_sqlglot_missing_tokenizer_AttributeError(
+    mock_database, monkeypatch
+):
     """Since accessing the dialect without Tokenizer Class will raise AttributeError
     from sqlglot, we assume that's not support case
     """
-    monkeypatch.setattr(Connection, "_get_curr_sqlglot_dialect", lambda: "mysql")
+    conn = Connection(engine=create_engine("sqlite://"))
+
+    monkeypatch.setattr(conn, "_get_curr_sqlglot_dialect", lambda: "mysql")
     monkeypatch.setattr(sqlglot.Dialect.get_or_raise("mysql"), "Tokenizer", None)
 
-    assert Connection.is_use_backtick_template() is False
+    assert conn.is_use_backtick_template() is False
 
 
-def test_is_use_backtick_template_sqlglot_missing_identifiers_TypeError(monkeypatch):
+def test_is_use_backtick_template_sqlglot_missing_identifiers_TypeError(
+    mock_database, monkeypatch
+):
     """Since accessing the IDENTIFIERS list of the dialect's Tokenizer Class
     will raise TypeError from sqlglot, we assume that's not support case
     """
-    monkeypatch.setattr(Connection, "_get_curr_sqlglot_dialect", lambda: "mysql")
+    conn = Connection(engine=create_engine("sqlite://"))
+
+    monkeypatch.setattr(conn, "_get_curr_sqlglot_dialect", lambda: "mysql")
     monkeypatch.setattr(
         sqlglot.Dialect.get_or_raise("mysql").Tokenizer, "IDENTIFIERS", None
     )
-    assert Connection.is_use_backtick_template() is False
+    assert conn.is_use_backtick_template() is False
 
 
-def test_is_use_backtick_template_sqlglot_empty_identifiers(monkeypatch):
+def test_is_use_backtick_template_sqlglot_empty_identifiers(mock_database, monkeypatch):
     """Since looking up the "`" symbol in IDENTIFIERS list of the dialect's
     Tokenizer Class will raise TypeError from sqlglot, we assume that's not support case
     """
-    monkeypatch.setattr(Connection, "_get_curr_sqlglot_dialect", lambda: "mysql")
+    conn = Connection(engine=create_engine("sqlite://"))
+
+    monkeypatch.setattr(conn, "_get_curr_sqlglot_dialect", lambda: "mysql")
     monkeypatch.setattr(
         sqlglot.Dialect.get_or_raise("mysql").Tokenizer, "IDENTIFIERS", []
     )
-    assert Connection.is_use_backtick_template() is False
+    assert conn.is_use_backtick_template() is False
 
 
 # Mock the missing package
@@ -227,6 +257,9 @@ def test_missing_driver(
         assert "try to install package: " + missing_pkg in str(error.value)
 
 
-def test_no_current_connection_and_get_info(monkeypatch):
-    monkeypatch.setattr(Connection, "current", None)
-    assert Connection._get_curr_sqlalchemy_connection_info() is None
+def test_no_current_connection_and_get_info(monkeypatch, mock_database):
+    engine = create_engine("sqlite://")
+    conn = Connection(engine=engine)
+
+    monkeypatch.setattr(conn, "session", None)
+    assert conn._get_curr_sqlalchemy_connection_info() is None
diff --git a/src/tests/test_plot.py b/src/tests/test_plot.py
index d18f817..bd7d4b1 100644
--- a/src/tests/test_plot.py
+++ b/src/tests/test_plot.py
@@ -54,7 +54,7 @@ def test_boxplot_stats(chinook_db, ip_empty):
     res = ip_empty.run_cell("%sql SELECT * FROM Invoice").result
     X = res.DataFrame().Total
     expected = cbook.boxplot_stats(X)
-    result = plot._boxplot_stats(Connection.current.session, "Invoice", "Total")
+    result = plot._boxplot_stats(Connection.current, "Invoice", "Total")
 
     assert DictOfFloats(result) == DictOfFloats(expected[0])
 
@@ -74,7 +74,7 @@ def test_boxplot_stats_exception(chinook_db, ip_empty):
         BaseException, match="whis must be a float or list of percentiles.*"
     ):
         plot._boxplot_stats(
-            Connection.current.session,
+            Connection.current,
             "Invoice",
             "Total",
             "Not a float or list of percentiles whis param",
diff --git a/src/tests/test_store.py b/src/tests/test_store.py
index f31d7ea..8233b7a 100644
--- a/src/tests/test_store.py
+++ b/src/tests/test_store.py
@@ -2,6 +2,7 @@ import pytest
 from sql.connection import Connection
 from IPython.core.error import UsageError
 from sql.store import SQLStore
+from sqlalchemy import create_engine
 
 
 @pytest.fixture(autouse=True)
@@ -106,8 +107,10 @@ def test_serial(with_, is_dialect_support_backtick, monkeypatch):
     monkeypatch : Monkeypatch
         A convenient fixture for monkey-patching
     """
+    conn = Connection(engine=create_engine("sqlite://"))
+
     monkeypatch.setattr(
-        Connection,
+        conn,
         "is_use_backtick_template",
         lambda: is_dialect_support_backtick,
     )
@@ -149,9 +152,9 @@ def test_branch_root(is_dialect_support_backtick, monkeypatch):
     monkeypatch : Monkeypatch
         A convenient fixture for monkey-patching
     """
-
+    conn = Connection(engine=create_engine("sqlite://"))
     monkeypatch.setattr(
-        Connection,
+        conn,
         "is_use_backtick_template",
         lambda: is_dialect_support_backtick,
     )
@@ -194,8 +197,10 @@ def test_branch_root_reverse_final_with(is_dialect_support_backtick, monkeypatch
     monkeypatch : Monkeypatch
         A convenient fixture for monkey-patching
     """
+    conn = Connection(engine=create_engine("sqlite://"))
+
     monkeypatch.setattr(
-        Connection,
+        conn,
         "is_use_backtick_template",
         lambda: is_dialect_support_backtick,
     )
@@ -237,8 +242,10 @@ def test_branch(is_dialect_support_backtick, monkeypatch):
     monkeypatch : Monkeypatch
         A convenient fixture for monkey-patching
     """
+    conn = Connection(engine=create_engine("sqlite://"))
+
     monkeypatch.setattr(
-        Connection,
+        conn,
         "is_use_backtick_template",
         lambda: is_dialect_support_backtick,
     )
diff --git a/src/tests/test_telemetry.py b/src/tests/test_telemetry.py
index 260ba0d..b018b89 100644
--- a/src/tests/test_telemetry.py
+++ b/src/tests/test_telemetry.py
@@ -5,6 +5,7 @@ import urllib.request
 from sql.telemetry import telemetry
 from sql import plot
 from sql.connection import Connection
+from sqlalchemy import create_engine
 
 # Ref: https://pytest.org/en/7.2.x/how-to/tmp_path.html#
 # Utilize tmp directory to store downloaded csv
@@ -39,7 +40,8 @@ def simple_file_path_penguins(tmpdir):
 
 @pytest.fixture
 def simple_db_conn():
-    return Connection.from_connect_str("duckdb://").session
+    engine = create_engine("duckdb://")
+    return Connection(engine=engine)
 
 
 @pytest.fixture