Панель управления Django SQL

Панельуправленияdjangosql

Я выпустил первую не альфа-версию Django SQL Dashboard , который предоставляет интерфейс для выполнения произвольных SQL-запросов только для чтения непосредственно к базе данных PostgreSQL, защищенной схемой аутентификации Django. Его также можно использовать для создания сохраненных информационных панелей, которые можно публиковать или совместно использовать внутри компании.

Я начал создавать этот инструмент еще в марте в рамках моей работы по переносу VaccinateCA из Airtable на пользовательский бэкэнд Django . Одной из сильных сторон Airtable является то, что он позволяет проводить специальные исследования данных и создавать отчеты, и я хотел предоставить альтернативу этому для нового бэкэнда Django.

Я также хотел опробовать некоторые новые идеи для Datasette , который (пока) не работает с PostgreSQL.

Сначала демонстрация

Я записал эту трехминутную демонстрацию программного обеспечения, используя базу данных моего блога в качестве примера.

В этом видео я запускаю следующие SQL-запросы, чтобы исследовать таблицу «многие ко многим», которая отображает теги в мой блог. записи:

 Выбрать * из blog_entry_tags; 

В начале таблица выглядит так – не особо интересно:

id entry_id tag_id 5043 379 1931 5044 379 2118 5045 379 119 id entry_id tag_id 5043 379 1931 5044 379 2118 5045 379 119

Тогда я бегу этот запрос, чтобы присоединиться к blog_tag и получите подробную информацию о каждом теге:

Выбрать    из  blog_entry_tags  присоединиться к  blog_tag  в   blog_tag .я бы знак равно  blog_entry_tags .  tag_id  

id entry_id tag_id id tag 5043 379 1931 1931 ie6 5044 379 2118 2118 jeffreyzeldman 5045 379 119 119 alistapart 5046 1533 2432 2432 lugradiolive

Это немного полезнее. Затем я нажимаю ссылку «подсчитать» вверху столбца «Теги». Это создает для меня SQL-запрос, который использует счетчик а также сгруппировать по , чтобы вернуть количество каждого значения в этом столбце:

 Выбрать "тег " , считать( )  как  n  из  ( Выбрать    из  blog_entry_tags  присоединиться  blog_tag на  blog_tag .  id  знак равно   blog_entry_tags .  tag_id )  как  результаты  сгруппировать по   "тег "   заказ по  n  desc  

tag n quora 1003 webdevelopment 166 startups 157 conferences 152 datasette 110

Наконец, я продемонстрирую некоторые визуализации виджетов по умолчанию, которые поставляются с Django SQL Dashboard. Если я перепишу запрос, чтобы вернуть столбцы с именем bar_label и bar_quantity инструмент отобразит результаты в виде гистограммы:

Выбрать   "тег"   as  bar_label,  count  ( ) в виде bar_quantity  из  ( Выбрать    от  blog_entry_tags  присоединиться  blog_tag  на   blog_tag . я бы  знак равно  blog_entry_tags .  tag_id  ) в виде результаты  сгруппировать по    "тег"   упорядочить по  bar_quantity  desc  

The query shows its results as a bar chart

Далее я демонстрирую похожий трюк. который вместо этого создает облако слов путем наложения столбцов на wordcloud_word и wordcloud_count :

Выбрать "тег "  в виде wordcloud_word,  count  ( )  как  wordcloud_count  из  ( выберите    из blog_entry_tags  присоединиться  blog_tag на  blog_tag .  id   =   blog_entry_tags .  tag_id )  как  результаты группа по  "тег "   упорядочить по  wordcloud_count  desc  

My top tags, visualized as a word cloud

Наконец, я покажу, как этот запрос можно превратить в сохраненную информационную панель и сделать общедоступным. Вот сохраненная панель инструментов, которую я создал в видео:

https://simonwillison.net/dashboard/tag-cloud/

Это иллюстрирует ключевую идею, лежащую в основе как панели управления Django SQL, так и набора данных: My top tags, visualized as a word cloud полное приложение может быть определено как SQL-запрос!

Большая часть работы, которую мы выполняем как разработчики веб-приложений, может быть сведена к построению SQL-запроса и его подключению для вывода на веб-страницу. Если вы можете безопасно выполнять SQL-запросы из строк запроса страницы, это означает, что вы можете создавать собственные приложения, которые существуют полностью как URL-адреса, добавляемые в закладки.

