Repository: ploomber/jupysql
Pull request #293 (2023-03-22 17:52:16)

interactive parametrized SQL queries
## Describe your changes

## Issue ticket number and link
Closes #x

## Checklist before requesting a review

- [ ] I have performed a self-review of my code
- [ ] I have added thorough [tests](https://github.com/ploomber/contributing/blob/main/CONTRIBUTING.md#testing) (when necessary).
- [ ] 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--293.org.readthedocs.build/en/293/

<!-- readthedocs-preview jupysql end -->

edublancas: also, please address the merging conflicts

Patch:
diff --git a/.github/pull_request_template.md b/.github/pull_request_template.md
index 31cf702..f37a5a2 100644
--- a/.github/pull_request_template.md
+++ b/.github/pull_request_template.md
@@ -1,11 +1,13 @@
 ## Describe your changes
 
-## Issue ticket number and link
-Closes #x
+## Issue number
+
+Closes #X
 
 ## Checklist before requesting a review
 
-- [ ] I have performed a self-review of my code
-- [ ] I have added thorough [tests](https://github.com/ploomber/contributing/blob/main/CONTRIBUTING.md#testing) (when necessary).
-- [ ] 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)
+- [ ] Performed a self-review of my code
+- [ ] Formatted my code with [`pkgmt format`](https://github.com/ploomber/contributing/blob/main/CONTRIBUTING.md#lintingformatting)
+- [ ] Added [tests](https://github.com/ploomber/contributing/blob/main/CONTRIBUTING.md#testing) (when necessary).
+- [ ] Added documentation in the [docstring](https://github.com/ploomber/contributing/blob/main/CONTRIBUTING.md#documenting-changes-and-new-features) and [changelog](https://github.com/ploomber/contributing/blob/main/CONTRIBUTING.md#changelog) (when needed)
 
diff --git a/CHANGELOG.md b/CHANGELOG.md
index d847948..f06ce20 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -5,7 +5,9 @@
 * [API Change] Deprecates old SQL parametrization: `$var`, `:var`, and `{var}` in favor of `{{var}}`
 * [Fix] `--save` + `--with` double quotes syntax error in MySQL ([#145](https://github.com/ploomber/jupysql/issues/145))
 * [Feature] Adds sql magic test to list of possible magics to test datasets
+* [Feature] Adds `--interact` argument to `%%sql` to enable interactivity in parametrized SQL queries (#293)
 * [Feature] Results parse HTTP URLs to make them clickable (#230)
+* [Feature] Adds `ggplot` plotting API (histogram and boxplot)
 
 ## 0.6.6 (2023-03-16)
 
diff --git a/doc/_toc.yml b/doc/_toc.yml
index fe2006a..694997d 100644
--- a/doc/_toc.yml
+++ b/doc/_toc.yml
@@ -14,6 +14,7 @@ parts:
     - file: user-guide/tables-columns
     - file: plot-legacy
     - file: user-guide/template
+    - file: user-guide/interactive
     - file: user-guide/data-profiling
     - file: user-guide/ggplot
     
diff --git a/doc/environment.yml b/doc/environment.yml
index 70b43dd..9fba620 100644
--- a/doc/environment.yml
+++ b/doc/environment.yml
@@ -10,6 +10,7 @@ dependencies:
   - pandas
   - pip
   - pip:
+    - -e ..
     - jupyter-book
     # duckdb example
     - duckdb>=0.7.1
@@ -22,4 +23,5 @@ dependencies:
     - polars
     # for developer guide
     - pytest
-    - -e ..
\ No newline at end of file
+    # for %%sql --interact
+    - ipywidgets
\ No newline at end of file
diff --git a/doc/user-guide/interactive.md b/doc/user-guide/interactive.md
new file mode 100644
index 0000000..d38c03a
--- /dev/null
+++ b/doc/user-guide/interactive.md
@@ -0,0 +1,125 @@
+---
+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
+---
+
+# Interactive SQL Queries
+
+```{note}
+This feature will be released in version 0.7, but you can give it a try now!
+
+~~~
+pip uninstall jupysql -y
+pip install git+https://github.com/ploomber/jupysql
+~~~
+```
+
+
+Interactive command allows you to visualize and manipulate widget and interact with your SQL cluase.
+We will demonstrate how to create widgets and dynamically query the dataset.
+
+```{note}
+`%sql --interact` requires `ipywidgets`: `pip install ipywidgets`
+```
+
+## `%sql --interact {{widget_variable}}`
+
+First, you need to define the variable as the form of basic data type or ipywidgets Widget.
+Then pass the variable name into `--interact` argument
+
+```{code-cell} ipython3
+%load_ext sql
+import ipywidgets as widgets
+
+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://
+```
+
+## Basic Data Types
+
+The simplest way is to declare a variable with basic data types (Numeric, Text, Boolean...), the [ipywidgets](https://ipywidgets.readthedocs.io/en/stable/examples/Using%20Interact.html?highlight=interact#Basic-interact) will autogenerates UI controls for those variables
+
+```{code-cell} ipython3
+body_mass_min = 3500
+%sql --interact body_mass_min SELECT * FROM penguins.csv WHERE body_mass_g > {{body_mass_min}} LIMIT 5
+```
+
+```{code-cell} ipython3
+island = (  # Try to change Torgersen to Biscoe, Torgersen or Dream in the below textbox
+    "Torgersen"
+)
+%sql --interact island SELECT * FROM penguins.csv WHERE island == '{{island}}' LIMIT 5
+```
+
+## `ipywidgets` Widget
+
+You can use widgets to build fully interactive GUIs for your SQL clause.
+
+See more for complete [Widget List](https://ipywidgets.readthedocs.io/en/stable/examples/Widget%20List.html)
+
++++
+
+### IntSlider
+
+```{code-cell} ipython3
+body_mass_lower_bound = widgets.IntSlider(min=2500, max=3500, step=25, value=3100)
+
+%sql --interact body_mass_lower_bound SELECT * FROM penguins.csv WHERE body_mass_g <= {{body_mass_lower_bound}} LIMIT 5
+```
+
+### FloatSlider
+
+```{code-cell} ipython3
+bill_length_mm_lower_bound = widgets.FloatSlider(
+    min=35.0, max=45.0, step=0.1, value=40.0
+)
+
+%sql --interact bill_length_mm_lower_bound SELECT * FROM penguins.csv WHERE bill_length_mm <= {{bill_length_mm_lower_bound}} LIMIT 5
+```
+
+## Complete Example
+
+To demostrate the way to combine basic data type and ipywidgets into our interactive SQL Clause
+
+```{code-cell} ipython3
+body_mass_lower_bound = 3600
+show_limit = (0, 50, 1)
+sex_selection = widgets.RadioButtons(
+    options=["MALE", "FEMALE"], description="Sex", disabled=False
+)
+species_selections = widgets.SelectMultiple(
+    options=["Adelie", "Chinstrap", "Gentoo"],
+    value=["Adelie", "Chinstrap"],
+    # rows=10,
+    description="Species",
+    disabled=False,
+)
+```
+
+```{code-cell} ipython3
+%%sql --interact show_limit --interact body_mass_lower_bound --interact species_selections --interact sex_selection
+SELECT * FROM penguins.csv 
+WHERE species IN{{species_selections}} AND 
+body_mass_g > {{body_mass_lower_bound}} AND 
+sex == '{{sex_selection}}'
+LIMIT {{show_limit}} 
+```
+
+```{code-cell} ipython3
+
+```
diff --git a/setup.py b/setup.py
index 4c5a997..be9e52d 100644
--- a/setup.py
+++ b/setup.py
@@ -24,8 +24,8 @@ install_requires = [
     "sqlglot",
     "jinja2",
     "sqlglot>=11.3.7",
-    "ploomber-core>=0.2.4",
-    'importlib-metadata;python_version<"3.8"'
+    "ploomber-core>=0.2.7",
+    'importlib-metadata;python_version<"3.8"',
 ]
 
 DEV = [
@@ -42,6 +42,8 @@ DEV = [
     # sql.plot module tests
     "matplotlib",
     "black",
+    # for %%sql --interact
+    "ipywidgets",
 ]
 
 setup(
diff --git a/src/sql/ggplot/aes.py b/src/sql/ggplot/aes.py
index 62b69f3..8ecb50e 100644
--- a/src/sql/ggplot/aes.py
+++ b/src/sql/ggplot/aes.py
@@ -1,3 +1,5 @@
+from sql.telemetry import telemetry
+
 
 class aes():
     """
@@ -15,6 +17,7 @@ class aes():
         The edge color of a shape
     """
 
+    @telemetry.log_call("aes-init")
     def __init__(self,
                  x=None,
                  fill=None,
diff --git a/src/sql/ggplot/facet_wrap.py b/src/sql/ggplot/facet_wrap.py
index 74ce746..387e127 100644
--- a/src/sql/ggplot/facet_wrap.py
+++ b/src/sql/ggplot/facet_wrap.py
@@ -2,6 +2,7 @@ from jinja2 import Template
 import math
 import sql.connection
 from sql.store import store
+from sql.telemetry import telemetry
 
 
 def _run_query(query, with_=None, conn=None):
@@ -44,7 +45,7 @@ class facet_wrap(facet):
     facet : str
         Column to groupby and plot on different panels.
     """
-
+    @telemetry.log_call("facet-wrap-init")
     def __init__(self, facet: str, legend=True):
         self.facet = facet
         self.legend = legend
diff --git a/src/sql/ggplot/geom/geom_boxplot.py b/src/sql/ggplot/geom/geom_boxplot.py
index 908c00f..8203b7e 100644
--- a/src/sql/ggplot/geom/geom_boxplot.py
+++ b/src/sql/ggplot/geom/geom_boxplot.py
@@ -1,5 +1,6 @@
 from sql import plot
 from sql.ggplot.geom.geom import geom
+from sql.telemetry import telemetry
 
 
 class geom_boxplot(geom):
@@ -10,6 +11,7 @@ class geom_boxplot(geom):
     def __init__(self):
         pass
 
+    @telemetry.log_call("ggplot-boxplot")
     def draw(self, gg, ax=None):
         plot.boxplot(
             table=gg.table,
diff --git a/src/sql/ggplot/geom/geom_histogram.py b/src/sql/ggplot/geom/geom_histogram.py
index 4e3efad..7351262 100644
--- a/src/sql/ggplot/geom/geom_histogram.py
+++ b/src/sql/ggplot/geom/geom_histogram.py
@@ -1,5 +1,6 @@
 from sql import plot
 from sql.ggplot.geom.geom import geom
+from sql.telemetry import telemetry
 
 
 class geom_histogram(geom):
@@ -25,6 +26,7 @@ class geom_histogram(geom):
         self.cmap = cmap
         super().__init__(**kwargs)
 
+    @telemetry.log_call("ggplot-histogram")
     def draw(self, gg, ax=None, facet=None):
         plot.histogram(table=gg.table,
                        column=gg.mapping.x,
diff --git a/src/sql/ggplot/ggplot.py b/src/sql/ggplot/ggplot.py
index 80bd158..a3077d2 100644
--- a/src/sql/ggplot/ggplot.py
+++ b/src/sql/ggplot/ggplot.py
@@ -4,6 +4,7 @@ from sql.ggplot.facet_wrap import facet_wrap
 import matplotlib as mpl
 import matplotlib.pyplot as plt
 from ploomber_core.dependencies import requires
+from sql.telemetry import telemetry
 
 
 def _expand_to_multipanel_ax(figure, ax_to_clear=None):
@@ -26,6 +27,7 @@ class ggplot:
     figure: mpl.figure.Figure
     axs: list
 
+    @telemetry.log_call("ggplot-init")
     def __init__(self, table, mapping: aes = None, conn=None, with_=None) -> None:
         self.table = table
         self.with_ = [with_] if with_ else None
diff --git a/src/sql/magic.py b/src/sql/magic.py
index a0bb303..76250aa 100644
--- a/src/sql/magic.py
+++ b/src/sql/magic.py
@@ -1,5 +1,10 @@
 import json
 import re
+
+try:
+    from ipywidgets import interact
+except ModuleNotFoundError:
+    interact = None
 from ploomber_core.exceptions import modify_exceptions
 from IPython.core.magic import (
     Magics,
@@ -20,7 +25,7 @@ from sql.store import store
 from sql.command import SQLCommand
 from sql.magic_plot import SqlPlotMagic
 from sql.magic_cmd import SqlCmdMagic
-
+from ploomber_core.dependencies import check_installed
 
 from traitlets.config.configurable import Configurable
 from traitlets import Bool, Int, Unicode, observe
@@ -33,6 +38,8 @@ except ImportError:
 
 from sql.telemetry import telemetry
 
+SUPPORT_INTERACTIVE_WIDGETS = ["Checkbox", "Text", "IntSlider", ""]
+
 
 @magics_class
 class RenderMagic(Magics):
@@ -206,6 +213,12 @@ class SqlMagic(Magics, Configurable):
         type=str,
         help="Assign an alias to the connection",
     )
+    @argument(
+        "--interact",
+        type=str,
+        action="append",
+        help="Interactive mode",
+    )
     def execute(self, line="", cell="", local_ns=None):
         """
         Runs SQL statement against a database, specified by
@@ -233,10 +246,17 @@ class SqlMagic(Magics, Configurable):
           mysql+pymysql://me:mypw@localhost/mydb
 
         """
-        return self._execute(line=line, cell=cell, local_ns=local_ns)
+        return self._execute(
+            line=line, cell=cell, local_ns=local_ns, is_interactive_mode=False
+        )
 
     @telemetry.log_call("execute", payload=True)
-    def _execute(self, payload, line, cell, local_ns):
+    def _execute(self, payload, line, cell, local_ns, is_interactive_mode=False):
+        def interactive_execute_wrapper(**kwargs):
+            for key, value in kwargs.items():
+                local_ns[key] = value
+            return self._execute(line, cell, local_ns, is_interactive_mode=True)
+
         """
         This function implements the cell logic; we create this private
         method so we can control how the function is called. Otherwise,
@@ -252,7 +272,6 @@ class SqlMagic(Magics, Configurable):
 
         # %%sql {line}
         # {cell}
-
         if local_ns is None:
             local_ns = {}
 
@@ -264,6 +283,18 @@ class SqlMagic(Magics, Configurable):
         # args.line: contains the line after the magic with all options removed
 
         args = command.args
+        # Create the interactive slider
+        if args.interact and not is_interactive_mode:
+            check_installed(["ipywidgets"], "--interactive argument")
+            interactive_dict = {}
+            for key in args.interact:
+                interactive_dict[key] = local_ns[key]
+            print(
+                "Interactive mode, please interact with below "
+                "widget(s) to control the variable"
+            )
+            interact(interactive_execute_wrapper, **interactive_dict)
+            return
         if args.connections:
             return sql.connection.Connection.connections
         elif args.close:
@@ -317,7 +348,6 @@ class SqlMagic(Magics, Configurable):
 
         if not command.sql:
             return
-
         # store the query if needed
         if args.save:
             if "-" in args.save:
@@ -418,3 +448,6 @@ def load_ipython_extension(ip):
     ip.register_magics(RenderMagic)
     ip.register_magics(SqlPlotMagic)
     ip.register_magics(SqlCmdMagic)
+
+
+# %%


Test patch:
diff --git a/src/tests/test_command.py b/src/tests/test_command.py
index 4e20f42..e0ee9f5 100644
--- a/src/tests/test_command.py
+++ b/src/tests/test_command.py
@@ -133,6 +133,7 @@ def test_args(ip, sql_magic):
         "append": False,
         "connection_arguments": None,
         "file": None,
+        "interact": None,
         "save": None,
         "with_": ["author_one"],
         "no_execute": False,
diff --git a/src/tests/test_magic.py b/src/tests/test_magic.py
index 7d6afb8..050e1a3 100644
--- a/src/tests/test_magic.py
+++ b/src/tests/test_magic.py
@@ -2,13 +2,14 @@ import platform
 from pathlib import Path
 import os.path
 import re
+import sys
 import tempfile
 from textwrap import dedent
+from unittest.mock import patch
 
 import pytest
 from sqlalchemy import create_engine
 from IPython.core.error import UsageError
-
 from sql.connection import Connection
 from sql.magic import SqlMagic
 from sql.run import ResultSet
@@ -722,3 +723,47 @@ def test_save_with_bad_query_save(ip, capsys):
     ip.run_cell("%sql --with my_query SELECT * FROM my_query")
     out, _ = capsys.readouterr()
     assert '(sqlite3.OperationalError) near "non_existing_table": syntax error' in out
+
+
+def test_interact_basic_data_types(ip, capsys):
+    ip.user_global_ns["my_variable"] = 5
+    ip.run_cell(
+        "%sql --interact my_variable SELECT * FROM author LIMIT {{my_variable}}"
+    )
+    out, _ = capsys.readouterr()
+
+    assert (
+        "Interactive mode, please interact with below widget(s)"
+        " to control the variable" in out
+    )
+
+
+@pytest.fixture
+def mockValueWidget(monkeypatch):
+    with patch("ipywidgets.widgets.IntSlider") as MockClass:
+        instance = MockClass.return_value
+        yield instance
+
+
+def test_interact_basic_widgets(ip, mockValueWidget, capsys):
+    print("mock", mockValueWidget.value)
+    ip.user_global_ns["my_widget"] = mockValueWidget
+
+    ip.run_cell(
+        "%sql --interact my_widget SELECT * FROM number_table LIMIT {{my_widget}}"
+    )
+    out, _ = capsys.readouterr()
+    assert (
+        "Interactive mode, please interact with below widget(s)"
+        " to control the variable" in out
+    )
+
+
+def test_interact_and_missing_ipywidgets_installed(ip):
+    with patch.dict(sys.modules):
+        sys.modules["ipywidgets"] = None
+        ip.user_global_ns["my_variable"] = 5
+        out = ip.run_cell(
+            "%sql --interact my_variable SELECT * FROM author LIMIT {{my_variable}}"
+        )
+        assert isinstance(out.error_in_exec, ModuleNotFoundError)
diff --git a/src/tests/test_parse.py b/src/tests/test_parse.py
index 3777214..f765cae 100644
--- a/src/tests/test_parse.py
+++ b/src/tests/test_parse.py
@@ -189,6 +189,7 @@ def complete_with_defaults(mapping):
         "append": False,
         "connection_arguments": None,
         "file": None,
+        "interact": None,
         "save": None,
         "with_": None,
         "no_execute": False,