Closed simonw closed 10 months ago
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
}
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,
)
This will be on by default but I'll provide a setting for turning it off.
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
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.
It's this problem here:
Pinning to pytest-django==4.2.0
still fixes it.
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
Error:
E django.db.utils.NotSupportedError: PostgreSQL 12 or later is required (found 10.23).
I'll drop older PostgreSQL.
Simple feature:
/dashboard/name.json
gives you a JSON version of that saved dashboard data, if you have permission to view the page.