My приложение рисования фигуры на карте для поиска мини-парков в Калифорнии, сделанного несколько месяцев назад, – еще один пример этого шаблона в действии.

Пользовательские виджеты

Создание новых пользовательских виджетов для этого инструмента _extremely_easy – отсюда и пример облака слов, который я построил для этой демонстрации. Все, что вам нужно предоставить, – это один файл шаблона Django.

Если ваш виджет будет отвечать на возвращенные столбцы wordcloud_word и wordcloud_count имя этого шаблона - это те столбцы, отсортированные в алфавитном порядке и соединенные дефисами:

wordcloud_count-wordcloud_word.html

Поместите это в django_sql_dashboard / widgets , и новый виджет будет готов к использованию. Вот полная реализация виджета облака слов.

Поддержка именованных параметров

Я поднял эту функцию прямо из набора данных . Вы можете создавать запросы SQL, которые выглядят следующим образом:

 Выбрать    из  blog_entry , где  id  = % (id) s 

Для именованных параметров используется синтаксис psycopg2. Значение будет правильно заключено в кавычки и экранировано, поэтому это полезный инструмент для предотвращения атак SQL-инъекций.

Djang SQL Dashboard обнаруживает эти параметры и превращает их в поля формы. Вот как это выглядит в интерфейсе:

A form asking for the ID parameter for that query

Эти формы отправляются с использованием GET, поэтому результат можно пометить закладками. Вот сохраненная панель управления, которую вы можете использовать для получения сведений о любой из моих записей в блоге по их числовому идентификатору:

https://simonwillison.net/dashboard/blog-entry-by-id/?id знак равно

Вы можете включить несколько параметров SQL на одной панели мониторинга и в любой форме. параметры будут доступны для всех этих запросов.

Это означает, что вы можете создавать информационные панели, которые запускают несколько запросов с одними и теми же аргументами. Представьте, например, что вы хотите создать отчет о действиях определенного пользователя в нескольких таблицах - вы можете принять его идентификатор пользователя в качестве параметра, а затем отобразить вывод нескольких запросов (включая настраиваемые визуализации), каждый из которых ссылается на этот параметр.

Экспорт путем копирования и вставки

Мне нравится копировать и вставлять как механизм экспорта данных из системы. Django SQL Dashboard принимает это двумя способами:

Экспортировать все результаты запроса как CSV / TSV

Это часто возникает в Vaccinate CA: мы проводим большой анализ данных, когда нам нужно работать с другими инструментами или отправлять данные партнерам, а также иметь возможность экспортировать полный набор результатов для запрос (вместо усечения до первой тысячи, чтобы избежать сбоя браузера пользователя) был частой необходимостью. - Django SQL Dashboard предоставляет эту опцию, используя комбинацию Django

механизм потокового HTTP-ответа и PostgreSQL серверные курсоры для эффективной потоковой передачи больших объемов данных без исчерпания ресурсов.

Сложный пример: поиск примеров кода в моем блоге

Я решил посмотреть, как далеко я смогу продвинуть PostgreSQL.

Я часто включаю код в свои записи в блоге - примеры, заключенные в

  тег.  Внутри этого тега я иногда применяю подсветку синтаксиса (набор    элементы). 

Оказывается, я включил фрагменты кода в 152 разные записи в блоге:

  выберите   количество  ( ) из blog_entry  где  body ~   ' 
. 
  '  

Могу ли я использовать регулярные выражения в PostgreSQL, чтобы извлечь только примеры кода, очистить их (удалить эти промежутки, изменить кодировку HTML-сущностей), а затем обеспечить простой поиск? скопировать текст этих примеров одним запросом?

Оказывается, я могу!

Вот сохраненная панель управления, которую вы можете использовать для выполнения поиска только по содержимому этих

  во всех записях на моем блог: 

