Repository: ploomber/jupysql (python)

Issue:
resultset are indistinguishable from pandas data frames
ResultSet (the data structure we use by default to store db results), is currently indistinguishable from pandas data frames:

![image](https://user-images.githubusercontent.com/989250/235201469-14c5b7ee-6f4a-4094-a8de-5a07292feee9.png)

this can confuse users, as they might think the're data frames. we should ensure they look different. perhaps add result set at the bottom.

maybe we should also detect when they try do perform pandas-like operations? e.g.,

```
df.head()
```

Error should be: `head is not a valid operation, you can convert this into a pandas data frame by calling .DataFrame() or a polars data frame by calling .PolarsDataFrame()`


Pull request description:
Resultset distinguishable
## Describe your changes

Adding description after html representation of `ResultSet` and error message for AttributeError when invalid operation is performed

## Issue number

Closes #468 

## 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 -->
----
:books: Documentation preview :books:: https://jupysql--655.org.readthedocs.build/en/655/

<!-- readthedocs-preview jupysql end -->

Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 60ab95b..b707558 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -4,10 +4,13 @@
 
 * [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)
+
 * [Doc] Modified integrations content to ensure they're all consistent (#523)
 * [Doc] Document --persist-replace in API section (#539)
 * [Fix] Fixed CI issue by updating `invalid_connection_string_duckdb` in `test_magic.py` (#631)
 * [Fix] Refactored `ResultSet` to lazy loading (#470)
+* [Fix] Removed `WITH` when a snippet does not have a dependency (#657)
 
 ## 0.7.9 (2023-06-19)
 
diff --git a/src/sql/run.py b/src/sql/run.py
index fb8bcaf..e6e76cd 100644
--- a/src/sql/run.py
+++ b/src/sql/run.py
@@ -126,6 +126,16 @@ class ResultSet(ColumnGuesserMixin):
         if self.pretty:
             self.pretty.add_rows(self)
             result = self.pretty.get_html_string()
+            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>"
+            )
+            result = HTML % (result)
+
             # to create clickable links
             result = html.unescape(result)
             result = _cell_with_spaces_pattern.sub(_nonbreaking_spaces, result)
@@ -179,6 +189,18 @@ class ResultSet(ColumnGuesserMixin):
                 raise KeyError('%d results for "%s"' % (len(result), key))
             return result[0]
 
+    def __getattribute__(self, attr):
+        "Raises AttributeError when invalid operation is performed."
+        try:
+            return object.__getattribute__(self, attr)
+        except AttributeError:
+            err_msg = (
+                f"'{attr}' is not a valid operation, you can convert this "
+                "into a pandas data frame by calling '.DataFrame()' or a "
+                "polars data frame by calling '.PolarsDataFrame()'"
+            )
+            raise AttributeError(err_msg) from None
+
     def dict(self):
         """Returns a single dict built from the result set
 
diff --git a/src/sql/store.py b/src/sql/store.py
index ffad19e..70e5ead 100644
--- a/src/sql/store.py
+++ b/src/sql/store.py
@@ -130,6 +130,9 @@ class SQLQuery:
         template = (
             with_clause_template_backtick if is_use_backtick else with_clause_template
         )
+        # return query without 'with' when no dependency exists
+        if len(with_all) == 0:
+            return self._query.strip()
         return template.render(
             query=self._query,
             saved=self._store._data,


Test patch:
diff --git a/src/tests/test_magic_cte.py b/src/tests/test_magic_cte.py
index a76a1b9..8b405c5 100644
--- a/src/tests/test_magic_cte.py
+++ b/src/tests/test_magic_cte.py
@@ -170,7 +170,7 @@ def test_snippets_delete(ip, capsys):
     )
     result = ip.run_cell("%sqlrender final").result
     expected = (
-        "WITH\n\n        SELECT o.order_id, customers.name, "
+        "SELECT o.order_id, customers.name, "
         "o.order_value\n        "
         "FROM another_orders o\n        INNER JOIN customers "
         "ON o.customer_id=customers.customer_id"
diff --git a/src/tests/test_resultset.py b/src/tests/test_resultset.py
index 52c7f33..4bf6cb3 100644
--- a/src/tests/test_resultset.py
+++ b/src/tests/test_resultset.py
@@ -77,7 +77,33 @@ def test_resultset_repr_html(result_set):
         "<th>x</th>\n        </tr>\n    </thead>\n    <tbody>\n        "
         "<tr>\n            <td>0</td>\n        </tr>\n        <tr>\n            "
         "<td>1</td>\n        </tr>\n        <tr>\n            <td>2</td>\n        "
-        "</tr>\n    </tbody>\n</table>"
+        "</tr>\n    </tbody>\n</table>\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>"
+    )
+
+
+@pytest.mark.parametrize(
+    "fname, parameters",
+    [
+        ("head", -1),
+        ("tail", None),
+        ("value_counts", None),
+        ("not_df_function", None),
+    ],
+)
+def test_invalid_operation_error(result_set, fname, parameters):
+    with pytest.raises(AttributeError) as excinfo:
+        getattr(result_set, fname)(parameters)
+
+    assert str(excinfo.value) == (
+        f"'{fname}' is not a valid operation, you can convert this "
+        "into a pandas data frame by calling '.DataFrame()' or a "
+        "polars data frame by calling '.PolarsDataFrame()'"
     )