Repository: ploomber/jupysql
Pull request #819 (2023-08-19 22:09:14)

auto-connect from default connection in `.ini` file
## Describe your changes

when initializing the extension via `%load_ext sql`, JupySQL will automatically connect to a database if the configuration file has a database in a `[default]` section

## Issue number

Closes #X

## Checklist before requesting a review

- [ ] Performed a self-review of my code
- [ ] 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)



<!-- readthedocs-preview jupysql start -->
----
:books: Documentation preview :books:: https://jupysql--819.org.readthedocs.build/en/819/

<!-- readthedocs-preview jupysql end -->

neelasha23: We can add some some message that `default connection loaded from  ~/.jupysql/connections.ini file`:
<img width="674" alt="Screenshot 2023-08-20 at 12 16 58 PM" src="https://github.com/ploomber/jupysql/assets/29096135/b0d09473-8de4-4f62-8753-4c90b5a1b28d">

edublancas: > We can add some some message that default connection loaded from  ~/.jupysql/connections.ini file:

we're tackling that here: https://github.com/ploomber/jupysql/issues/807

edublancas: > Some of the information looks scattered in the docs. Currently there are two ways of setting the connections file: Either set in the notebook itself %config SqlMagic.dsn_filename = "connections.ini or define it in the pyproject.toml https://jupysql.ploomber.io/en/latest/api/configuration.html#loading-from-pyproject-toml.
I think it might be good if this information can also be added for a quick reference [here](https://jupysql--819.org.readthedocs.build/en/819/connecting.html#connecting-with-a-ini-file)

not sure what your suggestion is. the objective  [here](https://jupysql--819.org.readthedocs.build/en/819/connecting.html#connecting-with-a-ini-file) is to show at a high-level what options the user has. but I modified the connection file tutorial and added a link to the pyproject toml section: https://github.com/ploomber/jupysql/pull/819/commits/d9abd25e827f7417502ecbccf3bca3107f42001f

neelasha23: > d9abd25

yes this is what I meant!

Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 0e56a82..79954d1 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -1,6 +1,7 @@
 # CHANGELOG
 
 ## 0.10.1dev
+* [Feature] Automatically connect if the `dsn_filename` (defaults to `~/.jupysql/connections.ini`) contains a `default` section
 
 ## 0.10.0 (2023-08-19)
 
diff --git a/doc/connecting.md b/doc/connecting.md
index 0c7467f..102b408 100644
--- a/doc/connecting.md
+++ b/doc/connecting.md
@@ -6,7 +6,7 @@ jupytext:
     extension: .md
     format_name: myst
     format_version: 0.13
-    jupytext_version: 1.14.5
+    jupytext_version: 1.15.0
 kernelspec:
   display_name: Python 3 (ipykernel)
   language: python
@@ -20,11 +20,36 @@ myst:
 
 # Connecting to a database
 
-Learn how to connect to various databases using JupySQL.
+JupySQL offers several ways to configure a database connection. In this guide, we discuss the pros and cons of each.
+
+## Connecting with a `.ini` file
+
+```{versionchanged} 0.10.0
+```
+
+Using a `.ini` file is the recommended way to connect to databases. By default, JupySQL reads the `~/.jupysql/connections.ini` file, but you can change this setting. A `.ini` file looks like this:
+
+```ini
+[mydb]
+drivername = postgresql
+username = person
+password = mypass
+host = localhost
+port = 5432
+database = db
+```
+
+To learn more, see: [](user-guide/connection-file.md).
+
++++
 
 ## Connect with a URL string
 
-Connection strings follow the [SQLAlchemy URL format](http://docs.sqlalchemy.org/en/latest/core/engines.html#database-urls). This is the fastest way to connect to your database and the recommended way if you're using SQLite or DuckDB.
+```{important}
+If you connect using a URL string, **do not hardcode your password in your notebook**, see: [](building-url-strings-securely)
+```
+
+Connection strings follow the [SQLAlchemy URL format](http://docs.sqlalchemy.org/en/latest/core/engines.html#database-urls).
 
 Database URLs have the following format:
 
@@ -32,14 +57,17 @@ Database URLs have the following format:
 dialect+driver://username:password@host:port/database
 ```
 
+In-memory databases have the following format:
 
-```{important}
-If you're using a database that requires a password, keep reading for more secure methods.
+```
+sqlite://
+duckdb://
 ```
 
-## Building URL strings securely
+(building-url-strings-securely)=
+### Building URL strings securely
 
-To connect in a more secure way, you can dynamically build your URL string so your password isn't hardcoded:
+To connect more securely, you can dynamically build your URL string so your password isn't hardcoded; you can use the `getpass` function so you're prompted for your password whenever you want to connect:
 
 ```python
 from getpass import getpass
@@ -91,23 +119,6 @@ engine = create_engine(db_url)
 
 +++ {"user_expressions": []}
 
-## Secure Connections
-
-
-**It is highly recommended** that you do not pass plain credentials.
-
-```{code-cell} ipython3
-:tags: [remove-output]
-
-%load_ext sql
-```
-
-```{code-cell} ipython3
-%sql engine
-```
-
-+++ {"user_expressions": []}
-
 ```{important}
 Unlike `ipython-sql`, JupySQL doesn't allow expanding your database URL with the `$` character:
 
@@ -122,7 +133,9 @@ db_url = "dialect+driver://username:password@host:port/database"
 
 ## Securely storing your password
 
-If you want to store your password securely (and don't get prompted whenever you start a connection), you can use [keyring](https://github.com/jaraco/keyring):
+Using a `.ini` file has the advantage of not having to hardcode your password. However, it's still stored in a file in plain text. On the other hand, using `getpass` will always prompt you for your password, which isn't ideal when running [scheduled notebooks.](https://github.com/ploomber/ploomber-engine)
+
+The most secure way to store your password is to use [keyring](https://github.com/jaraco/keyring), a library that uses the operating system credentials manager to securely store your password. The caveat is that the configuration settings depend on your operating system.
 
 ```{code-cell} ipython3
 :tags: [remove-output]
@@ -132,7 +145,7 @@ If you want to store your password securely (and don't get prompted whenever you
 
 +++ {"user_expressions": []}
 
-Execute the following in your notebook:
+Once `keyring` is configured. Execute the following in your notebook:
 
 ```python
 import keyring
@@ -280,7 +293,7 @@ df.to_sql("numbers", engine)
 SELECT * FROM numbers
 ```
 
-## Custom Connection
+## DBAPI connections
 
 ```{versionadded} 0.7.2
 ```
@@ -324,23 +337,4 @@ You're all set
 %sql select * from penguins.csv limit 3
 ```
 
-For a more detailed example please see [QuestDB tutorial](integrations/questdb.ipynb)
-
-## Conclusion
-
-Once the URL string is constructed, you can create an engine using `create_engine` and establish a connection to the database. JupySQL provides the `%load_ext sql` magic command to load the SQL extension. Then, you can execute SQL queries using `%sql`.
-
-JupySQL offers flexible and secure methods for connecting to databases in Jupyter notebooks, allowing you to execute SQL queries and interact with various database systems efficiently.
-
-## Tutorials
-
-Vendor-specific details are available in our tutorials:
-
-- [PostgreSQL](integrations/postgres-connect)
-- [ClickHouse](integrations/clickhouse)
-- [MariaDB](integrations/mariadb)
-- [MindsDB](integrations/mindsdb)
-- [MSSQL](integrations/mssql)
-- [MySQL](integrations/mysql)
-- [QuestDB](integrations/questdb)
-- [Oracle](integrations/oracle)
+For a more detailed example, see [QuestDB tutorial](integrations/questdb.ipynb)
diff --git a/doc/user-guide/connection-file.md b/doc/user-guide/connection-file.md
index 1ed55d3..4635d7c 100644
--- a/doc/user-guide/connection-file.md
+++ b/doc/user-guide/connection-file.md
@@ -19,6 +19,10 @@ myst:
 
 # Using a connection file
 
+```{important}
+When using a connection file, ensure the file has the appropriate permissions, so only you can read its contents.
+```
+
 Using a connection file is the recommended way to manage connections, it helps you to:
 
 - Avoid storing your credentials in your notebook
@@ -41,6 +45,10 @@ However, you can change this:
 %config SqlMagic.dsn_filename = "connections.ini"
 ```
 
+```{tip}
+You can also define your configuration settings in a [`pyproject.toml` file.](../api/configuration.md#loading-from-pyprojecttoml)
+```
+
 The `.ini` format defines sections and you can define key-value pairs within each section. For example:
 
 ```ini
@@ -65,6 +73,18 @@ For example, to configure an in-memory DuckDB database:
 drivername = duckdb
 ```
 
+Or, to connect to a PostgreSQL database:
+
+```ini
+[pg]
+drivername = postgresql
+username = person
+password = mypass
+host = localhost
+port = 5432
+database = db
+```
+
 ```{code-cell} ipython3
 from pathlib import Path
 
@@ -119,6 +139,8 @@ select * from penguins.csv
 select * from penguins
 ```
 
+## Managing multiple connections
+
 Let's now define another connection so we can show how we can manage multiple ones:
 
 ```{code-cell} ipython3
@@ -169,6 +191,16 @@ We can change back to the other connection:
 %sqlcmd tables
 ```
 
-```{code-cell} ipython3
+## Setting a default connection
+
+```{versionadded} 0.10.1
+```
 
+If JupySQL finds a `default` section in your connections file, it'll automatically connect to it when the extension is loaded. For example, to connect to an in-memory DuckDB database:
+
+```ini
+[default]
+drivername = duckdb
 ```
+
+Then, whenever you run: `load_ext %sql`, the connection will start.
diff --git a/src/sql/command.py b/src/sql/command.py
index b933162..0c6cd4b 100644
--- a/src/sql/command.py
+++ b/src/sql/command.py
@@ -65,7 +65,7 @@ class SQLCommand:
 
             self.command_text = file_contents + "\n" + self.command_text
 
-        self.parsed = parse.parse(self.command_text, magic)
+        self.parsed = parse.parse(self.command_text, magic.dsn_filename)
 
         self.parsed["sql_original"] = self.parsed["sql"] = self._var_expand(
             self.parsed["sql"], user_ns, magic
diff --git a/src/sql/connection/connection.py b/src/sql/connection/connection.py
index ec5ae5f..dd03cc7 100644
--- a/src/sql/connection/connection.py
+++ b/src/sql/connection/connection.py
@@ -25,7 +25,11 @@ from sql.store import store
 from sql.telemetry import telemetry
 from sql import exceptions, display
 from sql.error_message import detail
-from sql.parse import escape_string_literals_with_colon_prefix, find_named_parameters
+from sql.parse import (
+    escape_string_literals_with_colon_prefix,
+    find_named_parameters,
+    ConnectionsFile,
+)
 from sql.warnings import JupySQLQuotedNamedParametersWarning, JupySQLRollbackPerformed
 from sql import _current
 
@@ -397,6 +401,23 @@ class ConnectionManager:
 
         return connection
 
+    @classmethod
+    def load_default_connection_from_file_if_any(cls, config):
+        try:
+            connections_file = ConnectionsFile(path_to_file=config.dsn_filename)
+        except FileNotFoundError:
+            return
+
+        default_url = connections_file.get_default_connection_url()
+
+        if default_url is not None:
+            cls.set(
+                default_url,
+                displaycon=False,
+                alias="default",
+                config=config,
+            )
+
 
 class AbstractConnection(abc.ABC):
     """The abstract base class for all connections"""
diff --git a/src/sql/display.py b/src/sql/display.py
index 7fee72b..e7cfdb0 100644
--- a/src/sql/display.py
+++ b/src/sql/display.py
@@ -92,6 +92,16 @@ def message_success(message):
     display(Message(message, style="color: green"))
 
 
+def message_warning(message):
+    """Display a success message"""
+    display(
+        Message(
+            message,
+            style="background-color:#fff3cd;color:#d39e00",
+        )
+    )
+
+
 def message_html(message):
     """Display a message as HTML"""
     display(HTML(str(Message(message))))
diff --git a/src/sql/magic.py b/src/sql/magic.py
index 5a31e5f..3bbf49e 100644
--- a/src/sql/magic.py
+++ b/src/sql/magic.py
@@ -703,7 +703,19 @@ def load_SqlMagic_configs(ip):
     """Loads saved SqlMagic configs in pyproject.toml"""
     file_path = util.find_path_from_root("pyproject.toml")
     if file_path:
-        table_rows = set_configs(ip, file_path)
+        try:
+            table_rows = set_configs(ip, file_path)
+        except Exception as e:
+            if type(e).__name__ == "TomlDecodeError":
+                display.message_warning(
+                    f"Could not load configuration file at {file_path} "
+                    "(default configuration will be used).\nPlease "
+                    f"check that it is valid TOML: {e}"
+                )
+                return
+            else:
+                raise
+
         if table_rows:
             display.message("Settings changed:")
             display.table(["Config", "value"], table_rows)
@@ -713,12 +725,17 @@ def load_ipython_extension(ip):
     """Load the magics, this function is executed when the user runs: %load_ext sql"""
     sql_magic = SqlMagic(ip)
     _set_sql_magic(sql_magic)
-
     ip.register_magics(sql_magic)
+
+    load_SqlMagic_configs(ip)
+
+    # start the default connection if the user has one in their config file
+    sql.connection.ConnectionManager.load_default_connection_from_file_if_any(
+        config=sql_magic
+    )
+
     ip.register_magics(RenderMagic)
     ip.register_magics(SqlPlotMagic)
     ip.register_magics(SqlCmdMagic)
 
     patch_ipython_usage_error(ip)
-
-    load_SqlMagic_configs(ip)
diff --git a/src/sql/parse.py b/src/sql/parse.py
index cead924..145d95a 100644
--- a/src/sql/parse.py
+++ b/src/sql/parse.py
@@ -12,6 +12,24 @@ from IPython.core.magic_arguments import parse_argstring
 from sql import exceptions
 
 
+class ConnectionsFile:
+    def __init__(self, path_to_file) -> None:
+        self.parser = configparser.ConfigParser()
+        dsn_file = Path(path_to_file)
+
+        cfg_content = dsn_file.read_text()
+        self.parser.read_string(cfg_content)
+
+    def get_default_connection_url(self):
+        try:
+            section = self.parser.items("default")
+        except configparser.NoSectionError:
+            return None
+
+        url = URL.create(**dict(section))
+        return str(url.render_as_string(hide_password=False))
+
+
 def connection_str_from_dsn_section(section, config):
     """Return a SQLAlchemy connection string from a section in a DSN file
 
@@ -69,7 +87,7 @@ def connection_str_from_dsn_section(section, config):
     return str(url.render_as_string(hide_password=False))
 
 
-def _connection_string(s, config):
+def _connection_string(arg, path_to_file):
     """
     Given a string, return a SQLAlchemy connection string if possible.
 
@@ -78,19 +96,27 @@ def _connection_string(s, config):
     - If the string is a valid URL, return it
     - If the string is a valid section in the DSN file return the connection string
     - Otherwise return an empty string
+
+    Parameters
+    ----------
+    arg : str
+        The string to parse
+
+    path_to_file : str
+        The path to the DSN file
     """
     # for environment variables
-    s = expandvars(s)
+    arg = expandvars(arg)
 
     # if it's a URL, return it
-    if "@" in s or "://" in s:
-        return s
+    if "@" in arg or "://" in arg:
+        return arg
 
     # if it's a section in the DSN file, return the connection string
-    if s.startswith("[") and s.endswith("]"):
-        section = s.lstrip("[").rstrip("]")
+    if arg.startswith("[") and arg.endswith("]"):
+        section = arg.lstrip("[").rstrip("]")
         parser = configparser.ConfigParser()
-        parser.read(config.dsn_filename)
+        parser.read(path_to_file)
         cfg_dict = dict(parser.items(section))
         url = URL.create(**cfg_dict)
         url_ = str(url.render_as_string(hide_password=False))
@@ -107,7 +133,7 @@ def _connection_string(s, config):
     return ""
 
 
-def parse(cell, config):
+def parse(arg, path_to_file):
     """Extract connection info and result variable from SQL
 
     Please don't add any more syntax requiring
@@ -116,6 +142,14 @@ def parse(cell, config):
 
     We're grandfathering the
     connection string and `<<` operator in.
+
+    Parameters
+    ----------
+    arg : str
+        The string to parse
+
+    path_to_file : str
+        The path to the DSN file
     """
     result = {
         "connection": "",
@@ -124,21 +158,21 @@ def parse(cell, config):
         "return_result_var": False,
     }
 
-    pieces = cell.split(None, 1)
+    pieces = arg.split(None, 1)
     if not pieces:
         return result
 
-    result["connection"] = _connection_string(pieces[0], config)
+    result["connection"] = _connection_string(pieces[0], path_to_file)
 
     if result["connection"]:
         if len(pieces) == 1:
             return result
-        cell = pieces[1]
+        arg = pieces[1]
 
-    pointer = cell.find("<<")
+    pointer = arg.find("<<")
     if pointer != -1:
-        left = cell[:pointer].replace(" ", "").replace("\n", "")
-        right = cell[pointer + 2 :].strip(" ")
+        left = arg[:pointer].replace(" ", "").replace("\n", "")
+        right = arg[pointer + 2 :].strip(" ")
 
         if "=" in left:
             result["result_var"] = left[:-1]
@@ -148,7 +182,7 @@ def parse(cell, config):
 
         result["sql"] = right
     else:
-        result["sql"] = cell
+        result["sql"] = arg
     return result
 
 


Test patch:
diff --git a/src/sql/_testing.py b/src/sql/_testing.py
index 6bdd623..fb399c0 100644
--- a/src/sql/_testing.py
+++ b/src/sql/_testing.py
@@ -6,6 +6,7 @@ import time
 from sqlalchemy.engine import URL
 import sqlalchemy
 from IPython.core.interactiveshell import InteractiveShell
+from traitlets.config import Config
 
 from ploomber_core.dependencies import requires
 
@@ -37,6 +38,24 @@ class TestingShell(InteractiveShell):
         result.raise_error()
         return result
 
+    @classmethod
+    def preconfigured_shell(cls):
+        c = Config()
+
+        # By default, InteractiveShell will record command's history in a SQLite
+        # database which leads to "too many open files" error when running tests;
+        # this setting disables the history recording.
+        # https://ipython.readthedocs.io/en/stable/config/options/terminal.html#configtrait-HistoryAccessor.enabled
+        c.HistoryAccessor.enabled = False
+        ip = cls(config=c)
+
+        # there is some weird bug in ipython that causes this function to hang the
+        # pytest process when all tests have been executed (an internal call to
+        # gc.collect() hangs). This is a workaround.
+        ip.displayhook.flush = lambda: None
+
+        return ip
+
 
 class DatabaseConfigHelper:
     @staticmethod
diff --git a/src/tests/conftest.py b/src/tests/conftest.py
index 1a7e674..d291c44 100644
--- a/src/tests/conftest.py
+++ b/src/tests/conftest.py
@@ -1,11 +1,9 @@
-from traitlets.config import Config
 import os
 import urllib.request
 from pathlib import Path
 
 import pytest
 
-
 from sql.magic import SqlMagic, RenderMagic
 from sql.magic_plot import SqlPlotMagic
 from sql.magic_cmd import SqlCmdMagic
@@ -77,49 +75,36 @@ def clean_conns():
 
 
 @pytest.fixture
-def ip_empty():
-    c = Config()
-    # By default, InteractiveShell will record command's history in a SQLite database
-    # which leads to "too many open files" error when running tests; this setting
-    # disables the history recording.
-    # https://ipython.readthedocs.io/en/stable/config/options/terminal.html#configtrait-HistoryAccessor.enabled
-    c.HistoryAccessor.enabled = False
-    ip_session = TestingShell(config=c)
+def ip_no_magics():
+    ip_session = TestingShell.preconfigured_shell()
 
-    sql_magic = SqlMagic(ip_session)
-    _current._set_sql_magic(sql_magic)
+    # to prevent using the actual default, which reads from the home directory
+    ip_session.run_cell("%config SqlMagic.dsn_filename = 'default.ini'")
 
-    ip_session.register_magics(sql_magic)
-    ip_session.register_magics(RenderMagic)
-    ip_session.register_magics(SqlPlotMagic)
-    ip_session.register_magics(SqlCmdMagic)
+    yield ip_session
+    ConnectionManager.close_all()
 
-    # there is some weird bug in ipython that causes this function to hang the pytest
-    # process when all tests have been executed (an internal call to gc.collect()
-    # hangs). This is a workaround.
-    ip_session.displayhook.flush = lambda: None
 
-    yield ip_session
+@pytest.fixture
+def ip_empty(ip_no_magics):
+    sql_magic = SqlMagic(ip_no_magics)
+    _current._set_sql_magic(sql_magic)
+
+    ip_no_magics.register_magics(sql_magic)
+    ip_no_magics.register_magics(RenderMagic)
+    ip_no_magics.register_magics(SqlPlotMagic)
+    ip_no_magics.register_magics(SqlCmdMagic)
+
+    yield ip_no_magics
     ConnectionManager.close_all()
 
 
 @pytest.fixture
 def sql_magic():
-    c = Config()
-    # By default, InteractiveShell will record command's history in a SQLite database
-    # which leads to "too many open files" error when running tests; this setting
-    # disables the history recording.
-    # https://ipython.readthedocs.io/en/stable/config/options/terminal.html#configtrait-HistoryAccessor.enabled
-    c.HistoryAccessor.enabled = False
-    ip_session = TestingShell(config=c)
+    ip_session = TestingShell.preconfigured_shell()
 
     sql_magic = SqlMagic(ip_session)
 
-    # there is some weird bug in ipython that causes this function to hang the pytest
-    # process when all tests have been executed (an internal call to gc.collect()
-    # hangs). This is a workaround.
-    ip_session.displayhook.flush = lambda: None
-
     yield sql_magic
     ConnectionManager.close_all()
 
diff --git a/src/tests/test_config.py b/src/tests/test_config.py
index cfefb0a..75906d1 100644
--- a/src/tests/test_config.py
+++ b/src/tests/test_config.py
@@ -1,6 +1,32 @@
-# we have a lot of config tests in test_magic.py, we should move them here
+import os
+import re
 from pathlib import Path
 
+import pytest
+
+from sql.magic import load_ipython_extension
+from sql.connection import ConnectionManager
+from sql.util import get_default_configs
+from IPython.core.error import UsageError
+
+
+def get_current_configs(magic):
+    cfg = magic.trait_values()
+    del cfg["parent"]
+    del cfg["config"]
+    return cfg
+
+
+def get_default_testing_configs(sql):
+    """
+    Returns a dictionary of SqlMagic configuration settings users can set
+    with their default values.
+    """
+    cfg = get_default_configs(sql)
+    # we're overriding this in conftest.py
+    cfg["dsn_filename"] = "default.ini"
+    return cfg
+
 
 def test_dsn_filename_default_value(sql_magic):
     assert sql_magic.dsn_filename == str(
@@ -19,3 +45,263 @@ def test_dsn_filename_resolves_user_directory(sql_magic):
 
     # but it should resolve the path
     assert sql_magic.dsn_filename == expected
+
+
+def test_no_error_if_connection_file_doesnt_exist(tmp_empty, ip_no_magics):
+    ip_no_magics.run_cell("%config SqlMagic.dsn_filename = 'connections.ini'")
+
+    load_ipython_extension(ip_no_magics)
+
+    assert not Path("connections.ini").exists()
+
+
+def test_no_error_if_connection_file_doesnt_have_default_section(
+    tmp_empty, ip_no_magics
+):
+    Path("connections.ini").write_text(
+        """
+[duck]
+drivername = sqlite
+"""
+    )
+
+    ip_no_magics.run_cell("%config SqlMagic.dsn_filename = 'connections.ini'")
+
+    load_ipython_extension(ip_no_magics)
+
+    assert not ConnectionManager.connections
+
+
+def test_start_ini_default_connection_if_any(tmp_empty, ip_no_magics):
+    Path("connections.ini").write_text(
+        """
+[default]
+drivername = sqlite
+"""
+    )
+
+    ip_no_magics.run_cell("%config SqlMagic.dsn_filename = 'connections.ini'")
+
+    load_ipython_extension(ip_no_magics)
+
+    assert set(ConnectionManager.connections) == {"default"}
+    assert ConnectionManager.current.dialect == "sqlite"
+
+
+def test_start_ini_default_connection_using_pyproject_if_any(tmp_empty, ip_no_magics):
+    Path("pyproject.toml").write_text(
+        """
+[tool.jupysql.SqlMagic]
+dsn_filename = 'myconnections.ini'
+"""
+    )
+
+    Path("myconnections.ini").write_text(
+        """
+[default]
+drivername = duckdb
+"""
+    )
+
+    load_ipython_extension(ip_no_magics)
+
+    assert set(ConnectionManager.connections) == {"default"}
+    assert ConnectionManager.current.dialect == "duckdb"
+
+
+def test_magic_initialization_with_no_pyproject(tmp_empty, ip_no_magics):
+    load_ipython_extension(ip_no_magics)
+
+
+def test_magic_initialization_with_corrupted_pyproject(tmp_empty, ip_no_magics, capsys):
+    Path("pyproject.toml").write_text(
+        """
+[tool.jupysql.SqlMagic]
+dsn_filename = myconnections.ini
+"""
+    )
+
+    load_ipython_extension(ip_no_magics)
+
+    captured = capsys.readouterr()
+    assert "Could not load configuration file" in captured.out
+
+
+@pytest.mark.parametrize(
+    "file_content, expect, config_expected",
+    [
+        (
+            """
+[tool.jupysql.SqlMagic]
+autocommit = false
+autolimit = 1
+style = "RANDOM"
+""",
+            [
+                "Found pyproject.toml from '%s'",
+                "Settings changed:",
+                r"autocommit\s*\|\s*False",
+                r"autolimit\s*\|\s*1",
+                r"style\s*\|\s*RANDOM",
+            ],
+            {"autocommit": False, "autolimit": 1, "style": "RANDOM"},
+        ),
+        (
+            """
+[tool.jupysql.SqlMagic]
+""",
+            ["Found pyproject.toml from '%s'"],
+            {},
+        ),
+        (
+            """
+[test]
+github = "ploomber/jupysql"
+""",
+            ["Found pyproject.toml from '%s'"],
+            {},
+        ),
+        (
+            """
+[tool.pkgmt]
+github = "ploomber/jupysql"
+""",
+            ["Found pyproject.toml from '%s'"],
+            {},
+        ),
+        (
+            """
+[tool.jupysql.test]
+github = "ploomber/jupysql"
+""",
+            ["Found pyproject.toml from '%s'"],
+            {},
+        ),
+        (
+            "",
+            ["Found pyproject.toml from '%s'"],
+            {},
+        ),
+    ],
+)
+def test_loading_valid_pyproject_toml_shows_feedback_and_modifies_config(
+    tmp_empty,
+    ip_no_magics,
+    capsys,
+    file_content,
+    expect,
+    config_expected,
+):
+    Path("pyproject.toml").write_text(file_content)
+    toml_dir = os.getcwd()
+
+    os.mkdir("sub")
+    os.chdir("sub")
+
+    load_ipython_extension(ip_no_magics)
+
+    magic = ip_no_magics.find_magic("sql").__self__
+
+    combined = {**get_default_testing_configs(magic), **config_expected}
+    out, _ = capsys.readouterr()
+
+    expect[0] = expect[0] % (re.escape(toml_dir))
+    assert all(re.search(substring, out) for substring in expect)
+    assert get_current_configs(magic) == combined
+
+
+@pytest.mark.parametrize(
+    "file_content, error_msg",
+    [
+        (
+            """
+[tool.jupysql.SqlMagic]
+autocommit = true
+autocommit = true
+""",
+            "Duplicate key found : 'autocommit'",
+        ),
+        (
+            """
+[tool.jupySql.SqlMagic]
+autocommit = true
+""",
+            "'jupySql' is an invalid section name. Did you mean 'jupysql'?",
+        ),
+        (
+            """
+[tool.jupysql.SqlMagic]
+autocommit = True
+""",
+            (
+                "Invalid value 'True' in 'autocommit = True'. "
+                "Valid boolean values: true, false"
+            ),
+        ),
+        (
+            """
+[tool.jupysql.SqlMagic]
+autocommit = invalid
+""",
+            (
+                "Invalid value 'invalid' in 'autocommit = invalid'. "
+                "To use str value, enclose it with ' or \"."
+            ),
+        ),
+    ],
+)
+def test_error_on_toml_parsing(
+    tmp_empty, ip_no_magics, capsys, file_content, error_msg
+):
+    Path("pyproject.toml").write_text(file_content)
+    toml_dir = os.getcwd()
+    found_statement = "Found pyproject.toml from '%s'" % (toml_dir)
+    os.makedirs("sub")
+    os.chdir("sub")
+
+    with pytest.raises(UsageError) as excinfo:
+        load_ipython_extension(ip_no_magics)
+
+    out, _ = capsys.readouterr()
+
+    assert out.strip() == found_statement
+    assert excinfo.value.error_type == "ConfigurationError"
+    assert str(excinfo.value) == error_msg
+
+
+def test_valid_and_invalid_configs(tmp_empty, ip_no_magics, capsys):
+    Path("pyproject.toml").write_text(
+        """
+[tool.jupysql.SqlMagic]
+autocomm = true
+autop = false
+autolimit = "text"
+invalid = false
+displaycon = false
+"""
+    )
+    toml_dir = os.getcwd()
+    os.makedirs("sub")
+    os.chdir("sub")
+
+    load_ipython_extension(ip_no_magics)
+    out, _ = capsys.readouterr()
+    expect = [
+        "Found pyproject.toml from '%s'" % (re.escape(toml_dir)),
+        "'autocomm' is an invalid configuration. Did you mean 'autocommit'?",
+        (
+            "'autop' is an invalid configuration. "
+            "Did you mean 'autopandas', or 'autopolars'?"
+        ),
+        (
+            "'text' is an invalid value for 'autolimit'. "
+            "Please use int value instead."
+        ),
+        r"displaycon\s*\|\s*False",
+    ]
+    assert all(re.search(substring, out) for substring in expect)
+
+    # confirm the correct changes are applied
+    confirm = {"displaycon": False, "autolimit": 0}
+    sql = ip_no_magics.find_cell_magic("sql").__self__
+    assert all([getattr(sql, config) == value for config, value in confirm.items()])
diff --git a/src/tests/test_magic.py b/src/tests/test_magic.py
index dd39084..0954bc1 100644
--- a/src/tests/test_magic.py
+++ b/src/tests/test_magic.py
@@ -1349,7 +1349,6 @@ def mockValueWidget(monkeypatch):
 
 
 def test_interact_basic_widgets(ip, mockValueWidget, capsys):
-    print("mock", mockValueWidget.value)
     ip.user_global_ns["my_widget"] = mockValueWidget
 
     ip.run_cell(
@@ -1377,186 +1376,6 @@ def test_interact_and_missing_ipywidgets_installed(ip):
     )
 
 
-@pytest.mark.parametrize(
-    "file_content, expect, revert",
-    [
-        (
-            """
-[tool.jupysql.SqlMagic]
-autocommit = false
-autolimit = 1
-style = "RANDOM"
-""",
-            [
-                "Found pyproject.toml from '%s'",
-                "Settings changed:",
-                r"autocommit\s*\|\s*False",
-                r"autolimit\s*\|\s*1",
-                r"style\s*\|\s*RANDOM",
-            ],
-            {"autocommit": True, "autolimit": 0, "style": "DEFAULT"},
-        ),
-        (
-            """
-[tool.jupysql.SqlMagic]
-""",
-            ["Found pyproject.toml from '%s'"],
-            {},
-        ),
-        (
-            """
-[test]
-github = "ploomber/jupysql"
-""",
-            ["Found pyproject.toml from '%s'"],
-            {},
-        ),
-        (
-            """
-[tool.pkgmt]
-github = "ploomber/jupysql"
-""",
-            ["Found pyproject.toml from '%s'"],
-            {},
-        ),
-        (
-            """
-[tool.jupysql.test]
-github = "ploomber/jupysql"
-""",
-            ["Found pyproject.toml from '%s'"],
-            {},
-        ),
-        (
-            "",
-            ["Found pyproject.toml from '%s'"],
-            {},
-        ),
-    ],
-)
-def test_valid_loading_toml(tmp_empty, ip, capsys, file_content, expect, revert):
-    Path("pyproject.toml").write_text(file_content)
-    toml_dir = os.getcwd()
-    os.makedirs("sub")
-    os.chdir("sub")
-
-    ip.run_cell("%load_ext sql").result
-    out, _ = capsys.readouterr()
-
-    expect[0] = expect[0] % (re.escape(toml_dir))
-    assert all(re.search(substring, out) for substring in expect)
-
-    sql = ip.find_cell_magic("sql").__self__
-    [setattr(sql, config, value) for config, value in revert.items()]
-
-
-def test_no_toml(tmp_empty, ip, capsys):
-    os.makedirs("sub")
-    os.chdir("sub")
-
-    ip.run_cell("%load_ext sql").result
-    out, _ = capsys.readouterr()
-
-    assert out == ""
-
-
-@pytest.mark.parametrize(
-    "file_content, error_msg",
-    [
-        (
-            """
-[tool.jupysql.SqlMagic]
-autocommit = true
-autocommit = true
-""",
-            "Duplicate key found : 'autocommit'",
-        ),
-        (
-            """
-[tool.jupySql.SqlMagic]
-autocommit = true
-""",
-            "'jupySql' is an invalid section name. Did you mean 'jupysql'?",
-        ),
-        (
-            """
-[tool.jupysql.SqlMagic]
-autocommit = True
-""",
-            (
-                "Invalid value 'True' in 'autocommit = True'. "
-                "Valid boolean values: true, false"
-            ),
-        ),
-        (
-            """
-[tool.jupysql.SqlMagic]
-autocommit = invalid
-""",
-            (
-                "Invalid value 'invalid' in 'autocommit = invalid'. "
-                "To use str value, enclose it with ' or \"."
-            ),
-        ),
-    ],
-)
-def test_error_on_toml_parsing(tmp_empty, ip, capsys, file_content, error_msg):
-    Path("pyproject.toml").write_text(file_content)
-    toml_dir = os.getcwd()
-    found_statement = "Found pyproject.toml from '%s'" % (toml_dir)
-    os.makedirs("sub")
-    os.chdir("sub")
-
-    with pytest.raises(UsageError) as excinfo:
-        ip.run_cell("%load_ext sql")
-    out, _ = capsys.readouterr()
-
-    assert out.strip() == found_statement
-    assert excinfo.value.error_type == "ConfigurationError"
-    assert str(excinfo.value) == error_msg
-
-
-def test_valid_and_invalid_configs(tmp_empty, ip, capsys):
-    Path("pyproject.toml").write_text(
-        """
-[tool.jupysql.SqlMagic]
-autocomm = true
-autop = false
-autolimit = "text"
-invalid = false
-displaycon = false
-"""
-    )
-    toml_dir = os.getcwd()
-    os.makedirs("sub")
-    os.chdir("sub")
-
-    ip.run_cell("%load_ext sql")
-    out, _ = capsys.readouterr()
-    expect = [
-        "Found pyproject.toml from '%s'" % (re.escape(toml_dir)),
-        "'autocomm' is an invalid configuration. Did you mean 'autocommit'?",
-        (
-            "'autop' is an invalid configuration. "
-            "Did you mean 'autopandas', or 'autopolars'?"
-        ),
-        (
-            "'text' is an invalid value for 'autolimit'. "
-            "Please use int value instead."
-        ),
-        r"displaycon\s*\|\s*False",
-    ]
-    assert all(re.search(substring, out) for substring in expect)
-
-    # confirm the correct changes are applied
-    confirm = {"displaycon": False, "autolimit": 0}
-    sql = ip.find_cell_magic("sql").__self__
-    assert all([getattr(sql, config) == value for config, value in confirm.items()])
-
-    # revert back to a default setting
-    setattr(sql, "displaycon", True)
-
-
 @pytest.mark.parametrize(
     "fixture_name",
     [
diff --git a/src/tests/test_parse.py b/src/tests/test_parse.py
index 163dab3..045cb42 100644
--- a/src/tests/test_parse.py
+++ b/src/tests/test_parse.py
@@ -12,23 +12,21 @@ from sql.parse import (
     escape_string_literals_with_colon_prefix,
     find_named_parameters,
     _connection_string,
+    ConnectionsFile,
 )
 
-try:
-    from traitlets.config.configurable import Configurable
-except ImportError:
-    from IPython.config.configurable import Configurable
 
-empty_config = Configurable()
 default_connect_args = {"options": "-csearch_path=test"}
 
+PATH_TO_DSN_FILE = "src/tests/test_dsn_config.ini"
+
 
 class DummyConfig:
     dsn_filename = Path("src/tests/test_dsn_config.ini")
 
 
 def test_parse_no_sql():
-    assert parse("will:longliveliz@localhost/shakes", empty_config) == {
+    assert parse("will:longliveliz@localhost/shakes", PATH_TO_DSN_FILE) == {
         "connection": "will:longliveliz@localhost/shakes",
         "sql": "",
         "result_var": None,
@@ -39,7 +37,7 @@ def test_parse_no_sql():
 def test_parse_with_sql():
     assert parse(
         "postgresql://will:longliveliz@localhost/shakes SELECT * FROM work",
-        empty_config,
+        PATH_TO_DSN_FILE,
     ) == {
         "connection": "postgresql://will:longliveliz@localhost/shakes",
         "sql": "SELECT * FROM work",
@@ -49,7 +47,7 @@ def test_parse_with_sql():
 
 
 def test_parse_sql_only():
-    assert parse("SELECT * FROM work", empty_config) == {
+    assert parse("SELECT * FROM work", PATH_TO_DSN_FILE) == {
         "connection": "",
         "sql": "SELECT * FROM work",
         "result_var": None,
@@ -58,7 +56,7 @@ def test_parse_sql_only():
 
 
 def test_parse_postgresql_socket_connection():
-    assert parse("postgresql:///shakes SELECT * FROM work", empty_config) == {
+    assert parse("postgresql:///shakes SELECT * FROM work", PATH_TO_DSN_FILE) == {
         "connection": "postgresql:///shakes",
         "sql": "SELECT * FROM work",
         "result_var": None,
@@ -68,7 +66,7 @@ def test_parse_postgresql_socket_connection():
 
 def test_expand_environment_variables_in_connection():
     os.environ["DATABASE_URL"] = "postgresql:///shakes"
-    assert parse("$DATABASE_URL SELECT * FROM work", empty_config) == {
+    assert parse("$DATABASE_URL SELECT * FROM work", PATH_TO_DSN_FILE) == {
         "connection": "postgresql:///shakes",
         "sql": "SELECT * FROM work",
         "result_var": None,
@@ -77,7 +75,7 @@ def test_expand_environment_variables_in_connection():
 
 
 def test_parse_shovel_operator():
-    assert parse("dest << SELECT * FROM work", empty_config) == {
+    assert parse("dest << SELECT * FROM work", PATH_TO_DSN_FILE) == {
         "connection": "",
         "sql": "SELECT * FROM work",
         "result_var": "dest",
@@ -108,7 +106,7 @@ def test_parse_return_shovel_operator_with_equal(input_string, ip):
         "result_var": "dest",
         "return_result_var": True,
     }
-    assert parse(input_string, empty_config) == result_var
+    assert parse(input_string, PATH_TO_DSN_FILE) == result_var
 
 
 @pytest.mark.parametrize(
@@ -129,11 +127,11 @@ def test_parse_return_shovel_operator_without_equal(input_string, ip):
         "result_var": "dest",
         "return_result_var": False,
     }
-    assert parse(input_string, empty_config) == result_var
+    assert parse(input_string, PATH_TO_DSN_FILE) == result_var
 
 
 def test_parse_connect_plus_shovel():
-    assert parse("sqlite:// dest << SELECT * FROM work", empty_config) == {
+    assert parse("sqlite:// dest << SELECT * FROM work", PATH_TO_DSN_FILE) == {
         "connection": "sqlite://",
         "sql": "SELECT * FROM work",
         "result_var": "dest",
@@ -142,7 +140,7 @@ def test_parse_connect_plus_shovel():
 
 
 def test_parse_early_newlines():
-    assert parse("--comment\nSELECT *\n--comment\nFROM work", empty_config) == {
+    assert parse("--comment\nSELECT *\n--comment\nFROM work", PATH_TO_DSN_FILE) == {
         "connection": "",
         "sql": "--comment\nSELECT *\n--comment\nFROM work",
         "result_var": None,
@@ -151,7 +149,7 @@ def test_parse_early_newlines():
 
 
 def test_parse_connect_shovel_over_newlines():
-    assert parse("\nsqlite://\ndest\n<<\nSELECT *\nFROM work", empty_config) == {
+    assert parse("\nsqlite://\ndest\n<<\nSELECT *\nFROM work", PATH_TO_DSN_FILE) == {
         "connection": "sqlite://",
         "sql": "\nSELECT *\nFROM work",
         "result_var": "dest",
@@ -203,7 +201,7 @@ def test_connection_from_dsn_section(section, expected):
     ],
 )
 def test_connection_string(input_, expected):
-    assert _connection_string(input_, DummyConfig) == expected
+    assert _connection_string(input_, "src/tests/test_dsn_config.ini") == expected
 
 
 class Bunch:
@@ -383,3 +381,46 @@ def test_escape_string_literals_with_colon_prefix(
 )
 def test_find_named_parameters(query, expected):
     assert find_named_parameters(query) == expected
+
+
+@pytest.mark.parametrize(
+    "content, expected",
+    [
+        (
+            """
+[duck]
+drivername = duckdb
+""",
+            None,
+        ),
+        (
+            """
+[default]
+drivername = duckdb
+""",
+            "duckdb://",
+        ),
+        (
+            """
+[default]
+drivername = postgresql
+host = localhost
+port = 5432
+username = user
+password = pass
+database = db
+""",
+            "postgresql://user:pass@localhost:5432/db",
+        ),
+    ],
+    ids=[
+        "no-default",
+        "default",
+        "default-postgres",
+    ],
+)
+def test_connections_file_get_default_connection_url(tmp_empty, content, expected):
+    Path("conns.ini").write_text(content)
+
+    cf = ConnectionsFile(path_to_file="conns.ini")
+    assert cf.get_default_connection_url() == expected