Repository: ploomber/jupysql (python)

Issue:
A value is required for bind parameter 'fruit' (snowflake)
### What happens?

The snowflake query `select parse_json('{"fruit":"banana"}'):fruit`  errors with:
```
RuntimeError: (Your query contains named parameters (fruit) but the named parameters feature is disabled. Enable it with: %config SqlMagic.named_parameters=True)
(sqlalchemy.exc.InvalidRequestError) A value is required for bind parameter 'fruit'
[SQL: select parse_json('{"fruit":"banana"}')%(fruit)s]
```

Despite being valid snowflake sql. The `:` is [GET_PATH](https://docs.snowflake.com/en/sql-reference/functions/get_path) 

### To Reproduce

```python
from snowflake.sqlalchemy import URL
from sqlalchemy import create_engine

url = URL(
    user=...
    account=...
)

engine = create_engine(url)

%reload_ext sql

%config SqlMagic.feedback = False
%config SqlMagic.displaycon = False
%config SqlMagic.autopandas = True

%sql select parse_json('{"fruit":"banana"}'):fruit
```

### OS:

macos

### JupySQL Version:

0.10.7

### Full Name:

Oliver Mannion

### Affiliation:

Xero

Pull request:
Disable Named Parameters
## Describe your changes
- Edited the `named_parameters` option in config to have 3 options: 
  - `warn`: default, it behaves the same as we set it to right now False
  - `enabled`: behaves as setting to True
  - `disabled`: completely disables named parameters using exec_driver_sql
- Created a new file, `src/sql/traits.py`, which holds a custom traitlet type for named_parameters. This is necessary for backward compatibility ie. converting `named_parameters=True` to `named_parameters="enabled"`. The `Unicode` trait doesn't allow you to do this by default. For more info see here: https://traitlets.readthedocs.io/en/stable/defining_traits.html
- When named parameters are disabled, `connection.py` will now use `self._connection.exec_driver_sql()` rather than `self._connection.execute()`. This allows execution through sqlalchemy while bypassing the parsing of bind parameters and other sql compilation steps. Found this solution [here](https://github.com/bikeshedder/tusker/issues/18#issuecomment-1179173057).
- Updated `doc/api/configuration.md` and `doc/user-guide/template.md` to with new feature description

## Issue number

Closes #972 and #971

## Checklist before requesting a review

- [x] Performed a self-review of my code
- [x] Formatted my code with [`pkgmt format`](https://ploomber-contributing.readthedocs.io/en/latest/contributing/pr.html#linting-formatting)
- [x] Added [tests](https://ploomber-contributing.readthedocs.io/en/latest/contributing/pr.html#testing) (when necessary).
- [x] 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 -->
----
📚 Documentation preview 📚: https://jupysql--980.org.readthedocs.build/en/980/

<!-- readthedocs-preview jupysql end -->

Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index bca8bac..21d45b4 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -1,7 +1,12 @@
 # CHANGELOG
 
+* [Fix] Fix error when fuzzy matching configuration file (now we only match config keys) (#975 by @maciejb)
+* [Fix] Fix error that caused JupySQL to read a config file even when there was no JupySQL config (#975 by @maciejb)
+
 ## 0.10.9dev
 
+* [Feature] Add option to disable named parameters; options now changed to: `warn`, `enabled`, and `disabled` - fixes (#971) and (#972)
+
 ## 0.10.8 (2024-01-25)
 
 * [Feature] Add support for parametrizing string type arguments of `%%sql`, `%sqlplot`, `%sqlcmd`' ([#699](https://github.com/ploomber/jupysql/issues/699))
diff --git a/benchmarks/profiling.py b/benchmarks/profiling.py
index 96ea3db..7165817 100644
--- a/benchmarks/profiling.py
+++ b/benchmarks/profiling.py
@@ -4,6 +4,7 @@ Sample script to profile the sql magic.
 >>> pip install line_profiler
 >>> kernprof -lv profiling.py
 """
+
 from sql.magic import SqlMagic
 from IPython import InteractiveShell
 import duckdb
diff --git a/doc/api/configuration.md b/doc/api/configuration.md
index 254ea71..0ae1e34 100644
--- a/doc/api/configuration.md
+++ b/doc/api/configuration.md
@@ -256,15 +256,28 @@ res = %sql SELECT * FROM languages
 
 ## `named_parameters`
 
-```{versionadded} 0.9
+```{versionchanged} 0.10.9
 ```
 
-Default: `False`
+Default: `warn`
+
+If `warn`, a warning will be raised when named parameters are included in the statement.
+If `enabled`, the statement will be executed with named parameters enabled.
+If `disabled`, the statement will be executed with named parameters disabled.
+
+```{important}
+The `disabled` feature makes use of SQLAlchemy's `exec_driver_sql()` instead of `execute()`
+to execute SQL statements without the use of bound parameters. This operation doesn't include 
+other SQL compilation steps which could affect the behavior of your program.
+If you encounter problems, please open an issue on [Slack](https://ploomber.io/community) or [Github](https://github.com/ploomber/jupysql).
+```
+
+Learn more in the [tutorial.](../user-guide/template.md)
 
-If True, it enables named parameters `:variable`. Learn more in the [tutorial.](../user-guide/template.md)
+Named parameters can be declared with `:variable`. 
 
 ```{code-cell} ipython3
-%config SqlMagic.named_parameters=True
+%config SqlMagic.named_parameters="enabled"
 ```
 
 ```{code-cell} ipython3
diff --git a/doc/user-guide/template.md b/doc/user-guide/template.md
index 47b6108..02c7277 100644
--- a/doc/user-guide/template.md
+++ b/doc/user-guide/template.md
@@ -213,7 +213,7 @@ Let's verify data in one of the tables:
 (named-parameters)=
 ## Parametrization via `:variable`
 
-```{versionadded} 0.9
+```{versionchanged} 0.10.9
 ```
 
 There is a second method to parametrize variables via `:variable`. This method has the following limitations
@@ -225,7 +225,7 @@ There is a second method to parametrize variables via `:variable`. This method h
 To enable it:
 
 ```{code-cell} ipython3
-%config SqlMagic.named_parameters = True
+%config SqlMagic.named_parameters = "enabled"
 ```
 
 ```{code-cell} ipython3
@@ -294,3 +294,14 @@ sex = "FEMALE"
 %%sql
 SELECT * FROM one_sex
 ```
+
+### Disabling named parameters
+
+Sometimes, valid SQL can contain instances of `:x` which should not be mistaken as named parameters.
+In this case, you may want to disable named parameters:
+
+```{code-cell} ipython3
+%config SqlMagic.named_parameters = "disabled"
+```
+
+This can be helpful when executing statements which include JSON or other DB-specific syntax.
\ No newline at end of file
diff --git a/scripts/large-table-gen.py b/scripts/large-table-gen.py
index 33fb3dd..e63b791 100644
--- a/scripts/large-table-gen.py
+++ b/scripts/large-table-gen.py
@@ -1,5 +1,6 @@
 """Renter large-table-template.sql
 """
+
 from pathlib import Path
 from jinja2 import Template
 
diff --git a/src/sql/connection/connection.py b/src/sql/connection/connection.py
index fc4552a..7cd74fe 100644
--- a/src/sql/connection/connection.py
+++ b/src/sql/connection/connection.py
@@ -723,7 +723,6 @@ class SQLAlchemyConnection(AbstractConnection):
         parameters : dict, default None
             Parameters to use in the query (:variable format)
         """
-        parameters = parameters or {}
         # we do not support multiple statements
         if len(sqlparse.split(query)) > 1:
             raise NotImplementedError("Only one statement is supported.")
@@ -758,6 +757,10 @@ class SQLAlchemyConnection(AbstractConnection):
 
     def _execute_with_parameters(self, query, parameters):
         """Execute the query with the given parameters"""
+        if parameters == {}:
+            return self._connection.exec_driver_sql(query)
+
+        parameters = parameters or {}
         if IS_SQLALCHEMY_ONE:
             out = self._connection.execute(sqlalchemy.text(query), **parameters)
         else:
@@ -818,7 +821,7 @@ class SQLAlchemyConnection(AbstractConnection):
             return self._connection_execute(query, parameters)
         else:
             try:
-                return self._connection_execute(query)
+                return self._connection_execute(query, parameters)
             except StatementError as e:
                 # add a more helpful message if the users passes :variable but
                 # the feature isn't enabled
@@ -829,9 +832,20 @@ class SQLAlchemyConnection(AbstractConnection):
                         named_params_ = ", ".join(named_params)
                         e.add_detail(
                             f"Your query contains named parameters ({named_params_}) "
-                            "but the named parameters feature is disabled. Enable it "
-                            "with: %config SqlMagic.named_parameters=True"
+                            'but the named parameters feature is "warn". \nEnable it '
+                            'with: %config SqlMagic.named_parameters="enabled" \nor '
+                            "disable it with: "
+                            '%config SqlMagic.named_parameters="disabled"\n'
+                            "For more info, see the docs: "
+                            "https://jupysql.ploomber.io/en/latest/api/configuration.html#named-parameters"  # noqa
                         )
+                elif parameters == {}:
+                    e.add_detail(
+                        'The named parameters feature is "disabled". '
+                        'Enable it with: %config SqlMagic.named_parameters="enabled".\n'
+                        "For more info, see the docs: "
+                        "https://jupysql.ploomber.io/en/latest/api/configuration.html#named-parameters"  # noqa
+                    )
                 raise
 
     def _execute_with_error_handling(self, operation):
@@ -1129,9 +1143,7 @@ class SparkConnectConnection(AbstractConnection):
         mode = (
             "overwrite"
             if if_exists == "replace"
-            else "append"
-            if if_exists == "append"
-            else "error"
+            else "append" if if_exists == "append" else "error"
         )
         self._connection.createDataFrame(data_frame).write.mode(mode).saveAsTable(
             f"{schema}.{table_name}" if schema else table_name
diff --git a/src/sql/display.py b/src/sql/display.py
index 12d4d41..172c88d 100644
--- a/src/sql/display.py
+++ b/src/sql/display.py
@@ -1,6 +1,7 @@
 """
 A module to display confirmation messages and contextual information to the user
 """
+
 import html
 
 from prettytable import PrettyTable
diff --git a/src/sql/exceptions.py b/src/sql/exceptions.py
index 7e1b383..2f804da 100644
--- a/src/sql/exceptions.py
+++ b/src/sql/exceptions.py
@@ -10,6 +10,7 @@ IPython.core.error.UsageError doesn't work, so `exception_factory` is a workarou
 to create new errors that are IPython.core.error.UsageError but with a different name.
 
 """
+
 from IPython.core import error
 
 
diff --git a/src/sql/magic.py b/src/sql/magic.py
index 464bf29..e073b29 100644
--- a/src/sql/magic.py
+++ b/src/sql/magic.py
@@ -26,6 +26,7 @@ from sqlalchemy.exc import (
 )
 from traitlets.config.configurable import Configurable
 from traitlets import Bool, Int, TraitError, Unicode, Dict, observe, validate
+from sql.traits import Parameters
 
 import warnings
 import shlex
@@ -155,8 +156,8 @@ class SqlMagic(Magics, Configurable):
         "without expensive compute."
         "Currently only supported for Spark Connection.",
     )
-    named_parameters = Bool(
-        default_value=False,
+    named_parameters = Parameters(
+        default_value="warn",
         config=True,
         help=(
             "Allow named parameters in queries "
@@ -571,13 +572,14 @@ class SqlMagic(Magics, Configurable):
             display.message("Skipping execution...")
             return
 
+        parameters = None
+        if self.named_parameters == "disabled":
+            parameters = {}
+        elif self.named_parameters == "enabled":
+            parameters = user_ns
+
         try:
-            result = run_statements(
-                conn,
-                command.sql,
-                self,
-                parameters=user_ns if self.named_parameters else None,
-            )
+            result = run_statements(conn, command.sql, self, parameters=parameters)
 
             if (
                 result is not None
diff --git a/src/sql/plot.py b/src/sql/plot.py
index d3cecb7..ec4e22b 100644
--- a/src/sql/plot.py
+++ b/src/sql/plot.py
@@ -1,6 +1,7 @@
 """
 Plot using the SQL backend
 """
+
 from ploomber_core.dependencies import requires
 from ploomber_core.exceptions import modify_exceptions
 from jinja2 import Template
diff --git a/src/sql/traits.py b/src/sql/traits.py
new file mode 100644
index 0000000..c6f52ec
--- /dev/null
+++ b/src/sql/traits.py
@@ -0,0 +1,55 @@
+from traitlets import TraitError, TraitType
+from sql import display
+import warnings
+
+VALUE_WARNING = (
+    'Please use a valid option: "warn", "enabled", or "disabled". \n'
+    "For more information, "
+    "see the docs: "
+    "https://jupysql.ploomber.io/en/latest/api/configuration.html#named-parameters"
+)
+
+
+class Parameters(TraitType):
+    def __init__(self, **kwargs):
+        super(Parameters, self).__init__(**kwargs)
+
+    def validate(self, obj, value):
+        if isinstance(value, bool):
+            if value:
+                warnings.warn(
+                    "named_parameters: boolean values are now deprecated. "
+                    f'Value {value} will be treated as "enabled". \n'
+                    f"{VALUE_WARNING}",
+                    FutureWarning,
+                )
+                return "enabled"
+            else:
+                warnings.warn(
+                    "named_parameters: boolean values are now deprecated. "
+                    f'Value {value} will be treated as "warn" (default). \n'
+                    f"{VALUE_WARNING}",
+                    FutureWarning,
+                )
+                return "warn"
+        elif isinstance(value, str):
+            if not value:
+                display.message(
+                    'named_parameters: Value "" will be treated as "warn" (default)'
+                )
+                return "warn"
+
+            value = value.lower()
+            if value not in ("warn", "enabled", "disabled"):
+                raise TraitError(
+                    f"{value} is not a valid option for named_parameters. "
+                    f'Valid options are: "warn", "enabled", or "disabled".'
+                )
+
+            return value
+
+        else:
+            raise TraitError(
+                f"{value} is not a valid option for named_parameters. "
+                f'Valid options are: "warn", "enabled", or "disabled".'
+            )
diff --git a/src/sql/util.py b/src/sql/util.py
index b58419e..6022c3b 100644
--- a/src/sql/util.py
+++ b/src/sql/util.py
@@ -391,45 +391,52 @@ def get_user_configs(primary_path, alternate_path):
     # in that particular order
     path_list = [primary_path, alternate_path]
     for file_path in path_list:
-        section_to_find = None
         section_found = False
         if file_path and file_path.exists():
             data = load_toml(file_path)
-            section_names = ["tool", "jupysql", "SqlMagic"]
 
-            # Look for SqlMagic section in toml file
-            while section_names:
-                section_found = False
-                section_to_find, sections_from_user = section_names.pop(0), data.keys()
-
-                if section_to_find not in sections_from_user:
-                    close_match = difflib.get_close_matches(
-                        section_to_find, sections_from_user
-                    )
+            data = data.get("tool")
+
+            # Look for jupysql section under tool
+            if data:
+                keys = data.keys()
+                data = data.get("jupysql")
+                if data is None:
+                    similar_key = case_insensitive_match("jupysql", keys)
+                    if similar_key:
+                        display.message(
+                            f"Hint: We found 'tool.{similar_key}' in {file_path}. "
+                            f"Did you mean 'tool.jupysql'?"
+                        )
 
-                    if not close_match:
-                        if display_tip:
-                            display.message(
-                                f"Tip: You may define configurations in {primary_path}"
-                                f" or {alternate_path}. "
-                            )
-                            display_tip = False
-                        break
-                    else:
+            # Look for SqlMagic section under jupysql
+            if data:
+                keys = data.keys()
+                data = data.get("SqlMagic")
+                if data is None:
+                    similar_key_list = find_close_match("SqlMagic", keys)
+                    if similar_key_list:
                         raise exceptions.ConfigurationError(
-                            f"{pretty_print(close_match)} is an invalid section "
-                            f"name in {file_path}. "
-                            f"Did you mean '{section_to_find}'?"
+                            f"[tool.jupysql.{similar_key_list[0]}] is an "
+                            f"invalid section name in {file_path}. "
+                            f"Did you mean [tool.jupysql.SqlMagic]?"
                         )
 
+            if data is None:
+                if display_tip:
+                    display.message(
+                        f"Tip: You may define configurations in {primary_path}"
+                        f" or {alternate_path}. "
+                    )
+                    display_tip = False
+            elif data == {}:
+                section_found = True
+                display.message(
+                    f"[tool.jupysql.SqlMagic] present in {file_path} but empty. "
+                )
+                display_tip = False
+            else:
                 section_found = True
-                data = data[section_to_find]
-
-        if section_to_find == "SqlMagic" and section_found and not data:
-            display.message(
-                f"[tool.jupysql.SqlMagic] present in {file_path} but empty. "
-            )
-            display_tip = False
 
         if not display_tip and not configuration_docs_displayed:
             display.message_html(
@@ -443,7 +450,7 @@ def get_user_configs(primary_path, alternate_path):
         elif section_found and data:
             return data, file_path
 
-    return data, None
+    return None, None
 
 
 def get_default_configs(sql):
@@ -632,3 +639,32 @@ def expand_args(args, user_ns):
             else:
                 rendered_value = render_string_using_namespace(value, user_ns)
                 setattr(args, attribute, rendered_value)
+
+
+def case_insensitive_match(target, string_list):
+    """
+    Perform a case-insensitive match of a target string against a list of strings.
+
+    Parameters
+    ----------
+    target : str
+        The target string to match.
+    string_list : list of str
+        The list of strings to search through.
+
+    Returns
+    -------
+    str or None
+        The first matching string from the list, preserving its original case,
+        or None if there is no match.
+
+    Examples
+    --------
+    >>> case_insensitive_match('foo', ['bar', 'FOO'])
+    'FOO'
+    """
+    target_lower = target.lower()
+    for string in string_list:
+        if string.lower() == target_lower:
+            return string
+    return None


Test patch:
diff --git a/src/tests/test_config.py b/src/tests/test_config.py
index 7cbd937..a17d344 100644
--- a/src/tests/test_config.py
+++ b/src/tests/test_config.py
@@ -415,10 +415,11 @@ autocommit = true
         ),
         (
             """
-[tool.jupySql.SqlMagic]
+[tool.jupysql.SQLMagics]
 autocommit = true
 """,
-            "'jupySql' is an invalid section name in {path}. Did you mean 'jupysql'?",
+            "[tool.jupysql.SQLMagics] is an invalid section name in {path}. "
+            "Did you mean [tool.jupysql.SqlMagic]?",
         ),
         (
             """
@@ -584,6 +585,14 @@ autopandas=True
                 "[tool.jupysql.SqlMagic] present in {pyproject_path} but empty.",
             ],
         ),
+        (
+            "[tool.JupySQL.SqlMagic]",
+            "",
+            [
+                "Hint: We found 'tool.JupySQL' in {pyproject_path}. "
+                "Did you mean 'tool.jupysql'?",
+            ],
+        ),
     ],
 )
 def test_user_config_load_sequence_and_messages(
diff --git a/src/tests/test_magic.py b/src/tests/test_magic.py
index e241b97..a6617df 100644
--- a/src/tests/test_magic.py
+++ b/src/tests/test_magic.py
@@ -1569,9 +1569,13 @@ def test_error_suggests_turning_feature_on_if_it_detects_named_params(ip):
         ip.run_cell("%sql SELECT * FROM penguins.csv where species = :species")
 
     suggestion = (
-        "Your query contains named parameters (species) but the named "
-        "parameters feature is disabled. Enable it with: "
-        "%config SqlMagic.named_parameters=True"
+        "Your query contains named parameters (species) "
+        'but the named parameters feature is "warn". \nEnable it '
+        'with: %config SqlMagic.named_parameters="enabled" \nor '
+        "disable it with: "
+        '%config SqlMagic.named_parameters="disabled"\n'
+        "For more info, see the docs: "
+        "https://jupysql.ploomber.io/en/latest/api/configuration.html"
     )
     assert suggestion in str(excinfo.value)
 
@@ -2724,3 +2728,40 @@ drivername = duckdb
 
     conns = ConnectionManager.connections
     assert conns == {"duck": ANY}
+
+
+@pytest.mark.parametrize(
+    "query, expected",
+    [
+        (
+            '%sql select json(\'[{"a":1}, {"b":2}]\')',
+            '[{"a":1},{"b":2}]',
+        ),
+        (
+            '%sql select \'[{"a":1}, {"b":2}]\'::json',
+            '[{"a":1}, {"b":2}]',
+        ),
+    ],
+)
+def test_disable_named_parameters_with_json(ip, query, expected):
+    ip.run_cell("%sql duckdb://")
+    ip.run_cell("%config SqlMagic.named_parameters='disabled'")
+    result = ip.run_cell(query).result
+    assert str(list(result.dict().values())[0][0]) == expected
+
+
+def test_disabled_named_parameters_shows_disabled_warning(ip):
+    ip.run_cell("%config SqlMagic.named_parameters='disabled'")
+    query_should_warn = "%sql select json('[{\"a\"::1}')"
+
+    with pytest.raises(UsageError) as excinfo:
+        ip.run_cell(query_should_warn)
+
+    expected_warning = (
+        'The named parameters feature is "disabled". '
+        'Enable it with: %config SqlMagic.named_parameters="enabled".\n'
+        "For more info, see the docs: "
+        "https://jupysql.ploomber.io/en/latest/api/configuration.html"
+    )
+
+    assert expected_warning in str(excinfo.value)