Repository: ploomber/jupysql
Pull request #696 (2023-06-30 00:17:44)

fix "too many open files" when running tests
## Describe your changes

Fixed an issue that caused errors when running unit tests.

## 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--696.org.readthedocs.build/en/696/

<!-- readthedocs-preview jupysql end -->

tonykploomber: Thanks for the fix, I do see this issue recently in integration testing too
edublancas: @tonykploomber I think this will fix the issue for integration tests but please open an issue if it doesn't

Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index b707558..dc526c2 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -5,6 +5,7 @@
 * [Feature] Modified `TableDescription` to add styling, generate messages and format the calculated outputs (#459)
 * [Feature] Support flexible spacing `myvar=<<` operator ([#525](https://github.com/ploomber/jupysql/issues/525))
 * [Feature] Added a line under `ResultSet` to distinguish it from data frame and error message when invalid operations are performed (#468)
+* [Feature] Moved `%sqlrender` feature to `%sqlcmd snippets` (#647)
 
 * [Doc] Modified integrations content to ensure they're all consistent (#523)
 * [Doc] Document --persist-replace in API section (#539)
diff --git a/doc/_toc.yml b/doc/_toc.yml
index be32a57..7682b7f 100644
--- a/doc/_toc.yml
+++ b/doc/_toc.yml
@@ -43,7 +43,6 @@ parts:
     chapters:
     - file: api/magic-sql
     - file: api/magic-plot
-    - file: api/magic-render
     - file: api/magic-snippets
     - file: api/configuration
     - file: api/python
diff --git a/doc/api/magic-render.md b/doc/api/magic-render.md
deleted file mode 100644
index d2dbca5..0000000
--- a/doc/api/magic-render.md
+++ /dev/null
@@ -1,82 +0,0 @@
----
-jupytext:
-  notebook_metadata_filter: myst
-  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
-myst:
-  html_meta:
-    description lang=en: Documentation for the %sqlrender magic from JupySQL
-    keywords: jupyter, sql, jupysql
-    property=og:locale: en_US
----
-
-# `%sqlrender`
-
-```{versionadded} 0.4.3
-```
-
-`%sqlrender` helps you compose large SQL queries.
-
-```{note}
-You can view the documentation and command line arguments by running `%sqlrender?`
-```
-
-```{code-cell} ipython3
-%load_ext sql
-```
-
-```{code-cell} ipython3
-%sql sqlite:///
-```
-
-```{code-cell} ipython3
-import pandas as pd
-
-url = (
-    "https://gist.githubusercontent.com/jaidevd"
-    "/23aef12e9bf56c618c41/raw/c05e98672b8d52fa0"
-    "cb94aad80f75eb78342e5d4/books.csv"
-)
-books = pd.read_csv(url)
-```
-
-```{code-cell} ipython3
-%sql --persist books
-```
-
-```{code-cell} ipython3
-%sql SELECT * FROM books LIMIT 5
-```
-
-## `%sqlrender`
-
-`-w`/`--with` Use a previously saved query as input data
-
-```{code-cell} ipython3
-%%sql --save books_fav --no-execute
-SELECT *
-FROM books
-WHERE genre = 'data_science'
-```
-
-```{code-cell} ipython3
-%%sql --save books_fav_long --no-execute --with books_fav
-SELECT * FROM books_fav
-WHERE Height >= 240
-```
-
-```{code-cell} ipython3
-query = %sqlrender books_fav_long --with books_fav_long
-print(query)
-```
-
-```{code-cell} ipython3
-
-```
diff --git a/doc/api/magic-snippets.md b/doc/api/magic-snippets.md
index d84d6c9..b7926b0 100644
--- a/doc/api/magic-snippets.md
+++ b/doc/api/magic-snippets.md
@@ -5,15 +5,14 @@ jupytext:
     extension: .md
     format_name: myst
     format_version: 0.13
-    jupytext_version: 1.14.5
+    jupytext_version: 1.14.6
 kernelspec:
   display_name: Python 3 (ipykernel)
   language: python
   name: python3
 myst:
   html_meta:
-    description lang=en: Documentation for  %sqlcmd snippets
-      from JupySQL
+    description lang=en: Documentation for %sqlcmd snippets from JupySQL
     keywords: jupyter, sql, jupysql, snippets
     property=og:locale: en_US
 ---
@@ -73,6 +72,8 @@ Returns all the snippets saved in the environment
 
 Arguments:
 
+`{snippet_name}` Return a snippet.
+
 `-d`/`--delete` Delete a snippet.
 
 `-D`/`--delete-force` Force delete a snippet. This may be useful if there are other dependent snippets, and you still need to delete this snippet.
@@ -80,21 +81,39 @@ Arguments:
 `-A`/`--delete-force-all` Force delete a snippet and all dependent snippets.
 
 ```{code-cell} ipython3
+chinstrap_snippet = %sqlcmd snippets chinstrap
+print(chinstrap_snippet)
+```
+
+This returns the stored snippet `chinstrap`.
+
+Calling `%sqlcmd snippets {snippet_name}` also works on a snippet that is dependent on others. To demonstrate it, let's create a snippet dependent on the `chinstrap` snippet.
+
+```{code-cell} ipython3
+%%sql --save chinstrap_sub
+SELECT * FROM chinstrap where island == 'Dream'
+```
+
+```{code-cell} ipython3
+chinstrap_sub_snippet = %sqlcmd snippets chinstrap_sub
+print(chinstrap_sub_snippet)
+```
+
+This returns the stored snippet `chinstrap_sub`.
+
+Now, let's see how to delete a stored snippet.
 
+```{code-cell} ipython3
 %sqlcmd snippets -d gentoo
 ```
 
 This deletes the stored snippet `gentoo`.
 
-To demonstrate `force-delete` let's create a snippet dependent on `chinstrap` snippet.
+Now, let's see how to delete a stored snippet that other snippets are dependent on. Recall we have created `chinstrap_sub` which is dependent on `chinstrap`.
 
 ```{code-cell} ipython3
-:tags: [hide-output]
-
-%%sql --save chinstrap_sub
-SELECT * FROM chinstrap where island == 'Dream'
+print(chinstrap_sub_snippet)
 ```
-+++
 
 Trying to delete the `chinstrap` snippet will display an error message:
 
@@ -104,10 +123,9 @@ Trying to delete the `chinstrap` snippet will display an error message:
 %sqlcmd snippets -d chinstrap
 ```
 
-If you still wish to delete this snippet, you can run the below command:
+If you still wish to delete this snippet, you should use `force-delete` by running the below command:
 
 ```{code-cell} ipython3
-
 %sqlcmd snippets -D chinstrap
 ```
 
@@ -130,6 +148,5 @@ SELECT * FROM chinstrap where island == 'Dream'
 Now, force delete `chinstrap` and its dependent `chinstrap_sub`:
 
 ```{code-cell} ipython3
-
 %sqlcmd snippets -A chinstrap
 ```
diff --git a/doc/compose.md b/doc/compose.md
index f8016f0..b31c29f 100644
--- a/doc/compose.md
+++ b/doc/compose.md
@@ -5,16 +5,16 @@ jupytext:
     extension: .md
     format_name: myst
     format_version: 0.13
-    jupytext_version: 1.14.4
+    jupytext_version: 1.14.6
 kernelspec:
   display_name: Python 3 (ipykernel)
   language: python
   name: python3
 myst:
   html_meta:
-    description lang=en: "Use JupySQL to organize large SQL queries in a Jupyter notebook"
-    keywords: "jupyter, sql, jupysql"
-    property=og:locale: "en_US"
+    description lang=en: Use JupySQL to organize large SQL queries in a Jupyter notebook
+    keywords: jupyter, sql, jupysql
+    property=og:locale: en_US
 ---
 
 # Organizing Large Queries
@@ -144,10 +144,10 @@ top_artist.bar()
 
 It looks like Iron Maiden had the highest number of rock and metal songs in the table.
 
-We can render the full query with the `%sqlrender` magic:
+We can render the full query with the `%sqlcmd snippets {name}` magic:
 
 ```{code-cell} ipython3
-final = %sqlrender top_artist
+final = %sqlcmd snippets top_artist
 print(final)
 ```
 
@@ -160,4 +160,4 @@ We can verify the retrieved query returns the same result:
 
 ## Summary
 
-In the given example, we demonstrated JupySQL's usage as a tool for managing large SQL queries in Jupyter Notebooks. It effectively broke down a complex query into smaller, organized parts, simplifying the process of analyzing a record store's sales database. By using JupySQL, users can easily maintain and reuse their queries, enhancing the overall data analysis experience.
\ No newline at end of file
+In the given example, we demonstrated JupySQL's usage as a tool for managing large SQL queries in Jupyter Notebooks. It effectively broke down a complex query into smaller, organized parts, simplifying the process of analyzing a record store's sales database. By using JupySQL, users can easily maintain and reuse their queries, enhancing the overall data analysis experience.
diff --git a/doc/integrations/mariadb.ipynb b/doc/integrations/mariadb.ipynb
index 20983bd..de365ee 100644
--- a/doc/integrations/mariadb.ipynb
+++ b/doc/integrations/mariadb.ipynb
@@ -847,7 +847,7 @@
     }
    ],
    "source": [
-    "query = %sqlrender trip_stats\n",
+    "query = %sqlcmd snippets trip_stats\n",
     "print(query)"
    ]
   },
diff --git a/doc/integrations/mssql.ipynb b/doc/integrations/mssql.ipynb
index 00b2b0d..3cf06eb 100644
--- a/doc/integrations/mssql.ipynb
+++ b/doc/integrations/mssql.ipynb
@@ -1049,7 +1049,7 @@
     }
    ],
    "source": [
-    "query = %sqlrender trip_stats\n",
+    "query = %sqlcmd snippets trip_stats\n",
     "print(query)"
    ]
   },
diff --git a/doc/integrations/mysql.ipynb b/doc/integrations/mysql.ipynb
index 6f27b69..e6e9f69 100644
--- a/doc/integrations/mysql.ipynb
+++ b/doc/integrations/mysql.ipynb
@@ -903,7 +903,7 @@
     }
    ],
    "source": [
-    "query = %sqlrender trip_stats\n",
+    "query = %sqlcmd snippets trip_stats\n",
     "print(query)"
    ]
   },
diff --git a/doc/integrations/oracle.ipynb b/doc/integrations/oracle.ipynb
index 8dd4443..a648ec8 100644
--- a/doc/integrations/oracle.ipynb
+++ b/doc/integrations/oracle.ipynb
@@ -613,7 +613,7 @@
     }
    ],
    "source": [
-    "query = %sqlrender saved_cte\n",
+    "query = %sqlcmd snippets saved_cte\n",
     "print(query)"
    ]
   },
@@ -757,7 +757,7 @@
    "name": "python",
    "nbconvert_exporter": "python",
    "pygments_lexer": "ipython3",
-   "version": "3.10.10"
+   "version": "3.10.11"
   },
   "myst": {
    "html_meta": {
diff --git a/doc/integrations/postgres-connect.ipynb b/doc/integrations/postgres-connect.ipynb
index 3379411..0ac9cc7 100644
--- a/doc/integrations/postgres-connect.ipynb
+++ b/doc/integrations/postgres-connect.ipynb
@@ -771,7 +771,7 @@
     }
    ],
    "source": [
-    "query = %sqlrender trip_stats\n",
+    "query = %sqlcmd snippets trip_stats\n",
     "print(query)"
    ]
   },
@@ -1058,7 +1058,7 @@
    "name": "python",
    "nbconvert_exporter": "python",
    "pygments_lexer": "ipython3",
-   "version": "3.10.9"
+   "version": "3.10.11"
   },
   "myst": {
    "html_meta": {
diff --git a/doc/integrations/snowflake.ipynb b/doc/integrations/snowflake.ipynb
index 47bb773..409530f 100644
--- a/doc/integrations/snowflake.ipynb
+++ b/doc/integrations/snowflake.ipynb
@@ -751,7 +751,7 @@
     }
    ],
    "source": [
-    "final = %sqlrender no_nulls\n",
+    "final = %sqlcmd snippets no_nulls\n",
     "print(final)"
    ]
   },
diff --git a/doc/integrations/trinodb.ipynb b/doc/integrations/trinodb.ipynb
index 693a874..7228c4c 100644
--- a/doc/integrations/trinodb.ipynb
+++ b/doc/integrations/trinodb.ipynb
@@ -775,7 +775,7 @@
     }
    ],
    "source": [
-    "query = %sqlrender trip_stats\n",
+    "query = %sqlcmd snippets trip_stats\n",
     "print(query)"
    ]
   },
diff --git a/doc/user-guide/template.md b/doc/user-guide/template.md
index 5136813..6f057fe 100644
--- a/doc/user-guide/template.md
+++ b/doc/user-guide/template.md
@@ -5,7 +5,7 @@ jupytext:
     extension: .md
     format_name: myst
     format_version: 0.13
-    jupytext_version: 1.14.5
+    jupytext_version: 1.14.6
 kernelspec:
   display_name: Python 3 (ipykernel)
   language: python
@@ -41,7 +41,6 @@ The benefits of using parametrized SQL queries are:
 Let's load some data and connect to the in-memory DuckDB instance:
 
 ```{code-cell} ipython3
-
 %load_ext sql
 from pathlib import Path
 from urllib.request import urlretrieve
@@ -93,7 +92,7 @@ group by sex
 Here's the final compiled query:
 
 ```{code-cell} ipython3
-final = %sqlrender avg_body_mass
+final = %sqlcmd snippets avg_body_mass
 print(final)
 ```
 
@@ -101,7 +100,7 @@ print(final)
 
 `Macros` is a construct analogous to functions that promote re-usability. We'll first define a macro for converting a value from `millimetre` to `centimetre`. And then use this macro in the query using variable expansion.
 
-```{code-cell} python
+```{code-cell} ipython3
 %%sql --save convert
 {% macro mm_to_cm(column_name, precision=2) %}
     ({{ column_name }} / 10)::numeric(16, {{ precision }})
@@ -116,27 +115,31 @@ from penguins.csv
 
 Let's see the final rendered query:
 
-```{code-cell} python
-final = %sqlrender convert
+```{code-cell} ipython3
+final = %sqlcmd snippets convert
 print(final)
 ```
 
+```{code-cell} ipython3
+%sqlcmd snippets -d convert
+```
+
 ## Create tables in loop
 
 We can also create multiple tables in a loop using parametrized queries. Let's segregate the dataset by `island`.
 
-```{code-cell} python
+```{code-cell} ipython3
 for island in ("Torgersen", "Biscoe", "Dream"):
     %sql CREATE TABLE {{island}} AS (SELECT * from penguins.csv WHERE island = '{{island}}')
 ```
 
-```{code-cell} python
+```{code-cell} ipython3
 %sqlcmd tables
 ```
 
 Let's verify data in one of the tables:
 
-```{code-cell} python
+```{code-cell} ipython3
 %sql SELECT * FROM Torgersen;
 ```
 
diff --git a/src/sql/cmd/snippets.py b/src/sql/cmd/snippets.py
index 9b041cb..08f6a57 100644
--- a/src/sql/cmd/snippets.py
+++ b/src/sql/cmd/snippets.py
@@ -1,6 +1,7 @@
 from sql import util
 from sql.exceptions import UsageError
 from sql.cmd.cmd_utils import CmdParser
+from sql.store import store
 
 
 def _modify_display_msg(key, remaining_keys, dependent_keys=None):
@@ -62,6 +63,20 @@ def snippets(others):
         help="Force delete all stored snippets",
         required=False,
     )
+    if len(others) == 1:
+        all_snippets = util.get_all_keys()
+        if others[0] in all_snippets:
+            return str(store[others[0]])
+
+        base_err_msg = f"'{others[0]}' is not a snippet. "
+        if len(all_snippets) == 0:
+            err_msg = "%sThere is no available snippet."
+        else:
+            err_msg = "%sAvailable snippets are " f"{util.pretty_print(all_snippets)}."
+        err_msg = err_msg % (base_err_msg)
+
+        raise UsageError(err_msg)
+
     args = parser.parse_args(others)
     SNIPPET_ARGS = [args.delete, args.delete_force, args.delete_force_all]
     if SNIPPET_ARGS.count(None) == len(SNIPPET_ARGS):
diff --git a/src/sql/magic.py b/src/sql/magic.py
index 18bded3..d74d07e 100644
--- a/src/sql/magic.py
+++ b/src/sql/magic.py
@@ -72,6 +72,13 @@ class RenderMagic(Magics):
     @telemetry.log_call("sqlrender")
     def sqlrender(self, line):
         args = parse_argstring(self.sqlrender, line)
+        warnings.warn(
+            "\n'%sqlrender' will be deprecated soon, "
+            f"please use '%sqlcmd snippets {args.line[0]}' instead. "
+            "\n\nFor documentation, follow this link : "
+            "https://jupysql.ploomber.io/en/latest/api/magic-snippets.html#id1",
+            FutureWarning,
+        )
         return str(store[args.line[0]])
 
 


Test patch:
diff --git a/src/tests/conftest.py b/src/tests/conftest.py
index bd2422e..6200eb6 100644
--- a/src/tests/conftest.py
+++ b/src/tests/conftest.py
@@ -1,3 +1,4 @@
+from traitlets.config import Config
 import os
 import urllib.request
 from pathlib import Path
@@ -5,6 +6,7 @@ from pathlib import Path
 import pytest
 from IPython.core.interactiveshell import InteractiveShell
 
+
 from sql.magic import SqlMagic, RenderMagic
 from sql.magic_plot import SqlPlotMagic
 from sql.magic_cmd import SqlCmdMagic
@@ -50,7 +52,14 @@ def clean_conns():
 
 @pytest.fixture
 def ip_empty():
-    ip_session = InteractiveShell()
+    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 = InteractiveShell(config=c)
+
     ip_session.register_magics(SqlMagic)
     ip_session.register_magics(RenderMagic)
     ip_session.register_magics(SqlPlotMagic)
@@ -107,6 +116,7 @@ def tmp_empty(tmp_path):
     Create temporary path using pytest native fixture,
     them move it, yield, and restore the original path
     """
+
     old = os.getcwd()
     os.chdir(str(tmp_path))
     yield str(Path(tmp_path).resolve())
diff --git a/src/tests/test_magic_cmd.py b/src/tests/test_magic_cmd.py
index d9b7ef9..1e5a1c7 100644
--- a/src/tests/test_magic_cmd.py
+++ b/src/tests/test_magic_cmd.py
@@ -421,6 +421,40 @@ def test_snippet(ip_snippets):
     assert "high_price, high_price_a, high_price_b" in out
 
 
+@pytest.mark.parametrize(
+    "precmd, cmd, err_msg",
+    [
+        (
+            None,
+            "%sqlcmd snippets invalid",
+            (
+                "'invalid' is not a snippet. Available snippets are 'high_price', "
+                "'high_price_a', and 'high_price_b'."
+            ),
+        ),
+        (
+            "%sqlcmd snippets -d high_price_b",
+            "%sqlcmd snippets invalid",
+            (
+                "'invalid' is not a snippet. Available snippets are 'high_price', "
+                "and 'high_price_a'."
+            ),
+        ),
+        (
+            "%sqlcmd snippets -A high_price",
+            "%sqlcmd snippets invalid",
+            "'invalid' is not a snippet. There is no available snippet.",
+        ),
+    ],
+)
+def test_invalid_snippet(ip_snippets, precmd, cmd, err_msg):
+    if precmd:
+        ip_snippets.run_cell(precmd)
+    out = ip_snippets.run_cell(cmd)
+    assert isinstance(out.error_in_exec, UsageError)
+    assert str(out.error_in_exec) == err_msg
+
+
 @pytest.mark.parametrize("arg", ["--delete", "-d"])
 def test_delete_saved_key(ip_snippets, arg):
     out = ip_snippets.run_cell(f"%sqlcmd snippets {arg} high_price_a").result
diff --git a/src/tests/test_magic_cte.py b/src/tests/test_magic_cte.py
index 8b405c5..a7b96ad 100644
--- a/src/tests/test_magic_cte.py
+++ b/src/tests/test_magic_cte.py
@@ -25,9 +25,7 @@ SELECT * FROM positive_y;
 """
     )
 
-    cell_final_query = ip.run_cell(
-        "%sqlrender final --with positive_x --with positive_y"
-    )
+    cell_final_query = ip.run_cell("%sqlcmd snippets final")
 
     assert cell_execution.success
     assert cell_final_query.result == (
@@ -51,7 +49,7 @@ def test_infer_dependencies(ip, capsys):
         "SELECT last_name FROM author_sub;",
     )
     out, _ = capsys.readouterr()
-    result = ip.run_cell("%sqlrender final").result
+    result = ip.run_cell("%sqlcmd snippets final").result
     expected = (
         "WITH `author_sub` AS (\nSELECT last_name FROM author "
         "WHERE year_of_death > 1900)\nSELECT last_name FROM author_sub;"
@@ -168,7 +166,7 @@ def test_snippets_delete(ip, capsys):
         INNER JOIN customers ON o.customer_id=customers.customer_id;
         """,
     )
-    result = ip.run_cell("%sqlrender final").result
+    result = ip.run_cell("%sqlcmd snippets final").result
     expected = (
         "SELECT o.order_id, customers.name, "
         "o.order_value\n        "
diff --git a/src/tests/test_telemetry.py b/src/tests/test_telemetry.py
index b018b89..35ef207 100644
--- a/src/tests/test_telemetry.py
+++ b/src/tests/test_telemetry.py
@@ -112,8 +112,10 @@ def test_data_frame_telemetry_execution(mock_log_api, ip, simple_file_path_iris)
     )
 
 
-def test_sqlrender_telemetry_execution(mock_log_api, ip, simple_file_path_iris):
-    # Simulate the sqlrender query
+def test_sqlcmd_snippets_query_telemetry_execution(
+    mock_log_api, ip, simple_file_path_iris
+):
+    # Simulate the sqlcmd snippets query
     ip.run_cell("%sql duckdb://")
     ip.run_cell(
         "%sql --save class_setosa --no-execute "
@@ -122,10 +124,10 @@ def test_sqlrender_telemetry_execution(mock_log_api, ip, simple_file_path_iris):
         + "')"
         + " WHERE class='Iris-setosa'"
     )
-    ip.run_cell("%sqlrender class_setosa")
+    ip.run_cell("%sqlcmd snippets class_setosa")
 
     mock_log_api.assert_called_with(
-        action="jupysql-sqlrender-success", total_runtime=ANY, metadata=ANY
+        action="jupysql-execute-success", total_runtime=ANY, metadata=ANY
     )