Repository: ploomber/jupysql (python)

Issue:
truncated message does not appear in the terminal
we truncate results to 10 by default and show [a message](https://github.com/ploomber/jupysql/blob/aa360f9505c7b0e3ad8968cf38b2ccfc9df9d4a1/src/sql/run.py#L132) in Jupyter to let the user know. However, this message is not implemented in the text-based view, which is rendered when running jupysql in the terminal (the `__repr__` method)

```
In [1]: %load_ext sql

In [2]: %sql duckdb://

In [3]: %sql select * from penguins.csv
Running query in 'duckdb://'
Out[3]:
+---------+-----------+----------------+---------------+-------------------+-------------+--------+
| species |   island  | bill_length_mm | bill_depth_mm | flipper_length_mm | body_mass_g |  sex   |
+---------+-----------+----------------+---------------+-------------------+-------------+--------+
|  Adelie | Torgersen |      39.1      |      18.7     |        181        |     3750    |  MALE  |
|  Adelie | Torgersen |      39.5      |      17.4     |        186        |     3800    | FEMALE |
|  Adelie | Torgersen |      40.3      |      18.0     |        195        |     3250    | FEMALE |
|  Adelie | Torgersen |      None      |      None     |        None       |     None    |  None  |
|  Adelie | Torgersen |      36.7      |      19.3     |        193        |     3450    | FEMALE |
|  Adelie | Torgersen |      39.3      |      20.6     |        190        |     3650    |  MALE  |
|  Adelie | Torgersen |      38.9      |      17.8     |        181        |     3625    | FEMALE |
|  Adelie | Torgersen |      39.2      |      19.6     |        195        |     4675    |  MALE  |
|  Adelie | Torgersen |      34.1      |      18.1     |        193        |     3475    |  None  |
|  Adelie | Torgersen |      42.0      |      20.2     |        190        |     4250    |  None  |
+---------+-----------+----------------+---------------+-------------------+-------------+--------+
```

Pull request description:
add resultset footer to plain text representation
## Describe your changes

## Issue number

Closes #682

## 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--802.org.readthedocs.build/en/802/

<!-- readthedocs-preview jupysql end -->

Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 083be62..3f69503 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -12,6 +12,7 @@
 * [Fix] `ResultSet` footer only displayed when `feedback=2`
 * [Fix] Current connection and switching connections message only displayed when `feedback>=1`
 * [Fix] `--persist/--persist-replace` perform `ROLLBACK` automatically when needed
+* [Fix] `ResultSet` footer (when `displaylimit` truncates results and when showing how to convert to a data frame) now appears in the `ResultSet` plain text representation (#682)
 
 ## 0.9.1 (2023-08-10)
 
diff --git a/src/sql/run/resultset.py b/src/sql/run/resultset.py
index 6b5a026..607d206 100644
--- a/src/sql/run/resultset.py
+++ b/src/sql/run/resultset.py
@@ -2,7 +2,7 @@ import re
 import operator
 from functools import reduce
 from io import StringIO
-import html
+from html import unescape
 from collections.abc import Iterable
 
 
@@ -142,35 +142,48 @@ class ResultSet(ColumnGuesserMixin):
 
     def _repr_html_(self):
         self.fetch_for_repr_if_needed()
-
-        _cell_with_spaces_pattern = re.compile(r"(<td>)( {2,})")
-
         result = self._pretty_table.get_html_string()
+        return self._add_footer(result, html=True)
 
+    def _add_footer(self, result, *, html):
         if _config_feedback_all():
-            HTML = (
-                "%s\n<span style='font-style:italic;font-size:11px'>"
-                "<code>ResultSet</code>: to convert to pandas, call <a href="
-                "'https://jupysql.ploomber.io/en/latest/integrations/pandas.html'>"
-                "<code>.DataFrame()</code></a> or to polars, call <a href="
-                "'https://jupysql.ploomber.io/en/latest/integrations/polars.html'>"
-                "<code>.PolarsDataFrame()</code></a></span><br>"
+            data_frame_footer = (
+                (
+                    "\n<span style='font-style:italic;font-size:11px'>"
+                    "<code>ResultSet</code>: to convert to pandas, call <a href="
+                    "'https://jupysql.ploomber.io/en/latest/integrations/pandas.html'>"
+                    "<code>.DataFrame()</code></a> or to polars, call <a href="
+                    "'https://jupysql.ploomber.io/en/latest/integrations/polars.html'>"
+                    "<code>.PolarsDataFrame()</code></a></span><br>"
+                )
+                if html
+                else (
+                    "\nResultSet: to convert to pandas, call .DataFrame() "
+                    "or to polars, call .PolarsDataFrame()"
+                )
             )
-            result = HTML % (result)
+
+            result = f"{result}{data_frame_footer}"
 
         # to create clickable links
-        result = html.unescape(result)
+        result = unescape(result)
+        _cell_with_spaces_pattern = re.compile(r"(<td>)( {2,})")
         result = _cell_with_spaces_pattern.sub(_nonbreaking_spaces, result)
 
         if self._config.displaylimit != 0 and not self._done_fetching():
-            HTML = (
-                '%s\n<span style="font-style:italic;text-align:center;">'
-                'Truncated to <a href="https://jupysql.ploomber.io/en/'
-                'latest/api/configuration.html#displaylimit">'
-                "displaylimit</a> of %d.</span>"
+            displaylimit_footer = (
+                (
+                    '\n<span style="font-style:italic;text-align:center;">'
+                    'Truncated to <a href="https://jupysql.ploomber.io/en/'
+                    'latest/api/configuration.html#displaylimit">'
+                    f"displaylimit</a> of {self._config.displaylimit}.</span>"
+                )
+                if html
+                else f"\nTruncated to displaylimit of {self._config.displaylimit}."
             )
 
-            result = HTML % (result, self._config.displaylimit)
+            result = f"{result}{displaylimit_footer}"
+
         return result
 
     def __len__(self):
@@ -186,7 +199,8 @@ class ResultSet(ColumnGuesserMixin):
 
     def __str__(self):
         self.fetch_for_repr_if_needed()
-        return str(self._pretty_table)
+        result = str(self._pretty_table)
+        return self._add_footer(result, html=False)
 
     def __repr__(self) -> str:
         return str(self)


Test patch:
diff --git a/src/tests/test_resultset.py b/src/tests/test_resultset.py
index 8ccd0c3..953ddfb 100644
--- a/src/tests/test_resultset.py
+++ b/src/tests/test_resultset.py
@@ -96,6 +96,13 @@ def test_resultset_repr_html_when_feedback_is_2(result_set, ip_empty):
         "<code>.PolarsDataFrame()</code></a></span><br>"
     ) in html_
 
+    plain = (
+        "ResultSet: to convert to pandas, call .DataFrame() "
+        "or to polars, call .PolarsDataFrame()"
+    )
+    assert plain in str(result_set)
+    assert plain in repr(result_set)
+
 
 @pytest.mark.parametrize("feedback", [0, 1])
 def test_resultset_repr_html_with_reduced_feedback(result_set, ip_empty, feedback):
@@ -508,25 +515,28 @@ def test_display_limit_respected_even_when_feched_all(results):
 @pytest.mark.parametrize(
     "displaylimit, message",
     [
-        (1, "Truncated to $HTML_LINK of 1."),
-        (2, "Truncated to $HTML_LINK of 2."),
+        (1, "Truncated to $DISPLAYLIMIT of 1."),
+        (2, "Truncated to $DISPLAYLIMIT of 2."),
     ],
 )
-def test_displaylimit_message(displaylimit, message, results):
+def test_displaylimit_truncated_footer(displaylimit, message, results):
     HTML_LINK = (
         '<a href="https://jupysql.ploomber.io/en/'
         'latest/api/configuration.html#displaylimit">displaylimit</a>'
     )
 
-    message = string.Template(message).substitute(HTML_LINK=HTML_LINK)
-
     mock = Mock()
     mock.displaylimit = displaylimit
     mock.autolimit = 0
 
     rs = ResultSet(results, mock, statement=None, conn=Mock())
 
-    assert message in rs._repr_html_()
+    message_html = string.Template(message).substitute(DISPLAYLIMIT=HTML_LINK)
+    assert message_html in rs._repr_html_()
+
+    message_plain = string.Template(message).substitute(DISPLAYLIMIT="displaylimit")
+    assert message_plain in repr(rs)
+    assert message_plain in str(rs)
 
 
 @pytest.mark.parametrize("displaylimit", [0, 1000])
@@ -538,6 +548,8 @@ def test_no_displaylimit_message(results, displaylimit):
     rs = ResultSet(results, mock, statement=None, conn=Mock())
 
     assert "Truncated to displaylimit" not in rs._repr_html_()
+    assert "Truncated to displaylimit" not in repr(rs)
+    assert "Truncated to displaylimit" not in str(rs)
 
 
 def test_refreshes_sqlaproxy_for_sqlalchemy_duckdb():