Repository: ploomber/jupysql (python)

Issue:
improve duckdb integration docs
improve [duckdb's integration](https://jupysql.ploomber.io/en/latest/integrations/duckdb.html) documentation by showing how to pass parameters to the connection: 

https://github.com/Mause/duckdb_engine#configuration

https://jupysql.ploomber.io/en/latest/howto.html#connect-to-existing-engine


Pull request:
improved duckdb integration doc
## Describe your changes
passed parameters into connections

## Issue ticket number and link
Closes #174 

## 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--190.org.readthedocs.build/en/190/

<!-- readthedocs-preview jupysql end -->

Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 20c331c..147613c 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -1,6 +1,11 @@
 # CHANGELOG
 
-## 0.6.1dev
+## 0.6.2dev
+
+## 0.6.1 (2023-03-02)
+
+* [Feature] Support new variable substitution using `{{variable}}` format ([#137](https://github.com/ploomber/jupysql/pull/137))
+* [Fix] Adds support for newer versions of prettytable
 
 ## 0.6.0 (2023-02-27)
 
diff --git a/doc/_toc.yml b/doc/_toc.yml
index 1fa9fc1..215c1b4 100644
--- a/doc/_toc.yml
+++ b/doc/_toc.yml
@@ -13,6 +13,7 @@ parts:
     - file: compose
     - file: user-guide/tables-columns
     - file: plot-legacy
+    - file: user-guide/template
 
   - caption: Integrations
     chapters:
diff --git a/doc/community/vs.md b/doc/community/vs.md
index 06aac9f..808774a 100644
--- a/doc/community/vs.md
+++ b/doc/community/vs.md
@@ -6,4 +6,5 @@ JupySQL is an actively maintained fork of [ipython-sql](https://github.com/cathe
 
 If you're migrating from `ipython-sql` to JupySQL, these are the differences (it most cases, no code changes are needed):
 
-- Since `0.6` JupySQL no longer supports old versions of IPython
\ No newline at end of file
+- Since `0.6` JupySQL no longer supports old versions of IPython
+- Variable expansion is being replaced from `{variable}`, `${variable}` to `{{variable}}`
\ No newline at end of file
diff --git a/doc/integrations/duckdb.md b/doc/integrations/duckdb.md
index 28d4e5f..ba69cc2 100644
--- a/doc/integrations/duckdb.md
+++ b/doc/integrations/duckdb.md
@@ -249,3 +249,21 @@ SELECT *
 FROM df
 WHERE x > 95
 ```
+
+## Passing parameters to connection
+
+```{code-cell} ipython3
+from sqlalchemy import create_engine
+
+some_engine = create_engine(
+    'duckdb:///:memory:',
+    connect_args={
+        'preload_extensions': ['excel'],
+    }
+)
+```
+
+```{code-cell} ipython3
+%sql some_engine
+```
+
diff --git a/doc/intro.md b/doc/intro.md
index 350e492..e39597c 100644
--- a/doc/intro.md
+++ b/doc/intro.md
@@ -124,6 +124,10 @@ or a single dictionary with a tuple of scalar values per key (``result.dict()``)
 
 ## Variable substitution 
 
+```{versionchanged} 0.5.7
+This is a legacy API that's kept for backwards compatibility.
+```
+
 Bind variables (bind parameters) can be used in the "named" (:x) style.
 The variable names used should be defined in the local namespace.
 
@@ -166,6 +170,8 @@ can be used in multi-line ``%%sql``:
 FROM languages
 ```
 
++++
+
 ## Considerations
 
 Because jupysql accepts `--`-delimited options like `--persist`, but `--` 
diff --git a/doc/user-guide/template.md b/doc/user-guide/template.md
new file mode 100644
index 0000000..03bef4d
--- /dev/null
+++ b/doc/user-guide/template.md
@@ -0,0 +1,52 @@
+---
+jupytext:
+  text_representation:
+    extension: .md
+    format_name: myst
+    format_version: 0.13
+    jupytext_version: 1.14.5
+kernelspec:
+  display_name: Python 3 (ipykernel)
+  language: python
+  name: python3
+---
+
+# Template
+
+## Variable Expansion as `{{variable}}`
+
+We support the variable expansion in the form of `{{variable}}`, this also allows the user to write the query as template with some dynamic variables
+
+```{code-cell} ipython3
+:tags: [remove-cell]
+
+%load_ext sql
+from pathlib import Path
+from urllib.request import urlretrieve
+
+if not Path("penguins.csv").is_file():
+    urlretrieve(
+        "https://raw.githubusercontent.com/mwaskom/seaborn-data/master/penguins.csv",
+        "penguins.csv",
+    )
+%sql duckdb://
+```
+
+Now, let's give a simple query template and define some variables where we will apply in the template:
+
+```{code-cell} ipython3
+dynamic_limit = 5
+dynamic_column = "island, sex"
+```
+
+```{code-cell} ipython3
+%sql SELECT {{dynamic_column}} FROM penguins.csv LIMIT {{dynamic_limit}}
+```
+
+Note that variables will be fetched from the local namespace into the SQL statement.
+
+Please aware that we also support the `$variable` or `{variable_name}` way, but those will be deprecated in future version, [see more](https://jupysql.ploomber.io/en/latest/intro.html?highlight=variable#variable-substitution).
+
+```{code-cell} ipython3
+
+```
diff --git a/setup.py b/setup.py
index 899f687..1234d09 100644
--- a/setup.py
+++ b/setup.py
@@ -16,7 +16,7 @@ with open("src/sql/__init__.py", "rb") as f:
     )
 
 install_requires = [
-    "prettytable<1",
+    "prettytable",
     "ipython>=1.0",
     "sqlalchemy>=0.6.7,<2.0",
     "sqlparse",
diff --git a/src/sql/__init__.py b/src/sql/__init__.py
index 51ec0df..381261d 100644
--- a/src/sql/__init__.py
+++ b/src/sql/__init__.py
@@ -1,6 +1,6 @@
 from .magic import RenderMagic, SqlMagic, load_ipython_extension
 
-__version__ = "0.6.1dev"
+__version__ = "0.6.2dev"
 
 
 __all__ = [
diff --git a/src/sql/column_guesser.py b/src/sql/column_guesser.py
index 7a634c6..12fb291 100644
--- a/src/sql/column_guesser.py
+++ b/src/sql/column_guesser.py
@@ -28,16 +28,19 @@ class ColumnGuesserMixin(object):
     pie: ... y
     """
 
+    def __init__(self):
+        self.keys = None
+
     def _build_columns(self):
         self.columns = [Column() for col in self.keys]
         for row in self:
-            for (col_idx, col_val) in enumerate(row):
+            for col_idx, col_val in enumerate(row):
                 col = self.columns[col_idx]
                 col.append(col_val)
                 if (col_val is not None) and (not is_quantity(col_val)):
                     col.is_quantity = False
 
-        for (idx, key_name) in enumerate(self.keys):
+        for idx, key_name in enumerate(self.keys):
             self.columns[idx].name = key_name
 
         self.x = Column()
diff --git a/src/sql/command.py b/src/sql/command.py
index 319f0b6..11af091 100644
--- a/src/sql/command.py
+++ b/src/sql/command.py
@@ -1,4 +1,7 @@
+import re
+import warnings
 from IPython.core.magic_arguments import parse_argstring
+from jinja2 import Template
 
 from sqlalchemy.engine import Engine
 
@@ -19,12 +22,9 @@ class SQLCommand:
     """
 
     def __init__(self, magic, user_ns, line, cell) -> None:
-        # Parse variables (words wrapped in {}) for %%sql magic
-        # (for %sql this is done automatically)
-        cell = magic.shell.var_expand(cell)
-
+        # Support for the variable substition in the SQL clause
+        line, cell = self._var_expand(magic, user_ns, line, cell)
         self.args = parse.magic_args(magic.execute, line)
-
         # self.args.line (everything that appears after %sql/%%sql in the first line)
         # is splited in tokens (delimited by spaces), this checks if we have one arg
         one_arg = len(self.args.line) == 1
@@ -45,7 +45,6 @@ class SQLCommand:
             add_alias = True
         else:
             add_alias = False
-
         self.command_text = " ".join(line_for_command) + "\n" + cell
 
         if self.args.file:
@@ -89,3 +88,33 @@ class SQLCommand:
     def result_var(self):
         """Returns the result_var"""
         return self.parsed["result_var"]
+
+    def _var_expand(self, magic, user_ns, line, cell):
+        """
+        Support for the variable substition in the SQL clause
+        For now, we have enabled two ways:
+        1. Latest format, {{a}}, we use jinja2 to parse the string with {{a}} format
+        2. Legacy format, {a}, $a, and :a format.
+
+        We will deprecate the legacy format feature in next major version
+        """
+        self.is_legacy_var_expand_parsed = False
+        # Latest format parsing
+        # TODO: support --param and --use-global logic here
+        # Ref: https://github.com/ploomber/jupysql/issues/93
+        line = Template(line).render(user_ns)
+        cell = Template(cell).render(user_ns)
+        # Legacy format parsing
+        parsed_cell = magic.shell.var_expand(cell, depth=2)
+        parsed_line = magic.shell.var_expand(line, depth=2)
+        # Exclusive the string with "://", but has :variable
+        has_SQLAlchemy_var_expand = re.search("(?<!://):[^/]+", line) or ":" in cell
+        if parsed_line != line or parsed_cell != cell or has_SQLAlchemy_var_expand:
+            self.is_legacy_var_expand_parsed = True
+            warnings.warn(
+                "Variable substitution with $var and {var} has been "
+                "deprecated and will be removed in a future version. "
+                "Use {{var}} instead.",
+                FutureWarning,
+            )
+        return parsed_line, parsed_cell
diff --git a/src/sql/connection.py b/src/sql/connection.py
index 313e53d..516d79f 100644
--- a/src/sql/connection.py
+++ b/src/sql/connection.py
@@ -78,7 +78,7 @@ def get_missing_package_suggestion_str(e):
         module_name, MISSING_PACKAGE_LIST_EXCEPT_MATCHERS.keys()
     )
     if close_matches:
-        return f"Perhaps you meant to use driver the dialect: \"{close_matches[0]}\""
+        return f'Perhaps you meant to use driver the dialect: "{close_matches[0]}"'
     # Not found
     return (
         suggestion_prefix + "make sure you are using correct driver name:\n"
diff --git a/src/sql/magic.py b/src/sql/magic.py
index 9912350..c15cdcd 100644
--- a/src/sql/magic.py
+++ b/src/sql/magic.py
@@ -7,6 +7,7 @@ from IPython.core.magic import (
     line_magic,
     magics_class,
     needs_local_scope,
+    no_var_expand,
 )
 from IPython.core.magic_arguments import argument, magic_arguments, parse_argstring
 from sqlalchemy.exc import OperationalError, ProgrammingError, DatabaseError
@@ -19,6 +20,7 @@ from sql.command import SQLCommand
 from sql.magic_plot import SqlPlotMagic
 from sql.magic_cmd import SqlCmdMagic
 
+
 from traitlets.config.configurable import Configurable
 from traitlets import Bool, Int, Unicode, observe
 
@@ -134,6 +136,7 @@ class SqlMagic(Magics, Configurable):
                 setattr(self, other, False)
                 print(f"Disabled '{other}' since '{change['name']}' was enabled.")
 
+    @no_var_expand
     @needs_local_scope
     @line_magic("sql")
     @cell_magic("sql")
@@ -249,6 +252,9 @@ class SqlMagic(Magics, Configurable):
         # %%sql {line}
         # {cell}
 
+        if local_ns is None:
+            local_ns = {}
+
         # save globals and locals so they can be referenced in bind vars
         user_ns = self.shell.user_ns.copy()
         user_ns.update(local_ns)
diff --git a/src/sql/parse.py b/src/sql/parse.py
index 6eb11a1..3c04e9d 100644
--- a/src/sql/parse.py
+++ b/src/sql/parse.py
@@ -15,7 +15,6 @@ def connection_from_dsn_section(section, config):
 
 
 def _connection_string(s, config):
-
     s = expandvars(s)  # for environment variables
     if "@" in s or "://" in s:
         return s
diff --git a/src/sql/plot.py b/src/sql/plot.py
index f0de992..7aeddee 100644
--- a/src/sql/plot.py
+++ b/src/sql/plot.py
@@ -244,9 +244,7 @@ def boxplot(payload, table, column, *, orient="v", with_=None, conn=None):
     if not conn:
         conn = sql.connection.Connection.current.session
 
-    payload[
-            "connection_info"
-        ] = sql.connection.Connection._get_curr_connection_info()
+    payload["connection_info"] = sql.connection.Connection._get_curr_connection_info()
 
     ax = plt.gca()
     vert = orient == "v"
@@ -328,9 +326,7 @@ def histogram(payload, table, column, bins, with_=None, conn=None):
     .. plot:: ../examples/plot_histogram_many.py
     """
     ax = plt.gca()
-    payload[
-            "connection_info"
-        ] = sql.connection.Connection._get_curr_connection_info()
+    payload["connection_info"] = sql.connection.Connection._get_curr_connection_info()
     if isinstance(column, str):
         bin_, height = _histogram(table, column, bins, with_=with_, conn=conn)
         ax.bar(bin_, height, align="center", width=bin_[-1] - bin_[-2])
diff --git a/src/sql/run.py b/src/sql/run.py
index faa5f15..77969db 100644
--- a/src/sql/run.py
+++ b/src/sql/run.py
@@ -101,21 +101,19 @@ class ResultSet(list, ColumnGuesserMixin):
     Can access rows listwise, or by string value of leftmost column.
     """
 
-    def __init__(self, sqlaproxy, sql, config):
-        self.keys = sqlaproxy.keys()
-        self.sql = sql
+    def __init__(self, sqlaproxy, config):
         self.config = config
-        self.limit = config.autolimit
-        style_name = config.style
-        self.style = prettytable.__dict__[style_name.upper()]
+        self.keys = {}
         if sqlaproxy.returns_rows:
-            if self.limit:
-                list.__init__(self, sqlaproxy.fetchmany(size=self.limit))
+            self.keys = sqlaproxy.keys()
+            if config.autolimit:
+                list.__init__(self, sqlaproxy.fetchmany(size=config.autolimit))
             else:
                 list.__init__(self, sqlaproxy.fetchall())
             self.field_names = unduplicate_field_names(self.keys)
-            self.pretty = PrettyTable(self.field_names, style=self.style)
-            # self.pretty.set_style(self.style)
+            self.pretty = PrettyTable(
+                self.field_names, style=prettytable.__dict__[config.style.upper()]
+            )
         else:
             list.__init__(self, [])
             self.pretty = None
@@ -405,7 +403,7 @@ def run(conn, sql, config, user_namespace):
             _commit(conn=conn, config=config)
             if result and config.feedback:
                 print(interpret_rowcount(result.rowcount))
-        resultset = ResultSet(result, statement, config)
+        resultset = ResultSet(result, config)
         if config.autopandas:
             return resultset.DataFrame()
         elif config.autopolars:


Test patch:
diff --git a/src/tests/test_command.py b/src/tests/test_command.py
index a0aaabe..63ee871 100644
--- a/src/tests/test_command.py
+++ b/src/tests/test_command.py
@@ -170,7 +170,47 @@ def test_parse_sql_when_passing_engine(ip, sql_magic, tmp_empty, line):
     assert cmd.sql_original == sql_expected
 
 
-def test_variable_substitution_cell_magic(ip, sql_magic):
+def test_variable_substitution_legacy_warning_message_dollar_prefix(
+    ip, sql_magic, capsys
+):
+    with pytest.warns(FutureWarning):
+        ip.user_global_ns["limit_number"] = 1
+        ip.run_cell_magic(
+            "sql",
+            "",
+            """
+            SELECT * FROM author LIMIT $limit_number
+            """,
+        )
+
+
+def test_variable_substitution_legacy_warning_message_single_curly(
+    ip, sql_magic, capsys
+):
+    with pytest.warns(FutureWarning):
+        ip.user_global_ns["limit_number"] = 1
+        ip.run_cell_magic(
+            "sql",
+            "",
+            """
+            SELECT * FROM author LIMIT {limit_number}
+            """,
+        )
+
+
+def test_variable_substitution_legacy_warning_message_colon(ip, sql_magic, capsys):
+    with pytest.warns(FutureWarning):
+        ip.user_global_ns["limit_number"] = 1
+        ip.run_cell_magic(
+            "sql",
+            "",
+            """
+            SELECT * FROM author LIMIT :limit_number
+            """,
+        )
+
+
+def test_variable_substitution_legacy_dollar_prefix_cell_magic(ip, sql_magic):
     ip.user_global_ns["username"] = "some-user"
 
     cmd = SQLCommand(
@@ -181,3 +221,44 @@ def test_variable_substitution_cell_magic(ip, sql_magic):
     )
 
     assert cmd.parsed["sql"] == "\nGRANT CONNECT ON DATABASE postgres TO some-user;"
+
+
+def test_variable_substitution_legacy_single_curly_cell_magic(ip, sql_magic):
+    ip.user_global_ns["username"] = "some-user"
+
+    cmd = SQLCommand(
+        sql_magic,
+        ip.user_ns,
+        line="",
+        cell="GRANT CONNECT ON DATABASE postgres TO {username};",
+    )
+
+    assert cmd.parsed["sql"] == "\nGRANT CONNECT ON DATABASE postgres TO some-user;"
+
+
+def test_variable_substitution_double_curly_cell_magic(ip, sql_magic):
+    ip.user_global_ns["username"] = "some-user"
+
+    cmd = SQLCommand(
+        sql_magic,
+        ip.user_ns,
+        line="",
+        cell="GRANT CONNECT ON DATABASE postgres TO {{username}};",
+    )
+
+    print("cmd.parsed['sql']", cmd.parsed["sql"])
+    assert cmd.parsed["sql"] == "\nGRANT CONNECT ON DATABASE postgres TO some-user;"
+
+
+def test_variable_substitution_double_curly_line_magic(ip, sql_magic):
+    ip.user_global_ns["limit_number"] = 5
+    ip.user_global_ns["column_name"] = "first_name"
+    cmd = SQLCommand(
+        sql_magic,
+        ip.user_ns,
+        line="SELECT {{column_name}} FROM author LIMIT {{limit_number}};",
+        cell="",
+    )
+
+    # print ("cmd.parsed['sql']", cmd.parsed["sql"])
+    assert cmd.parsed["sql"] == "SELECT first_name FROM author LIMIT 5;\n"
diff --git a/src/tests/test_magic.py b/src/tests/test_magic.py
index 89622b4..8b0086c 100644
--- a/src/tests/test_magic.py
+++ b/src/tests/test_magic.py
@@ -255,10 +255,11 @@ def test_autopolars(ip):
     dframe = runsql(ip, "SELECT * FROM test;")
 
     import polars as pl
+
     assert type(dframe) == pl.DataFrame
     assert not dframe.is_empty()
     assert len(dframe.shape) == 2
-    assert dframe['name'][0] == "foo"
+    assert dframe["name"][0] == "foo"
 
 
 def test_mutex_autopolars_autopandas(ip):
@@ -266,11 +267,13 @@ def test_mutex_autopolars_autopandas(ip):
     assert type(dframe) == ResultSet
 
     import polars as pl
+
     ip.run_line_magic("config", "SqlMagic.autopolars = True")
     dframe = runsql(ip, "SELECT * FROM test;")
     assert type(dframe) == pl.DataFrame
 
     import pandas as pd
+
     ip.run_line_magic("config", "SqlMagic.autopandas = True")
     dframe = runsql(ip, "SELECT * FROM test;")
     assert type(dframe) == pd.DataFrame
diff --git a/src/tests/test_parse.py b/src/tests/test_parse.py
index 8297d06..3777214 100644
--- a/src/tests/test_parse.py
+++ b/src/tests/test_parse.py
@@ -101,7 +101,6 @@ class DummyConfig:
 
 
 def test_connection_from_dsn_section():
-
     result = connection_from_dsn_section(section="DB_CONFIG_1", config=DummyConfig())
     assert result == "postgres://goesto11:seentheelephant@my.remote.host:5432/pgmain"
     result = connection_from_dsn_section(section="DB_CONFIG_2", config=DummyConfig())
@@ -132,54 +131,46 @@ parser_stub = ParserStub()
 
 
 def test_without_sql_comment_plain():
-
     line = "SELECT * FROM author"
     assert without_sql_comment(parser=parser_stub, line=line) == line
 
 
 def test_without_sql_comment_with_arg():
-
     line = "--file moo.txt --persist SELECT * FROM author"
     assert without_sql_comment(parser=parser_stub, line=line) == line
 
 
 def test_without_sql_comment_with_comment():
-
     line = "SELECT * FROM author -- uff da"
     expected = "SELECT * FROM author"
     assert without_sql_comment(parser=parser_stub, line=line) == expected
 
 
 def test_without_sql_comment_with_arg_and_comment():
-
     line = "--file moo.txt --persist SELECT * FROM author -- uff da"
     expected = "--file moo.txt --persist SELECT * FROM author"
     assert without_sql_comment(parser=parser_stub, line=line) == expected
 
 
 def test_without_sql_comment_unspaced_comment():
-
     line = "SELECT * FROM author --uff da"
     expected = "SELECT * FROM author"
     assert without_sql_comment(parser=parser_stub, line=line) == expected
 
 
 def test_without_sql_comment_dashes_in_string():
-
     line = "SELECT '--very --confusing' FROM author -- uff da"
     expected = "SELECT '--very --confusing' FROM author"
     assert without_sql_comment(parser=parser_stub, line=line) == expected
 
 
 def test_without_sql_comment_with_arg_and_leading_comment():
-
     line = "--file moo.txt --persist --comment, not arg"
     expected = "--file moo.txt --persist"
     assert without_sql_comment(parser=parser_stub, line=line) == expected
 
 
 def test_without_sql_persist():
-
     line = "--persist my_table --uff da"
     expected = "--persist my_table"
     assert without_sql_comment(parser=parser_stub, line=line) == expected
@@ -224,7 +215,6 @@ def complete_with_defaults(mapping):
     ],
 )
 def test_magic_args(ip, line, expected):
-
     sql_line = ip.magics_manager.lsmagic()["line"]["sql"]
 
     args = magic_args(sql_line, line)