DevGang
Авторизоваться

Преобразование JSON в форму с помощью SQLite

Очевидно, что большая часть мира решила, что они хотят использовать JSON для своих общедоступных конечных точек API. Однако большую часть времени вам придется иметь дело с механизмами хранения, которые не очень хорошо работают с JSON. Это может привести к путанице, потому что вам нужно вставить квадратный колышек в круглое отверстие.

Однако в SQLite добавлены функции JSON, позволяющие вам изменять и изменять данные JSON любым творческим способом. Вы можете использовать эти триггеры и триггеры SQLite для автоматического преобразования JSON в таблицы любого типа, которые вы хотите. Добавьте дополнительные настройки, и вы сможете сделать все еще более автоматизированным. Эта поддержка была добавлена в SQLite 3.9.0 (выпущена в 2015 году), поэтому, если предположить, что Debian не отключил ее без уважительной причины, вы сможете использовать ее сегодня.

В этом примере мы собираемся запрашивать общедоступные конечные точки JSONFeed и превращать их в таблицы SQL. Начнем со схемы таблицы, которая выглядит так:

CREATE TABLE IF NOT EXISTS jsonfeed_raw
  ( feed_url     TEXT  PRIMARY KEY
  , scrape_date  TEXT  NOT NULL  DEFAULT (DATE('now'))
  , raw          TEXT  NOT NULL
  );

scrape date — это, по сути, дата, когда строка JSONFeed была вставлена ​​в базу данных. Это может быть полезно при написании других частей стека для автоматического запроса фидов на наличие изменений. Это оставлено читателю в качестве упражнения.

Затем вы можете вставлять данные в базу данных SQLite, используя модуль Python sqlite3:

#!/usr/bin/env nix-shell
#! nix-shell -p python39 --run python

import sqlite3
import urllib.request

con = sqlite3.connect("data.db")

def get_feed(feed_url):
    req = urllib.request.Request(feed_url, headers={"User-Agent": "Xe/feedfetch"})
    with urllib.request.urlopen(req) as response:
        cur = con.cursor()
        body = response.read()
        cur.execute("""
           INSERT INTO jsonfeed_raw
             (feed_url, raw)
           VALUES
             (?, json(?))
        """, (feed_url, body))
        con.commit()
        print("got feed %s" % (feed_url))

get_feed("https://christine.website/blog.json")

Итак, теперь давайте поиграем с данными! Давайте загрузим схему базы данных с помощью команды sqlite3:

$ sqlite3 data.db < schema.sql

Символ "меньше" является перенаправлением, он загружает данные из schema.sql в качестве стандартного ввода в команду sqlite.

Затем запустите этот скрипт Python для заполнения базы данных:

$ python ./jsonfeedfetch.py
got feed https://christine.website/blog.json

Затем откройте командную строку SQLite:

$ sqlite3 data.db
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite>

А теперь мы можем поиграть с некоторыми функциями JSON. Сначала продемонстрируем json_extract. Это позволяет вам извлечь значение из объекта JSON. Например, давайте возьмем заголовок фида из JSONFeed моего сайта:

sqlite> select json_extract(raw, '$.title') from jsonfeed_raw;
Xe's Blog

Мы можем использовать эту функцию, чтобы помочь нам создать таблицу, в которой хранятся метаданные, которые нам нужны, из JSONFeed, например:

CREATE TABLE IF NOT EXISTS jsonfeed_metadata
  ( feed_url      TEXT  PRIMARY KEY
  , title         TEXT  NOT NULL
  , description   TEXT
  , home_page_url TEXT
  , updated_at    TEXT  NOT NULL  DEFAULT (DATE('now'))
  );

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

Затем мы можем заполнить эту таблицу запросом, подобным этому:

INSERT INTO jsonfeed_metadata
            ( feed_url
            , title
            , description
            , home_page_url
            , updated_at
            )
SELECT jsonfeed_raw.feed_url AS feed_url
     , json_extract(jsonfeed_raw.raw, '$.title') AS title
     , json_extract(jsonfeed_raw.raw, '$.description') AS description
     , json_extract(jsonfeed_raw.raw, '$.home_page_url') AS home_page_url
     , DATE('now') AS updated_at
FROM jsonfeed_raw;

Ключевое слово AS позволяет связать значения в операторе SELECT с именами для использования в другом месте запроса.Я не знаю, нужно ли это *strictly*, однако это заставляет имена выстраиваться в линию, и SQLite не жалуется на это, так что, вероятно, все в порядке.

Теперь это работает, однако знаете, что проще, чем писать операторы в консоли SQLite вот так? Триггеры SQLite позволяют нам автоматически запускать операторы базы данных при возникновении определенных условий. Основное условие, о котором мы хотим позаботиться прямо сейчас, — это когда мы вставляем новые данные. Мы можем превратить этот оператор в триггер после вставки следующим образом:

CREATE TRIGGER IF NOT EXISTS jsonfeed_raw_ins
  AFTER INSERT ON jsonfeed_raw
  BEGIN
    INSERT INTO jsonfeed_metadata
                ( feed_url
                , title
                , description
                , home_page_url
                )
    VALUES ( NEW.feed_url
           , json_extract(NEW.raw, '$.title')
           , json_extract(NEW.raw, '$.description')
           , json_extract(NEW.raw, '$.home_page_url')
           );
  END;

Затем мы можем запустить несколько команд, чтобы уничтожить все состояние базы данных:

sqlite3> DELETE FROM jsonfeed_metadata;
sqlite3> DELETE FROM jsonfeed_raw;

И снова запустите этот скрипт Python, после чего данные должны появиться автоматически:

sqlite3> SELECT * FROM jsonfeed_metadata;
https://christine.website/blog.json|Xe's Blog|My blog posts and rants about various technology things.|https://christine.website|2022-01-04

Это как волшебство!

Однако, если вы снова запустите этот скрипт Python, не удаляя строки, вы получите нарушение первичного ключа. Мы можем исправить это, превратив вставку в upsert примерно так:

cur.execute("""
    INSERT INTO jsonfeed_raw
      (feed_url, raw)
    VALUES
      (?, json(?))
    ON CONFLICT DO
      UPDATE SET raw = json(?)
""", (feed_url, body, body))

А также сделайте дополнительный триггер обновления для таблицы jsonfeed_raw:

CREATE TRIGGER IF NOT EXISTS jsonfeed_raw_upd
  AFTER UPDATE ON jsonfeed_raw
  BEGIN
    INSERT INTO jsonfeed_metadata
                ( feed_url
                , title
                , description
                , home_page_url
                )
    VALUES ( NEW.feed_url
            , json_extract(NEW.raw, '$.title')
            , json_extract(NEW.raw, '$.description')
            , json_extract(NEW.raw, '$.home_page_url')
            )
    ON CONFLICT DO
       UPDATE SET
             title         = json_extract(NEW.raw, '$.title')
           , description   = json_extract(NEW.raw, '$.description')
           , home_page_url = json_extract(NEW.raw, '$.home_page_url')

Вероятно, вам следует обновить исходный триггер, чтобы он тоже был upsert. Вы можете следовать этому триггеру в качестве руководства. Но обязательно DROP TRIGGER jsonfeed_raw_upd; cначала!

Мы также можем очистить элементы фида с помощью json_eachjson_each позволяет перебирать массив JSON и возвращает строки SQLite для каждого значения в этом массиве. Например:

sqlite> select * from json_each('["foo", "bar"]');
0|foo|text|foo|1||$[0]|$
1|bar|text|bar|2||$[1]|$

Схему для временной таблицы, которую json_each(и связанную с ней json_tree) использует можно найти здесь. Вы также можете получить что-то из списка в объекте со вторым аргументом json_each, так что вы можете делать такие вещи:

sqlite> select * from json_each('{"spam": ["foo", "bar"]}', '$.spam');
0|foo|text|foo|3||$.spam[0]|$.spam
1|bar|text|bar|4||$.spam[1]|$.spam

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

CREATE TABLE IF NOT EXISTS jsonfeed_posts
  ( url             TEXT  PRIMARY KEY
  , feed_url        TEXT  NOT NULL
  , title           TEXT  NOT NULL
  , date_published  TEXT  NOT NULL
  );

А затем удалить все данные из базы данных с помощью такого запроса:

INSERT INTO jsonfeed_posts
            ( url
            , feed_url
            , title
            , date_published
            )
SELECT
  json_extract(json_each.value, '$.url') AS url
, jsonfeed_raw.feed_url AS feed_url
, json_extract(json_each.value, '$.title') AS title
, json_extract(json_each.value, '$.date_published') AS date_published
FROM
  jsonfeed_raw
, json_each(jsonfeed_raw.raw, '$.items');

Это извлечет все значения поля items в каждом JSONFeed, а затем автоматически заполнит их в таблице jsonfeed_posts. Однако превращение этого в триггер при наивном подходе не сработает мгновенно.

Допустим, у нас есть форма триггера, которая выглядит так:

CREATE TRIGGER IF NOT EXISTS jsonfeed_raw_upd_posts
  AFTER INSERT ON jsonfeed_raw
  BEGIN
    INSERT INTO jsonfeed_posts
                ( url
                , feed_url
                , title
                , date_published
                )
    SELECT
        json_extract(json_each.value, '$.url') AS url
      , NEW.feed_url AS feed_url
      , json_extract(json_each.value, '$.title') AS title
      , json_extract(json_each.value, '$.date_published') AS date_published
    FROM json_each(NEW.raw, '$.items')
    ON CONFLICT DO
      UPDATE SET title = excluded.title
               , date_published = excluded.date_published
               ;
  END;

Если вы вставите это в свою консоль SQLite, вы получите эту ошибку:

Error: near "DO": syntax error

На самом деле это связано с неоднозначностью синтаксического анализа в SQLite. Для того, чтобы исправить это вам нужно будет добавить WHERE TRUE между пунктами триггера FROM и ON CONFLICT:

-- ...
FROM json_each(NEW.raw, '$.items')
WHERE TRUE
ON CONFLICT DO
-- ...

Таким образом день спасает wheretrue, скрытый высший хищник царства SQLite, роковое значение, которое не является ложным только ночью. Плачьте от ужаса, как бы он не добавил вас к своей таблице жертв!

Коррелирующее изменение триггера вставки также является упражнением для читателя.

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

Источник:

#JavaScript #Python #SQL
Комментарии
Чтобы оставить комментарий, необходимо авторизоваться

Присоединяйся в тусовку

Поделитесь своим опытом, расскажите о новом инструменте, библиотеке или фреймворке. Для этого не обязательно становится постоянным автором.

Попробовать

В подарок 100$ на счет при регистрации

Получить