https://simonwillison.net/dashboard/code-examples/?search=select

 с полосками результатов  как  ( выберите  id, title, replace (replace (replace (replace (replace (regexp_replace ((regexp_matches (body,    ' 
 (. *?) 

' , 'грамм' )) , E ' <[^>] +> ' , ' ' , ' gi ' ), ' " ' || chr ( 119 ), ' " ' ), '> ' || chr ( 110 ), '> ' ), ' < ' || chr ( 110 ), ' < ' ), ' & # 110 ' || chr ( 59 ), chr ( 46 )), ' & ' || chr ( 110 ), ' & ' ) как код из blog_entry где тело ~ '

. 
  ' ) Выбрать идентификатор, заголовок, код,   ' https: //simonwillison.net/e/'   ||  id  как  ссылка  из  results_stripped  где  код нравиться   ' %%  '   || % (поиск) s  ||    ' %%  '   предел   13  

Есть много чего Здесь. Ключевым компонентом является этот бит:

  regexp_matches (body, '
 (. *?) 

', 'грамм'))[1]

<[^> функция regexp_matches () с 'грамм', возвращает каждое совпадение для данного регулярного выражения. В рамках более крупного выберите , это означает, что если выражение соответствует три раза, вы вернете три строки в выводе (в данном случае с дубликатом id и заголовок столбцы) - вот что Я хочу сюда.

Он завернут в ужасающее множество дополнительных функций. Они служат двум целям: удаляют все вложенные теги HTML и отменяют экранирование ", <, > , & а также & # 46; HTML-сущности. Я сделал это как вложенный блок replace () функции - вероятно, здесь есть более изящное решение.

chr (110) биты - это взлом: Django SQL Dashboard запрещает ; , чтобы люди не могли выполнять несколько SQL-запросов, что может быть использовано для обхода некоторых параметров транзакции. защитные настройки, применяемые инструментом.

Но мне нужно выполнить поиск и заменить " - поэтому я использую этот шаблон для включения точки с запятой:

  replace (text, '"' ||  chr (134), '"') 

Где || - это PostgreSQL оператор конкатенации строк.

Сам поиск строится следующим образом:

  где код вроде '%%' ||% (поиск) s || '%%'  

Создает подобный запрос к '% your- search-term% ' - экранирование двойного знака процента необходимо, потому что % здесь имеет особое значение (это часть % (search) s именованный параметр).

Последний трюк: окончательный вывод запроса производится этим :

  выберите  id, название, код,   ' https://simonwillison.net/e/  '   ||  идентификатор  как  ссылка  из  results_stripped 

results_stripped это CTE , определенный ранее - я обычно пытаюсь завершить сложные странные такие вещи, как вложенные replace () вызывает CTE, чтобы я мог написать простой окончательный запрос.

'https://simonwillison.net/e/' || id as link здесь объединяет URL-адрес, который ссылается на мою запись на основе ее идентификатора. В моем блоге используется / yyyy / Mon / slug / , но генерируя их из SQL-запроса к созданным колонка была немного суетливой, поэтому я добавлены URL-адреса перенаправления / e / ID , чтобы упростить создание ссылок в запросах панели управления.

Планы на будущее

Django SQL Dashboard уже зарекомендовал себя бесценным для моего текущего проекта. Я предполагаю, что буду использовать его для каждого проекта Django, который я буду строить в будущем - возможность запрашивать базу данных таким образом, создавать специальные визуализации и затем связываться с ними - это огромный рост производительности.

Более важный вопрос заключается в том, как это перекрывается с Datasette .

Набор данных предназначен только для SQLite с тех пор, как я начал проект три с половиной года назад, потому что я знаю, что создание уровня абстракции базы данных - это огромная дополнительная задача, а для набора данных Первоначальная цель - помочь публиковать данные только для чтения, в этом не было необходимости.

У меня растет подозрение, что получение набора данных для работы с PostgreSQL (и других баз данных) в дополнение к SQLite требует меньше усилий, чем я первоначально думал.

Набор данных также построен на основе ASGI. Django 3.0 представила поддержку ASGI , поэтому теперь можно размещать приложения ASGI, такие как Datasette, как часть унифицированного приложения Django.

Так что вполне возможно, что будущее Django SQL Dashboard будет за набором данных, который в конечном итоге сделает его устаревшим.

Это его не останавливает. от того, чтобы быть чрезвычайно полезным сегодня. Если вы попробуете, я буду рад получить известие от вас! Я также очень хочу, чтобы люди начали расширять его для своих собственных проектов, особенно через механизм настраиваемых виджетов .

Дай мне знать, если попробуешь!

Leave a comment

Your email address will not be published. Required fields are marked *

14 + 8 =