.json extension to get JSON of a dashboard

[simonw]

Simple feature: /dashboard/name.json gives you a JSON version of that saved dashboard data, if you have permission to view the page.

[simonw]

Got a prototype working that looks like this:

{
  "title": "Tag word cloud",
  "queries": [
    {
      "sql": "select \"tag\" as wordcloud_word, count(*) as wordcloud_count from (select blog_tag.tag from blog_entry_tags join blog_tag on blog_entry_tags.tag_id = blog_tag.id\r\nunion all\r\nselect blog_tag.tag from blog_blogmark_tags join blog_tag on blog_blogmark_tags.tag_id = blog_tag.id\r\nunion all\r\nselect blog_tag.tag from blog_quotation_tags join blog_tag on blog_quotation_tags.tag_id = blog_tag.id) as results where tag != 'quora' group by \"tag\" order by wordcloud_count desc",
      "rows": [
        {
          "wordcloud_word": "python",
          "wordcloud_count": 826
        },
        {
          "wordcloud_word": "javascript",
          "wordcloud_count": 604
        },
        {
          "wordcloud_word": "django",
          "wordcloud_count": 529
        },
        {
          "wordcloud_word": "security",
          "wordcloud_count": 402
        },
        {
          "wordcloud_word": "datasette",
          "wordcloud_count": 331
        },
        {
          "wordcloud_word": "projects",
          "wordcloud_count": 282
        }

[simonw]

Here's that prototype:

diff --git a/django_sql_dashboard/urls.py b/django_sql_dashboard/urls.py
index ac37a1f..3bc2eec 100644
--- a/django_sql_dashboard/urls.py
+++ b/django_sql_dashboard/urls.py
@@ -1,8 +1,9 @@
 from django.urls import path
 
-from .views import dashboard, dashboard_index
+from .views import dashboard, dashboard_json, dashboard_index
 
 urlpatterns = [
     path("", dashboard_index, name="django_sql_dashboard-index"),
     path("<slug>/", dashboard, name="django_sql_dashboard-dashboard"),
+    path("<slug>.json", dashboard_json, name="django_sql_dashboard-dashboard_json"),
 ]
diff --git a/django_sql_dashboard/views.py b/django_sql_dashboard/views.py
index c51bcef..220fdba 100644
--- a/django_sql_dashboard/views.py
+++ b/django_sql_dashboard/views.py
@@ -13,6 +13,7 @@ from django.forms import CharField, ModelForm, Textarea
 from django.http.response import (
     HttpResponseForbidden,
     HttpResponseRedirect,
+    JsonResponse,
     StreamingHttpResponse,
 )
 from django.shortcuts import get_object_or_404, render
@@ -137,6 +138,7 @@ def _dashboard_index(
     too_long_so_use_post=False,
     template="django_sql_dashboard/dashboard.html",
     extra_context=None,
+    json_mode=False,
 ):
     query_results = []
     alias = getattr(settings, "DASHBOARD_DB_ALIAS", "dashboard")
@@ -329,6 +331,22 @@ def _dashboard_index(
             )
         ]
 
+    if json_mode:
+        return JsonResponse(
+            {
+                "title": title or "SQL Dashboard",
+                "queries": [
+                    {"sql": r["sql"], "rows": r["rows"]} for r in query_results
+                ],
+            },
+            json_dumps_params={
+                "indent": 2,
+                "default": lambda o: o.isoformat()
+                if hasattr(o, "isoformat")
+                else str(o),
+            },
+        )
+
     context = {
         "title": title or "SQL Dashboard",
         "html_title": html_title,
@@ -362,7 +380,11 @@ def _dashboard_index(
     return response
 
 
-def dashboard(request, slug):
+def dashboard_json(request, slug):
+    return dashboard(request, slug, json_mode=True)
+
+
+def dashboard(request, slug, json_mode=False):
     dashboard = get_object_or_404(Dashboard, slug=slug)
     # Can current user see it, based on view_policy?
     view_policy = dashboard.view_policy
@@ -398,6 +420,7 @@ def dashboard(request, slug):
         description=dashboard.description,
         dashboard=dashboard,
         template="django_sql_dashboard/saved_dashboard.html",
+        json_mode=json_mode,
     )

[simonw]

This will be on by default but I'll provide a setting for turning it off.

[simonw]

I can't figure out how to run the tests locally. I tried this but it didn't work:

diff --git a/pytest.ini b/pytest.ini
index db54719..4d0a8d7 100644
--- a/pytest.ini
+++ b/pytest.ini
@@ -1,4 +1,6 @@
 [pytest]
 addopts = -p pytest_use_postgresql
-DJANGO_SETTINGS_MODULE = config.settings
-site_dirs = test_project/
+DJANGO_SETTINGS_MODULE = config.settings_interactive
+pythonpath = test_project/
+usefixtures =
+    db
diff --git a/setup.py b/setup.py
index 85f96f0..5dff834 100644
--- a/setup.py
+++ b/setup.py
@@ -42,9 +42,8 @@ setup(
         "test": [
             "black>=22.3.0",
             "psycopg2",
-            "pytest",
+            "pytest>=7.0",
             "pytest-django==4.2.0",
-            "pytest-pythonpath",
             "dj-database-url",
             "testing.postgresql",
             "beautifulsoup4",

And:

PYTHONPATH=.:test_project POSTGRESQL_PATH=/Applications/Postgres.app/Contents/Versions/15/bin/postgres \
DJANGO_SETTINGS_MODULE=config.settings_interactive INITDB_PATH=/Applications/Postgres.app/Contents/Versions/15/bin/initdb python -m pytest  -x

[simonw]

All tests are broken in CI too: https://github.com/simonw/django-sql-dashboard/actions/runs/7230325348/job/19702260457

I think it's a pytest version issue.

[simonw]

It's this problem here:

• Upgrade to pytest-django 4.3.0 #109

Pinning to pytest-django==4.2.0 still fixes it.

[simonw]

Managed to run tests locally with:

PYTHONPATH=pytest_plugins:test_project \
POSTGRESQL_PATH=/Applications/Postgres.app/Contents/Versions/15/bin/postgres \
DJANGO_SETTINGS_MODULE=config.settings_interactive \
INITDB_PATH=/Applications/Postgres.app/Contents/Versions/15/bin/initdb \
python -m pytest --lf -x --pdb

[simonw]

Error:

E django.db.utils.NotSupportedError: PostgreSQL 12 or later is required (found 10.23).

I'll drop older PostgreSQL.