Repository: ploomber/jupysql (python)

Issue:
switch to conf.py to fix binder links from PRs
in sklearn-evaluation, we switched to using [conf.py](https://github.com/ploomber/sklearn-evaluation/tree/master/docs) instead of `_config.yml`; since the former allows us to embed logic necessary to customize the binder links when the docs are generated from a PR. We should apply that here. We must also document the change in the contributing repo

Pull request:
conf.py generated, updated environment.yml
## Describe your changes
1) Generated `conf.py` from `_config.yml`
2) Edited `.readthedocs.yml`, removed `jupyter-book config sphinx`
3) Added `pkgmt>=0.1.7` to `environment.yml`
4) Edited `conf.py` 

## Issue ticket number and link
Closes #123 

## Checklist before requesting a review

- [x] I have performed a self-review of my code
- [x] I have added thorough [tests](https://github.com/ploomber/contributing/blob/main/CONTRIBUTING.md#testing) (when necessary).
- [x] I have added the right 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--135.org.readthedocs.build/en/135/

<!-- readthedocs-preview jupysql end -->

Patch:
diff --git a/.gitignore b/.gitignore
index ab1b296..410326a 100644
--- a/.gitignore
+++ b/.gitignore
@@ -10,7 +10,6 @@ doc/**/*.csv
 doc/**/*.db
 doc/**/*.sql
 doc/**/*.py
-doc/**/*.sql
 examples/*.csv
 examples/*.db
 examples/*.sql
diff --git a/.readthedocs.yml b/.readthedocs.yml
index 66c6c09..21c02d0 100644
--- a/.readthedocs.yml
+++ b/.readthedocs.yml
@@ -5,10 +5,6 @@ build:
   tools:
     python: "mambaforge-4.10"
 
-  jobs:
-    pre_build:
-      - "jupyter-book config sphinx doc/"
-
 conda:
   environment: doc/environment.yml
 
diff --git a/CHANGELOG.md b/CHANGELOG.md
index b669fa0..0fc0d2a 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -2,6 +2,8 @@
 
 ## 0.5.6dev
 
+* [Feature] Shows missing driver package suggestion message ([#124](https://github.com/ploomber/jupysql/issues/124))
+
 ## 0.5.5 (2023-02-08)
 
 * [Fix] Clearer error message on connection failure ([#120](https://github.com/ploomber/jupysql/issues/120))
diff --git a/doc/conf.py b/doc/conf.py
new file mode 100644
index 0000000..ee5c7e8
--- /dev/null
+++ b/doc/conf.py
@@ -0,0 +1,88 @@
+from pkgmt.github import get_repo_and_branch_for_readthedocs
+
+repository_url, repository_branch = get_repo_and_branch_for_readthedocs(
+    repository_url="https://github.com/ploomber/jupysql",
+    default_branch="master",
+)
+
+###############################################################################
+# Auto-generated by `jupyter-book config`
+# If you wish to continue using _config.yml, make edits to that file and
+# re-generate this one.
+###############################################################################
+author = "Ploomber"
+comments_config = {"hypothesis": False, "utterances": False}
+copyright = "2023"
+exclude_patterns = ["**.ipynb_checkpoints", ".DS_Store", "Thumbs.db", "_build"]
+execution_allow_errors = False
+execution_excludepatterns = ["howto/*-connect.ipynb", "integrations/mindsdb.ipynb"]
+execution_in_temp = False
+execution_show_tb = True
+execution_timeout = 90
+extensions = [
+    "sphinx_togglebutton",
+    "sphinx_copybutton",
+    "myst_nb",
+    "jupyter_book",
+    "sphinx_thebe",
+    "sphinx_comments",
+    "sphinx_external_toc",
+    "sphinx.ext.intersphinx",
+    "sphinx_design",
+    "sphinx_book_theme",
+    "sphinx.ext.autodoc",
+    "sphinx.ext.napoleon",
+    "sphinx.ext.autosummary",
+    "matplotlib.sphinxext.plot_directive",
+    "sphinx_jupyterbook_latex",
+]
+external_toc_exclude_missing = False
+external_toc_path = "_toc.yml"
+html_baseurl = ""
+html_favicon = ""
+html_logo = "square-no-bg-small.png"
+html_sourcelink_suffix = ""
+html_theme = "sphinx_book_theme"
+html_theme_options = {
+    "search_bar_text": "Search this book...",
+    "launch_buttons": {
+        "notebook_interface": "jupyterlab",
+        "binderhub_url": "https://binder.ploomber.io",
+        "jupyterhub_url": "",
+        "thebe": False,
+        "colab_url": "",
+    },
+    "path_to_docs": "doc",
+    "repository_url": repository_url,
+    "repository_branch": repository_branch,
+    "google_analytics_id": "G-JBZ8NNQSLN",
+    "extra_navbar": 'Join us on <a href="https://ploomber.io/community/">Slack!</a>',
+    "extra_footer": "",
+    "home_page_in_toc": True,
+    "announcement": "To launch any tutorial in JupyterLab, \
+        click on the 🚀 button below!",
+    "use_repository_button": True,
+    "use_edit_page_button": False,
+    "use_issues_button": True,
+}
+html_title = "JupySQL"
+jupyter_cache = ""
+jupyter_execute_notebooks = "force"
+latex_engine = "pdflatex"
+myst_enable_extensions = [
+    "colon_fence",
+    "dollarmath",
+    "linkify",
+    "substitution",
+    "tasklist",
+]
+myst_url_schemes = ["mailto", "http", "https"]
+nb_output_stderr = "show"
+numfig = True
+plot_html_show_formats = False
+plot_html_show_source_link = False
+plot_include_source = True
+pygments_style = "sphinx"
+suppress_warnings = ["myst.domains"]
+use_jupyterbook_latex = True
+use_multitoc_numbering = True
diff --git a/doc/environment.yml b/doc/environment.yml
index 792f73c..0ecef01 100644
--- a/doc/environment.yml
+++ b/doc/environment.yml
@@ -17,4 +17,5 @@ dependencies:
     # plot example
     - memory-profiler
     - pyarrow
+    - pkgmt>=0.1.7
     - -e ..
\ No newline at end of file
diff --git a/src/sql/connection.py b/src/sql/connection.py
index 4ba0fd1..79be479 100644
--- a/src/sql/connection.py
+++ b/src/sql/connection.py
@@ -3,7 +3,89 @@ from difflib import get_close_matches
 
 import sqlalchemy
 from sqlalchemy.engine import Engine
+from sqlalchemy.exc import NoSuchModuleError
 from IPython.core.error import UsageError
+import difflib
+
+PLOOMBER_SUPPORT_LINK_STR = (
+    "For technical support: https://ploomber.io/community"
+    "\nDocumentation: https://jupysql.ploomber.io/en/latest/connecting.html"
+)
+
+# Check Full List: https://docs.sqlalchemy.org/en/20/dialects
+MISSING_PACKAGE_LIST_EXCEPT_MATCHERS = {
+    # SQLite
+    "sqlite": "sqlite",
+    "pysqlcipher3": "pysqlcipher3",
+    # DuckDB
+    "duckdb": "duckdb-engine",
+    # MySQL + MariaDB
+    "pymysql": "pymysql",
+    "mysqldb": "mysqlclient",
+    "mariadb": "mariadb",
+    "mysql": "mysql-connector-python",
+    "asyncmy": "asyncmy",
+    "aiomysql": "aiomysql",
+    "cymysql": "cymysql",
+    "pyodbc": "pyodbc",
+    # PostgreSQL
+    "psycopg2": "psycopg2",
+    "psycopg": "psycopg",
+    "pg8000": "pg8000",
+    "asyncpg": "asyncpg",
+    "psycopg2cffi": "psycopg2cffi",
+    # Oracle
+    "cx_oracle": "cx_oracle",
+    "oracledb": "oracledb",
+    # MSSQL
+    "pyodbc": "pyodbc",
+    "pymssql": "pymssql",
+}
+
+
+def extract_module_name_from_ModuleNotFoundError(e):
+    return e.name
+
+
+def extract_module_name_from_NoSuchModuleError(e):
+    return str(e).split(":")[-1].split(".")[-1]
+
+
+"""
+When there is ModuleNotFoundError or NoSuchModuleError case
+Three types of suggestions will be shown when the missing module name is:
+1. Excepted in the pre-defined map, suggest the user to install the driver pkg
+2. Closely matched to the pre-defined map, suggest the user to type correct driver name
+3. Not found in the pre-defined map, suggest user to use valid driver pkg
+"""
+
+
+def get_missing_package_suggestion_str(e):
+    suggestion_prefix = "To fix it, "
+    module_name = None
+    if isinstance(e, ModuleNotFoundError):
+        module_name = extract_module_name_from_ModuleNotFoundError(e)
+    elif isinstance(e, NoSuchModuleError):
+        module_name = extract_module_name_from_NoSuchModuleError(e)
+
+    module_name = module_name.lower()
+    # Excepted
+    for matcher, suggested_package in MISSING_PACKAGE_LIST_EXCEPT_MATCHERS.items():
+        if matcher == module_name:
+            return suggestion_prefix + "try to install package: " + suggested_package
+    # Closely matched
+    close_matches = difflib.get_close_matches(
+        module_name, MISSING_PACKAGE_LIST_EXCEPT_MATCHERS.keys()
+    )
+    if close_matches:
+        return "Perhaps you meant to use driver the dialect: \"{}\"".format(
+            close_matches[0]
+        )
+    # Not found
+    return (
+        suggestion_prefix + "make sure you are using correct driver name:\n"
+        "Ref: https://docs.sqlalchemy.org/en/20/core/engines.html#database-urls"
+    )
 
 
 def rough_dict_get(dct, sought, default=None):
@@ -15,7 +97,7 @@ def rough_dict_get(dct, sought, default=None):
     """
 
     sought = sought.split("@")
-    for (key, val) in dct.items():
+    for key, val in dct.items():
         if not any(s.lower() not in key.lower() for s in sought):
             return val
     return default
@@ -36,6 +118,16 @@ class Connection:
     # all connections
     connections = {}
 
+    @classmethod
+    def _suggest_fix_no_module_found(module_name):
+        DEFAULT_PREFIX = "\n\n"
+
+        prefix = DEFAULT_PREFIX
+        suffix = "To fix it:"
+        suggest_str = "Install X package and try again"
+        options = [f"{prefix}{suffix}", suggest_str]
+        return "\n\n".join(options)
+
     @classmethod
     def _suggest_fix(cls, env_var, connect_str=None):
         """
@@ -82,10 +174,7 @@ class Connection:
         if len(options) >= 3:
             options.insert(-1, "OR")
 
-        options.append(
-            "For technical support: https://ploomber.io/community"
-            "\nDocumentation: https://jupysql.ploomber.io/en/latest/connecting.html"
-        )
+        options.append(PLOOMBER_SUPPORT_LINK_STR)
 
         return "\n\n".join(options)
 
@@ -101,6 +190,10 @@ class Connection:
             f"{e}.{cls._suggest_fix(env_var=False, connect_str=connect_str)}"
         )
 
+    @classmethod
+    def _error_module_not_found(cls, e):
+        return ModuleNotFoundError("test")
+
     def __init__(self, engine, alias=None):
         self.dialect = engine.url.get_dialect()
         self.metadata = sqlalchemy.MetaData(bind=engine)
@@ -130,6 +223,17 @@ class Connection:
                     connect_str,
                     connect_args=connect_args,
                 )
+        except (ModuleNotFoundError, NoSuchModuleError) as e:
+            suggestion_str = get_missing_package_suggestion_str(e)
+            raise UsageError(
+                "\n\n".join(
+                    [
+                        str(e),
+                        suggestion_str,
+                        PLOOMBER_SUPPORT_LINK_STR,
+                    ]
+                )
+            ) from e
         except Exception as e:
             raise cls._error_invalid_connection_info(e, connect_str) from e
 
@@ -169,7 +273,6 @@ class Connection:
                 )
 
         else:
-
             if cls.connections:
                 if displaycon:
                     # display list of connections


Test patch:
diff --git a/src/tests/mock_pymysql.py b/src/tests/mock_pymysql.py
new file mode 100644
index 0000000..e69de29
diff --git a/src/tests/test_connection.py b/src/tests/test_connection.py
index 6422e86..1e99d01 100644
--- a/src/tests/test_connection.py
+++ b/src/tests/test_connection.py
@@ -1,10 +1,9 @@
 import sys
-from unittest.mock import Mock
-
+from unittest.mock import Mock, patch
 import pytest
 from sqlalchemy.engine import Engine
-
 from sql.connection import Connection
+from IPython.core.error import UsageError
 
 
 @pytest.fixture
@@ -35,3 +34,52 @@ def test_alias(cleanup):
     Connection.from_connect_str("sqlite://", alias="some-alias")
 
     assert list(Connection.connections) == ["some-alias"]
+
+
+# Mock the missing package
+# Ref: https://stackoverflow.com/a/28361013
+def test_missing_duckdb_dependencies(cleanup, monkeypatch):
+    with patch.dict(sys.modules):
+        sys.modules["duckdb"] = None
+        sys.modules["duckdb-engine"] = None
+
+        with pytest.raises(UsageError) as error:
+            Connection.from_connect_str("duckdb://")
+        assert "try to install package: duckdb-engine" + str(error.value)
+
+
+@pytest.mark.parametrize(
+    "missing_pkg, except_missing_pkg_suggestion, connect_str",
+    [
+        # MySQL + MariaDB
+        ["pymysql", "pymysql", "mysql+pymysql://"],
+        ["mysqlclient", "mysqlclient", "mysql+mysqldb://"],
+        ["mariadb", "mariadb", "mariadb+mariadbconnector://"],
+        ["mysql-connector-python", "mysql-connector-python", "mysql+mysqlconnector://"],
+        ["asyncmy", "asyncmy", "mysql+asyncmy://"],
+        ["aiomysql", "aiomysql", "mysql+aiomysql://"],
+        ["cymysql", "cymysql", "mysql+cymysql://"],
+        ["pyodbc", "pyodbc", "mysql+pyodbc://"],
+        # PostgreSQL
+        ["psycopg2", "psycopg2", "postgresql+psycopg2://"],
+        ["psycopg", "psycopg", "postgresql+psycopg://"],
+        ["pg8000", "pg8000", "postgresql+pg8000://"],
+        ["asyncpg", "asyncpg", "postgresql+asyncpg://"],
+        ["psycopg2cffi", "psycopg2cffi", "postgresql+psycopg2cffi://"],
+        # Oracle
+        ["cx_oracle", "cx_oracle", "oracle+cx_oracle://"],
+        ["oracledb", "oracledb", "oracle+oracledb://"],
+        # MSSQL
+        ["pyodbc", "pyodbc", "mssql+pyodbc://"],
+        ["pymssql", "pymssql", "mssql+pymssql://"],
+    ],
+)
+def test_missing_driver(
+    missing_pkg, except_missing_pkg_suggestion, connect_str, monkeypatch
+):
+    with patch.dict(sys.modules):
+        sys.modules[missing_pkg] = None
+        with pytest.raises(UsageError) as error:
+            Connection.from_connect_str(connect_str)
+
+        assert "try to install package: " + missing_pkg in str(error.value)
diff --git a/src/tests/test_magic.py b/src/tests/test_magic.py
index 88d7d5c..dddc184 100644
--- a/src/tests/test_magic.py
+++ b/src/tests/test_magic.py
@@ -489,12 +489,10 @@ def test_error_on_invalid_connection_string(ip_empty, clean_conns):
 
 
 invalid_connection_string_format = """\
-An error happened while creating the connection: Can't load plugin: sqlalchemy.dialects:something.
+Can't load plugin: sqlalchemy.dialects:something
 
-To fix it:
-
-Pass a valid connection string:
-    Example: %sql postgresql://username:password@hostname/dbname
+To fix it, make sure you are using correct driver name:
+Ref: https://docs.sqlalchemy.org/en/20/core/engines.html#database-urls
 
 For technical support: https://ploomber.io/community
 Documentation: https://jupysql.ploomber.io/en/latest/connecting.html
@@ -509,17 +507,10 @@ def test_error_on_invalid_connection_string_format(ip_empty, clean_conns):
 
 
 invalid_connection_string_existing_conns = """
-An error happened while creating the connection: Can't load plugin: sqlalchemy.dialects:something.
-
-To fix it:
-
-Pass a valid connection string:
-    Example: %sql postgresql://username:password@hostname/dbname
+Can't load plugin: sqlalchemy.dialects:something
 
-OR
-
-Pass a connection key (one of: 'sqlite://')
-    Example: %sql 'sqlite://'
+To fix it, make sure you are using correct driver name:
+Ref: https://docs.sqlalchemy.org/en/20/core/engines.html#database-urls
 
 For technical support: https://ploomber.io/community
 Documentation: https://jupysql.ploomber.io/en/latest/connecting.html
@@ -535,19 +526,9 @@ def test_error_on_invalid_connection_string_with_existing_conns(ip_empty, clean_
 
 
 invalid_connection_string_with_possible_typo = """
-An error happened while creating the connection: Can't load plugin: sqlalchemy.dialects:sqlit.
-
-Perhaps you meant to use the existing connection: %sql 'sqlite://'?
-
-Otherwise, try the following:
-
-Pass a valid connection string:
-    Example: %sql postgresql://username:password@hostname/dbname
-
-OR
+Can't load plugin: sqlalchemy.dialects:sqlit
 
-Pass a connection key (one of: 'sqlite://')
-    Example: %sql 'sqlite://'
+Perhaps you meant to use driver the dialect: "sqlite"
 
 For technical support: https://ploomber.io/community
 Documentation: https://jupysql.ploomber.io/en/latest/connecting.html