Repository: ploomber/jupysql (python)

Issue:
proposal: `%sqlcmd profile`
When working with a new dataset, practitioners need to explore and summarize it quickly: column values, types, distributions, etc. We could create a `%sqlcmd profile` magic that produces an HTML table/report of a table. Similar to [pandas-profiling](https://github.com/ydataai/pandas-profiling), except this would run the analysis on the SQL engine, making it more scalable.

Examples:

display an embedded summary:

```
%sqlcmd profile --table my_table
```


store a report:

```
%sqlcmd profile --table my_table --output report.html
```


Pull request:
Table profile added
## Describe your changes
`profile` magic command added to `%sqlcmd`.

How to use it:
`%sqlcmd profile -t table_name`

## Issue ticket number and link
Closes #66 

## 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--168.org.readthedocs.build/en/168/

<!-- readthedocs-preview jupysql end -->

Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 5be7c4d..392ae3a 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -1,7 +1,7 @@
 # CHANGELOG
 
 ## 0.7.0dev
-
+* [Feature] Adds `%sqlcmd profile` (#66)
 * [API Change] Deprecates old SQL parametrization: `$var`, `:var`, and `{var}` in favor of `{{var}}`
 * [Feature] Adds sql magic test to list of possible magics to test datasets
 
diff --git a/doc/_toc.yml b/doc/_toc.yml
index 5d2ed52..0e1d9db 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/data-profiling
 
   - caption: Integrations
     chapters:
diff --git a/doc/user-guide/data-profiling.md b/doc/user-guide/data-profiling.md
new file mode 100644
index 0000000..b363590
--- /dev/null
+++ b/doc/user-guide/data-profiling.md
@@ -0,0 +1,157 @@
+---
+jupytext:
+  text_representation:
+    extension: .md
+    format_name: myst
+    format_version: 0.13
+    jupytext_version: 1.14.4
+kernelspec:
+  display_name: Python 3 (ipykernel)
+  language: python
+  name: python3
+---
+
+# Data profiling
+
+When dealing with a new dataset, it's crucial for practitioners to have a comprehensive understanding of the data in a timely manner. This involves exploring and summarizing the dataset efficiently to extract valuable insights. However, this can be a time-consuming process. Fortunately, `%sqlcmd profile` offers an easy way to generate statistics and descriptive information, enabling practitioners to quickly gain a deeper understanding of the dataset.
+
+Availble statistics:
+
+* The count of non empty values
+* The number of unique values
+* The top (most frequent) value
+* The frequency of your top value
+* The mean, standard deviation, min and max values
+* The percentiles of your data: 25%, 50% and 75%.
+
+## Examples
+
+### DuckDB
+
+In this example we'll demonstrate the process of profiling a sample dataset that contains historical taxi data from NYC, using DuckDB. However, the code used here is compatible with all major databases.
+
+Download the data
+
+```{code-cell} ipython3
+from pathlib import Path
+from urllib.request import urlretrieve
+
+url = "https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2021-01.parquet"
+
+if not Path("yellow_tripdata_2021-01.parquet").is_file():
+    urlretrieve(url, "yellow_tripdata_2021-01.parquet")
+```
+
+Setup
+
+```{note}
+This example requires duckdb-engine: `pip install duckdb-engine`
+```
+
+Load the extension and connect to an in-memory DuckDB database:
+
+```{code-cell} ipython3
+%load_ext sql
+```
+
+```{code-cell} ipython3
+%sql duckdb://
+```
+
+Profile table
+
+```{code-cell} ipython3
+%sqlcmd profile --table "yellow_tripdata_2021-01.parquet"
+```
+
+### SQLite
+
+We can easily explore large SQLite database using DuckDB.
+
+```{code-cell} ipython3
+:tags: [hide-output]
+
+import urllib.request
+from pathlib import Path
+
+if not Path("example.db").is_file():
+    url = "https://raw.githubusercontent.com/lerocha/chinook-database/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite"  # noqa
+    urllib.request.urlretrieve(url, "example.db")
+```
+
+
+```{code-cell} ipython3
+:tags: [hide-output]
+
+%%sql duckdb:///
+INSTALL 'sqlite_scanner';
+LOAD 'sqlite_scanner';
+CALL sqlite_attach('example.db');
+```
+
+```{code-cell} ipython3
+%sqlcmd profile -t track
+```
+
+### Saving report as HTML
+
+To save the generated report as an HTML file, use the `--output`/`-o` attribute followed by the desired file name
+
+```{code-cell} ipython3
+:tags: [hide-output]
+
+%sqlcmd profile -t track --output my-report.html
+```
+
+```{code-cell} ipython3
+from IPython.display import HTML
+HTML("my-report.html")
+```
+
+### Use schemas
+
+To profile a specific table from various tables in different schemas, we can use the `--schema/-s` attribute.
+
+```{code-cell} ipython3
+:tags: [hide-output]
+
+import sqlite3
+
+with sqlite3.connect("a.db") as conn:
+    conn.execute("CREATE TABLE my_numbers (number FLOAT)")
+    conn.execute("INSERT INTO my_numbers VALUES (1)")
+    conn.execute("INSERT INTO my_numbers VALUES (2)")
+    conn.execute("INSERT INTO my_numbers VALUES (3)")
+```
+
+```{code-cell} ipython3
+:tags: [hide-output]
+
+%%sql
+ATTACH DATABASE 'a.db' AS a_schema
+```
+
+```{code-cell} ipython3
+:tags: [hide-output]
+
+import sqlite3
+
+with sqlite3.connect("b.db") as conn:
+    conn.execute("CREATE TABLE my_numbers (number FLOAT)")
+    conn.execute("INSERT INTO my_numbers VALUES (11)")
+    conn.execute("INSERT INTO my_numbers VALUES (22)")
+    conn.execute("INSERT INTO my_numbers VALUES (33)")
+```
+
+```{code-cell} ipython3
+:tags: [hide-output]
+
+%%sql
+ATTACH DATABASE 'b.db' AS b_schema
+```
+
+Let's profile `my_numbers` of `b_schema`
+
+```{code-cell} ipython3
+%sqlcmd profile --table my_numbers --schema b_schema
+```
diff --git a/setup.py b/setup.py
index 2ddf74a..e96a0ec 100644
--- a/setup.py
+++ b/setup.py
@@ -24,7 +24,7 @@ install_requires = [
     "sqlglot",
     "jinja2",
     "ploomber-core>=0.2.4",
-    'importlib-metadata;python_version<"3.8"',
+    'importlib-metadata;python_version<"3.8"'
 ]
 
 DEV = [
diff --git a/src/sql/inspect.py b/src/sql/inspect.py
index 751f864..a8970ae 100644
--- a/src/sql/inspect.py
+++ b/src/sql/inspect.py
@@ -1,9 +1,11 @@
 from sqlalchemy import inspect
 from prettytable import PrettyTable
 from ploomber_core.exceptions import modify_exceptions
-
 from sql.connection import Connection
 from sql.telemetry import telemetry
+import sql.run
+import math
+from sql.util import convert_to_scientific
 
 
 def _get_inspector(conn):
@@ -73,6 +75,167 @@ class Columns(DatabaseInspection):
         self._table_txt = self._table.get_string()
 
 
+@modify_exceptions
+class TableDescription(DatabaseInspection):
+    """
+    Generates descriptive statistics.
+
+    Descriptive statistics are:
+
+    Count - Number of all not None values
+
+    Mean - Mean of the values
+
+    Max - Maximum of the values in the object.
+
+    Min - Minimum of the values in the object.
+
+    STD - Standard deviation of the observations
+
+    25h, 50h and 75h percentiles
+
+    Unique - Number of not None unique values
+
+    Top - The most frequent value
+
+    Freq - Frequency of the top value
+
+    """
+
+    def __init__(self, table_name, schema=None) -> None:
+        if schema:
+            table_name = f"{schema}.{table_name}"
+
+        columns = sql.run.raw_run(
+            Connection.current, f"SELECT * FROM {table_name} WHERE 1=0"
+        ).keys()
+
+        table_stats = dict({})
+        columns_to_include_in_report = set()
+
+        for column in columns:
+            table_stats[column] = dict()
+
+            # Note: index is reserved word in sqlite
+            try:
+                result_col_freq_values = sql.run.raw_run(
+                    Connection.current,
+                    f"""SELECT DISTINCT {column} as top,
+                    COUNT({column}) as frequency FROM {table_name}
+                    GROUP BY {column} ORDER BY Count({column}) Desc"""
+                ).fetchall()
+
+                table_stats[column]["freq"] = result_col_freq_values[0][1]
+                table_stats[column]["top"] = result_col_freq_values[0][0]
+
+                columns_to_include_in_report.update(["freq", "top"])
+
+            except Exception:
+                pass
+
+            try:
+                # get all non None values, min, max and avg.
+                result_value_values = sql.run.raw_run(
+                    Connection.current,
+                    f"""
+                    SELECT MIN({column}) AS min,
+                    MAX({column}) AS max,
+                    COUNT(DISTINCT {column}) AS unique_count,
+                    COUNT({column}) AS count
+                    FROM {table_name}
+                    WHERE {column} IS NOT NULL
+                    """
+                ).fetchall()
+
+                table_stats[column]["min"] = result_value_values[0][0]
+                table_stats[column]["max"] = result_value_values[0][1]
+                table_stats[column]["unique"] = result_value_values[0][2]
+                table_stats[column]["count"] = result_value_values[0][3]
+
+                columns_to_include_in_report.update(["count", "unique", "min", "max"])
+
+            except Exception:
+                pass
+
+            try:
+                results_avg = sql.run.raw_run(
+                    Connection.current,
+                    f"""
+                                SELECT AVG({column}) AS avg
+                                FROM {table_name}
+                                WHERE {column} IS NOT NULL
+                                """
+                ).fetchall()
+
+                table_stats[column]["mean"] = float(results_avg[0][0])
+                columns_to_include_in_report.update(["mean"])
+
+            except Exception:
+                table_stats[column]["mean"] = math.nan
+
+            # These keys are numeric and work only on duckdb
+            special_numeric_keys = ["std", "25%", "50%", "75%"]
+
+            try:
+                # Note: stddev_pop and PERCENTILE_DISC will work only on DuckDB
+                result = sql.run.raw_run(
+                    Connection.current,
+                    f"""
+                    SELECT
+                        stddev_pop({column}) as key_std,
+                        percentile_disc(0.25) WITHIN GROUP
+                        (ORDER BY {column}) as key_25,
+                        percentile_disc(0.50) WITHIN GROUP
+                        (ORDER BY {column}) as key_50,
+                        percentile_disc(0.75) WITHIN GROUP
+                        (ORDER BY {column}) as key_75
+                    FROM {table_name}
+                    """
+                ).fetchall()
+
+                for i, key in enumerate(special_numeric_keys):
+                    # r_key = f'key_{key.replace("%", "")}'
+                    table_stats[column][key] = float(result[0][i])
+
+                columns_to_include_in_report.update(special_numeric_keys)
+
+            except TypeError:
+                # for non numeric values
+                for key in special_numeric_keys:
+                    table_stats[column][key] = math.nan
+
+            except Exception as e:
+                # We tried to apply numeric function on
+                # non numeric value, i.e: DateTime
+                if "duckdb.BinderException" or "add explicit type casts" in str(e):
+                    for key in special_numeric_keys:
+                        table_stats[column][key] = math.nan
+
+                # Failed to run sql command/func (e.g stddev_pop).
+                # We ignore the cell stats for such case.
+                pass
+
+        self._table = PrettyTable()
+        self._table.field_names = [" "] + list(table_stats.keys())
+
+        rows = list(columns_to_include_in_report)
+        rows.sort(reverse=True)
+        for row in rows:
+            values = [row]
+            for column in table_stats:
+                if row in table_stats[column]:
+                    value = table_stats[column][row]
+                else:
+                    value = ""
+                value = convert_to_scientific(value)
+                values.append(value)
+
+            self._table.add_row(values)
+
+        self._table_html = self._table.get_html_string()
+        self._table_txt = self._table.get_string()
+
+
 @telemetry.log_call()
 def get_table_names(schema=None):
     """Get table names for a given connection"""
@@ -83,3 +246,14 @@ def get_table_names(schema=None):
 def get_columns(name, schema=None):
     """Get column names for a given connection"""
     return Columns(name, schema)
+
+
+@telemetry.log_call()
+def get_table_statistics(name, schema=None):
+    """Get table statistics for a given connection.
+
+    For all data types the results will include `count`, `mean`, `std`, `min`
+    `max`, `25`, `50` and `75` percentiles. It will also include `unique`, `top`
+    and `freq` statistics.
+    """
+    return TableDescription(name, schema=schema)
diff --git a/src/sql/magic_cmd.py b/src/sql/magic_cmd.py
index 3bf7fea..3f52873 100644
--- a/src/sql/magic_cmd.py
+++ b/src/sql/magic_cmd.py
@@ -5,7 +5,7 @@ from IPython.utils.process import arg_split
 from IPython.core.magic import (
     Magics,
     line_magic,
-    magics_class,
+    magics_class
 )
 from IPython.core.magic_arguments import argument, magic_arguments
 from IPython.core.error import UsageError
@@ -38,7 +38,7 @@ class SqlCmdMagic(Magics, Configurable):
     @line_magic("sqlcmd")
     @magic_arguments()
     @argument("line", default="", type=str, help="Command name")
-    def execute(self, line="", cell="", local_ns=None):
+    def execute(self, line="", cell=""):
         """
         Command
         """
@@ -134,9 +134,36 @@ class SqlCmdMagic(Magics, Configurable):
             else:
                 return True
 
+        elif cmd_name == "profile":
+
+            parser = CmdParser()
+            parser.add_argument(
+                "-t", "--table", type=str, help="Table name", required=True
+            )
+
+            parser.add_argument(
+                "-s", "--schema", type=str, help="Schema name", required=False
+            )
+
+            parser.add_argument(
+                "-o", "--output", type=str, help="Store report location", required=False
+            )
+
+            args = parser.parse_args(others)
+
+            report = inspect.get_table_statistics(
+                schema=args.schema, name=args.table
+            )
+
+            if args.output:
+                with open(args.output, "w") as f:
+                    f.write(report._repr_html_())
+
+            return report
+
         raise UsageError(
             f"%sqlcmd has no command: {cmd_name!r}. "
-            "Valid commands are: 'tables', 'columns'"
+            "Valid commands are: 'tables', 'columns', 'profile'"
         )
 
 
diff --git a/src/sql/run.py b/src/sql/run.py
index 1c978e3..df03578 100644
--- a/src/sql/run.py
+++ b/src/sql/run.py
@@ -107,13 +107,18 @@ class ResultSet(list, ColumnGuesserMixin):
         self.keys = {}
         if sqlaproxy.returns_rows:
             self.keys = sqlaproxy.keys()
-            if config.autolimit:
+            if isinstance(config.autolimit, int) and config.autolimit > 0:
                 list.__init__(self, sqlaproxy.fetchmany(size=config.autolimit))
             else:
                 list.__init__(self, sqlaproxy.fetchall())
             self.field_names = unduplicate_field_names(self.keys)
+
+            _style = None
+            if isinstance(config.style, str):
+                _style = prettytable.__dict__[config.style.upper()]
+
             self.pretty = PrettyTable(
-                self.field_names, style=prettytable.__dict__[config.style.upper()]
+                self.field_names, style=_style
             )
         else:
             list.__init__(self, [])
@@ -348,7 +353,7 @@ class FakeResultProxy(object):
         def fetchmany(size):
             pos = 0
             while pos < len(source_list):
-                yield source_list[pos : pos + size]
+                yield source_list[pos: pos + size]
                 pos += size
 
         self.fetchmany = fetchmany
@@ -457,6 +462,10 @@ def run(conn, sql, config):
     return select_df_type(resultset, config)
 
 
+def raw_run(conn, sql):
+    return conn.session.execute(sql)
+
+
 class PrettyTable(prettytable.PrettyTable):
     def __init__(self, *args, **kwargs):
         self.row_count = 0
diff --git a/src/sql/util.py b/src/sql/util.py
new file mode 100644
index 0000000..f606391
--- /dev/null
+++ b/src/sql/util.py
@@ -0,0 +1,31 @@
+def convert_to_scientific(value):
+    """
+    Converts value to scientific notation if necessary
+
+    Parameters
+    ----------
+    value : any
+        Value to format.
+    """
+    if (
+        isinstance(value, (int, float))
+        and not isinstance(value, bool)
+        and _is_long_number(value)
+    ):
+        new_value = "{:,.3e}".format(value)
+
+    else:
+        new_value = value
+
+    return new_value
+
+
+def _is_long_number(num) -> bool:
+    """
+    Checks if num's digits > 10
+    """
+    if "." in str(num):
+        split_by_decimal = str(num).split(".")
+        if len(split_by_decimal[0]) > 10 or len(split_by_decimal[1]) > 10:
+            return True
+    return False


Test patch:
diff --git a/src/tests/integration/test_generic_db_opeations.py b/src/tests/integration/test_generic_db_opeations.py
index b39ddf2..0590783 100644
--- a/src/tests/integration/test_generic_db_opeations.py
+++ b/src/tests/integration/test_generic_db_opeations.py
@@ -3,6 +3,7 @@ import pytest
 import warnings
 from sql.telemetry import telemetry
 from unittest.mock import ANY, Mock
+import math
 
 
 @pytest.fixture(autouse=True)
@@ -189,3 +190,117 @@ def test_sql_cmd_magic_dos(ip_with_dynamic_db, request):
     assert len(result) == 1
     assert "greater_or_equal" in result.keys()
     assert list(result["greater_or_equal"]) == [2, 3]
+
+
+@pytest.mark.parametrize(
+    "ip_with_dynamic_db, table, table_columns, expected",
+    [
+        (
+            "ip_with_postgreSQL",
+            "taxi",
+            ["index", "taxi_driver_name"],
+            {
+                "count": [45, 45],
+                "mean": [22.0, math.nan],
+                "min": [0, "Eric Ken"],
+                "max": [44, "Kevin Kelly"],
+                "unique": [45, 3],
+                "freq": [1, 15],
+                "top": [0, "Eric Ken"],
+                "std": ["1.299e+01", math.nan],
+                "25%": [11.0, math.nan],
+                "50%": [22.0, math.nan],
+                "75%": [33.0, math.nan],
+            },
+        ),
+        pytest.param(
+            "ip_with_mySQL",
+            "taxi",
+            ["taxi_driver_name"],
+            {
+                "count": [45],
+                "mean": [0.0],
+                "min": ["Eric Ken"],
+                "max": ["Kevin Kelly"],
+                "unique": [3],
+                "freq": [15],
+                "top": ["Kevin Kelly"],
+            },
+            marks=pytest.mark.xfail(
+                reason="Need to get column names from table with a different query"
+            ),
+        ),
+        pytest.param(
+            "ip_with_mariaDB",
+            "taxi",
+            ["taxi_driver_name"],
+            {
+                "count": [45],
+                "mean": [0.0],
+                "min": ["Eric Ken"],
+                "max": ["Kevin Kelly"],
+                "unique": [3],
+                "freq": [15],
+                "top": ["Kevin Kelly"],
+            },
+            marks=pytest.mark.xfail(
+                reason="Need to get column names from table with a different query"
+            ),
+        ),
+        (
+            "ip_with_SQLite",
+            "taxi",
+            ["taxi_driver_name"],
+            {
+                "count": [45],
+                "mean": [0.0],
+                "min": ["Eric Ken"],
+                "max": ["Kevin Kelly"],
+                "unique": [3],
+                "freq": [15],
+                "top": ["Kevin Kelly"],
+            },
+        ),
+        (
+            "ip_with_duckDB",
+            "taxi",
+            ["index", "taxi_driver_name"],
+            {
+                "count": [45, 45],
+                "mean": [22.0, math.nan],
+                "min": [0, "Eric Ken"],
+                "max": [44, "Kevin Kelly"],
+                "unique": [45, 3],
+                "freq": [1, 15],
+                "top": [0, "Eric Ken"],
+                "std": ["1.299e+01", math.nan],
+                "25%": [11.0, math.nan],
+                "50%": [22.0, math.nan],
+                "75%": [33.0, math.nan],
+            },
+        ),
+    ],
+)
+def test_profile_query(request, ip_with_dynamic_db, table, table_columns, expected):
+    ip_with_dynamic_db = request.getfixturevalue(ip_with_dynamic_db)
+
+    out = ip_with_dynamic_db.run_cell(
+        f"""
+        %sqlcmd profile --table "{table}"
+        """
+    ).result
+
+    stats_table = out._table
+
+    assert len(stats_table.rows) == len(expected)
+
+    for row in stats_table:
+        criteria = row.get_string(fields=[" "], border=False).strip()
+
+        for i, column in enumerate(table_columns):
+            cell_value = row.get_string(
+                fields=[column], border=False, header=False
+            ).strip()
+
+            assert criteria in expected
+            assert cell_value == str(expected[criteria][i])
diff --git a/src/tests/test_magic_cmd.py b/src/tests/test_magic_cmd.py
index 7fc189d..20242df 100644
--- a/src/tests/test_magic_cmd.py
+++ b/src/tests/test_magic_cmd.py
@@ -2,6 +2,7 @@ import sqlite3
 
 import pytest
 from IPython.core.error import UsageError
+from pathlib import Path
 
 
 @pytest.mark.parametrize(
@@ -10,7 +11,8 @@ from IPython.core.error import UsageError
         [
             "%sqlcmd stuff",
             UsageError,
-            "%sqlcmd has no command: 'stuff'. Valid commands are: 'tables', 'columns'",
+            "%sqlcmd has no command: 'stuff'. Valid commands are: 'tables', "
+            "'columns', 'profile'",
         ],
         [
             "%sqlcmd columns",
@@ -70,3 +72,117 @@ ATTACH DATABASE 'my.db' AS some_schema
     ).result._repr_html_()
 
     assert "some_number" in out
+
+
+def test_table_profile(ip, tmp_empty):
+    ip.run_cell(
+        """
+    %%sql sqlite://
+    CREATE TABLE numbers (rating float, price float, number int, word varchar(50));
+    INSERT INTO numbers VALUES (14.44, 2.48, 82, 'a');
+    INSERT INTO numbers VALUES (13.13, 1.50, 93, 'b');
+    INSERT INTO numbers VALUES (12.59, 0.20, 98, 'a');
+    INSERT INTO numbers VALUES (11.54, 0.41, 89, 'a');
+    INSERT INTO numbers VALUES (10.532, 0.1, 88, 'c');
+    INSERT INTO numbers VALUES (11.5, 0.2, 84, '   ');
+    INSERT INTO numbers VALUES (11.1, 0.3, 90, 'a');
+    INSERT INTO numbers VALUES (12.9, 0.31, 86, '');
+    """
+    )
+
+    expected = {
+        "count": [8, 8, 8, 8],
+        "mean": [12.2165, "6.875e-01", 88.75, 0.0],
+        "min": [10.532, 0.1, 82, ""],
+        "max": [14.44, 2.48, 98, "c"],
+        "unique": [8, 7, 8, 5],
+        "freq": [1, 2, 1, 4],
+        "top": [14.44, 0.2, 98, "a"],
+    }
+
+    out = ip.run_cell("%sqlcmd profile -t numbers").result
+
+    stats_table = out._table
+
+    assert len(stats_table.rows) == len(expected)
+
+    for row in stats_table:
+        criteria = row.get_string(fields=[" "], border=False).strip()
+
+        rating = row.get_string(fields=["rating"], border=False, header=False).strip()
+
+        price = row.get_string(fields=["price"], border=False, header=False).strip()
+
+        number = row.get_string(fields=["number"], border=False, header=False).strip()
+
+        word = row.get_string(fields=["word"], border=False, header=False).strip()
+
+        assert criteria in expected
+        assert rating == str(expected[criteria][0])
+        assert price == str(expected[criteria][1])
+        assert number == str(expected[criteria][2])
+        assert word == str(expected[criteria][3])
+
+
+def test_table_schema_profile(ip, tmp_empty):
+
+    with sqlite3.connect("a.db") as conn:
+        conn.execute("CREATE TABLE t (n FLOAT)")
+        conn.execute("INSERT INTO t VALUES (1)")
+        conn.execute("INSERT INTO t VALUES (2)")
+        conn.execute("INSERT INTO t VALUES (3)")
+
+    with sqlite3.connect("b.db") as conn:
+        conn.execute("CREATE TABLE t (n FLOAT)")
+        conn.execute("INSERT INTO t VALUES (11)")
+        conn.execute("INSERT INTO t VALUES (22)")
+        conn.execute("INSERT INTO t VALUES (33)")
+
+    ip.run_cell(
+        """
+    %%sql sqlite://
+    ATTACH DATABASE 'a.db' AS a_schema;
+    ATTACH DATABASE 'b.db' AS b_schema;
+    """
+    )
+
+    expected = {
+        "count": [3],
+        "mean": [22.0],
+        "min": [11.0],
+        "max": [33.0],
+        "std": [11.0],
+        "unique": [3],
+        "freq": [1],
+        "top": [33.0],
+    }
+
+    out = ip.run_cell("%sqlcmd profile -t t --schema b_schema").result
+
+    stats_table = out._table
+
+    for row in stats_table:
+        criteria = row.get_string(fields=[" "], border=False).strip()
+
+        cell = row.get_string(fields=["n"], border=False, header=False).strip()
+
+        if criteria in expected:
+            assert cell == str(expected[criteria][0])
+
+
+def test_table_profile_store(ip, tmp_empty):
+    ip.run_cell(
+        """
+    %%sql sqlite://
+    CREATE TABLE test_store (rating, price, number, symbol);
+    INSERT INTO test_store VALUES (14.44, 2.48, 82, 'a');
+    INSERT INTO test_store VALUES (13.13, 1.50, 93, 'b');
+    INSERT INTO test_store VALUES (12.59, 0.20, 98, 'a');
+    INSERT INTO test_store VALUES (11.54, 0.41, 89, 'a');
+    """
+    )
+
+    ip.run_cell("%sqlcmd profile -t test_store --output test_report.html")
+
+    report = Path("test_report.html")
+    assert report.is_file()